Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 221

SELECT orderid, custid, val, 

  100. * val / SUM(val) OVER() AS pctall, 

  100. * val / SUM(val) OVER(PARTITION BY custid) AS pctcust 

FROM Sales.OrderValues;

This query returns the following output, shown here in abbreviated form.

orderid      custid val         pctall                        pctcust 

------------ ------ ----------- ----------------------------- ----------------------------- 

10643        1      814.50      0.0643470029014691672941      19.0615492628130119354083 

10692        1      878.00      0.0693636200705830925528      20.5476246197051252047741 

10702        1      330.00      0.0260706089103558320528      7.7229113035338169904048 

10835        1      845.80      0.0668197606556938265161      19.7940556985724315469225 

10952        1      471.20      0.0372256694501808123130      11.0273812309852562602387 

11011        1      933.50      0.0737482224782338461253      21.8464778843903580622513 

10926        2      514.40      0.0406385491620819394181      36.6655974910011048148544 

10759        2      320.00      0.0252805904585268674452      22.8090808653195053280587 

10625        2      479.75      0.0379011352264945770526      34.1958017035532271285505 

10308        2      88.80       0.0070153638522412057160      6.3295199401261627285362 

10365        3      403.20      0.0318535439777438529809      5.7403352515240647040566 

... 

 

(830 row(s) affected) 

SQL Server 2012 adds support for window ordering and framing for aggregate functions. This 

allows for more sophisticated calculations such as running and moving aggregates, YTD calculations, 

and others. Let’s re-examine the query I used in the introduction to the section about window func-

tions.

SELECT empid, ordermonth, val, 

  SUM(val) OVER(PARTITION BY empid 

                ORDER BY ordermonth 

                ROWS BETWEEN UNBOUNDED PRECEDING 

                         AND CURRENT ROW) AS runval 

FROM Sales.EmpOrders;

This query generates the following output (abbreviated).

empid  ordermonth  val      runval 

------ ----------- -------- ---------- 

1      2006-07-01  1614.88  1614.88 

1      2006-08-01  5555.90  7170.78 

1      2006-09-01  6651.00  13821.78 

1      2006-10-01  3933.18  17754.96 

1      2006-11-01  9562.65  27317.61 

... 

2      2006-07-01  1176.00  1176.00 

2      2006-08-01  1814.00  2990.00 

2      2006-09-01  2950.80  5940.80 

2      2006-10-01  5164.00  11104.80 

2      2006-11-01  4614.58  15719.38 

... 

 

(192 row(s) affected)

www.it-ebooks.info

---

222  

Microsoft SQL Server 2012 T-SQL Fundamentals

Each row in the EmpOrders view holds information about the order activity for each employee and 

month. The query returns for each employee and month the monthly total, plus the running-total 

values from the beginning of the employee’s activity through the current month. To apply the calcula-

tion to each employee independently, you partition the window by empid. Then you define ordering 

based on ordermonth, giving meaning to the window frame extent: ROWS BETWEEN UNBOUNDED 

PRECEDING AND CURRENT ROW. This frame means “all activity from the beginning of the partition 

through the current month.”

SQL Server supports other delimiters for the ROWS window frame unit. You can indicate an off-

set back from the current row as well as an offset forward. For example, to capture all rows from 

two rows before the current row and through one row ahead, you would use ROWS BETWEEN 2 

 PRECEDING AND 1 FOLLOWING. Also, if you want no upper bound, you can use UNBOUNDED 

FOLLOWING. SQL Server also supports a window frame unit called RANGE, but in a very limited 

form. This option is beyond the scope of this book, but I will say that at least with the current imple-

mentation, you should avoid it.

Because window functions are so profound and have so many practical uses, I urge you to invest 

the time and effort to get to know the concept well. The investment is worth its weight in gold.