This post has already been read 1155 times!

example-1
USE StockAnalysis;    
GO    
--1-1.1 Using a subquery    
SELECT  TickerSymbol ,
        TradeDate ,
        ClosePrice ,
        ( SELECT TOP ( 1 )
                    ClosePrice
          FROM      StockHistory AS SQ
          WHERE     SQ.TickerSymbol = OQ.TickerSymbol
                    AND SQ.TradeDate < OQ.TradeDate
          ORDER BY  TradeDate DESC
        ) AS PrevClosePrice
FROM    StockHistory AS OQ
ORDER BY TickerSymbol ,
        TradeDate;       
--1-1.2 Using LAG    
SELECT  TickerSymbol ,
        TradeDate ,
        ClosePrice ,
        LAG(ClosePrice) OVER ( PARTITION BY TickerSymbol ORDER BY TradeDate ) AS PrevClosePrice
FROM    StockHistory
ORDER BY TickerSymbol ,
        TradeDate; 
		
USE AdventureWorks;   
GO   
--1-2.1 Row numbers applied by CustomerID    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID ) AS RowNumber
FROM    Sales.SalesOrderHeader;       
--1-2.2 Row numbers applied by SalesOrderID    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS RowNumber
FROM    Sales.SalesOrderHeader;     
  
--1-3.1 Row number with a different ORDER BY    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID ) AS RowNumber
FROM    Sales.SalesOrderHeader
ORDER BY SalesOrderID;

--1-4.1 Row number with a descending ORDER BY    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID DESC ) AS RowNumber
FROM    Sales.SalesOrderHeader; 

--1-5.1 Row number with a random ORDER BY    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY NEWID() ) AS RowNumber
FROM    Sales.SalesOrderHeader; 

--1-6.1 Use a constant for an ORDER BY    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY ( SELECT   1
                                     ) ) AS RowNumber
FROM    Sales.SalesOrderHeader;       
--1-6.2 Apply an ORDER BY to the query    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY ( SELECT   1
                                     ) ) AS RowNumber
FROM    Sales.SalesOrderHeader
ORDER BY SalesOrderID;       
--1-6.3 No ROW_NUMBER and no ORDER BY    
SELECT  CustomerID ,
        SalesOrderID
FROM    Sales.SalesOrderHeader;       

--1-7.1 Use an expression in the ORDER BY    
SELECT  CustomerID ,
        SalesOrderID ,
        OrderDate ,
        ROW_NUMBER() OVER ( ORDER BY CASE WHEN OrderDate > '2013/12/31' THEN 0
                                          ELSE 1
                                     END, SalesOrderID ) AS RowNumber
FROM    Sales.SalesOrderHeader;      

--1-8.1 Use ROW_NUMBER with PARTITION BY    
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS RowNumber
FROM    Sales.SalesOrderHeader; 

--1-9.1 Using DISTINCT    
SELECT DISTINCT
        OrderDate ,
        ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNumber
FROM    Sales.SalesOrderHeader
ORDER BY RowNumber;       
--1-9.2 Separate logic with CTE    
WITH    OrderDates
          AS ( SELECT DISTINCT
                        OrderDate
               FROM     Sales.SalesOrderHeader
             )
    SELECT  OrderDate ,
            ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNumber
    FROM    OrderDates
    ORDER BY RowNumber; 
	
--1-10.1 Using TOP with ROW_NUMBER    
SELECT TOP ( 6 )
        CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS RowNumber
FROM    Sales.SalesOrderHeader
ORDER BY NEWID();     
--1-10.2 Separate the logic with a CTE    
WITH    Orders
          AS ( SELECT TOP ( 6 )
                        CustomerID ,
                        SalesOrderID
               FROM     Sales.SalesOrderHeader
               ORDER BY NEWID()
             )
    SELECT  CustomerID ,
            SalesOrderID ,
            ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS RowNumber
FROM Orders; 
example-2
USE AdventureWorks;    
GO    
--2-1.1 Using ROW_NUMBER with and without a PARTITION BY    
SELECT  CustomerID ,
        FORMAT(OrderDate, 'yyyy-MM-dd') AS OrderDate ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS WithPart ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID ) AS WithoutPart
FROM    Sales.SalesOrderHeader;  

--2-2.1 Query ORDER BY ascending    
SELECT  CustomerID ,
        OrderDate ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID ) AS RowNumber
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
        SalesOrderID;   
--2-2.2 Query ORDER BY descending    
SELECT  CustomerID ,
        OrderDate ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID ) AS RowNumber
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
        SalesOrderID DESC;   

--2-3.1 Using ROW_NUMBER a unique ORDER BY   
SELECT  CustomerID ,
        OrderDate ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID, SalesOrderID ) AS RowNum
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
        SalesOrderID; 
		
  
--2-3.2 Change to descending    
SELECT  CustomerID ,
        OrderDate ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( ORDER BY CustomerID, SalesOrderID ) AS RowNum
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
        SalesOrderID DESC;   

--2-4.1 Using RANK and DENSE_RANK    
SELECT  CustomerID ,
        OrderDate ,
        ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNumber ,
        RANK() OVER ( ORDER BY OrderDate ) AS [Rank] ,
        DENSE_RANK() OVER ( ORDER BY OrderDate ) AS DenseRank
