Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I showed how to write a simple recursive query recently that calculated an amount of money paid out each day. Now I want to extend this a bit to include a few math formulas.
Note: These aren’t terribly useful, but they are good practice for just writing recursive queries.
Fibonacci Series
I’m sure many of you have dealt with a Fibonacci series at some point in your life. This is a series where the current value is the sum of the two previous values. In other words,
term 3 = term 1 + term 2
term 4 = term 2 + term 3
etc.
The series starts with 0, 1 and goes from there. Can we do this in SQL? Sure.
Let’s start by looking at what we need. We need some counter, a current term, and a previous term. That’s 3 columns in our query. We start by building an anchor, which has the first two terms. The counter is n and the two terms are i and j.
— Anchor
select n = 1
, i = 0
, j = 1
Now we add the recursive part. In this case, the counter increases by 1. I only include the counter so I know when to stop. SQL Server has a finite size of various values, and we can exceed that without a way to stop.
The first value will be calculated from the current value + the next call. The current value will become the second one on the next last call, so we move that over. This gives us.
select counter + 1
, first = first + second
, second = first
With this, we can then add a WHERE clause to stop. I’ll stop at the first ten terms. Here’s the CTE.
WITH myFib (n, i, j)
AS
(
— anchor
SELECT ‘n’ = 1
, ‘i’ = 0
, ‘j’ = 1
UNION ALL
— recursive section
SELECT n + 1
, i + j
, i
FROM myFib
WHERE myFib.n < 10
)
SELECT
‘Level’ = myFib.n
, ‘Fibonacci’ = i FROM myFib
If we run this, we see:
We can extend this by altering the WHERE clause.
A Little Calculus
What about math functions? Have any of you worked with a series in calculus? If so, you might remember something like this:
This is a repetitive calculation, and should either converge or diverge. Can we implement this as a recursive CTE? Sure.
This one is really simple. I use the POWER() function in my recursive member to raise –1 to whatever counter I’m using for n. I then use a SUM() across all previous values in the outer query to get the sum.
WITH myPartialSum (n, s)
AS
(
SELECT ‘n’ = 1
, ‘s’ = POWER( -1, 0)
UNION ALL
SELECT n + 1
, POWER(-1, n)
FROM myPartialSum
WHERE n < 100
)
SELECT myPartialSum.n
,myPartialSum.s
, ‘partialsum’ = SUM(s) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM myPartialSum
Note: This series does not converge, as it alternates to infinity.
Neither of these is terribly useful, but they do allow some practice in writing CTEs that will recurse.
SQLNewBlogger
Implement some other series or sequence yourself and explain how it works.
