Note: this article is published on 06/19/2024.
Extend the article: SQL - Interesting Queries as a series of articles:
Introduction
SQL, the Structural Query Language, seems simple, but often with some tricks in them. This article will discuss a new issue.
The content of this article:
- Introduction
- Question
- Setup Table and Initial Data
- Answers
- Step 1 --- make a daily everage
- Step 2 --- The rolling average algorithm
- Step 3:--- Get Rolling Average by SubQueries
- Step 4:--- Get Rolling Average by a temp table
Question:
We have a table with three columns: Date, Currency, Rate, we need to get a output as
- Date
- Currency (Daily everage)
- Rolling Average of the three days including the current day and two previous days
Set up Table and initial Data
Table RateCal:
USE [Test]
GO
/****** Object: Table [dbo].[RateCal] Script Date: 7/19/2024 2:56:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RateCal](
[Date] [date] NOT NULL,
[Currency] [money] NOT NULL,
[Rate] [float] NOT NULL,
CONSTRAINT [PK_RateCal] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[Currency] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
The Table Structure:
Test Data:
Answers:
Step 1 --- make a daily everage
We realize the records could be more than one during a day, so the first step is to make daily everage:
We can eazily verify the result is correct, say, for 1/16, there are three values: 11.2, 11.3, 11.4, the everage is 11.3 that is correct.
Step 2 --- The rolling average algorithm
Rolling Average, also known as a moving average, is a statistical calculation used to analyze data over a sequence of consecutive periods.
For the given question, we have made the daily average, then the rolling average will be against the new created daily average that could be a subquery. To make the analysis simple, we make another data table, to simulate the rolling everage logic first and independently. To do this, make a simple table: RateCal1 as
The data is as simple as
Now we make a rolling everage:
We can eazily verify the result is correct, say, for 1/16, the rolling everage value is 14.6, that is calculated from the three records starting from 1/16 plus the two previous, that are 13.6, 14.5, and 15.7, respectively. The everage is just 14.6.
Logic analysis:
SELECT A.date, avg(B.rate)
FROM dbo.RateCal1 A
INNER Join dbo.RateCal1 as B
On B.date between dateadd(day, -2, A.Date) and A.Date
Group by A.Date
Examine the code, we made a self join for the Table RateCal1, For each row in our table, we join every row that was within the past 3 days and take the average.
Step 3:--- Get Rolling Average by SubQueries
We combine the Step 1 and Step 2, we have the code:
SELECT A.Date, AVG(B.AvgRate)
FROM
( -- subQuery
SELECT TOP 100 percent [Date]
,AVG([Currency]) as AvgCurrency
,AVG([Rate]) as AvgRate
FROM [Test].[dbo].[RateCal]
GROUP BY [Date]
ORDER BY [Date]
) A
INNER Join
( -- the same subQuery
SELECT TOP 100 percent [Date]
,AVG([Currency]) as AvgCurrency
,AVG([Rate]) as AvgRate
FROM [Test].[dbo].[RateCal]
GROUP BY [Date]
ORDER BY [Date]
) B
On B.date between dateadd(day, -2, A.Date) and A.Date
Group by A.Date
Although the code is a little bit ugly because one subQuery is repeated twice (A and B), the result is correct:
Note:
There are some details about SubQuery, we will discuss in another article.
Step 4:--- Get Rolling Average by a temp table
We combine the Step 1 and Step 2, with a temp table:
DROP TABLE IF EXISTS #Temp
SELECT
[Date]
,AVG([Currency]) as AvgCurrency
,AVG([Rate]) as AvgRate
INTO #Temp
FROM [Test].[dbo].[RateCal]
GROUP BY [Date]
ORDER BY [Date]
SELECT A.date, avg(B.AvgRate)
FROM #Temp A
INNER Join #Temp as B
On B.date between dateadd(day, -2, A.Date) and A.Date
Group by A.Date
The result is the same, but the coding is more elegent:
References:
Join the conversation! Your thoughts help the community grow.