MySQL 中的时间序列数据分析与处理

原创于 2025-06-24 20:22:43 发布 · 1.1k 阅读

CC 4.0 BY-SA版权

文章标签：

在互联网应用和企业业务系统中，特别是现在当下环境电商以及跨境电商火爆的情况下，时间序列数据无处不在，如电商订单时间、用户登录日志、设备监控数据等。MySQL 作为主流数据库，具备强大的时间序列数据处理能力。本文将结合电商订单场景，分享一系列实用的 MySQL 实战技巧，帮助你高效分析和处理时间序列数据。

一、数据准备与表结构设计

假设我们有一张orders表用于存储电商订单信息，表结构如下：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_time TIMESTAMP,
    customer_id INT,
    product_id INT,
    order_amount DECIMAL(10, 2),
    order_status VARCHAR(20)
);

INSERT INTO orders (order_time, customer_id, product_id, order_amount, order_status)
VALUES
    ('2024-10-01 10:15:00', 1, 101, 99.99, '已支付'),
    ('2024-10-01 14:30:00', 2, 102, 149.99, '已支付'),
    ('2024-10-02 09:00:00', 1, 103, 79.99, '已支付'),
    ('2024-10-02 20:45:00', 3, 104, 299.99, '已支付');

这段SQL代码创建了一个名为 orders 的订单表，并插入了四条示例数据，非常适合用于时间序列数据分析的教学。

首先，CREATE TABLE 语句定义了五个字段：

order_id 是主键并自动递增，确保每条订单唯一；
order_time 为时间戳类型，记录订单发生的时间；
customer_id 和 product_id 分别表示客户和商品的编号；
order_amount 表示订单金额，使用 DECIMAL 类型保证精度；
order_status 存储订单状态，如“已支付”。

接下来，INSERT INTO 语句向表中插入了四条订单记录，每条都包含时间和金额信息。

二、统计每日订单数量与总金额

分析订单数据时，首先会关注每日的订单情况。使用GROUP BY结合日期函数DATE()可轻松实现：

SELECT
    DATE(order_time) AS order_date,
    COUNT(order_id) AS order_count,
    SUM(order_amount) AS total_amount
FROM
    orders
GROUP BY
    DATE(order_time)
ORDER BY
    order_date;

这条 SQL 查询语句，首先，DATE(order_time) 函数将原始的时间戳提取为日期，忽略具体时间，便于按“天”进行分组统计。接着使用 COUNT(order_id) 统计每日订单数量，SUM(order_amount) 计算每日总销售额，实现了对订单数据的聚合汇总。

通过 GROUP BY DATE(order_time)，数据按照日期分组，再配合 ORDER BY order_date 按照时间顺序排列结果，使得输出呈现出清晰的时间序列趋势。

三、查找订单高峰时段

了解一天中哪个时段订单量最多，对合理安排客服、物流等资源至关重要。我们可以将order_time按小时分组统计订单数量：

SELECT
    HOUR(order_time) AS order_hour,
    COUNT(order_id) AS order_count
FROM
    orders
GROUP BY
    HOUR(order_time)
ORDER BY
    order_count DESC
LIMIT 1;

这条 SQL 查询语句用于分析一天中哪个小时的订单量最高。

首先，HOUR(order_time) 函数从订单时间中提取小时部分，使我们能按小时进行统计。然后使用 COUNT(order_id) 统计每个小时的订单数量。

通过 GROUP BY HOUR(order_time) 对每个小时的数据进行分组聚合，再用 ORDER BY order_count DESC 按订单数量从高到低排序，最后加上 LIMIT 1 只返回订单最多的那个小时。

四、计算订单平均处理时长

若订单表中还记录了订单完成时间complete_time，可以计算订单从生成到完成的平均处理时长：

SELECT
    AVG(TIMESTAMPDIFF(MINUTE, order_time, complete_time)) AS average_processing_time
FROM
    orders
WHERE
    complete_time IS NOT NULL;

TIMESTAMPDIFF(unit, start_time, end_time)函数用于计算两个时间戳之间的差值，这里以分钟为单位（MINUTE），AVG()函数计算平均处理时长。通过WHERE complete_time IS NOT NULL过滤掉未完成的订单。

五、分析订单趋势

通过分析订单数量或金额的趋势，能帮助企业预测未来业务走向。使用窗口函数计算订单数量的环比增长率：

SELECT
    DATE(order_time) AS order_date,
    COUNT(order_id) AS order_count,
    -- 计算环比增长率
    CONCAT(
        ROUND(
            (COUNT(order_id) - LAG(COUNT(order_id), 1, 0) OVER (ORDER BY DATE(order_time))) /
            LAG(COUNT(order_id), 1, 0) OVER (ORDER BY DATE(order_time)) * 100,
            2
        ),
        '%'
    ) AS growth_rate
FROM
    orders
GROUP BY
    DATE(order_time)
ORDER BY
    order_date;

这条 SQL 查询语句是对时间序列数据进行趋势分析与环比增长计算的典型案例。

首先，查询按日期（DATE(order_time)）对订单进行分组，统计每天的订单数量（COUNT(order_id)），这是典型的时间维度聚合操作。接下来是重点部分：使用了 LAG() 窗口函数来获取前一天的订单数量，从而计算出每日订单数的环比增长率。

LAG(COUNT(order_id), 1, 0) OVER (ORDER BY DATE(order_time)) 表示取上一天的订单数量，若没有（如第一天），则默认为 0。通过当前天与前一天的数量差值除以前一天数量，再乘以 100 得到百分比增长率，并使用 ROUND(..., 2) 保留两位小数，最后用 CONCAT(..., '%') 添加百分号，使结果更具可读性。