36、时间序列数据的 SQL 分析技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/redis7keeper/article/details/153172397

时间序列数据的 SQL 分析技巧

1. 引言

在数据分析领域，对不同时间范围内的数据进行比较和对比是一项关键技能。通过掌握相关的 SQL 技巧，我们可以轻松地对包含时间元素的数据进行切片和切块分析，进而跟踪销售、利润等指标随时间的演变。接下来，我们将深入探讨几种常见的时间序列数据分析方法。

2. 按时间分析数据的关键主题

以下是我们要探讨的几个关键主题：
- 计算年初至今的累计值
- 提取前一个月的数据
- 使用派生表比较当前年份与前一年的数据
- 计算一年中每个工作日的总销售额
- 计算两个日期之间的周末天数

3. 年初至今的累计值计算

销售经理通常关注按品牌统计的年度累计销售额，以便及时采取必要的纠正措施。以下是计算 2017 年年初至今累计销售额的 SQL 代码：

SELECT     MakeName, SUM(SalePrice) AS CumulativeSalesYTD
FROM       allsales
WHERE      SaleDate BETWEEN 
               DATE_TRUNC('YEAR', CURDATE())
               AND CURDATE()
GROUP BY   MakeName
ORDER BY   MakeName ASC;

工作原理 ：此查询的核心在于 WHERE 子句，通过 BETWEEN...AND 操作符指定日期范围。下限是当前年份的 1 月 1 日，使用 DATE_TRUNC('YEAR', CURDATE()) 函数获取；上限是查询执行的日期，由 CURDATE() 函数确定。
DATE_TRUNC() 函数的间隔类型 ：该函数可用于获取指定时间周期的起始日期或时间，支持的间隔类型如下表所示：
| 间隔 | 说明 |
| ---- | ---- |
| YEAR | 给出年份的起始日期，也可用 YYYY 或 YY 指定 |
| QUARTER | 给出季度的起始日期 |
| MONTH | 给出月份的第一天，也可用 MON 或 MM 指定 |
| WEEK | 给出周的起始日期 |
| DAY | 去除日期时间中的时间部分 |
| HOUR | 去除分钟和秒 |
| MINUTE | 去除秒 |
| SECOND | 去除秒的小数部分 |
| MILLISECOND | 将微秒置为零 |
| MICROSECOND | 保持不变 |
注意事项 ：
- 查询结果会因执行日期而异。
- 若想获取当前年份的 1 月 1 日，可使用 SELECT CONCAT(YEAR(CURDATE()), "-01-01") 。
- 使用 BETWEEN...AND 时，下限必须放在 BETWEEN 之后，上限放在 AND 之后。
- 也可使用 >= 和 <= 替代 BETWEEN...AND ，如 WHERE SaleDate >= DATE_TRUNC('YEAR', CURDATE()) AND SaleDate <= CURDATE() 。
- 可使用 TRUNC() 函数替代 DATE_TRUNC() ，如 TRUNC(CURDATE(), 'YEAR') ，但该函数仅支持 YEAR、QUARTER、MONTH 和 WEEK 作为日期元素。

4. 提取前一个月的数据

首席财务官通常希望了解本月采购与上月数据的对比情况。以下是提取前一个月按品牌统计的采购数据的 SQL 代码：

SELECT     MakeName, SUM(Cost) AS TotalCost
FROM       make AS MK JOIN model AS MD 
           ON MK.MakeID = MD.MakeID
JOIN       stock AS ST ON ST.ModelID = MD.ModelID
WHERE      DateBought BETWEEN TRUNC(CURDATE(), 'MONTH') 
           - INTERVAL 1 MONTH
           AND
           LAST_DAY(CURDATE()) - INTERVAL 1 MONTH 
GROUP BY   MakeName;

