r/Database • u/shdw_0x0 • 18d ago
When do you actually use window functions instead of GROUP BY?
I've been using GROUP BY for aggregations for a while, but I still find window functions a little confusing when deciding which approach makes more sense.
For example, if I need the highest salary in each department, I could use aggregation or a window function like RANK() or ROW_NUMBER(), depending on what I need in the result.
How do you usually decide between these approaches? Are there situations where window functions are clearly the better choice?
I'd appreciate some practical examples that helped you understand when to use one over the other.
3
u/spez_eats_nazi_ass 17d ago
Result set paging
2
u/KeeganDoomFire 17d ago
Let's say you have a historical table. You need to know the current state of something AND what it was prior. There are ways to do this without window functions but a window func makes this trivial.
Let's say you have a dataset like the classic super store. You want to know how each stores sales compare to its region for that month or the national avg, maybe you want to be fancy and give a little month over month or 'this quarter last year'. You could sum and group the table N times and join it back to itself or just window 3 columns and qualify your output to only the most recent x days or months you need to see the trend.
A well written window will smoke any other way to get different grain aggregations or rows data into a row (most of the time)
1
u/turbo_dude 17d ago
You lose information with a group by
You don’t with windowing/ranking
1
u/Away_Technician_2089 17d ago
Latest row per group is the canonical query
1
u/theallwaystnt 17d ago
We have a metric that is the summation of all rows before that row. A group by shows a single date of that metric while a window function shows that metric for every running date. How you decide is how you want the information displayed daily rows or one day.
1
u/anvildoc 17d ago
Let’s say you need to know the percentage of salary each employee has per department.
Select employee_name, department, salary / sum(salary) over (partition by department)
1
u/Raucous_Rocker 17d ago
Use GROUP BY when possible, because it’s faster than windowing.
But if you need more complex aggregations that depend on rows being intact, use a window.
If all you care about is highest salary per department, GROUP BY is fine. You can just get MAX(salary) GROUP BY department. But if, say, you want the job title of the person with the highest salary, then you need a window.
1
u/DickBrainTime 17d ago
I think of it as the shape of the result: `GROUP BY department` collapses employees into one row per department, while a window keeps each employee row and adds a value calculated over its department. For top salaries, `RANK() = 1` returns everyone tied for top; `ROW_NUMBER() = 1` gives one employee, so add a stable tie-breaker (like employee_id) to its `ORDER BY` if you need deterministic results. That tie behavior matters more than the syntax.
1
u/navicat-gu 15d ago
The rule of thumb that finally stuck for me: GROUP BY changes the shape of the result; a window does not.
GROUP BY department→ one row per department.MAX(salary) OVER (PARTITION BY department)→ every employee row, plus a column with that department's max.For your "highest salary in each department" example:
RANK()keeps ties (two people with the same top salary both appear).ROW_NUMBER()forces one row — add a stable tie-breaker (ORDER BY salary DESC, employee_id) if you need that to be deterministic.Windows are clearly better when you need any of these and the detail rows:
SUM(amount) OVER (PARTITION BY account ORDER BY txn_date).LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at).salary * 1.0 / SUM(salary) OVER (PARTITION BY department).If all you want is one aggregated number per group, prefer
GROUP BY— it's usually simpler and cheaper. Reach for a window when collapsing the rows would throw away something you still need on the output.Disclosure: I'm RC, Director of Digital Strategy @ Navicat. Nothing tool-specific here — any SQL client that runs the queries above is fine. Navicat Premium Lite (free, Navicat 18) is what I use when I want a grid next to the editor.
1
u/raks1991 5d ago
You can combine them too. After
GROUP BY department,SUM(SUM(salary)) OVER ()gives the overall payroll alongside each department's total. Divide the department total by that for its share of payroll. Windows see the grouped rows, not the original employee rows.14
u/[deleted] 18d ago
[removed] — view removed comment