T-SQL – Windowing

Windowing functions calculate result for every row given in OVER clause. Rows in OVER clause are called ‘rows window’. Calculation is performed for each such group.

Aggregating

SELECT 
custid, --customer
val, -- single order's value
SUM(val) OVER() AS totalSum, -- sum for all customers together
SUM(val) OVER (PARTITION BY custid) AS customerSum, -- sum for each customer
val / SUM(val) OVER (PARTITION BY custid) * 100 --percentage value per customer
FROM Sales.OrderValues

Aggregating with Framing – the same as aggregating, but now rows order matters. For example you can find orders’ values for single customer. What’s more you can order orders by date and show total sum of orders for this customer since first day to now (current row).

SELECT 
custid, --customer
val, -- single order's value
SUM(val) OVER 
(
PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS sumToCurrent, -- sum for each customer from first value to current one
SUM(val) OVER 
(
PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS sumPreviousAndThis, -- sum for each customer from previous one value to current one 
SUM(val) OVER 
(
PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
) AS sumThisAnd2Next -- sum for each customer from current value and two next to current one 
FROM Sales.OrderValues

Ranking – allows to add numbering to rows in specific order.

Row_number() – the unique numbering. Example below creates two columns:
totalNumber – numbering from 1 to n for all rows;
numberPerCustomer – numbering from 1 to n grouped per customer.

SELECT 
ROW_NUMBER() OVER (ORDER BY custid, orderdate) AS totalNumber,
ROW_NUMBER() OVER (PARTITION BY custid ORDER BY custid, orderdate) AS numberPerCustomer,
custid,
val,
orderdate
FROM Sales.OrderValues
ORDER BY custid, orderdate

Rank() – numbering where duplicates can occur. If two rows have the same value, both get the same number (i.e. 1) and the next row gets number 3.

Dense_rank() – as above, but when there are two rows with the same number (i.e. 1) the next one gets number 2.

SELECT 
ROW_NUMBER() OVER (ORDER BY orderdate) AS totalNumber,
RANK() OVER (ORDER BY orderdate) AS totalNumber1,
DENSE_RANK() OVER (ORDER BY orderdate) AS totalNumber2,
custid,
val,
orderdate
FROM Sales.OrderValues
ORDER BY orderdate

Ntile() – splits rows into n groups. In every group all numbers get the same number. Each group have different number.

SELECT 
NTILE(3) OVER(ORDER BY orderdate),
custid,
val,
orderdate
FROM Sales.OrderValues
WHERE custid < 3
ORDER BY orderdate

Offset – allows to find first, previous, next, last values relative to current row in window.

SELECT 
custid,
FIRST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) as [o_first], -- first order's value for customer
LAG(val, 1) OVER(PARTITION BY custid ORDER BY orderdate, orderid) as [o-1], -- previous order's value for customer
val as [o], -- current order's value
LEAD(val, 1) OVER(PARTITION BY custid ORDER BY orderdate, orderid) as [o+1], -- next order's value for customer
LEAD(val, 2) OVER(PARTITION BY custid ORDER BY orderdate, orderid) as [o+2], -- one after next order's value for customer
LAST_VALUE(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) as [o_last], -- last order's value for customer
orderdate
FROM Sales.OrderValues
ORDER BY custid, orderdate, orderid
Advertisements

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