FROM    Sales.SalesOrderHeader
WHERE   CustomerID IN ( 11330, 29676 );   

--2.5.1 Using NTILE    
WITH    Orders
          AS ( SELECT   MONTH(OrderDate) AS OrderMonth ,
                        FORMAT(SUM(TotalDue), 'C') AS Sales
               FROM     Sales.SalesOrderHeader
               WHERE    OrderDate >= '2013/01/01'
                        AND OrderDate < '2014/01/01'
               GROUP BY MONTH(OrderDate)
             )
    SELECT  OrderMonth ,
            Sales ,
            NTILE(4) OVER ( ORDER BY Sales ) AS Bucket
    FROM    Orders;   


--2.6.1 Using NTILE with uneven buckets    
WITH    Orders
          AS ( SELECT   MONTH(OrderDate) AS OrderMonth ,
                        FORMAT(SUM(TotalDue), 'C') AS Sales
               FROM     Sales.SalesOrderHeader
               WHERE    OrderDate >= '2013/01/01'
                        AND OrderDate < '2014/01/01'
               GROUP BY MONTH(OrderDate)
             )
    SELECT  OrderMonth ,
            Sales ,
            NTILE(5) OVER ( ORDER BY Sales ) AS Bucket
    FROM    Orders;   

--2-7.1 Create a table that will hold duplicate rows    
CREATE TABLE #dupes ( Col1 INT, Col2 CHAR(1) );       
--2-7.2 Insert some rows    
INSERT  INTO #dupes
        ( Col1, Col2 )
VALUES  ( 1, 'a' ),
        ( 1, 'a' ),
        ( 2, 'b' ),
        ( 3, 'c' ),
        ( 4, 'd' ),
        ( 4, 'd' ),
        ( 5, 'e' );       
--2-7.3    
SELECT Col1, Col2    
FROM #dupes;   

--2-8.1 Add ROW_NUMBER and Partition by all of the columns    
SELECT  Col1 ,
        Col2 ,
        ROW_NUMBER() OVER ( PARTITION BY Col1, Col2 ORDER BY Col1 ) AS RowNumber
FROM    #dupes;       
--2-8.2 Delete the rows with RowNumber > 1    
WITH    Dupes
          AS ( SELECT   Col1 ,
                        Col2 ,
                        ROW_NUMBER() OVER ( PARTITION BY Col1, Col2 ORDER BY Col1 ) AS RowNumber
               FROM     #dupes
             )
    DELETE  Dupes
    WHERE   RowNumber > 1;       
--2-8.3 The results    
SELECT Col1, Col2    
FROM #dupes;   


--2-9.1 Using CROSS APPLY to find the first four orders    
WITH    Months
          AS ( SELECT   MONTH(OrderDate) AS OrderMonth
               FROM     Sales.SalesOrderHeader
               WHERE    OrderDate >= '2013-01-01'
                        AND OrderDate < '2014-01-01'
               GROUP BY MONTH(OrderDate)
             )
    SELECT  OrderMonth ,
            CA.OrderDate ,
            CA.SalesOrderID ,
            CA.TotalDue
    FROM    Months
            CROSS APPLY ( SELECT TOP ( 4 )
                                    SalesOrderID ,
                                    OrderDate ,
                                    TotalDue
                          FROM      Sales.SalesOrderHeader AS IQ
                          WHERE     OrderDate >= '2013-01-01'
                                    AND OrderDate < '2014-01-01'
                                    AND MONTH(IQ.OrderDate) = Months.OrderMonth
                          ORDER BY  SalesOrderID
                        ) AS CA
    ORDER BY OrderMonth ,
            SalesOrderID;       
--2-9.2 Use ROW_NUMBER to find the first four orders    
WITH    Orders
          AS ( SELECT   MONTH(OrderDate) AS OrderMonth ,
                        OrderDate ,
                        SalesOrderID ,
                        TotalDue ,
                        ROW_NUMBER() OVER ( PARTITION BY MONTH(OrderDate) ORDER BY SalesOrderID ) AS RowNumber
               FROM     Sales.SalesOrderHeader
               WHERE    OrderDate >= '2013-01-01'
                        AND OrderDate < '2014-01-01'
             )
    SELECT  OrderMonth ,
            OrderDate ,
            SalesOrderID ,
            TotalDue
    FROM    Orders
    WHERE   RowNumber <= 4
    ORDER BY OrderMonth ,
            SalesOrderID;  
			
 --2-10.1 Create the #Islands table    
 CREATE TABLE #Islands ( ID INT NOT NULL );       
 --2-10.2 Populate the #Islands table    
 INSERT INTO #Islands
        ( ID )
 VALUES ( 101 ),
        ( 102 ),
        ( 103 ),
        ( 106 ),
        ( 108 ),
        ( 108 ),
        ( 109 ),
        ( 110 ),
        ( 111 ),
        ( 112 ),
        ( 112 ),
        ( 114 ),
        ( 115 ),
        ( 118 ),
        ( 119 );       
