The Power Of Window Functions

Kevin Feasel

2018-02-22

Syntax

Ben Richardson has an introduction to the concept of window functions:

This is a much more efficient result. In the first line of the script the id, name and gender columns are retrieved. These columns do not contain any aggregated results.

Next, for the columns that contain aggregated results, we simply specify the aggregated function, followed by the OVER clause and then within the parenthesis we specify the PARTITION BY clause followed by the name of the column that we want our results to be partitioned as shown below.

This post focuses on normal aggregates which accept windows.  Once you understand that, there’s a wide world beyond it, and you’ll quickly see how useful window functions can be.

Related Posts

Finding The Last Non-Null Value With Snowflake

Koen Verbeeck shows how two words makes solving a problem with Snowflake a lot easier than with SQL Server: Sometimes you need to find the previous value in a column. Easy enough, the LAG window function makes this a breeze (available since SQL Server 2012). But what if the previous value cannot be null? You […]

Read More

Concerns With DISTINCT

Anvesh Patel does not like DISTINCT: I am telling you personally that I hate the use of DISTINCT.DISTINCT used by those people, who are not sure about their data set or SELECT statement or JOINS.Whenever I get any query with DISTINCT, immediately I suggest to remove it. I agree with this sentiment about 85% of […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728