Scribd
Upload
3 views3 pages

Window Functions Notes

Window functions allow calculations without collapsing rows, unlike GROUP BY. They utilize ORDER BY to control the sequence of calculations and PARTITION BY to create separate groups for those calculations. A practical example is classroom ranking, where ORDER BY ranks students by marks and PARTITION BY provides separate rankings for each section.

Uploaded by

aggarwalmadhav001
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views3 pages

Window Functions Notes

Window functions allow calculations without collapsing rows, unlike GROUP BY. They utilize ORDER BY to control the sequence of calculations and PARTITION BY to create separate groups for those calculations. A practical example is classroom ranking, where ORDER BY ranks students by marks and PARTITION BY provides separate rankings for each section.

Uploaded by

aggarwalmadhav001
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

WINDOW FUNCTIONS NOTES

1. MAIN PURPOSE OF WINDOW FUNCTIONS


----------------------------------
Window functions perform calculations
WITHOUT collapsing rows.

GROUP BY → collapses rows


WINDOW FUNCTIONS → keep rows intact

2. ORDER BY IN WINDOW FUNCTIONS


--------------------------------
Purpose:
Controls calculation order/ranking order.

Meaning:
“In what order should calculation happen?”

Example:
RANK() OVER(ORDER BY sales DESC)

Meaning:
Rank highest sales first.

3. PARTITION BY IN WINDOW FUNCTIONS


-----------------------------------
Purpose:
Creates separate groups/windows for calculation.

Meaning:
“Inside which groups should calculation happen separately?”

Example:
RANK() OVER(
PARTITION BY region
ORDER BY sales DESC
)

Meaning:
Ranking restarts separately inside each region.

4. SIMPLE DIFFERENCE
--------------------
ORDER BY
→ calculation sequence/order

PARTITION BY
→ separate groups/windows

5. EASY MEMORY TRICK


--------------------
PARTITION BY
= divide into groups

ORDER BY
= arrange order inside groups

6. REAL-LIFE EXAMPLE
--------------------
Classroom Ranking:

ORDER BY marks DESC


→ highest marks gets rank 1

PARTITION BY section
→ each section gets separate ranking

You might also like