--2-10.3 View the data    
SELECT ID    
FROM #Islands;      

--2-11.1 Add ROW_NUMBER to the data    
SELECT  ID ,
        ROW_NUMBER() OVER ( ORDER BY ID ) AS RowNum
FROM    #Islands;       
--2-11.2 Subtract the RowNum from the ID    
SELECT  ID ,
        ROW_NUMBER() OVER ( ORDER BY ID ) AS RowNum ,
        ID - ROW_NUMBER() OVER ( ORDER BY ID ) AS Diff
FROM    #Islands;       
--2-11.3 Change to DENSE_RANK since there are duplicates    
SELECT  ID ,
        DENSE_RANK() OVER ( ORDER BY ID ) AS DenseRank ,
        ID - DENSE_RANK() OVER ( ORDER BY ID ) AS Diff
FROM    #Islands;       
--2-11.4 The complete Islands solution    
WITH    Islands
          AS ( SELECT   ID ,
                        DENSE_RANK() OVER ( ORDER BY ID ) AS DenseRank ,
                        ID - DENSE_RANK() OVER ( ORDER BY ID ) AS Diff
               FROM     #Islands
             )
    SELECT  MIN(ID) AS IslandStart ,
            MAX(ID) AS IslandEnd
    FROM    Islands
    GROUP BY Diff;   

--2-12.1 Using NTILE to assign bonuses    
WITH    Sales
          AS ( SELECT   SP.FirstName ,
                        SP.LastName ,
                        SUM(SOH.TotalDue) AS TotalSales
               FROM     [Sales].[vSalesPerson] SP
                        JOIN Sales.SalesOrderHeader SOH ON SP.BusinessEntityID = SOH.SalesPersonID
               WHERE    SOH.OrderDate >= '2011-01-01'
                        AND SOH.OrderDate < '2012-01-01'
               GROUP BY FirstName ,
                        LastName
             )
    SELECT  FirstName ,
            LastName ,
            TotalSales ,
            NTILE(4) OVER ( ORDER BY TotalSales ) * 1000 AS Bonus
FROM Sales; 
example 3
--3.2.1 Use different OVER clauses    
SELECT  CustomerID ,
        SalesOrderID ,
        FORMAT(TotalDue, 'c') AS TotalDue ,
        FORMAT(SUM(TotalDue) OVER ( PARTITION BY CustomerID ), 'c') AS CustomerSales ,
        FORMAT(SUM(TotalDue) OVER ( ), 'c') AS TotalSales
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
ORDER BY CustomerID ,
        SalesOrderID;   

--3-3.1 Add a window aggregate to an aggregate query    
SELECT  CustomerID ,
        SUM(TotalDue) AS CustomerTotal ,
        SUM(TotalDue) OVER ( ) AS GrandTotal
FROM    Sales.SalesOrderHeader
GROUP BY CustomerID;   

--3-4.1 How to add a window aggregate to an aggregate query    
SELECT  CustomerID ,
        SUM(TotalDue) AS CustomerTotal ,
        SUM(SUM(TotalDue)) OVER ( ) AS GrandTotal
FROM    Sales.SalesOrderHeader
GROUP BY CustomerID;   

--3-5.1 Window aggregate to multiple group by    
SELECT  YEAR(OrderDate) AS OrderYear ,
        CustomerID ,
        SUM(TotalDue) AS CustTotalForYear ,
        SUM(SUM(TotalDue)) OVER ( PARTITION BY CustomerID ) AS CustomerTotal
FROM    Sales.SalesOrderHeader
GROUP BY CustomerID ,
        YEAR(OrderDate)
ORDER BY CustomerID ,
        OrderYear; 
		
--3-6.1 Calculate the percent of sales    
SELECT  P.ProductID ,
        FORMAT(SUM(OrderQty * UnitPrice), 'C') AS ProductSales ,
        FORMAT(SUM(SUM(OrderQty * UnitPrice)) OVER ( ), 'C') AS TotalSales ,
        FORMAT(SUM(OrderQty * UnitPrice)
               / SUM(SUM(OrderQty * UnitPrice)) OVER ( ), 'P') AS PercentOfSales
FROM    Sales.SalesOrderDetail AS SOD
        JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
        JOIN Production.ProductSubcategory AS SUB ON P.ProductSubcategoryID = SUB.ProductSubcategoryID
        JOIN Production.ProductCategory AS CAT ON SUB.ProductCategoryID = CAT.ProductCategoryID
WHERE   CAT.Name = 'Bikes'
GROUP BY P.ProductID
ORDER BY PercentOfSales DESC;   

  
--3-7.1 Create the partition function    
CREATE PARTITION FUNCTION testFunction (DATE)    
AS RANGE RIGHT    
FOR VALUES ('2011-01-01','2012-01-01','2013-01-01','2014-01-01');    
GO       
--3-7.2 Create the partition scheme    
CREATE PARTITION SCHEME testScheme    
AS PARTITION testFunction ALL TO ('Primary');    
GO       
--3-7.3 Create a partitioned table    
CREATE TABLE dbo.Orders
    (
      CustomerID INT ,
      SalesOrderID INT ,
      OrderDate DATE ,
      TotalDue MONEY
    )
