Window

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

window rows:(range) range:(range) expanding:false rolling:0 (pipeline)

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:

Example

PRQL

from employees
group employee_id (
  sort month
  window rolling:12 (
    derive {trail_12_m_comp = sum paycheck}
  )
)

SQL

SELECT
  *,
  SUM(paycheck) OVER (
    PARTITION BY employee_id
    ORDER BY
      month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS trail_12_m_comp
FROM
  employees

PRQL

from orders
sort day
window rows:-3..3 (
  derive {centered_weekly_average = average value}
)
group {order_month} (
  sort day
  window expanding:true (
    derive {monthly_running_total = sum value}
  )
)

SQL

SELECT
  *,
  AVG(value) OVER (
    ORDER BY
      day ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_weekly_average,
  SUM(value) OVER (
    PARTITION BY order_month
    ORDER BY
      day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS monthly_running_total
FROM
  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

from employees
sort age
derive {rnk = rank age}

SQL

SELECT
  *,
  RANK() OVER (
    ORDER BY
      age
  ) AS rnk
FROM
  employees
ORDER BY
  age

You can also only apply group:

PRQL

from employees
group department (
  sort age
  derive {rnk = rank age}
)

SQL

SELECT
  *,
  RANK() OVER (
    PARTITION BY department
    ORDER BY
      age
  ) AS rnk
FROM
  employees

Window functions as first class citizens

There are no limitations on where windowed expressions can be used:

PRQL

from employees
filter salary < (average salary)

SQL

WITH table_0 AS (
  SELECT
    *,
    AVG(salary) OVER () AS _expr_0
  FROM
    employees
)
SELECT
  *
FROM
  table_0
WHERE
  salary < _expr_0