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