ON  testScheme(OrderDate);   
GO       
--3-7.4 Populate the table    
INSERT  INTO dbo.Orders
        ( CustomerID ,
          SalesOrderID ,
          OrderDate ,
          TotalDue
        )
        SELECT  CustomerID ,
                SalesOrderID ,
                OrderDate ,
                TotalDue
        FROM    Sales.SalesOrderHeader; 
GO       
--3-7.5 Create another partitioned table    
CREATE TABLE dbo.Customer
    (
      CustomerID INT ,
      ModifiedDate DATE
    )
ON  testScheme(ModifiedDate);    
GO       
--3-7.6 Populate the table    
INSERT  INTO dbo.Customer
        ( CustomerID ,
          ModifiedDate
        )
        SELECT  CustomerID ,
                ModifiedDate
        FROM    Sales.Customer;   

--3-8.1 Find the percent of rows by table    
SELECT  OBJECT_NAME(p.object_id) TableName ,
        ps.partition_number ,
        ps.row_count ,
--My solution starts here   
        FORMAT(ps.row_count * 1.0
               / SUM(ps.row_count) OVER ( PARTITION BY p.object_id ), 'p') AS PercentOfRows                    
--and ends here    
FROM    sys.data_spaces d
        JOIN sys.indexes i
        JOIN ( SELECT DISTINCT
                        object_id
               FROM     sys.partitions
               WHERE    partition_number > 1
             ) p ON i.object_id = p.object_id ON d.data_space_id = i.data_space_id
        JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id
                                             AND i.index_id = ps.index_id
WHERE   i.index_id < 2;  

--3-9 Drop objects created in this section    
DROP TABLE dbo.Customer;    
DROP TABLE dbo.Orders;    
DROP PARTITION SCHEME testScheme;    
DROP PARTITION FUNCTION testFunction; 

--3-10.1 Enable CRL    
EXEC sp_configure 'clr_enabled', 1;    
GO    
RECONFIGURE;    
GO       
--3-10.2 Register the DLL    
CREATE ASSEMBLY CustomAggregate FROM  'C:\Custom\CustomAggregate.dll' 
WITH PERMISSION_SET = SAFE;    
GO       
--3-10.3 Create the function    
CREATE Aggregate Median (@Value INT) RETURNS INT    
EXTERNAL NAME CustomAggregate.Median;    
GO   

--3-10.4 Test the function    
WITH    Orders
          AS ( SELECT   CustomerID ,
                        SUM(OrderQty) AS OrderQty ,
                        SOH.SalesOrderID
               FROM     Sales.SalesOrderHeader AS SOH
                        JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderDetailID
               GROUP BY CustomerID ,
                        SOH.SalesOrderID
             )
    SELECT  CustomerID ,
            OrderQty ,
            dbo.Median(OrderQty) OVER ( PARTITION BY CustomerID ) AS Median
    FROM    Orders
    WHERE   CustomerID IN ( 13011, 13012, 13019 ); 

--3-11.1 Drop the objects    
DROP AGGREGATE Median;    
DROP ASSEMBLY CustomAggregate;    
GO 
example-4
--Code for Chapter 4
--4-1.1 Query to produce Sequence Project
SELECT  CustomerID ,
        ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS RowNumber
FROM    Sales.SalesOrderHeader;

--4-2.1 A query to show the Sort operator
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate ) AS RowNumber
FROM    Sales.SalesOrderHeader;

--4-3.1 A query with a Table Spool operator
SELECT  CustomerID ,
        SalesOrderID ,
        SUM(TotalDue) OVER ( PARTITION BY CustomerID ) AS SubTotal
FROM    Sales.SalesOrderHeader;

--4.4.0 Settings
SET STATISTICS IO ON;
SET NOCOUNT ON;
GO
--4-4.1 Query to produce Sequence Project
PRINT '4-4.1';
SELECT  CustomerID ,
        ROW_NUMBER() OVER ( ORDER BY SalesOrderID ) AS RowNumber
FROM    Sales.SalesOrderHeader;

--4-4.2 A query to show the Sort operator
PRINT '4-4.2';
SELECT  CustomerID ,
        SalesOrderID ,
        ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate ) AS RowNumber
FROM    Sales.SalesOrderHeader;

--4-4.3 A query with a Table Spool operator
PRINT '4-4.3';
SELECT CustomerID, SalesOrderID, SUM(TotalDue) OVER(PARTITION BY CustomerID)
AS SubTotal
FROM Sales.SalesOrderHeader;

--4-5.0 Settings
SET STATISTICS IO ON;
SET NOCOUNT ON;
GO
--4-5.1 CTE
PRINT '4-5.1 CTE';
WITH    Totals
          AS ( SELECT   CustomerID ,
                        SUM(TotalDue) AS CustomerTotal
               FROM     Sales.SalesOrderHeader
               GROUP BY CustomerID
             )
    SELECT  SOH.CustomerID ,
            SalesOrderID ,
            OrderDate ,
            TotalDue ,
            CustomerTotal
    FROM    Sales.SalesOrderHeader AS SOH
            JOIN Totals ON SOH.CustomerID = Totals.CustomerID;

