How do LAG, LEAD, running totals and window frames (ROWS vs RANGE) work?
How do LAG, LEAD, running totals and window frames (ROWS vs RANGE) work?
LAG and LEAD read a value from a row before or after the current one. Running totals and moving averages use an aggregate with a frame that defines which neighbouring rows are included.
SELECT day, revenue,
LAG(revenue) OVER (ORDER BY day) AS prev_revenue,
LEAD(revenue, 1, 0) OVER (ORDER BY day) AS next_revenue, -- offset 1, default 0
revenue - LAG(revenue) OVER (ORDER BY day) AS change
FROM daily_sales;
The first row has no previous row, so LAG returns NULL unless you give a default.
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7
FROM daily_sales;
| Frame | Counts by | Behaviour with ties |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.