Window

Applies a pipeline to segments of rows, producing one output value for every input value.

For each row, the segment over which the pipeline is applied is determined by one of:

  • rows, which takes a range of rows relative to the current row position.
  • 0 references the current row.
  • range, which takes a range of values relative to current row value.

The bounds of the range are inclusive. If a bound is omitted, the segment will extend until the edge of the table or group.

For ease of use, there are two flags that override rows or range:

  • expanding:true is an alias for rows:..0. A sum using this window is also known as “cumulative sum”.
  • rolling:n is an alias for row:(-n+1)..0, where n is an integer. This will include n last values, including current row. An average using this window is also knows as a Simple Moving Average.

Some examples:

Expression Meaning
rows:0..2 current row plus two following
rows:-2..0 two preceding rows plus current row
rolling:3 (same as previous)
rows:-2..4 two preceding rows plus current row plus four following rows
rows:..0 all rows from the start of the table up to & including current row
expanding:true (same as previous)
rows:0.. current row and all following rows until the end of the table
rows:.. all rows, which same as not having window at all

Example

PRQL

1. from employees
2. group employee_id (
3. sort month
4. window rolling:12 (
5. derive [trail_12_m_comp = sum paycheck]
6. )
7. )

SQL

1. SELECT
2. *,
3. SUM(paycheck) OVER (
4. PARTITION BY employee_id
5. ORDER BY
6. month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
7. ) AS trail_12_m_comp
8. FROM
9. employees

PRQL

1. from orders
2. sort day
3. window rows:-3..3 (
4. derive [centered_weekly_average = average value]
5. )
6. group [order_month] (
7. sort day
8. window expanding:true (
9. derive [monthly_running_total = sum value]
10. )
11. )

SQL

1. SELECT
2. *,
3. AVG(value) OVER (
4. ORDER BY
5. day ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
6. ) AS centered_weekly_average,
7. SUM(value) OVER (
8. PARTITION BY order_month
9. ORDER BY
10. day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
11. ) AS monthly_running_total
12. FROM
13. orders

Windowing by default

If you use window functions without window transform, they will be applied to the whole table. Unlike in SQL, they will remain window functions and will not trigger aggregation.

PRQL

1. from employees
2. sort age
3. derive rnk = rank

SQL

1. SELECT
2. *,
3. RANK() OVER (
4. ORDER BY
5. age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
6. ) AS rnk
7. FROM
8. employees
9. ORDER BY
10. age

You can also only apply group:

PRQL

1. from employees
2. group department (
3. sort age
4. derive rnk = rank
5. )

SQL

1. SELECT
2. *,
3. RANK() OVER (
4. PARTITION BY department
5. ORDER BY
6. age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
7. ) AS rnk
8. FROM
9. employees

Window functions as first class citizens

There is no limitaions where windowed expressions can be used:

PRQL

1. from employees
2. filter salary < (average salary)

SQL

1. WITH table_1 AS (
2. SELECT
3. *,
4. AVG(salary) OVER () AS _expr_0
5. FROM
6. employees
7. )
8. SELECT
9. *
10. FROM
11. table_1 AS table_0
12. WHERE
13. salary < _expr_0