时间序列分析

时间在商业分析中是一个特殊的维度。与地理位置、产品类别等维度不同，时间具有方向性、连续性和周期性。理解时间序列数据的特征，识别业务中的时间规律，能够帮助我们预测未来趋势、优化业务节奏、制定更合理的计划。 这节课我们将学习如何处理和分析时间序列数据，使用SQL进行时间维度的聚合和计算，并通过Excel创建直观的时间趋势图表，发现隐藏在时间背后的业务规律。

时间序列数据

时间序列数据是按照时间顺序排列的数据序列。在商业分析中，我们经常遇到各种时间序列数据：每日销售额、每月订单数、每周访问量等。这些数据具有一些共同的特点，理解这些特点对于正确分析时间序列数据非常重要。

趋势性

时间序列数据往往表现出某种趋势。这种趋势可能是上升的，比如随着业务发展，销售额逐年增长；也可能是下降的，比如某个产品随着市场饱和，销量逐渐下降；还可能是平稳的，比如成熟市场的销售额保持相对稳定。

识别趋势对于业务规划非常重要。如果销售额呈现上升趋势，我们可以预期未来可能继续增长，从而制定相应的扩张计划；如果呈现下降趋势，我们需要及时采取措施，扭转局面。

季节性

很多业务数据都表现出季节性特征。比如，零售业在节假日期间销售额会显著增加，旅游业在夏季和冬季会有不同的高峰，电商平台在双十一等购物节会有爆发式增长。

季节性模式通常以年为周期重复出现。理解季节性规律能够帮助我们：提前准备，在销售旺季到来之前备足库存、增加人手；合理规划，在淡季进行促销活动，保持业务稳定；准确预测，基于历史季节性数据预测未来表现。

周期性

除了以年为单位的季节性，时间序列数据还可能表现出更短的周期性。比如，工作日和周末的销售模式可能不同，一天中的不同时段访问量可能不同，一周中的不同日期订单量可能不同。

这些周期性模式虽然不如季节性那么明显，但对于日常运营同样重要。了解这些模式，我们能够优化排班、调整库存、安排营销活动，提高运营效率。

异常值

时间序列数据中可能包含异常值，这些异常值通常是由特殊事件引起的。比如，促销活动可能导致销售额突然增加，突发事件可能导致业务暂时中断，竞争对手的行动可能影响我们的表现。

识别异常值很重要，因为它们可能扭曲我们对趋势和规律的理解。在分析时，我们需要区分哪些是真正的趋势变化，哪些只是特殊事件导致的暂时波动。

时间维度的切分与聚合

在分析时间序列数据时，我们经常需要按照不同的时间粒度进行聚合：按日、按周、按月、按年等。不同的时间粒度适合不同的分析目的，我们需要根据具体需求选择合适的粒度。

在开始之前，让我们先了解一下示例数据表的结构。假设我们有一个订单表，包含以下字段：订单ID、订单日期、订单金额、客户ID等。这个表记录了从2022年1月到2024年12月共三年的订单数据，总共约15000条订单记录。订单金额在50元到5000元之间，平均订单金额约为280元。这个数据集包含了明显的季节性特征，比如节假日期间订单量会增加，以及一些周期性模式，比如周末的订单量通常比工作日高。

提取时间维度

SQL提供了丰富的日期时间函数，帮助我们提取和操作时间维度。最常用的是提取年、月、日、周等：

|
SELECT 
    订单日期,
    YEAR(订单日期) AS 年份,
    MONTH(订单日期) AS 月份,
    DAY(订单日期) AS 日期,
    WEEK(订单日期) AS 周数,
    DAYOFWEEK(订单日期) AS 星期几
FROM 订单表
LIMIT 10;

这个查询展示了如何从日期字段中提取各种时间维度。YEAR()、MONTH()、DAY()分别提取年、月、日，WEEK()提取周数，DAYOFWEEK()返回星期几（1代表周日，2代表周一，以此类推）。

执行这个查询，我们会得到类似这样的结果：

从结果中我们可以看到，每条订单记录都被分解成了不同的时间维度。比如第一条记录是2023年3月15日，这是2023年的第11周，星期三（星期几为4）。这些维度将成为后续聚合分析的基础。

