Sometimes I am amazed at the lenghts I have gone to in TSQL to construct things that fit in a few lines, once you know how. Today I was reading about window functions. They are excellent for the kind of grouping where you want to keep each row, but also include some totals or other things that relate to the group that that row belongs to. Things like a sale and the sale total for that category of items.
Previously I would have made a subquery that found the category totals and the joined the two tables, but using a window function you can do it like:
SELECT category, sale, sum(sale) OVER (PARTITION by category) as categorySale
It also makes running averages much much easier than some of the hack I remember doing before:
SELECT custid, freight, AVG(freight) OVER (PARTITION by custid ORDER BY OrderDate, orderid
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as movingaverage
FROM Sales.Orders
Previously I would have made a subquery that found the category totals and the joined the two tables, but using a window function you can do it like:
SELECT category, sale, sum(sale) OVER (PARTITION by category) as categorySale
It also makes running averages much much easier than some of the hack I remember doing before:
SELECT custid, freight, AVG(freight) OVER (PARTITION by custid ORDER BY OrderDate, orderid
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as movingaverage
FROM Sales.Orders
No comments:
Post a Comment