Chapters
SQL Language
Window Functions
Chapters
Create A Practice Database in SSMS
To install MS SSMS visit the installation page for details on downloading and installation
Insert Records Into Newly Created Tables
Run the below query to create the database we would be working on: Run the below provided query to create the table we would be working on:
1) GroupBy
When using Group By() for a column, for each unique entry in that column, the aggregate
function is applied
Pay attention, to use Group By, we need to use Aggregate function for rest of the columns
mentioned in the SELECT statement
But what if, we needed to display the whole table, i.e. SELECT * FROM Table_Name, and
along with it, wanted to apply aggregate function
We dont wish to Group By at any column, we wish to display the whole table, and still,
want to make use of Aggregate Function
Here, OVER() comes to our rescue
Window functions
Before begining, a great introduction about window functions:
But what good is that newly created column, if it repeats just 1 value throughout?
Thus, lets create more windows, i.e. lets Partion the window. Assume, we want to create
separate windows for each state, then what we need is a window which has separate Partion for
each State: Partion By(State)
A good video about Partitions:
More vides from the same playlistBut, what about a situation where we don't have to group at a level, but perform calculation at a level; for various windows insisde a table?
One common use case of such a situation is, Calcualtion of Running Total or Average
Running total is, cumulative sum of all previous row
State Level Running Total
Preceding and Following
Row Number, Rank & Dense Rank
Cume Dist & Percent Rank
- percent_rank returns a number from 1 to 0. The highest being 1 and the lowest 0
- cume_dist will return a number from 1 towards 0 but never 0
Grouping
Lag and Lead: These functions allow you to examine the next or previous row, may then
compare this value to the current row
Unbounded Preceding
nth_value: Gives the nth-value, after the current row
nth_value will return the nth_value, but if we do not specify a range it will
return null if the current value is less than the nth. If we always want something displayed
we need to specify a range
ntile: ntile(n) divides the group into n equal partitions and denotes which partition
each row is in
Credits: windowfunctions.com