What are window functions and how do they differ from GROUP BY?
What are window functions and how do they differ from GROUP BY?
A window function computes a value for each row using a set of related rows (its window) without collapsing rows. GROUP BY returns one row per group; a window function keeps every input row and adds a calculated column.
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
| Part | Meaning |
|---|---|
PARTITION BY |
splits rows into independent groups (omit it for one global window) |
ORDER BY |
orders rows inside each partition; required for ranking and running calculations |
frame (ROWS / RANGE / GROUPS) |
which rows around the current one are included |
Đă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.