Advertisements
Home > SQL Server 2012 > Enhanced OVER() Clause in SQL Server 2012 – Part2

Enhanced OVER() Clause in SQL Server 2012 – Part2


First of all I would like to wish my readers a very Happy and Prosperous New Year – 2013!!!

This is my 1st post in this new year and I will try to come up with more informative and interesting topics related to SQL Server.

Coming back to the topic, sometime back in Part-1 [link] I discussed about how to use both PARTITION BY & ORDER BY clause within OVER() clause when using it with AGGREGATE functions, like SUM().

Here in this post we will see other features that are added to the OVER() clause.

ROWS/RANGE are new the 2 new clauses that you can use inside the OVER() clause to limit the records within the Partition by specifying start and end within the partition. They use a new set of keywords to define the boundaries within the partition, i.e. PREECEDING, FOLLOWING mixed with UNBOUNDED or CURRENT, or number of rows. The PRECEDING and FOLLOWING rows are defined based on the ordering in the ORDER BY clause of the query.

#1. Using ROWS/RANGE UNBOUNDED PRECEDING with OVER() clause:
Let’s check with 1st example where I want to calculate Cumulative Totals or Running Totals at each row. This total is calculated by the SUM of current and all previous rows.
We will use UNBOUND PRECEDING option here, which means that the window starts from the 1st row till the current row.

USE [AdventureWorks2012]
GO

-- To Calculate Cumulative SUM or Running Totals:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS UNBOUNDED PRECEDING) AS CumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE UNBOUNDED PRECEDING) AS CumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_1

In the Output above you can see the was the SUM is calculated, as we go down all previous and current rows are getting summed at all levels.
Here you can also see a difference between the way ROW & RANGE are calculated, for SalesPersonID=4:
– ROWS shows different and continuous calculation at different levels.
– But RANGE shows same calculation of all 3 records at different levels.


#2. Using ROWS/RANGE CURRENT ROW & UNBOUNDED FOLLOWING with OVER() clause:
Now, let’s say you’ve to do the same calculation, but in reverse order. So, here we will use BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING options to define our Window. This means the window starts from current row and ends at the last row.

-- To Calculate Cumulative SUM or Running Totals, but in REVERSE order:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRows
   ,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_2

In the Output above we can see that the Running totals are now in reverse order, compared to the 1st example.
Also, the ROWS & RANGE difference can be seen for the SalesPersonID=4 similar to the 1st example.


#3. Using ROWS/RANGE PRECEDING & FOLLOWING with OVER() clause, without UNBOUNDED:
Now, if you want to calculate the SUM only of those values that are just adjacent to a current row. Here we will use combination of row numbers with CURRENT/PRECEDING & FOLLOWING keywords.
Let’s us check this by following example:

-- To Calculate Moving SUM, by taking 3 moving rows:
;WITH CTE AS (
	SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
	FROM [HumanResources].[EmployeePayHistory]
	WHERE BusinessEntityID <= 10
	)
SELECT SalesPersonID, SalDate, Salary
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSumByRowsPrevCurrentNext
   ,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS MovingSumByRowsCurrentNext2
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:
SQL2012_OVER_Enhanced_Clause_2_3

In the output above for:
– Column MovingSumByRowsPrevCurrentNext: it calculates the SUM of just one Previous, Current & one Next row.
– Column MovingSumByRowsCurrentNext2: it calculates the SUM of Current and next two rows.


So, by above 3 examples we saw how to use new ROWS & RANGE clauses with a mix of UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, etc. Similarly there could be many different cases and scenarios where we can use there clauses and options.

I will try to post more on these features as I see anything new and exciting around this.

For more info on OVER() Clause check MS BOL: http://msdn.microsoft.com/en-us/library/ms189461.aspx

Advertisements
  1. December 16, 2016 at 12:09 pm

    Nice article. It was very helpful! Thanks for sharing such a nice article!

  2. Scott
    October 18, 2016 at 5:44 am

    Thanks for the example showing the difference between range and rows

  3. April 16, 2015 at 1:47 am

    If you did ORDER BY SalesPersonID,SalDate,Salary and if the table had the following values for rows with SalesPersonID=4 ( same date and id for 2 of the rows), then how would ‘RANGE’ aggregate the running total values as? – at what levels will it sum up

    SalesPersonID SalDate Salary
    4 2001-12-22:00:00:00.000 9
    4 2001-12-22:00:00:00.000 24 –same date as prev row
    4 2006-01-01:00:00:00.000 30

    My question may not be making sense from a logical point of view in a realistic environment but i wanted to understand what happens when you order by 3 columns where 2 of them have same values in more than 1 row.
    I am not able to try it out myself. Hence this question to you. Please do clarify.

    Divya

  4. March 18, 2014 at 10:00 pm

    Excellent post

  5. Sandip Davari
    January 9, 2014 at 4:46 pm

    SELECT RG23ID,InvDate ,
    SUM(ISNUll(OpCENVAT,0)) OVER (PARTITION BY RG23ID ORDER BY InvDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM RG23Register

    This is my Query. But as per you say it does not give the proper result.Instead of that it gives an syntax error.
    And Is it also possible to add two column value in cumulative Total.

    • January 9, 2014 at 6:00 pm

      Sandip, are you on SQL Server 2012 or older version. This query will only work in SQL 2012 and above. b.t.w. your query looks OK to me.

      ~manoj

  6. April 13, 2013 at 8:55 pm

    very useful post …

  7. February 5, 2013 at 4:31 pm

    very nice explanation of new features in SQL 2012

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: