In this demo,
we're going to use window functions to perform simple
time‑series forecasting on the stock market data.
In short,
time‑series forecasting means looking at past data to predict future values.
There are numerous methods for doing this,
but in this demo,
we're going to look at a simple method, the simple moving averages.
A simple moving average, or SMA, is a calculation that uses the mean of a
given metric over a specified time interval in the past.
The simple moving average is not able to predict the
exact future value of a metric.
For this, you need to use more complex methods; however,
you are still able to gain valuable insights from it. The simple moving
averages are often used to determine trend direction.
If the SMA is moving up, the trend is up.
If the SMA is moving down, the trend is down.
You can forecast the long‑term trend by including a large number
of past values in the calculation of the SMA,
and you can forecast intermediate or short‑term trends
by using a smaller number of values.
In order to be able to perform this calculation,
you need a date column and a column with the values you want to predict.
Going to SQL Server Management Studio,
we're going to use the date and the closing price of stocks.
The first step is to calculate an average of the
closing price for a time period.
Let's do it for the last 10 days.
I'm using the AVG function as a window,
and we'll use the ROWS clause, ROWS BETWEEN 9 PRECEDING and CURRENT ROW.
These are the results.
Looking directly at this result doesn't provide much direction, but
let's try to visualize it in a Power BI report.
In Power BI, I will connect to the StockMarket database and
use the query I just wrote to load the data.
I will make some changes to this report just to make everything clearer.
First,
I want to add the symbol as a filter and then a line
chart with the date on the X‑axis and the closing price
and moving average on the other.
I will switch the Type from Continuous to
Categorical, and let's look at the data.
I think the trend is noticeable for the Google stocks.
Let me quickly make some changes to the line colors.
The blue line is the actual closing price,
while the red line is the moving average.
While the blue has some fluctuations,
you are able to spot the trend by looking at the red line,
and you see that the trend is ascending.
However,
this will not guarantee that the next day's closing
price will be higher than today's.
So this method does not make accurate predictions,
but it's a simple way to look at the trend of a metric.
And using window functions,
you can do this easily without writing complex SQL queries.
Well, this is the end of this module and the end of this course.
Let's do a quick summary of what we discussed.
We talked about what window functions are and how people use them in real life.
Then, you looked at some demos in which you learned
to create benchmarks from data, run the data based on different conditions,
work with values from the previous and subsequent row,
find the first and last value from a subset of data, and use it in calculations.
Afterwards,
you learned the importance of framing and how you can use ROWS or RANGE to
implement it in the OVER clause of a window function.
And we actually got to put some frames to the windows in demos
that calculated running totals and used simple moving averages
to predict stock market trends.
I hope this short course was useful to you and that you gained some insights on how to use window functions in SQL Server.