This post explains couple of scenarios where new analytic and aggregation windowing functions of sql 2012 can be effectively used to achieve functionality as compared to sql 2008.
In one of my previous posts
http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html
I'd explained the application of APPLY operator in various scenarios. Now I'm going to use same scenario and show how we can achieve same solution using new analytic and aggregation windowing functions in SQL 2012
Lets consider the below example which is similar to what I did in earlier post
The output of above query would be as follows
As you see from above this gives sales details of product items with their total selling cost till date as well as the previous and next selling costs if any exists. As you see from above, we have made use of subqueries using APPLY operator to achieve this.
Now attempt is see how we can implement same functionality using new analytic/aggregate window functions in SQL 2012. The functions we use here would be SUM() and LAG() functions available in SQL 2012 to achieve same result. The modified query would be like below
As you see from above we get same intended result. Now we can analyse this query. As you see from above, we calculate running cost by using aggregate function SUM(). Starting from sql 2012, you can use ORDER BY clause along with PARTITION BY for aggregate functions like SUM(),AVG() etc to get running aggregate based on specified order. Here we've used ORDER BY Item_date to calculate the running aggregate based on sequence of sales date.
The previous and next selling costs are obtained with the help of two new functions LAG() and LEAD(). As name suggests LAG and LEAD enables us to traverse through group created by PARTITION BY in forward or backward direction and returns the required quantity value. The argument offset determine how long it needs to traverse front or back so as to reach required position.
So in this case LAG(sellingCost) over (partition by Product_ID order by Item_date) traverses backward within each Product_ID group and gives us next member on the order of Item_date. The default offset value is 1 which is what it assumes if we didnt specify anything for second argument. LEAD works exactly same way except for the fact that it traverses forward direction.
From above we can see how we effectively uses new functional enhancements in SQL 2012 to achieve the given business scenario
For more details on functions refer MSDN documentation below
SUM: http://msdn.microsoft.com/en-us/library/ms187810.aspx
LAG: http://msdn.microsoft.com/en-us/library/hh231256.aspx
LEAD: http://msdn.microsoft.com/en-us/library/hh213125.aspx
In one of my previous posts
http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html
I'd explained the application of APPLY operator in various scenarios. Now I'm going to use same scenario and show how we can achieve same solution using new analytic and aggregation windowing functions in SQL 2012
Lets consider the below example which is similar to what I did in earlier post
As you see from above we've two tables having product data and selling data for products. The items table lists the selling details of product with date of sale and cost on which sales happened. Now lets see how we can calculate the running total (total selling cost) of a product as on particular date and also last and next selling cost details for product. Till SQL 2008, We can make use of APPLY operator to achieve this requirement as follows.
As you see from above this gives sales details of product items with their total selling cost till date as well as the previous and next selling costs if any exists. As you see from above, we have made use of subqueries using APPLY operator to achieve this.
Now attempt is see how we can implement same functionality using new analytic/aggregate window functions in SQL 2012. The functions we use here would be SUM() and LAG() functions available in SQL 2012 to achieve same result. The modified query would be like below
As you see from above we get same intended result. Now we can analyse this query. As you see from above, we calculate running cost by using aggregate function SUM(). Starting from sql 2012, you can use ORDER BY clause along with PARTITION BY for aggregate functions like SUM(),AVG() etc to get running aggregate based on specified order. Here we've used ORDER BY Item_date to calculate the running aggregate based on sequence of sales date.
The previous and next selling costs are obtained with the help of two new functions LAG() and LEAD(). As name suggests LAG and LEAD enables us to traverse through group created by PARTITION BY in forward or backward direction and returns the required quantity value. The argument offset determine how long it needs to traverse front or back so as to reach required position.
So in this case LAG(sellingCost) over (partition by Product_ID order by Item_date) traverses backward within each Product_ID group and gives us next member on the order of Item_date. The default offset value is 1 which is what it assumes if we didnt specify anything for second argument. LEAD works exactly same way except for the fact that it traverses forward direction.
From above we can see how we effectively uses new functional enhancements in SQL 2012 to achieve the given business scenario
For more details on functions refer MSDN documentation below
SUM: http://msdn.microsoft.com/en-us/library/ms187810.aspx
LAG: http://msdn.microsoft.com/en-us/library/hh231256.aspx
LEAD: http://msdn.microsoft.com/en-us/library/hh213125.aspx
No comments:
Post a Comment