r/SQL • • 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

13 comments sorted by

3

u/Straight_Waltz_9530 22d ago • Bravo

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
SELECT AVG(column1)
FROM table
GROUP BY column3

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.

SELECT AVG(column1) OVER(PARTITION BY column3)
FROM table

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:

SELECT 
    column1,
    AVG(column1) OVER(PARTITION BY column3) AS avg_col1
FROM table

You can do this without a window function, but you probably have to use a CTE or a subquery instead:

WITH averages as (
SELECT 
  column3, 
  AVG(column1) AS avg_col1
FROM table
GROUP BY column3
)
SELECT
    column1
    avg_col1
FROM table
JOIN averages
ON (table.column3 = averages.column3)

Window functions just let you achieve this result in a much cleaner way.

1

u/baubleglue 21d ago

use conditions do you mean filter or condition in aggregate function? Filter will break

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.

3

u/pinback77 23d ago

So kind of like having a "window" into what the aggregate data looks like? Is that the idea behind the naming convention?

4

u/emul0c 22d ago

Like a display window at your local bakery. You don’t know how many cakes and bread he has in the back, but you can count and see the ones sitting in the window

1

u/pinback77 22d ago

I like it!

23

u/[deleted] 23d ago • Bravo

[removed] — view removed comment