工作原理 ：此查询使用 TRUNC() 函数获取本月的起始日期， LAST_DAY() 函数获取本月的最后一天。 WHERE 子句使用 INTERVAL 函数简化日期和时间计算，将整个前一个月作为日期范围。
INTERVAL 函数的使用步骤 ：
1. 输入算术运算符（如减号）。
2. 添加关键字 INTERVAL 和一个空格。
3. 添加要减去的间隔数。
4. 定义间隔类型（如月份）。
INTERVAL 函数支持的间隔类型 ：
| 间隔 | 说明 |
| ---- | ---- |
| YEAR | 指定年间隔 |
| MONTH | 指定月间隔 |
| DAY | 指定日间隔 |
| HOUR | 指定小时间隔 |
| MINUTE | 指定分钟间隔 |
| SECOND | 指定秒间隔 |
| MILLISECOND | 指定毫秒间隔 |
| MICROSECOND | 指定微秒间隔 |
注意事项 ：
- INTERVAL 函数必须应用于日期或日期时间字段。
- 也可使用其他方法计算月份的第一天和最后一天，但通常较为复杂。
- 若要测试间隔计算，可使用 SELECT CURDATE() + INTERVAL 1 MONTH 。

5. 使用派生表比较当前年份与前一年的数据

首席财务官认为季节性变化能提供有价值的信息，因此需要比较指定年份和前一年同一月份按颜色统计的平均销售额。以下是实现该功能的 SQL 代码：

SELECT     SA.Color, AVG(SA.TotalSalePrice) AS AverageMonthSales
           ,MIN(SQ.AveragePreviousMonthSales) AS AveragePreviousMonthSales
FROM       allsales AS SA
LEFT OUTER JOIN 
           (
            SELECT     Color, AVG(TotalSalePrice) 
                       AS AveragePreviousMonthSales
            FROM       allsales
            WHERE      YEAR(SaleDate) = YEAR(TO_DATE('2018-06-01')) - 1
                       AND MONTH(SaleDate) = MONTH(TO_DATE('2018-06-01'))
            GROUP BY   Color
           ) SQ   
           ON SQ.Color = SA.Color
WHERE      YEAR(SA.SaleDate) = YEAR(TO_DATE('2018-06-01'))
           AND MONTH(SA.SaleDate) = MONTH(TO_DATE('2018-06-01'))
GROUP BY   SA.Color;

工作原理 ：此查询分为两部分：
- 派生表：计算前一年同一月份按颜色统计的销售额。
- 外部查询：计算当前月份和年份按颜色统计的销售额。
两部分通过 LEFT JOIN 连接，确保显示当前年份销售的所有颜色。
注意事项 ：
- 前一个月未销售的颜色会返回 NULL 值。
- 可使用 CURDATE() 替代 TO_DATE() 函数，以显示当前年份和前一年的销售数据。
- 由于外部查询结果是聚合的，需要将派生表返回的 AveragePreviousMonthSales 字段包装在聚合函数中，如 MAX() 或 MIN() 。
- 也可使用 DATE_SUB 和 INTERVAL 技术让派生表仅返回前一年的数据。
- 可使用 LEFT OUTER JOIN 替代 LEFT JOIN ，提醒自己使用的是外部连接。

6. 计算一年中每个工作日的总销售额

销售经理想了解某些工作日是否比其他工作日更适合销售。以下是计算 2018 年每个工作日（不包括周末）总销售额的 SQL 代码：

SELECT     DAYOFYEAR(SaleDate) AS DayNumber
          ,SUM(SalePrice) AS SalePrice
          ,DATE_FORMAT(SaleDate, 'EEEE') AS Weekday
FROM      allsales
WHERE     YEAR(SaleDate) = 2018
          AND WEEKDAY(SaleDate) NOT IN (5,6)
GROUP BY  DAYOFYEAR(SaleDate)
          ,DATE_FORMAT(SaleDate, 'EEEE')
ORDER BY  DayNumber

工作原理 ：
- 选择：选择一年中的日期、总销售额和完整的星期几。
- 过滤：过滤出周一至周五的工作日。
- 聚合：按完整的星期几和一年中的日期进行聚合。

