|
CHAPTER 7
Beyond the Fundamentals of Querying
243
Solutions
This section provides solutions to the Chapter 7 exercises.
1
This exercise is very technical. It’s just a matter of being familiar with the syntax for window ranking
functions. Here’s the solution query, returning for each order both the rank and the dense rank, parti-
tioned by custid and ordered by qty.
SELECT custid, orderid, qty,
RANK() OVER(PARTITION BY custid ORDER BY qty) AS rnk,
DENSE_RANK() OVER(PARTITION BY custid ORDER BY qty) AS drnk
FROM dbo.Orders;
2
The window offset functions LAG and LEAD allow you to return an element from a previous and next
row, respectively, based on the indicated partitioning and ordering specification. In this exercise,
you need to perform the calculations within each customer’s orders, hence the window partitioning
should be based on custid. As for ordering, use orderdate as the first ordering column and orderid as
the tiebreaker. Here’s the complete solution query.
SELECT custid, orderid, qty,
qty - LAG(qty) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS diffprev,
qty - LEAD(qty) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS diffnext
FROM dbo.Orders;
This query is a good example that shows that you can mix detail elements from the row with win-
dow functions in the same expression.
3
Solving a pivoting problem is all about identifying the elements involved: the grouping element, the
spreading element, the aggregation element, and the aggregate function. After you identify the ele-
ments involved, you simply fit them into the “template” query for pivoting—whether it is the standard
solution or the solution using the native PIVOT operator.
In this exercise, the grouping element is the employee (empid), the spreading element is order
year (YEAR(orderdate)), and the aggregate function is COUNT; however, identifying the aggregation
element is not that straightforward. You want the COUNT aggregate function to count matching rows
and orders—you don’t really care which attribute it counts. In other words, you can use any attribute
that you want, as long as the attribute does not allow NULL marks, because aggregate functions
ignore NULL marks, and counting an attribute that allows NULL marks would result in an incorrect
count of the orders.
www.it-ebooks.info
244
Microsoft SQL Server 2012 T-SQL Fundamentals
If it doesn’t really matter which attribute you use as the input to the COUNT aggregate, why not
use the same attribute that you already use as the spreading element? In this case, you can use the
order year as both the spreading and aggregation element.
Now that you’ve identified all pivoting elements, you’re ready to write the complete solution.
Here’s the solution query without using the PIVOT operator.
USE TSQL2012;
SELECT empid,
COUNT(CASE WHEN orderyear = 2007 THEN orderyear END) AS cnt2007,
COUNT(CASE WHEN orderyear = 2008 THEN orderyear END) AS cnt2008,
COUNT(CASE WHEN orderyear = 2009 THEN orderyear END) AS cnt2009
FROM (SELECT empid, YEAR(orderdate) AS orderyear
FROM dbo.Orders) AS D
GROUP BY empid;
Recall that if you do not specify an ELSE clause in a CASE expression, an implicit ELSE NULL is as-
sumed. Thus the CASE expression produces non-NULL marks only for matching orders (orders placed
by the current employee in the current order year), and only those matching orders are taken into
consideration by the COUNT aggregate.
Notice that even though the standard solution does not require you to use a table expression, I
used one here to alias the YEAR(orderdate) expression as orderyear to avoid repeating the expression
YEAR(orderdate) multiple times in the outer query.
Here’s the solution query that uses the native PIVOT operator.
SELECT empid, [2007] AS cnt2007, [2008] AS cnt2008, [2009] AS cnt2009
FROM (SELECT empid, YEAR(orderdate) AS orderyear
FROM dbo.Orders) AS D
PIVOT(COUNT(orderyear)
FOR orderyear IN([2007], [2008], [2009])) AS P;
As you can see, it’s just a matter of fitting the pivoting elements in the right places.
If you prefer to use your own target column names and not the ones based on the actual data, of
course you can provide your own aliases in the SELECT list. In this query, I aliased the result columns
[2007], [2008], and [2009] as cnt2007, cnt2008, and cnt2009, respectively.
www.it-ebooks.info
Do'stlaringiz bilan baham: | 
