Skip to content

Windowing

Windowing is a process that allows us to smooth out random variations. A good example is a moving average style of windowing.

SELECT sale_date, SUM(value) OVER
    (ORDER BY sale_date BETWEEN rows 1 preceding and 1 following)
FROM sales

Alternative windowing style

  • between rows unbounded preceding and current
  • rows unbounded preceding
  • range between between 10 preceding and current row
  • range interval 10 day preceding

Windowing can also be done within partitions

SELECT account_number, date_time,
    SUM(value) OVER
        (PARTITION BY account_number
        ORDER BY date_time
        rows unbounded preceding)
AS balance
FROM transaction
ORDER BY account_number, date_time