7. 计算两个日期之间的周末天数

人力资源部门在进行更复杂的员工部署分析之前，想了解 2018 年 3 月和 4 月的周末天数。以下是实现该功能的 SQL 代码：

WITH TallyTable_CTE
AS
(
SELECT     ROW_NUMBER() OVER (ORDER BY StockCode) 
AS Nm
FROM       stock
ORDER BY   Nm
LIMIT      90
)
,WeekendList_CTE
AS
(
SELECT       DATE_ADD('2018-03-01', Nm - 1) AS 
WeekdayDate
FROM         TallyTable_CTE
WHERE        DAYOFWEEK(DATE_ADD('2018-03-01', Nm - 1))
                  IN (5,6)
             AND Nm <= DATEDIFF('2018-04-30', '2018-03-01')
)
SELECT    COUNT(*) AS WeekendDays FROM 
WeekendList_CTE;

工作原理 ：
1. 创建一个计数公共表表达式（CTE），提供从 1 开始的连续数字列表。
2. 创建第二个 CTE，使用 DATE_ADD() 函数生成日期列表，并过滤出星期六和星期日。
3. 最后，计算剩余记录的数量，即周末天数。

通过掌握这些 SQL 技巧，我们可以更高效地对时间序列数据进行分析，为决策提供有力支持。在实际应用中，根据具体需求灵活运用这些方法，将有助于挖掘数据背后的价值。

8. 时间序列数据分析技巧总结

在前面的内容中，我们介绍了多种时间序列数据的 SQL 分析技巧，下面对这些技巧进行总结：
| 分析类型 | 关键函数和操作 | 代码示例 |
| ---- | ---- | ---- |
| 年初至今累计值计算 | DATE_TRUNC() 、 CURDATE() 、 BETWEEN...AND | sql SELECT MakeName, SUM(SalePrice) AS CumulativeSalesYTD FROM allsales WHERE SaleDate BETWEEN DATE_TRUNC('YEAR', CURDATE()) AND CURDATE() GROUP BY MakeName ORDER BY MakeName ASC; |
| 提取前一个月的数据 | TRUNC() 、 LAST_DAY() 、 INTERVAL | sql SELECT MakeName, SUM(Cost) AS TotalCost FROM make AS MK JOIN model AS MD ON MK.MakeID = MD.MakeID JOIN stock AS ST ON ST.ModelID = MD.ModelID WHERE DateBought BETWEEN TRUNC(CURDATE(), 'MONTH') - INTERVAL 1 MONTH AND LAST_DAY(CURDATE()) - INTERVAL 1 MONTH GROUP BY MakeName; |
| 比较当前年份与前一年的数据 | 派生表、 LEFT JOIN 、 YEAR() 、 MONTH() | sql SELECT SA.Color, AVG(SA.TotalSalePrice) AS AverageMonthSales,MIN(SQ.AveragePreviousMonthSales) AS AveragePreviousMonthSales FROM allsales AS SA LEFT OUTER JOIN ( SELECT Color, AVG(TotalSalePrice) AS AveragePreviousMonthSales FROM allsales WHERE YEAR(SaleDate) = YEAR(TO_DATE('2018-06-01')) - 1 AND MONTH(SaleDate) = MONTH(TO_DATE('2018-06-01')) GROUP BY Color ) SQ ON SQ.Color = SA.Color WHERE YEAR(SA.SaleDate) = YEAR(TO_DATE('2018-06-01')) AND MONTH(SA.SaleDate) = MONTH(TO_DATE('2018-06-01')) GROUP BY SA.Color; |
| 计算一年中每个工作日的总销售额 | DAYOFYEAR() 、 DATE_FORMAT() 、 WEEKDAY() | sql SELECT DAYOFYEAR(SaleDate) AS DayNumber,SUM(SalePrice) AS SalePrice,DATE_FORMAT(SaleDate, 'EEEE') AS Weekday FROM allsales WHERE YEAR(SaleDate) = 2018 AND WEEKDAY(SaleDate) NOT IN (5,6) GROUP BY DAYOFYEAR(SaleDate),DATE_FORMAT(SaleDate, 'EEEE') ORDER BY DayNumber |
| 计算两个日期之间的周末天数 | CTE、 ROW_NUMBER() 、 DATE_ADD() 、 DAYOFWEEK() 、 DATEDIFF() | sql WITH TallyTable_CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY StockCode) AS Nm FROM stock ORDER BY Nm LIMIT 90 ),WeekendList_CTE AS ( SELECT DATE_ADD('2018-03-01', Nm - 1) AS WeekdayDate FROM TallyTable_CTE WHERE DAYOFWEEK(DATE_ADD('2018-03-01', Nm - 1)) IN (5,6) AND Nm <= DATEDIFF('2018-04-30', '2018-03-01') ) SELECT COUNT(*) AS WeekendDays FROM WeekendList_CTE; |

