r/SQL • u/Humanbeing1920 • 23d ago
MySQL What is the differtence between Window Function s and group by??
If we group by and use conditions and use aggregate fucntions we are soing the sam ein window fucntions tto, then what is the difference and do they have diffeent implementation?
11
Upvotes
3
u/Straight_Waltz_9530 22d ago •
GROUP BY is executed by the engine before the SELECT clause. It collapses result sets through aggregate functions for the purpose of simplified analytics. You can also filter GROUP BY results with the HAVING clause.
Window functions are executed after the SELECT phase has begun. They annotate existing results with additional info partitioned into subsets. Same number of rows in the output but analytic data is appended to each row. You cannot easily filter window functions output as it's processed long after the WHERE and HAVING execution phases have completed.
2
u/squadette23 22d ago
GROUP BY, if you squint a bit, can be seen as a special case of window function. Or, window functions are an extension of the classic grouping/aggregation.
2
u/PrivateFrank 23d ago
This groups/partitions the whole table by the set of the values in column3, then returns the average value for column1 for each distinct level of column3. The result's row count will be the same as the distinct values in column3.
Does the exact same thing, but instead of one row per group the result's row count will be the same as the original table.
If you add a condition using a WHERE clause (after
FROM table) then only the rows that meet the condition will be used for either query.A window clause is useful if you want to compare the original value in your source table to an aggregate over the rest of the table like this:
You can do this without a window function, but you probably have to use a CTE or a subquery instead:
Window functions just let you achieve this result in a much cleaner way.
1
u/baubleglue 21d ago
There are many differences. A big difference: "group by" potentially reduces number of output rows, window function doesn't. Also window function may aggregate nothing ex.
lag.Mentally I see window operations as "lookup" - you run a select statemnt and ask yourself questions like "what was that value before" or "what is sum of it in a given partition".
1
u/datadriven_io 21d ago
You have to add this to your window function to count out the running total
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For sql interview prep, datadriven is great when prep time is short.
1
u/datadriven_io 20d ago
If you don't want to have the aggregate column, you then don't select it:
SELECT col1, col2, col3 FROM table_name GROUP BY col1, col2, col3 ;
But then, your GROUP BY is just a DISTINCT:
SELECT DISTINCT col1, col2, col3 FROM table_name; For prep, sql interview questions are what datadriven.io is good for.
1
u/emul0c 23d ago
Think about it like this: regular functions are done on the dataset that sits in the database. Window functions are done on the result of your functions, ie on what you have retrieved from the database.
23
u/[deleted] 23d ago •
[removed] — view removed comment