TSQL Tuesday #026 – Second Chances

This month’s T-SQL Tuesday #026 hosted by David Howard asked people to take on a wide open task, to write about any of the previous 25 topics.  Having so many to choose from should have made it easy, but instead it was harder.  I could have done something with each topic since it seems that reading the old blogs only sparked more new ideas.

The Future

Instead of going to the past, I decided to go to the future and write about the new features in SQL Server 2012 that will help us with aggregation, a subject that should be close on any BI developer’s mind.  I chose the topic because of T-SQL #016 by Jes Borland on Aggregations since there are going to be some interesting new functions to help us aggregate better.

Windows in T-SQL

The Windowing functions (which have nothing to do with Windows) in the upcoming version of SQL Server let us do in one query Analytic calculations that include sliding aggregations, standard deviations, and more.  This way we can compare averages, sums, and other numbers using preceding and current rows in a much cleaner, simpler way than we can today.

SELECT OrderDateKey, DailySales,
WeekAvgCurrent = AVG(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
WeekAvg1WeekAgo = AVG(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 13 PRECEDING AND 7 PRECEDING),
WeekDiff = AVG(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) – AVG(DailySales)    OVER (ORDER BY OrderDateKey ROWS BETWEEN 13 PRECEDING AND 7 PRECEDING),
WeekStdDev = STDEV(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
FROM (SELECT f.OrderDateKey, SUM(SalesAmount) as DailySales
                  FROM FactInternetSales f
                  inner join DimDate dd on dd.DateKey = f.OrderDateKey
                  where dd.CalendarYear = 2003
                  group by f.OrderDateKey) as DailyTable
order by OrderDateKey

The query produces this result from AdventureWorksDW. Only the first 12 rows are shown and I modified the column titles slightly for readability:

Order Date Key Daily Sales This Week Avg Last Week Avg Difference Week-to-Week This Week Std Dev
20030101 12444.75 12444.75 NULL NULL NULL
20030102 19703.48 16074.12 NULL NULL 5132.7
20030103 13519.61 15222.62 NULL NULL 3917.584
20030104 18628.97 16074.21 NULL NULL 3623.873
20030105 13497.29 15558.82 NULL NULL 3343.268
20030106 4363.125 13692.87 NULL NULL 5461.919
20030107 14623.17 13825.77 NULL NULL 4998.41
20030108 15733.28 14295.56 12444.75 1850.814 5001.517
20030109 18141.69 14072.45 16074.12 -2001.67 4748.503
20030110 15558.27 14363.69 15222.62 -858.929 4771.408
20030111 13976.53 13699.05 16074.21 -2375.15 4386.78
20030112 13254.69 13664.39 15558.82 -1894.43 4389.597

The results show you that we can get the average sales for the last 7 days, the sales for the 7 days before that, compare the two to get the difference between the last two weeks, and the standard deviation for the week.

All in one fairly simple straightforward query. Very powerful.

About these ads

2 responses

  1. Nice post, Carlos. I’m looking forward to learning more about the new windowing functions in 2012. Thanks for participating!

  2. [...] Carlos Bossy (blog | @carlosbossy) covers a topic I’m excited to learn more about: the new windowing functions in SQL 2012, specifically in [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 473 other followers

%d bloggers like this: