Friday, May 29, 2015

Top 10 Applications of SQL Server Window Functions.

All these examples work in AdventureWorks Database For SQL Server 2012 and Higher.

Use AdventureWorksDW2014
GO


/*************************************************************

ONE
Running Total Calculation
Give Daily, Week to Date & Year to Date Sales

*****************************************************************/

SELECT
d.CalendarYear YEAR,
d.WeekNumberOfYear WEEK,
d.EnglishMonthName MONTH,
d.FullDateAlternateKey DATE,
SUM(f.SalesAmount) DailySales,
SUM(SUM(f.SalesAmount)) OVER ( PARTITION BY d.CalendarYear, WeekNumberOfYear ORDER BY FullDateAlternateKey  ) WTDSales,
SUM(SUM(f.SalesAmount)) OVER ( PARTITION BY d.CalendarYear, EnglishMonthName ORDER BY FullDateAlternateKey  ) MTDSales,
SUM(SUM(f.SalesAmount)) OVER ( PARTITION BY d.CalendarYear ORDER BY FullDateAlternateKey ) YTDSales
FROM [dbo].[FactInternetSales] f
JOIN [dbo].[DimDate] d
 ON f.OrderDateKey = d.DateKey
--where d.CalendarYear = 2013
 GROUP BY d.CalendarYear,
d.WeekNumberOfYear,
d.EnglishMonthName,
d.FullDateAlternateKey
ORDER BY
d.FullDateAlternateKey


Year
Week
Month
Date
DailySales
WTDSales
MTDSales
YTDSales
2010
53
December
12/29/10
14477.34
14477.34
14477.34
14477.34
2010
53
December
12/30/10
13931.52
28408.86
28408.86
28408.86
2010
53
December
12/31/10
15012.18
43421.04
43421.04
43421.04
2011
1
January
1/1/11
7156.54
7156.54
7156.54
7156.54
2011
2
January
1/2/11
15012.18
15012.18
22168.72
22168.72
2011
2
January
1/3/11
14313.08
29325.26
36481.8
36481.8
2011
2
January
1/4/11
7855.638
37180.9
44337.44
44337.44


/*************************************************************

TWO
Percent of Total.
% of Sales of This Product to Over All Sales.

*****************************************************************/


 SELECT
 p.EnglishProductName Product,
 SUM(SalesAmount) AS SalesAmount,
 CAST(100.00* SUM(SalesAmount) / SUM(SUM(SalesAmount)) OVER() AS DECIMAL(6,2)) AS [% OF Total]
 FROM dbo.FactResellerSales f
 JOIN dbo.DimReseller r
  ON f.ResellerKey = r.ResellerKey
 JOIN dbo.DimProduct p
 ON f.ProductKey = p.ProductKey
 GROUP BY EnglishProductName
 ORDER BY [% OF Total] DESC


Product
SalesAmount
% of Total
Mountain-200 Black, 38
3105726.659
3.86
Mountain-200 Black, 42
2646352.669
3.29
Mountain-200 Silver, 38
2354215.235
2.93
Mountain-200 Silver, 42
2181044.288
2.71
Mountain-200 Silver, 46
2133156.844
2.65



/*************************************************************

THREE
Gaps in Sequence
Find the date ranges when Orders for a Product  were not Received

*****************************************************************/


;WITH cte_orders AS
(

SELECT
 p.EnglishProductName AS  Product,
 f.OrderDate ,
 ROW_NUMBER() OVER ( PARTITION BY EnglishProductName ORDER BY OrderDate ) AS rownum
 FROM dbo.FactInternetSales f
 JOIN dbo.DimProduct p
  ON f.ProductKey = p.ProductKey
 WHERE f.OrderDateKey BETWEEN 20130101 AND 20131231 -- For Year 2013
 AND p.EnglishProductName = 'Short-Sleeve Classic Jersey, S'
 GROUP BY EnglishProductName,f.OrderDate
  )
SELECT cur.Product, cur.OrderDate + 1 [NO ORDER BEGIN] , nxt.OrderDate - 1 [NO ORDER END]
FROM cte_orders cur
JOIN cte_orders nxt
ON cur.Product = nxt.Product AND nxt.rownum = cur.rownum + 1
WHERE nxt.OrderDate > cur.OrderDate + 1
ORDER BY cur.Product, cur.OrderDate

Product
No Order Begin
No Order End
Short-Sleeve Classic Jersey, S
1/2/2013
1/4/2013
Short-Sleeve Classic Jersey, S
1/6/2013
1/6/2013
Short-Sleeve Classic Jersey, S
1/8/2013
1/10/2013
Short-Sleeve Classic Jersey, S
1/12/2013
1/12/2013
Short-Sleeve Classic Jersey, S
1/14/2013
1/15/2013
Short-Sleeve Classic Jersey, S
1/17/2013
1/17/2013
Short-Sleeve Classic Jersey, S
1/19/2013
1/22/2013
Short-Sleeve Classic Jersey, S
1/25/2013
1/27/2013
Short-Sleeve Classic Jersey, S
1/29/2013
1/30/2013


