Database.Middle.WindowFunctions.Explain ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Great question! The clause:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

is used within a window function to define the range of rows (called the window frame) that the function operates on relative to the current row.

---

🔍 What It Means

• UNBOUNDED PRECEDING: Start from the first row in the partition (group)
• CURRENT ROW: End at the current row (inclusive)

So you’re saying:

“Include all rows from the beginning of the group up to and including this one.”

✅ Used For: Running Totals, Running Averages, Running Max/Min

🧾 Example

SELECT
  employee_id,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (
    PARTITION BY employee_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

This computes a running total for each employee’s sales in order of sale_date.

---

📊 Visualization

Let’s say we have this:

employee_id	sale_date	sale_amount
101	2024-06-01	100
101	2024-06-02	150
101	2024-06-03	200

Here’s what each row sees:

Current Row	Rows in Frame	Running SUM
Row 1	[100]	100
Row 2	[100, 150]	250
Row 3	[100, 150, 200]	450

---

🔁 Variants

Frame Clause	What It Includes
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	All rows up to and including the current row
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING	All rows from current to end
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING	A moving window of 3 rows (prev, current, next)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	The entire partition

---

🧠 Summary

Clause Part	Meaning
ROWS	Operate on physical rows, not values
UNBOUNDED PRECEDING	Start from first row in group
CURRENT ROW	End at current row
Result	Rolling calculation up to this row