不同的数据库系统可能使用不同的函数名称，但基本思路是相似的。比如，在PostgreSQL中，我们可以使用EXTRACT(YEAR FROM 订单日期)来提取年份；在SQL Server中，我们可以使用DATEPART(YEAR, 订单日期)。

观察日常波动

按日聚合是最细粒度的时间聚合，适合观察短期的波动和趋势。我们可以计算每天的销售额、订单数等指标：

|
SELECT 
    DATE(订单日期) AS 日期,
    COUNT(*) AS 订单数,
    SUM(订单金额) AS 日销售额,
    AVG(订单金额) AS 平均订单金额,
    COUNT(DISTINCT 客户ID) AS 客户数
FROM 订单表
GROUP BY DATE(订单日期)
ORDER BY 日期;

这个查询按照日期聚合，计算每天的订单数、销售额、平均订单金额和客户数。通过查看这些数据，我们能够识别日常的波动模式，发现哪些日期表现特别好或特别差。

执行这个查询，我们会得到按日期聚合的结果。以下是2023年3月部分日期的示例数据：

从这些数据中，我们可以观察到一些有趣的模式。比如3月4日（周六）的订单数和销售额都明显高于工作日，这反映了周末消费的特点。3月6日（周一）的数据相对较低，这可能是因为工作日刚开始，消费活动还没有完全恢复。通过对比不同日期的数据，我们能够识别出日常的波动规律。

识别周期性模式

按周聚合能够帮助我们识别以周为单位的周期性模式。比如，我们可能发现周末的销售额通常比工作日高，或者某些工作日表现特别好。

|
SELECT 
    YEAR(订单日期) AS 年份,
    WEEK(订单日期) AS 周数,
    COUNT(*) AS 订单数,
    SUM(订单金额) AS 周销售额
FROM 订单表
GROUP BY YEAR(订单日期), WEEK(订单日期)
ORDER BY 年份, 周数;

这个查询按照年份和周数聚合。通过分析每周的数据，我们能够识别周级别的趋势和周期性模式。

执行这个查询，我们会得到按周聚合的结果。以下是2023年部分周数的示例数据：

从这些数据中，我们可以看到周销售额在8万到10万元之间波动。第13周的销售额明显高于其他周，这可能是因为包含了某个节假日或促销活动。通过观察周级别的数据，我们能够识别出更宏观的趋势，而不会被日常的随机波动所干扰。

如果我们想要分析一周中不同日期的表现，可以这样查询：

|
SELECT 
    DAYOFWEEK(订单日期) AS 星期几,
    CASE DAYOFWEEK(订单日期)
        WHEN 1 THEN '周日'
        WHEN 2 THEN '周一'
        WHEN 3 THEN '周二'
        WHEN 4 THEN '周三'
        WHEN 5 THEN '周四'
        WHEN 6 THEN '周五'
        WHEN 7 THEN '周六'
    END AS 星期名称,

这个查询分析一周中不同日期的表现。通过对比不同日期的数据，我们能够发现工作日和周末的差异，或者某些特定日期的特殊表现。

执行这个查询，我们会得到按星期几聚合的结果：

从这些数据中，我们可以清楚地看到一周中的消费模式。周六和周日的订单数明显高于工作日，平均订单金额也更高，这反映了周末消费的特点。周五的订单数也比较高，可能是因为临近周末，人们的消费意愿增强。 周一和周二的订单数相对较低，平均订单金额也较低，这可能是因为工作日刚开始，消费活动还没有完全恢复。这种周期性模式对于业务运营非常重要，可以帮助我们优化排班和库存管理。

观察长期趋势

按月聚合是最常用的时间聚合方式之一，它能够平衡细节和趋势，既不会太细碎，也不会丢失重要的变化信息。

|
SELECT 
    DATE_FORMAT(订单日期, '%Y-%m') AS 年月,
    COUNT(*) AS 订单数,
    SUM(订单金额) AS 月销售额,
    AVG(订单金额) AS 平均订单金额
FROM 订单表
GROUP BY DATE_FORMAT(订单日期, '%Y-%m')
ORDER BY 年月;

这个查询使用DATE_FORMAT函数将日期格式化为"年-月"的形式，然后按照这个格式聚合。通过查看月度数据，我们能够清晰地观察长期趋势，识别增长或下降的周期。