9. 时间序列数据分析的应用场景

时间序列数据分析在各个领域都有广泛的应用，以下是一些常见的应用场景：
- 销售分析 ：通过分析不同时间段的销售数据，了解销售趋势，找出销售旺季和淡季，为制定营销策略提供依据。例如，计算年初至今的累计销售额，比较不同年份同一月份的销售数据等。
- 财务分析 ：分析财务数据随时间的变化，如利润、成本等，评估企业的财务状况和经营绩效。例如，提取前一个月的采购数据，与本月数据进行对比。
- 人力资源管理 ：根据员工的出勤、绩效等数据随时间的变化，进行员工部署和管理。例如，计算两个日期之间的周末天数，为员工排班提供参考。

10. 时间序列数据分析的流程

下面是一个时间序列数据分析的基本流程：

graph LR
    A[确定分析目标] --> B[收集数据]
    B --> C[数据清洗和预处理]
    C --> D[选择分析方法]
    D --> E[执行分析]
    E --> F[结果可视化和解读]
    F --> G[根据结果决策]

具体步骤如下：
1. 确定分析目标 ：明确要分析的问题，例如了解销售趋势、评估财务状况等。
2. 收集数据 ：从各种数据源收集相关的时间序列数据。
3. 数据清洗和预处理 ：处理缺失值、异常值等，确保数据的质量。
4. 选择分析方法 ：根据分析目标和数据特点，选择合适的分析方法，如本文介绍的各种 SQL 技巧。
5. 执行分析 ：使用选定的分析方法对数据进行分析。
6. 结果可视化和解读 ：将分析结果以图表、报表等形式展示出来，并进行解读。
7. 根据结果决策 ：根据分析结果制定相应的决策和策略。

11. 时间序列数据分析的注意事项

在进行时间序列数据分析时，需要注意以下几点：
- 日期范围的设置 ：在使用 BETWEEN...AND 等关键字设置日期范围时，要确保下限放在 BETWEEN 之后，上限放在 AND 之后，避免出现错误。
- 函数的使用 ：不同的 SQL 函数有不同的功能和用法，要根据具体需求选择合适的函数，并注意函数的参数和返回值。
- 数据的动态性 ：分析结果可能会因查询执行的日期不同而有所变化，要考虑数据的动态性，确保分析结果的时效性。
- 性能优化 ：对于大规模的数据，要注意优化查询语句，避免出现性能问题。例如，合理使用索引、避免不必要的子查询等。

12. 总结

时间序列数据分析是一项重要的技能，通过掌握相关的 SQL 技巧，我们可以对包含时间元素的数据进行深入分析，挖掘数据背后的价值。本文介绍了多种时间序列数据分析方法，包括年初至今累计值计算、提取前一个月的数据、比较当前年份与前一年的数据等，并给出了具体的操作步骤和代码示例。同时，还介绍了时间序列数据分析的应用场景、流程和注意事项。希望这些内容对大家有所帮助，在实际应用中能够灵活运用这些方法，为决策提供有力支持。