I use window functions a lot however; I often find myself having to remind myself on certain syntaxes or the different outputs between two functions. The following code snippet is used just as a reminder on what is available and is not a complete exhaustive list.
The following code consists of three different CTES. Here is the sample data. Notice rows 1 and 2 are duplicated as are 6,7 and 11,12.
The first snippet shows using the COUNT function with and without partitioning or framing.
WITH CTE AS
(
SELECT *
FROM ( VALUES
(1,1,CAST(10 AS MONEY)), (1,1,CAST(10 AS MONEY)), (1,2,CAST(20 AS MONEY)), (1,3,CAST(30 AS MONEY)), (1,4,CAST(40 AS MONEY)),
(2,1,CAST(11 AS MONEY)), (2,1,CAST(11 AS MONEY)), (2,2,CAST(21 AS MONEY)), (2,3,CAST(31 AS MONEY)), (2,4,CAST(41 AS MONEY)),
(3,1,CAST(12 AS MONEY)), (3,1,CAST(12 AS MONEY)), (3,2,CAST(22 AS MONEY)), (3,3,CAST(32 AS MONEY)), (3,4,CAST(42 AS MONEY))
) AS D (ACCID, TRANID, AMOUNT)
), CTE_COUNT AS
(
SELECT *,
COUNT(*) OVER() AS _Count,
COUNT(*) OVER(PARTITION BY ACCID) AS _Count_Part,
COUNT(*) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Count_Part_Order
FROM CTE
)
SELECT *
FROM CTE_COUNT
ORDER BY ACCID, TRANID;
The second code snippet shows the use of the different functions over the same partitioning and framing.
WITH CTE AS
(
SELECT *
FROM ( VALUES
(1,1,CAST(10 AS MONEY)), (1,1,CAST(10 AS MONEY)), (1,2,CAST(20 AS MONEY)), (1,3,CAST(30 AS MONEY)), (1,4,CAST(40 AS MONEY)),
(2,1,CAST(11 AS MONEY)), (2,1,CAST(11 AS MONEY)), (2,2,CAST(21 AS MONEY)), (2,3,CAST(31 AS MONEY)), (2,4,CAST(41 AS MONEY)),
(3,1,CAST(12 AS MONEY)), (3,1,CAST(12 AS MONEY)), (3,2,CAST(22 AS MONEY)), (3,3,CAST(32 AS MONEY)), (3,4,CAST(42 AS MONEY))
) AS D (ACCID, TRANID, AMOUNT)
), CTE_FUNCS AS
(
SELECT *,
COUNT(*) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Count,
ROW_NUMBER() OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Row_Num,
RANK() OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Rank,
DENSE_RANK() OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Dense_Rank,
PERCENT_RANK() OVER(PARTITION BY ACCID ORDER BY TRANID) AS _PCent_Rank,
NTILE(2) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _NTile2,
NTILE(4) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _NTile4,
FIRST_VALUE(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _First_Value,
LAST_VALUE(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Last_Value,
LAG(AMOUNT,1) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Lag_1_Value,
LAG(AMOUNT,2) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Lag_2_Value,
LEAD(AMOUNT,1) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Lead_1_Value,
LEAD(AMOUNT,2) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Lead_2_Value
FROM CTE
)
SELECT *
FROM CTE_FUNCS
ORDER BY ACCID, TRANID;
The third code snippet shows the use of different framing. Notice how ROW and RANGE produce different results when duplicate data exists.
WITH CTE AS
(
SELECT *
FROM ( VALUES
(1,1,CAST(10 AS MONEY)), (1,1,CAST(10 AS MONEY)), (1,2,CAST(20 AS MONEY)), (1,3,CAST(30 AS MONEY)), (1,4,CAST(40 AS MONEY)),
(2,1,CAST(11 AS MONEY)), (2,1,CAST(11 AS MONEY)), (2,2,CAST(21 AS MONEY)), (2,3,CAST(31 AS MONEY)), (2,4,CAST(41 AS MONEY)),
(3,1,CAST(12 AS MONEY)), (3,1,CAST(12 AS MONEY)), (3,2,CAST(22 AS MONEY)), (3,3,CAST(32 AS MONEY)), (3,4,CAST(42 AS MONEY))
) AS D (ACCID, TRANID, AMOUNT)
), CTE_FRAME AS
(
SELECT *,
SUM(AMOUNT) OVER() AS _All, -- SUM All Rows
SUM(AMOUNT) OVER(PARTITION BY ACCID) AS _Part, -- SUM All Rows within Partitions, Whole Frame
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID) AS _Part_Ord, -- SUM All Rows within Partitions, Framed by TRANID (UP to CR)
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS _Part_Ord_Row1, -- SUM All Rows within Partitions, Framed by TRANID (UP to CR)
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS _Part_Ord_Range1,
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS _Part_Ord_Row2, -- SUM All Rows within Partitions, Framed by TRANID (UP to UF)
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS _Part_Ord_Range2,
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS _Part_Ord_Row3, -- SUM All Rows within Partitions, Framed by TRANID (CR to CR)
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS _Part_Ord_Range3,
SUM(AMOUNT) OVER(PARTITION BY ACCID ORDER BY TRANID ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS _Part_Ord_Row4 -- SUM All Rows within Partitions, Framed by TRANID (2P to 2F)
FROM CTE
)
SELECT *
FROM CTE_FRAME
ORDER BY ACCID, TRANID;
This is not supposed to be a lesson in window functions, more a refresher.
Denham Coder 