*****************************************************************/

FOUR
/* Islands in Sequence */
/* Countinuous Range of Dates when Orders for a Product were received */

*****************************************************************/

;WITH cte_orders AS
(

SELECT
 EnglishProductName AS Product   ,
 f.OrderDate 
 FROM dbo.FactInternetSales f
  JOIN dbo.DimProduct p
  ON f.ProductKey = p.ProductKey
 WHERE f.OrderDateKey BETWEEN 20130101 AND 20131231 -- For Year 2013
 AND p.EnglishProductName = 'Short-Sleeve Classic Jersey, S'
 GROUP BY EnglishProductName,f.OrderDate
  ) ,
start_dates AS
 (
  SELECT Product, OrderDate, ROW_NUMBER() OVER ( PARTITION BY Product ORDER BY OrderDate ) AS rownum
  FROM cte_orders o1
  WHERE NOT EXISTS (
   SELECT * FROM cte_orders o2 WHERE o1.Product = o2.Product AND o2.OrderDate = o1.OrderDate - 1
   )
  ) ,
end_dates AS
 (
  SELECT Product, OrderDate, ROW_NUMBER() OVER ( PARTITION BY Product ORDER BY OrderDate ) AS rownum
  FROM cte_orders o1
  WHERE NOT EXISTS (
   SELECT * FROM cte_orders o2 WHERE o1.Product = o2.Product AND o2.OrderDate = o1.OrderDate + 1
  )
 ) 
SELECT s.Product, s.OrderDate AS [ORDER START], e.OrderDate AS [ORDER END] FROM
start_dates s
JOIN end_dates e
ON s.Product = e.Product AND s.rownum = e.rownum
ORDER BY s.Product, s.OrderDate


Product
Order Start
Order End
Short-Sleeve Classic Jersey, S
1/1/2013
1/1/2013
Short-Sleeve Classic Jersey, S
1/5/2013
1/5/2013
Short-Sleeve Classic Jersey, S
1/7/2013
1/7/2013
Short-Sleeve Classic Jersey, S
1/11/2013
1/11/2013
Short-Sleeve Classic Jersey, S
1/13/2013
1/13/2013
Short-Sleeve Classic Jersey, S
1/16/2013
1/16/2013
Short-Sleeve Classic Jersey, S
1/18/2013
1/18/2013
Short-Sleeve Classic Jersey, S
1/23/2013
1/24/2013
Short-Sleeve Classic Jersey, S
1/28/2013
1/28/2013


*****************************************************************/

Five
first_value and last_value functions
Give Salary for Each Employee as well as Min and Max Sal For his Department

*****************************************************************/

SELECT FirstName, LastName, BaseRate*PayFrequency AS sal, DepartmentName,
first_value(BaseRate*PayFrequency) OVER ( PARTITION BY DepartmentName ORDER BY BaseRate*PayFrequency ) AS min_sal,
last_value(BaseRate*PayFrequency) OVER ( PARTITION BY DepartmentName ORDER BY BaseRate*PayFrequency ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS max_sal
 FROM [dbo].[DimEmployee]
 ORDER BY DepartmentName

FirstName
LastName
sal
DepartmentName
min_sal
max_sal
Zainal
Arifin
35.577
Document Control
20.5
35.577
Chris
Norred
33.6538
Document Control
20.5
35.577
Tengiz
Kharatishvili
33.6538
Document Control
20.5
35.577
Sean
Chai
20.5
Document Control
20.5
35.577
Karen
Berge
20.5
Document Control
20.5
35.577


*****************************************************************/

Six
-- Median Calcuation
-- Give Median Sales.

*****************************************************************/

-- Median Sales Amount. Method 1.
;WITH cte_x
AS (
      SELECT      SalesAmount,
            ROW_NUMBER() OVER (ORDER BY SalesAmount) AS RowNo,
            COUNT(*) OVER () AS Count1
      FROM  dbo.[FactInternetSales]
)
SELECT      SalesAmount AS MedianSalesAmt
FROM  cte_x
WHERE CEILING(0.5 * Count1) = RowNo;


-- Median Sales Amount. Method 2.
SELECT TOP 1 PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SalesAmount) OVER () AS MedianSalesAmount
FROM dbo.[FactInternetSales]




*****************************************************************/

Seven
Give Event Duration from Event Logs Table

*****************************************************************/


IF OBJECT_ID( 'tempdb.dbo.#event_logs' ) IS NOT NULL
DROP TABLE #event_logs