执行这个查询，我们会得到按年月聚合的结果。以下是2023年和2024年部分月份的示例数据：

从这些数据中，我们可以观察到明显的季节性特征。11月和12月的销售额明显高于其他月份，这可能是因为双十一和年末购物季的影响。 6月的销售额也比较高，这可能与夏季消费旺季有关。通过对比不同月份的数据，我们能够识别出业务的季节性规律，这对于库存管理和营销规划非常重要。

把握宏观趋势

按年聚合适合观察长期的宏观趋势。虽然这种聚合会丢失很多细节，但它能够帮助我们把握大的方向和周期。

|
SELECT 
    YEAR(订单日期) AS 年份,
    COUNT(*) AS 订单数,
    SUM(订单金额) AS 年销售额,
    AVG(订单金额) AS 平均订单金额,
    COUNT(DISTINCT 客户ID) AS 客户数
FROM 订单表
GROUP BY YEAR(订单日期)
ORDER BY 年份;

这个查询按照年份聚合，计算每年的订单数、销售额、平均订单金额和客户数。通过对比不同年份的数据，我们能够了解业务的长期发展趋势。

执行这个查询，我们会得到按年份聚合的结果：

从这些数据中，我们可以清楚地看到业务的长期发展趋势。订单数从2022年的15230单增长到2024年的18240单，增长了约20%。年销售额从428万元增长到536万元，增长了约25%。客户数从3240人增长到3980人，增长了约23%。 平均订单金额也在稳步上升，从281.50元增长到294.18元，这表明客户的消费能力在提升。这种宏观视角能够帮助我们把握业务的大方向，制定长期的发展战略。

使用SQL进行时间序列计算

除了简单的聚合，SQL还提供了强大的时间序列计算功能，包括移动平均、同比增长、环比增长等。这些计算能够帮助我们更好地理解时间序列数据的特征。

计算移动平均

移动平均是时间序列分析中常用的方法，它能够平滑数据的波动，让我们更容易识别趋势。移动平均的计算方法是：对于每个时间点，计算它及其前面若干个时间点的平均值。

在SQL中，我们可以使用窗口函数来计算移动平均：

