Analytical Window Functions

In the previous posts we covered the ranking and aggregate window functions and this time we’ll be finishing the series covering the Analytic functions, seeing what an alternative to Window Functions might look like, and then wrapping up what we’ve covered in this series of posts.

As with our previous examples we’ll set up our sample data the same way:

CREATE TABLE #QuarterlySales (
	FinancialYear CHAR(6),
	FinancialQuarter CHAR(6),
	QuarterlySales INT
);

INSERT INTO #QuarterlySales
VALUES ('FY2020', '2020Q3', 1), ('FY2020', '2020Q4', 2),
	('FY2021', '2021Q1', 4), ('FY2021', '2021Q2', 8),
	('FY2021', '2021Q3', 16), ('FY2021', '2021Q4', 32),
	('FY2022', '2022Q1', 64), ('FY2022', '2022Q2', 128),
	('FY2022', '2022Q3', 256), ('FY2022', '2022Q4', 512),
	('FY2023', '2023Q1', 0);

What are analytic functions?

We’ve already looked at the aggregate functions available and these can be particularly useful when reviewing and analysing data. When it comes to providing meaning to the data in a way that supports decision making – that’s where these functions are targeted. Microsoft describes them as follows:

Use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

You can find more details for the functions which come under this category in the documentation, and they include the following:

• CUME_DIST – calculating the cumulative distribution
• FIRST_VALUE – the first value in a sequence of records
• LAG – retrieve a value from a prior row in the result set
• LAST_VALUE – the last value in a sequence of records
• LEAD – retrieve a value from a following row in the result set

In practice I’ve seen the terms analysis and analytics used interchangeably and typically folks would tend to focus on the aggregate functions – myself I’ve also not had much call for these in the past but there are a couple I’ve had particular use for.

Lagging and leading

The two of these which I particularly wanted to demonstrate are the LAG and LEAD functions. The syntax for these is the same as we’ve seen previously where we’d used the PARTITION BY and ORDER BY clauses applied. These are used to retrieve the value for a field but from a row which is either prior to or following the record in the results. Lets show that in a quick example with our sample data:

SELECT
	*,
	LAG(QuarterlySales, 1, 0) OVER (
		ORDER BY FinancialQuarter) [PriorQuarter]
FROM
	#QuarterlySales;

Here I’ve chosen not to use the PARTITION clause as I wanted to focus on how the function works with the data. You’ll see that the LAG function is returning the value from the previous record so in this example we can show changes in sales volume each quarter. There are 3 parameters needed with the LAG function and these would also be used for the LEAD function too. They are as follows:

• Expression – QuarterlySales – this is the field we’re choosing to show from the prior (or following) records
• Offset – 1 – how many records behind (or ahead in the case of LEAD) the function should look for the expression value
• Default – 0 – the default value to be returned if there is no result from the function, for example the first record

A note about the offset in that it can’t be a negative number so you can’t perform a LAG with offset of -1, you should be performing a LEAD with an offset of 1.

What about alternatives?

One point we haven’t yet touch on in this series was the question of – what if you don’t have access to window functions due to the version of SQL Server you’re running?

The functions have been around in SQL Server for over a decade so I’m sure the vast majority of us have the capability. If however you’ve had the pleasure(?) of trying to achieve the same goals as window functions in earlier versions where they aren’t available then you’ll have likely had to hand-craft your way around these.

Its certainly possible to do that with some creative joins depending on which function you’re trying to replicate. It might not be the prettiest but its possible – lets see an example of how we might do this in the case of the ROW_NUMBER function:

/* With a windowing function */
SELECT *, ROW_NUMBER() OVER (
	PARTITION BY FinancialYear 
	ORDER BY FinancialQuarter) AS [QuarterlyOrder]
FROM #QuarterlySales;

/* Hand crafted, with love ❤ */
SELECT
	a.FinancialYear,
	a.FinancialQuarter,
	a.QuarterlySales,
	COUNT(b.FinancialQuarter) AS [QuarterlyOrder]
FROM
	#QuarterlySales a
	LEFT JOIN #QuarterlySales b
		ON a.FinancialYear = b.FinancialYear
		AND a.FinancialQuarter >= b.FinancialQuarter
GROUP BY
	a.FinancialYear,
	a.FinancialQuarter,
	a.QuarterlySales;

We can see the complexities which are added just by trying to get a row number for each record. We’ve managed to get our result with a self-join. The data has been limited with the join on the FinancialYear effectively creating our partition. We then use the range on our FinancialQuarter as a replacement for the ordering in our window function to provide the number of records behind the one we’re looking at and provide a row count.

The same approach could be taken for a year to date sales figure. It could also be adapted to cater for other functions, although the implementation would differ slightly depending on the function and potentially the data types being used. This type of code can be very bespoke so its worth investing a bit of time into documenting it if you’re backed into the corner of writing it.

Wrap Up

Through this series of posts we’ve covered a few areas around Window Functions. We started with an introduction where we worked through the ranking functions with the use of the ORDER BY and PARTITION BY clauses as a foundation. We then moved onto the aggregate functions, looked at how they behave differently with an ORDER BY and also looked at the ROWS clause. We’ve then wrapped up with the analytic functions and taken a look at what alternatives to the functions may look like.

I think the biggest takeaway for me with Window Functions is the breadth of the functionality available and how much more accessible these would be compared to implementing them yourself. On top of this they’re implemented in very consistent ways so other than understanding the operation they’re performing there’s very little overhead in documenting these unlike if you were crafting your own version of them.

These are one of my favourite under-used features in SQL Server which is why I wanted to take the time to cover them over a few posts. I hope its been useful to dive into them and they can save you some time and effort in your own adventures.

In this series

• Window Functions and Ranking – an introduction to Window Functions and Ranking
• Aggregate Window Functions – Aggregate functions and Row clauses
• Analytical Window Functions – Analytical functions, alternatives, and series wrap up