--4-5.1 The same results using a window aggregate
PRINT '4-5.2 The window aggregate';
SELECT  CustomerID ,
        SalesOrderID ,
        OrderDate ,
        TotalDue ,
        SUM(TotalDue) OVER ( PARTITION BY CustomerID ) AS CustomerTotal
FROM    Sales.SalesOrderHeader;

--4-6.1 Drop the existing index
DROP INDEX [IX_SalesOrderHeader_CustomerID] ON [Sales].[SalesOrderHeader];
GO
--4-6.2 Create a new index for the query
CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_CustomerID_OrderDate]
ON [Sales].[SalesOrderHeader] ([CustomerID], [OrderDate]);

--4-7.1 query with a join
SELECT  SOH.CustomerID ,
        SOH.SalesOrderID ,
        SOH.OrderDate ,
        C.TerritoryID ,
        ROW_NUMBER() OVER ( PARTITION BY SOH.CustomerID ORDER BY SOH.OrderDate ) AS RowNumber
FROM    Sales.SalesOrderHeader AS SOH
        JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID;

--4-7.2 Rearrange the query
WITH    Sales
          AS ( SELECT   CustomerID ,
                        OrderDate ,
                        SalesOrderID ,
                        ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate ) AS RowNumber
               FROM     Sales.SalesOrderHeader
             )
    SELECT  Sales.CustomerID ,
            Sales.SalesOrderID ,
            Sales.OrderDate ,
            C.TerritoryID ,
            Sales.RowNumber
    FROM    Sales
            JOIN Sales.Customer AS C ON C.CustomerID = Sales.CustomerID;

--4-8.0 Settings
SET STATISTICS IO OFF;
SET STATISTICS TIME ON;
SET NOCOUNT ON;
GO
--4-8.1 The join query
PRINT '4-8.1';
SELECT  SOH.CustomerID ,
        SOH.SalesOrderID ,
        SOH.OrderDate ,
        C.TerritoryID ,
        ROW_NUMBER() OVER ( PARTITION BY SOH.CustomerID ORDER BY SOH.OrderDate ) AS RowNumber
FROM    Sales.SalesOrderHeader AS SOH
        JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID;

--4-8.2 The CTE
PRINT '4-8.2';
WITH    Sales
          AS ( SELECT   CustomerID ,
                        OrderDate ,
                        SalesOrderID ,
                        ROW_NUMBER() OVER ( PARTITION BY CustomerID ORDER BY OrderDate ) AS RowNumber
               FROM     Sales.SalesOrderHeader
             )
    SELECT  Sales.CustomerID ,
            Sales.SalesOrderID ,
            Sales.OrderDate ,
            C.TerritoryID ,
            Sales.RowNumber
    FROM    Sales
            JOIN Sales.Customer AS C ON C.CustomerID = Sales.CustomerID;

--4-9.0 Set up a loop
DECLARE @count INT = 0;
WHILE @count < 1000
    BEGIN
--4-9.1 The query
        SELECT  SOH.CustomerID ,
                SalesOrderID ,
                OrderDate ,
                C.TerritoryID ,
                ROW_NUMBER() OVER ( PARTITION BY SOH.CustomerID ORDER BY OrderDate ) AS RowNumber
        FROM    Sales.SalesOrderHeader AS SOH
                JOIN Sales.Customer C ON SOH.CustomerID = C.CustomerID;
        SET @count += 1;
    END;

GO
--4-10.0 Set up a loop
DECLARE @count INT = 0;
WHILE @count < 1000 BEGIN
	--4-10.1 The query
	WITH Sales AS (
	SELECT CustomerID, OrderDate, SalesOrderID,
	ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate)
	AS RowNumber
	FROM Sales.SalesOrderHeader)
	SELECT Sales.CustomerID, SALES.SalesOrderID, Sales.OrderDate, C.TerritoryID,
	Sales.RowNumber
	FROM Sales
	JOIN Sales.Customer AS C ON C.CustomerID = Sales.CustomerID;
	SET @count += 1;
END;

