SQL Window Functions vs GROUP BY, Made Simple
Zusammenfassung
Pixel shares a beginner-friendly guide to SQL window functions and how PARTITION BY differs from GROUP BY: grouping rows while preserving each original row. Examples use SUM to calculate per-name sales totals and ratios, plus ROW_NUMBER to rank sales within each group. This helps community members write queries that retain detail while adding useful calculations.
DEV Community
Window Functions vs GROUP BY, Explained the Super Simple Way
What is a window function? A window function is any function used with OVER (...). SUM(amount) OVER (PARTITION BY name)
The whole SUM(amount) OVER (...) is the window function PARTITION BY goes inside it and says which groups to calculate over PARTITION BY is optional. If you leave it out, the whole table is treated as one group. Note: "Table partitioning" (CREATE TABLE ... PARTITION BY), which physically splits a table, uses the same keyword but is unrelated to window functions. GROUP BY vs PARTITION BY The short answer: this is the only difference. GROUP BY: splits rows into groups and collapses each group into one row PARTITION BY: splits rows into groups but keeps every row (each row gets the calculated value) Sample table sales table name amount Alice 100 Alice 200 Bob 500 With GROUP BY SELECT name, SUM(amount)
FROM sales
GROUP BY name;
name sum Alice 300 Bob 500 Each name is collapsed into one row. The original "100" and "200" rows are gone. With PARTITION BY SELECT name, amount, SUM(amount) OVER (PARTITION BY name) AS total
FROM sales;
name amount total Alice 100 300 Alice 200 300 Bob 500 500 You still have 3 rows, and each row gets the total for its name. Why is this useful? Because the rows are kept, you can use the original value and the total in the same row. For example, you can get "what share of this person's sales is this one sale" in a single query. SELECT name, amount, amount / SUM(amount) OVER (PARTITION BY name) AS ratio
FROM sales;
name amount ratio Alice 100 0.33 Alice 200 0.67 Bob 500 1.00 ROW_NUMBER: numbering rows within each group ROW_NUMBER() is a window function that numbers rows 1, 2, 3… within each group. Adding ORDER BY inside OVER (...) sets the order within each group. SELECT name, amount,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num
FROM sales;
name amount row_num Alice 200 1 Alice 100 2 Bob 500 1 Each name's rows are numbered from the largest amount down. Common pattern: get the top row of each group Filter on row_num = 1 to get each person's highest sale. SELECT * FROM (
SELECT name, amount,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num
FROM sales
) t
WHERE row_num = 1;
name amount row_num Alice 200 1 Bob 500 1 row_num is created in the SELECT, so you can't use it in the WHERE of the same query. That's why it's wrapped in a subquery first. ROW_NUMBER vs RANK They differ in how they number ties. amount ROW_NUMBER RANK 500 1 1 500 2 1 300 3 3 ROW_NUMBER: always gives a different number, even for ties (1, 2, 3) RANK: gives ties the same number, then skips ahead (1, 1, 3)

Powered by Readybot.io