|
SELECT 
    日期,
    日销售额,
    AVG(日销售额) OVER (
        ORDER BY 日期 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS 七日移动平均
FROM (
    SELECT 
        DATE(订单日期) AS 日期,
        SUM(订单金额) AS 日销售额
    FROM 订单表
    GROUP BY DATE(订单日期)
) 

这个查询计算七日移动平均。ROWS BETWEEN 6 PRECEDING AND CURRENT ROW定义了窗口范围：从当前行往前6行到当前行，共7行数据。移动平均能够帮助我们过滤掉短期的随机波动，更清楚地看到趋势。

执行这个查询，我们会得到包含移动平均的结果。以下是2023年3月部分日期的示例数据：

从这些数据中，我们可以看到移动平均的效果。前6天的移动平均会逐渐稳定，因为需要累积足够的数据点。从3月7日开始，移动平均开始反映最近7天的平均水平。比如3月7日的七日移动平均是13410.00元，这是从3月1日到3月7日这7天的平均值。移动平均线比原始数据更平滑，能够帮助我们识别趋势，而不会被单日的异常波动所干扰。比如3月6日的日销售额只有9870.00元，明显低于其他日期，但移动平均仍然保持在合理范围内，这表明这可能只是暂时的波动。

计算同比增长

同比增长（Year-over-Year Growth）是比较同一时期不同年份的数据，这对于识别季节性模式非常有用。比如，我们可以比较今年1月和去年1月的销售额，看看是否有增长。

|
WITH 月度销售 AS (
    SELECT 
        YEAR(订单日期) AS 年份,
        MONTH(订单日期) AS 月份,
        SUM(订单金额) AS 月销售额
    FROM 订单表
    GROUP BY YEAR(订单日期), MONTH(订单日期)
),
同比增长 AS (
    SELECT 
        m1.年份 AS 当前年,
        m1.月份

这个查询使用CTE来计算同比增长。首先，月度销售CTE计算每年的月度销售额；然后，同比增长CTE将当前年的数据与上一年的同月数据进行比较，计算同比增长率。通过分析同比增长率，我们能够识别哪些月份增长较快，哪些月份可能出现下降。

执行这个查询，我们会得到同比增长的分析结果。以下是2023年和2024年部分月份的示例数据：

从这些数据中，我们可以清楚地看到同比增长的情况。2023年1月相比2022年1月增长了10.21%，2023年11月和12月的同比增长率都超过了12%，这表明年末购物季的增长势头很强劲。2024年1月和2月相比2023年同期也都有超过11%的增长，说明业务继续保持良好的增长态势。同比增长分析能够帮助我们识别季节性模式，因为它是比较同一时期不同年份的数据，能够消除季节性的影响，更准确地反映业务的真实增长情况。

计算环比增长

环比增长（Month-over-Month Growth）是比较相邻时期的数据，比如这个月和上个月的比较。环比增长能够帮助我们观察短期的变化趋势。

|
WITH 月度销售 AS (
    SELECT 
        DATE_FORMAT(订单日期, '%Y-%m') AS 年月,
        SUM(订单金额) AS 月销售额
    FROM 订单表
    GROUP BY DATE_FORMAT(订单日期, '%Y-%m')
),
环比增长 AS (
    SELECT 
        年月,
        月销售额,
        LAG(月销售额) OVER (ORDER BY 年月) AS 上月销售额,

这个查询使用窗口函数LAG来获取上个月的销售额，然后计算环比增长率。环比增长能够帮助我们识别短期的趋势变化，发现业务中的转折点。

执行这个查询，我们会得到环比增长的分析结果。以下是2023年和2024年部分月份的示例数据：

从这些数据中，我们可以观察到短期的变化趋势。2023年3月相比2月环比增长了13.68%，这是一个明显的增长。2023年11月相比10月环比增长了14.95%，这反映了双十一购物节的影响。2024年1月相比2023年12月环比下降了32.37%，这是一个大幅下降，但这可能是因为12月是购物旺季，1月回归正常水平，这种下降是季节性的正常现象。环比增长分析能够帮助我们识别短期的趋势变化，发现业务中的转折点，但它会受到季节性因素的影响，所以我们需要结合同比增长分析来全面理解业务的变化。

识别峰值和谷值

在时间序列分析中，识别峰值（最高点）和谷值（最低点）很重要。这些点可能代表重要的业务事件，或者需要特别关注的时期。

|
WITH 日销售 AS (
    SELECT 
        DATE(订单日期) AS 日期,
        SUM(订单金额) AS 日销售额
    FROM 订单表
    GROUP BY DATE(订单日期)
),
峰值谷值 AS (
    SELECT 
        日期,
        日销售额,
        CASE 
            WHEN 日销售额 = MAX(日销售额) OVER (
                ORDER BY 日期 

这个查询使用窗口函数来识别局部峰值和谷值。对于每个日期，我们检查它是否是其周围13天（前后各6天）内的最高值或最低值。如果是，就标记为峰值或谷值。通过识别这些点，我们能够发现重要的业务事件或需要关注的时期。

执行这个查询，我们会得到识别出的峰值和谷值。以下是2023年部分日期的示例数据：

从这些数据中，我们可以发现一些重要的业务事件。2023年3月11日的日销售额达到18950.00元，是周围13天内的峰值，这可能是因为某个促销活动。2023年5月1日的峰值可能与劳动节假期有关。2023年11月11日的峰值达到45680.00元，这是双十一购物节的影响，这个峰值非常明显，远超其他日期。2024年1月2日的谷值只有5230.00元，这可能是因为元旦假期后的业务低谷。通过识别这些峰值和谷值，我们能够发现重要的业务事件，分析它们的原因，并制定相应的策略。

使用Excel进行时间趋势分析

SQL查询能够帮助我们获取时间序列数据并进行各种计算，但要直观地观察趋势和模式，可视化是必不可少的。Excel提供了强大的时间序列可视化功能，包括折线图、面积图、组合图等。

创建时间序列折线图

折线图是展示时间序列数据最常用的图表类型。它能够清晰地展示数据随时间的变化趋势，帮助我们识别上升、下降、波动等模式。

在Excel中创建时间序列折线图很简单。我们首先准备数据，确保日期列格式正确，然后选中包含日期和数值的数据区域，在"插入"选项卡中选择"折线图"。Excel会自动识别日期列，并将其作为横轴。

下面是一组可以直接复制粘贴到Excel中进行测试的数据。我们打开Excel，新建一个工作表，然后将以下数据复制粘贴到A1单元格开始的位置：

|
日期	日销售额	订单数	客户数
2023-03-01	12850	42	38
2023-03-02	11230	38	35
2023-03-03	14560	45	41
2023-03-04	16890	52	48
2023-03-05	15230	48	44
2023-03-06	9870	35	32
2023-03-07	13240	41	37
2023-03-08	14560	45	40
2023-03-09	15890	49	44
2023-03-10	14230	44	40
2023-03-11	18950	58	52
2023-03-12	17560	54	49
2023-03-13	13890	43	39
2023-03-14	15230	47	42
2023-03-15	16450	51	46
2023-03-16	17890	55	50
2023-03-17	19230	59	53
2023-03-18	8950	28	25
2023-03-19	12340	38	34
2023-03-20	14560	45	41
2023-03-21	15890	49	44
2023-03-22	17230	53	48
2023-03-23	18560	57	51
2023-03-24	19890	61	55
2023-03-25	18230	56	50
2023-03-26	16890	52	47
2023-03-27	15560	48	43
2023-03-28	14230	44	40
2023-03-29	12900	40	36
2023-03-30	11560	36	32
2023-03-31	10230	32	29

选中包含日期和日销售额的两列数据（A列和B列），创建折线图后，我们会看到一条随时间变化的曲线。这条曲线能够清晰地展示销售额的波动趋势，比如我们可以看到3月4日、11日、17日、24日（都是周末）的销售额明显高于其他日期，这反映了周末消费的特点。

折线图能够同时展示多个系列的数据。比如，我们可以同时展示销售额、订单数、客户数等多个指标，通过不同的线条和颜色来区分。这样，我们就能在一个图表中观察多个指标的变化趋势，发现它们之间的关系。当我们同时展示销售额和订单数时，可能会发现它们的趋势基本一致，这表明平均订单金额相对稳定。

添加趋势线

Excel允许我们在图表中添加趋势线，帮助我们更清楚地识别趋势。趋势线可以是线性的，也可以是指数、对数、多项式等类型，我们可以根据数据的特征选择合适的类型。

要添加趋势线，我们右键点击数据系列，选择"添加趋势线"。Excel会弹出对话框，让我们选择趋势线类型和选项。我们可以选择显示公式和R²值，这样就能了解趋势线的数学表达式和拟合程度。

趋势线不仅能够帮助我们识别趋势，还能够用于预测。我们可以延长趋势线，预测未来的可能值。当然，这种预测是基于历史趋势的简单外推，实际预测时需要考虑更多因素。

创建组合图表

有时候，我们需要在同一个图表中展示多个不同量级的指标。比如，销售额可能是几万元，而订单数可能是几百个。如果直接用同一个纵轴，订单数的变化可能看不清楚。

下面是一组可以直接复制粘贴到Excel中进行测试的数据，用于创建组合图表：

|
日期	日销售额	订单数
2023-03-01	12850	42
2023-03-02	11230	38
2023-03-03	14560	45
2023-03-04	16890	52
2023-03-05	15230	48
2023-03-06	9870	35
2023-03-07	13240	41
2023-03-08	14560	45
2023-03-09	15890	49
2023-03-10	14230	44
2023-03-11	18950	58
2023-03-12	17560	54
2023-03-13	13890	43
2023-03-14	15230	47
2023-03-15	16450	51
2023-03-16	17890	55
2023-03-17	19230	59
2023-03-18	8950	28
2023-03-19	12340	38
2023-03-20	14560	45

我们将这三列数据全部选中，创建折线图。由于销售额和订单数的量级不同（销售额是万元级别，订单数是几十个），如果使用同一个纵轴，订单数的变化趋势可能看不清楚。

这时候，我们可以使用组合图表，为不同的指标使用不同的纵轴。在Excel中，我们右键点击"订单数"这条线，选择"设置数据系列格式"，然后选择"次坐标轴"。这样，"订单数"系列就会使用右侧的纵轴，而"日销售额"系列继续使用左侧的纵轴。

组合图表能够让我们同时观察多个指标的变化，发现它们之间的关系。比如，我们可能会发现销售额和订单数的变化趋势基本一致，这表明平均订单金额相对稳定。或者我们可能会发现客户数的增长领先于销售额的增长，这表明我们需要关注客户获取和留存。

识别季节性模式

Excel还提供了专门的功能来识别和分析季节性模式。我们可以使用移动平均来平滑数据，然后计算季节性指数。

首先，我们计算移动平均来去除趋势和随机波动。下面是一组完整的月度销售数据，可以直接复制粘贴到Excel中进行测试。我们打开Excel，新建一个工作表，然后将以下数据复制粘贴到A1单元格开始的位置：

|
年月	月销售额
2023-01	358920
2023-02	342560
2023-03	389450
2023-04	376890
2023-05	418560
2023-06	452380
2023-07	435670
2023-08	421230
2023-09	445890
2023-10	489230
2023-11	562340
2023-12	589670
2024-01	398760
2024-02	381450
2024-03	425680
2024-04	412340
2024-05	456780
2024-06	489560
2024-07	472890
2024-08	458230
2024-09	482560
2024-10	528940
2024-11	608720
2024-12	635890

在C2单元格中输入以下公式来计算12个月的移动平均：

|
=AVERAGE(B2:B13)

然后将这个公式向下拖动到C13单元格。这样，C13单元格会计算2023年1月到12月的移动平均，C14单元格会计算2023年2月到2024年1月的移动平均，以此类推。

接下来，我们在D13单元格中输入以下公式来计算季节性指数：

|
=B13/C13

然后将这个公式向下拖动到D24单元格。季节性指数是原始值与移动平均的比值，它反映了季节性因素。

比如2023年12月的季节性指数约为1.24，这意味着12月的销售额比全年平均水平高出24%，这反映了年末购物季的影响。2024年1月的季节性指数约为0.83，这意味着1月的销售额比全年平均水平低17%，这可能是因为元旦假期后的业务低谷。

通过分析这些比值，我们能够识别哪些月份通常表现较好，哪些月份通常表现较差。这些信息对于业务规划非常重要，能够帮助我们提前准备，优化资源配置。比如，如果我们知道12月的季节性指数是1.24，我们就可以提前在11月增加库存，确保12月有足够的供应。

时间序列分析的商业应用

掌握了时间序列分析的方法后，我们可以将这些方法应用到实际的商业场景中，解决具体的业务问题。

销售预测与库存管理

通过分析历史销售数据的时间序列特征，我们能够预测未来的销售趋势，从而优化库存管理。如果预测未来销售额会增长，我们可以提前增加库存；如果预测会下降，我们可以减少库存，避免积压。

时间序列分析还能够帮助我们识别季节性模式。比如，如果某个产品在夏季销量通常较高，我们可以提前在春季增加库存，确保夏季有足够的供应。

需求规划与资源调配

理解业务的时间规律，我们能够更好地规划需求和调配资源。比如，如果我们知道周末的订单量通常比工作日高，我们就可以在周末安排更多的人手，提高服务质量。

同样，如果我们知道某些月份是业务高峰期，我们可以提前准备，增加产能、调配资源，确保能够满足高峰期的需求。

异常检测与问题识别

时间序列分析还能够帮助我们识别异常。如果某个时期的销售额明显偏离正常趋势，可能表示发生了特殊事件，或者出现了问题。

通过对比实际值和预期值（基于历史趋势计算），我们能够快速识别异常。这些异常可能代表机会（比如意外的销售增长），也可能代表风险（比如意外的销售下降），需要我们及时关注和处理。

课程小结

通过本节课的学习，我们掌握了时间序列分析的核心方法，包括时间维度的切分、时间序列的计算，以及如何利用SQL进行聚合和各类指标的计算，结合Excel的可视化工具，将数据的趋势、季节性和周期性特征直观地展现出来。

在实际操作中，我们需要重视数据的完整性与规范性，做好清洗和预处理，并结合业务背景深入理解数据背后的含义。下一节课我们将学习“客户能持续多久”，探索客户留存分析和生存分析的方法。这将帮助我们理解客户的生命周期价值，识别影响客户留存的因素，并制定有效的客户 retention 策略。