--4-11.1 Drop index
DROP INDEX [IX_SalesOrderHeader_CustomerID_OrderDate] ON Sales.
SalesOrderHeader;
GO
--4-11-2 Recreate original index
CREATE INDEX [IX_SalesOrderHeader_CustomerID] ON Sales.SalesOrderHeader
(CustomerID);
example-5
--5-1.1 A running total
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        TotalDue ,
        SUM(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS RunningTotal
FROM    Sales.SalesOrderHeader;

--5-2.1 Three month sum and average for products qty sold
SELECT  MONTH(SOH.OrderDate) AS OrderMonth ,
        SOD.ProductID ,
        SUM(SOD.OrderQty) AS QtySold ,
        SUM(SUM(SOD.OrderQty)) OVER ( PARTITION BY SOD.ProductID ORDER BY MONTH(SOH.OrderDate)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS ThreeMonthSum ,
        AVG(SUM(SOD.OrderQty)) OVER ( PARTITION BY SOD.ProductID ORDER BY MONTH(SOH.OrderDate)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS ThreeMonthAvg
FROM    Sales.SalesOrderHeader AS SOH
        JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
        JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(SOH.OrderDate) ,
        SOD.ProductID;

--5-3.1 Create the table
CREATE TABLE #TheTable ( ID INT, Data INT );
--5-3.2 Populate the table
INSERT  INTO #TheTable
        ( ID, Data )
VALUES  ( 1, 1 ),
        ( 2, 1 ),
        ( 3, NULL ),
        ( 4, NULL ),
        ( 5, 6 ),
        ( 6, NULL ),
        ( 7, 5 ),
        ( 8, 10 ),
        ( 9, 11 );
--5-3.3 Display the results
SELECT  *
FROM    #TheTable;

--5-4.1 Find the max non-null row
SELECT  ID ,
        Data ,
        MAX(CASE WHEN Data IS NOT NULL THEN ID
            END) OVER ( ORDER BY ID ) AS MaxRow
FROM    #TheTable;

--5-5.1 The solution
WITH    MaxData
          AS ( SELECT   ID ,
                        Data ,
                        MAX(CASE WHEN Data IS NOT NULL THEN ID
                            END) OVER ( ORDER BY ID ) AS MaxRow
               FROM     #TheTable
             )
    SELECT  ID ,
            Data ,
            MAX(Data) OVER ( PARTITION BY MaxRow ) AS NewData
    FROM    MaxData;

/*
Download the data from 
www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/ 
*/
--5-6.1 The subscription data
SELECT  *
FROM    Registrations;

--5-7.1 Solve the subscription problem
WITH    NewSubs
          AS ( SELECT   EOMONTH(DateJoined) AS TheMonth ,
                        COUNT(DateJoined) AS PeopleJoined
               FROM     Registrations
               GROUP BY EOMONTH(DateJoined)
             ),
        Cancelled
          AS ( SELECT   EOMONTH(DateLeft) AS TheMonth ,
                        COUNT(DateLeft) AS PeopleLeft
               FROM     Registrations
               GROUP BY EOMONTH(DateLeft)
             )
    SELECT  NewSubs.TheMonth AS TheMonth ,
            NewSubs.PeopleJoined ,
            Cancelled.PeopleLeft ,
            SUM(NewSubs.PeopleJoined - ISNULL(Cancelled.PeopleLeft, 0)) OVER ( ORDER BY NewSubs.TheMonth ) AS Subscriptions
    FROM    NewSubs
            LEFT JOIN Cancelled ON NewSubs.TheMonth = Cancelled.TheMonth;
example-7
--7-1.1 Use LAG and LEAD
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        LAG(CAST(OrderDate AS DATE)) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS PrevOrderDate ,
        LEAD(CAST(OrderDate AS DATE)) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS NextOrderDate
FROM    Sales.SalesOrderHeader;

--7-1.2 Use LAG and LEAD as an argument
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        DATEDIFF(DAY,
                 LAG(OrderDate) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ),
                 OrderDate) AS DaysSincePrevOrder ,
        DATEDIFF(DAY, OrderDate,
                 LEAD(OrderDate) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID )) AS DaysUntilNextOrder
FROM    Sales.SalesOrderHeader;

--7-2.1 Using Offset with LAG
WITH    Totals
          AS ( SELECT   YEAR(OrderDate) AS OrderYear ,
                        MONTH(OrderDate) / 4 + 1 AS OrderQtr ,
                        SUM(TotalDue) AS TotalSales
               FROM     Sales.SalesOrderHeader
               GROUP BY YEAR(OrderDate) ,
                        MONTH(OrderDate) / 4 + 1
             )
    SELECT  OrderYear ,
            Totals.OrderQtr ,
            TotalSales ,
            LAG(TotalSales, 4) OVER ( ORDER BY OrderYear, OrderQtr ) AS PreviousYearsSales
    FROM    Totals
    ORDER BY OrderYear ,
            OrderQtr;

--7-3.1 Using Offset with LAG
WITH    Totals
          AS ( SELECT   YEAR(OrderDate) AS OrderYear ,
                        MONTH(OrderDate) / 4 + 1 AS OrderQtr ,
                        SUM(TotalDue) AS TotalSales
               FROM     Sales.SalesOrderHeader
               GROUP BY YEAR(OrderDate) ,
                        MONTH(OrderDate) / 4 + 1
             )
    SELECT  OrderYear ,
            Totals.OrderQtr ,
            TotalSales ,
            LAG(TotalSales, 4, 0) OVER ( ORDER BY OrderYear, OrderQtr ) AS PreviousYearsSales
    FROM    Totals
    ORDER BY OrderYear ,
            OrderQtr;

--7-4.1 Using FIRST_VALUE and LAST_VALUE
SELECT  CustomerID ,
        SalesOrderID ,
        TotalDue ,
        FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS FirstOrderAmt ,
        LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS LastOrderAmt_WRONG ,
        LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS LastOrderAmt
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
        SalesOrderID;

--7-5.1 Calculate Year-Over-Year Growth
WITH    Level1
          AS ( SELECT   YEAR(OrderDate) AS SalesYear ,
                        MONTH(OrderDate) AS SalesMonth ,
                        SUM(TotalDue) AS TotalSales
               FROM     Sales.SalesOrderHeader
               GROUP BY YEAR(OrderDate) ,
                        MONTH(OrderDate)
             ),
        Level2
          AS ( SELECT   SalesYear ,
                        SalesMonth ,
                        TotalSales ,
                        LAG(TotalSales, 12) OVER ( ORDER BY SalesYear ) AS PrevYearSales
               FROM     Level1
             )
    SELECT  SalesYear ,
            SalesMonth ,
            FORMAT(TotalSales, 'C') AS TotalSales ,
            FORMAT(PrevYearSales, 'C') AS PrevYearSales ,
            FORMAT(( TotalSales - PrevYearSales ) / PrevYearSales, 'P') AS YOY_Growth
    FROM    Level2
    WHERE   PrevYearSales IS NOT NULL;

--7-6.1 Create the #Islands table
CREATE TABLE #Islands ( ID INT NOT NULL );
--7-6.2 Populate the #Islands table
INSERT  INTO #Islands
        ( ID )
VALUES  ( 1 ),
        ( 2 ),
        ( 3 ),
        ( 6 ),
        ( 8 ),
        ( 8 ),
        ( 9 ),
        ( 10 ),
        ( 11 ),
        ( 12 ),
        ( 12 ),
        ( 14 ),
        ( 15 ),
        ( 18 ),
        ( 19 );
--7-6.3 The Islands
WITH    Islands
          AS ( SELECT   ID ,
                        DENSE_RANK() OVER ( ORDER BY ID ) AS DenseRank ,
                        ID - DENSE_RANK() OVER ( ORDER BY ID ) AS Diff
               FROM     #Islands
             )
    SELECT  MIN(ID) AS IslandStart ,
            MAX(ID) AS IslandEnd
    FROM    Islands
    GROUP BY Diff;
--7-7.1 Find the Gaps
WITH    Level1
          AS ( SELECT   ID ,
                        DENSE_RANK() OVER ( ORDER BY ID ) AS DenseRank ,
                        ID - DENSE_RANK() OVER ( ORDER BY ID ) AS Diff
               FROM     #Islands
             ),
        Level2
          AS ( SELECT   MIN(ID) AS IslandStart ,
                        MAX(ID) AS IslandEnd
               FROM     Level1
               GROUP BY Diff
             ),
        Level3
          AS ( SELECT   IslandEnd + 1 AS GapStart ,
                        LEAD(IslandStart) OVER ( ORDER BY IslandStart ) - 1 AS GapEnd
               FROM     Level2
             )
    SELECT  GapStart ,
            GapEnd
    FROM    Level3
    WHERE   GapEnd IS NOT NULL;

--7-8.0 Set up
SET STATISTICS IO ON;
SET NOCOUNT ON;
GO
--7-8.1 Use LAG and LEAD
PRINT '7-8.1'
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        LAG(CAST(OrderDate AS DATE)) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS PrevOrderDate
FROM    Sales.SalesOrderHeader;
--7-8.2 Use Correlated Subquery
PRINT '7-8.2'
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        ( SELECT TOP ( 1 )
                    CAST(OrderDate AS DATE)
          FROM      Sales.SalesOrderHeader AS IQ
          WHERE     IQ.CustomerID = OQ.CustomerID
                    AND IQ.SalesOrderID < OQ.SalesOrderID
          ORDER BY  SalesOrderID
        ) AS PrevOrderDate
FROM    Sales.SalesOrderHeader AS OQ;
--7-8.3 Use OUTER APPLY
PRINT '7-8.3'
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        OA.PrevOrderDate
FROM    Sales.SalesOrderHeader AS OQ
        OUTER APPLY ( SELECT TOP ( 1 )
                                CAST(OrderDate AS DATE) AS PrevOrderDate
                      FROM      Sales.SalesOrderHeader AS IQ
                      WHERE     IQ.CustomerID = OQ.CustomerID
                                AND IQ.SalesOrderID < OQ.SalesOrderID
                      ORDER BY  SalesOrderID
                    ) AS OA;

--7-9.0 Set up
SET STATISTICS IO ON;
SET NOCOUNT ON;
GO
--7-9.1 A dynamic offset
DECLARE @Offset INT = 1;
SELECT  CustomerID ,
        SalesOrderID ,
        CAST(OrderDate AS DATE) AS OrderDate ,
        LAG(CAST(OrderDate AS DATE), @Offset) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS PrevOrderDate
FROM    Sales.SalesOrderHeader;

--7-10.0 Set up
SET STATISTICS IO ON;
SET NOCOUNT ON;
GO
--7-10.1 Default frame
PRINT '7-10.1'
SELECT  CustomerID ,
        SalesOrderID ,
        TotalDue ,
        FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS FirstOrderAmt
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
        SalesOrderID;
--7-10.2 ROWS
PRINT '7-10.2'
SELECT  CustomerID ,
        SalesOrderID ,
        TotalDue ,
        FIRST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ROWS UNBOUNDED PRECEDING ) AS FirstOrderAmt