CREATE TABLE #event_logs ( person VARCHAR(20), event_type VARCHAR(20), event_time DATETIME )
INSERT INTO #event_logs VALUES ( 'Tom' , 'Login', '05-01-2015 08:00 AM' ),
                                                ( 'Tom' , 'Away', '05-01-2015 12:00 PM' ),
                                                ( 'Tom' , 'Available', '05-01-2015 1:00 PM' ),
                                                ( 'Tom' , 'Logout', '05-01-2015 5:00 PM' ),
                                                ( 'Jim' , 'Login', '05-02-2015 08:10 AM' ),
                                                ( 'Jim' , 'Away', '05-02-2015 12:01 PM' ),
                                                ( 'Jim' , 'Available', '05-02-2015 1:01 PM' ),
                                                ( 'Jim' , 'Logout', '05-02-2015 5:10 PM' )
;WITH cte_x AS
(                                        
SELECT
* , ROW_NUMBER() OVER ( PARTITION BY person, CAST(event_time AS DATE) ORDER BY event_time ) rowno
FROM #event_logs
)
SELECT
start.person, start.event_type, start.event_time AS start_time, end1.event_time AS end_time
FROM cte_x START
LEFT JOIN cte_x AS end1
ON start.person = end1.person
AND CAST(start.event_time AS DATE) = CAST(end1.event_time AS DATE) /* Limit to same date */
AND start.rowno = end1.rowno - 1


person
event_type
start_time
end_time
Jim
Login
5/2/15 8:10 AM
5/2/15 12:01 PM
Jim
Away
5/2/15 12:01 PM
5/2/15 1:01 PM
Jim
Available
5/2/15 1:01 PM
5/2/15 5:10 PM
Jim
Logout
5/2/15 5:10 PM
NULL
Tom
Login
5/1/15 8:00 AM
5/1/15 12:00 PM
Tom
Away
5/1/15 12:00 PM
5/1/15 1:00 PM
Tom
Available
5/1/15 1:00 PM
5/1/15 5:00 PM
Tom
Logout
5/1/15 5:00 PM
NULL


*****************************************************************/

Eight
Find & Delete Duplicates from Table

*****************************************************************/

IF OBJECT_ID( 'tempdb.dbo.#t' ) IS NOT NULL
DROP TABLE #t

CREATE TABLE #t ( ID INT )

INSERT INTO #t
VALUES (1), (1), (2), (3), (4), (5)

/* Find Duplicates */
;WITH cte_x AS (
      SELECT ID,
      ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY (SELECT NULL) ) row_no
      FROM #t
)
SELECT ID FROM cte_x WHERE row_no > 1


/* Delete Duplicates */
;WITH cte_x AS (
      SELECT ID, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY (SELECT NULL) ) row_no
      FROM #t
      )
DELETE FROM cte_x WHERE row_no > 1



*****************************************************************/

NINE
NTile Functions.
Divide Products into 4 Quartiles.
Top 25 % are in First Quartile and Bottom 25 % are in Last Quartile.

*****************************************************************/

SELECT
 s.EnglishProductSubcategoryName [Prod Category],
 SUM(SalesAmount) AS SalesAmount,
 NTILE(4) OVER ( ORDER BY SUM(SalesAmount) DESC ) Quartile
 FROM dbo.FactResellerSales f
 JOIN dbo.DimProduct p
 ON f.ProductKey = p.ProductKey
 JOIN [dbo].[DimProductSubcategory] s
  ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
JOIN dbo.DimProductCategory c
  ON c.ProductCategoryKey = s.ProductCategoryKey
WHERE EnglishProductCategoryName = 'Clothing'
GROUP BY c.EnglishProductCategoryName,EnglishProductSubcategoryName
ORDER BY SalesAmount DESC


Prod Category
SalesAmount
Quartile
Jerseys
579308.7084
1
Shorts
342202.717
1
Vests
223801.3707
2
Gloves
207775.1742
2
Tights
201833.006
3
Bib-Shorts
166739.7086
3
Caps
31541.3461
4
Socks
24638.8081
4


*****************************************************************/

TEN
MOVING Average Sales for Last Three Months.

*****************************************************************/

SELECT
 CalendarYear YEAR,
d.EnglishMonthName MONTH,
SUM(f.SalesAmount)  MonthlySales,
 (SUM(f.SalesAmount) +
      lag(SUM(f.SalesAmount)) OVER( ORDER BY CalendarYear * 100 + MonthNumberOfYear) +
      lag(SUM(f.SalesAmount),2) OVER( ORDER BY CalendarYear * 100 + MonthNumberOfYear)
      ) / 3 AS ThreeMosMovingAVG
FROM [dbo].[FactInternetSales] f
JOIN [dbo].[DimDate] d
 ON f.OrderDateKey = d.DateKey
--where d.CalendarYear = 2013
 GROUP BY d.CalendarYear,
d.MonthNumberOfYear,
d.EnglishMonthName
ORDER BY
 d.CalendarYear DESC,
d.MonthNumberOfYear DESC


Year
Month
MonthlySales
ThreeMosMovingAVG
2014
January
45694.72
1233658.357
2013
December
1874360.29
1776191.253
2013
November
1780920.06
1633903.053
2013
October
1673293.41
1557284.887
2013
September
1447495.69
1456745.687
2013
August
1551065.56
1521973.05
2013
July
1371675.81
1433148.84



No comments: