This post is another example of aggregate window functions. This time we are going to use some Toronto Raptors data. To super-simplify the data, we will only look at the first two games of only two players: DeMar DeRozan and Kyle Lowry. Also, we will only look at the number of points they got in those two games, not at minutes played, assists, rebounds, fouls and so on. Once we have defined the queries properly, we can add back in the full data, although we won’t show that here, for brevity.

One-to-Many

So customers place orders that have dollar values, over time. One customer can place multiple orders. Basketball players play many games and get points, over time. The data is not the same when comparing customers and their orders and basketball players and their points, but the queries will look identical except for the column names and table names. The queries will be very similar because in both cases we are working with a one-to-many relationship between tables.

I took the source data and created a view called [First2GamesPTSDerozenLowry]. When we run a basic SELECT query against the data we get the following results. Below is the data we are working with.

Let’s use a window function to get the percentages. We want the percentages. Below is our query.

SELECT [Player],[PTS],[GameDate],
     CAST(100 * PTS / Sum(PTS) OVER() AS NUMERIC(5,2)) AS pctPTSAll,
	 CAST (100 * PTS / Sum(PTS) OVER(PARTITION BY Player) AS NUMERIC(5,2)) AS pctPTSPlayer
FROM [First2GamesPTSDerozenLowry]
ORDER BY Player, GameDate

Below is our output in SSMS. There is a problem however. They percentages are being rounded and therefore do not add up to 100.

SELECT [Player],[PTS],[GameDate],
     CAST ((100 * PTS) AS Numeric(7,2)) / cast(Sum(PTS) OVER() AS Numeric(7,2)) AS pctPTSAll,
	 CAST ((100 * PTS) AS Numeric(7,2)) / cast(Sum(PTS) OVER(PARTITION BY Player) AS NUMERIC(7,2)) AS pctPTSPlayer
FROM [dbo].[First2GamesPTSDerozenLowry]
ORDER BY Player, GameDate

Below are the results in SSMS of the above query.

Running Totals

Suppose we need to show the running totals for the points.

select Player,PTS,GameDate,
  sum(PTS) over(partition by Player
     order by GameDate
	 rows unbounded preceding) as runningPts
  from [dbo].[First2GamesPTSDerozenLowry]

Below are the results of the above query.

We could go back and show percents also, as in the following query.

select Player,GameDate,PTS,
   sum(PTS) over(partition by Player order by GameDate rows between unbounded preceding and current row) as runningPts,
   cast ((100 * PTS) AS Numeric(7,2)) / cast(Sum(PTS) OVER(PARTITION BY Player) AS NUMERIC(7,2)) AS pctPTSPlayer
from [dbo].[First2GamesPTSDerozenLowry]
order by Player, GameDate 

Below are the results of the above query.

Since the above form of rows between unbounded preceding and current row is so common there is a short form of that: rows unbounded preceding. There is a different way to achieve the same results with a query using a join and grouping, however it is not nearly as elegant and efficient.