FROM    Sales.SalesOrderHeader
ORDER BY CustomerID ,
SalesOrderID;
example-8
--8-1.1 Using PERCENT_RANK and CUME_DIST
SELECT  COUNT(*) NumberOfOrders ,
        MONTH(OrderDate) AS OrderMonth ,
        RANK() OVER ( ORDER BY COUNT(*) ) AS Ranking ,
        PERCENT_RANK() OVER ( ORDER BY COUNT(*) ) AS PercentRank ,
        CUME_DIST() OVER ( ORDER BY COUNT(*) ) AS CumeDist
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-2.1 Find median for the set
SELECT  COUNT(*) NumberOfOrders ,
        MONTH(OrderDate) AS orderMonth ,
        PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileCont ,
        PERCENTILE_DISC(.5) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileDisc
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-2.2 Return just the answer
SELECT DISTINCT
        PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileCont ,
        PERCENTILE_DISC(.5) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileDisc
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-3.1 Filter out January
SELECT DISTINCT
        PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileCont ,
        PERCENTILE_DISC(.5) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileDisc
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-02-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-4.1 Set up variables and table
DECLARE @score DECIMAL(5, 2)
DECLARE @count INT = 1;
CREATE TABLE #scores
    (
      StudentID INT IDENTITY ,
      Score DECIMAL(5, 2)
    );
--8-4.2 Loop to generate 1000 scores
WHILE @count <= 1000
    BEGIN
        SET @score = CAST(RAND() * 100 AS DECIMAL(5, 2));
        INSERT  INTO #scores
                ( Score )
        VALUES  ( @score );
        SET @count += 1;
    END;

--8-4.3 Return the score at the top 25%
SELECT DISTINCT
        PERCENTILE_DISC(.25) WITHIN GROUP ( ORDER BY Score DESC ) OVER ( ) AS Top25
FROM    #scores;

--8-5.1 Using 2005 functionality
SELECT  COUNT(*) NumberOfOrders ,
        MONTH(OrderDate) AS OrderMonth ,
        ( ( RANK() OVER ( ORDER BY COUNT(*) ) - 1 ) * 1.0 )
        / ( COUNT(*) OVER ( ) - 1 ) AS PercentRank ,
        ( RANK() OVER ( ORDER BY COUNT(*) ) * 1.0 ) / COUNT(*) OVER ( ) AS CumeDist
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-6.1 PERCENTILE_DISC
SELECT DISTINCT
        PERCENTILE_DISC(0.75) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentileDisc
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-6.2 Old method
WITH    Level1
          AS ( SELECT   COUNT(*) NumberOfOrders ,
                        ( ( RANK() OVER ( ORDER BY COUNT(*) ) - 1 ) * 1.0 )
                        / ( COUNT(*) OVER ( ) - 1 ) AS PercentRank
               FROM     Sales.SalesOrderHeader
               WHERE    OrderDate >= '2013-01-01'
                        AND OrderDate < '2014-01-01'
               GROUP BY MONTH(OrderDate)
             )
    SELECT TOP ( 1 )
            NumberOfOrders AS PercentileDisc
    FROM    Level1
    WHERE   Level1.PercentRank <= 0.75
    ORDER BY Level1.PercentRank DESC;

--8-7.1 PERCENTILE_CONT
SELECT DISTINCT
        PERCENTILE_CONT(0.75) WITHIN GROUP ( ORDER BY COUNT(*) )
OVER ( ) AS PercentCont
FROM    Sales.SalesOrderHeader
WHERE   OrderDate >= '2013-01-01'
        AND OrderDate < '2014-01-01'
GROUP BY MONTH(OrderDate);

--8-7.2 Using 2005 functionality
WITH    Level1
          AS ( SELECT   COUNT(*) NumberOfOrders ,
                        CAST(( RANK() OVER ( ORDER BY COUNT(*) ) - 1 ) AS FLOAT)
                        / ( COUNT(*) OVER ( ) - 1 ) AS PercentRank
               FROM     Sales.SalesOrderHeader
               WHERE    OrderDate >= '2013-01-01'
                        AND OrderDate < '2014-01-01'
               GROUP BY MONTH(OrderDate)
             ),
        Level2
          AS ( SELECT   NumberOfOrders ,
                        SIGN(PercentRank - 0.75) AS SGN ,
                        ROW_NUMBER() OVER ( PARTITION BY SIGN(PercentRank
                                                              - 0.75) ORDER BY ABS(PercentRank
                                                              - 0.75) ) AS rownumber
               FROM     Level1
             ),
        Level3
          AS ( SELECT   SUM(CASE WHEN SGN = 0 THEN NumberOfOrders
                            END) AS ExactRow ,
                        SUM(CASE WHEN SGN = -1 THEN NumberOfOrders
                            END) AS LowerRow ,
                        SUM(CASE WHEN SGN = 1 THEN NumberOfOrders
                            END) AS UpperRow
               FROM     Level2
               WHERE    rownumber = 1
             )
    SELECT  CASE WHEN ExactRow IS NOT NULL THEN ExactRow
                 ELSE UpperRow - ( UpperRow - LowerRow ) * 0.75
            END AS PercentCont
    FROM    Level3;

Leave a Reply

Post Navigation