假设我们有一个记录设备每日数值的数据表，需要编写一个每日定时执行的MySQL脚本，输出以下统计结果：

• 输出字段：
  • 设备编号、累计天数及累计数值

    累计数值：设备自首次产生数值后，第1天至第7天的累计数值（即需要生成 0day 到 7day 共8个字段）。

• 核心难点：
  1. 计算每个设备随时间推移的累计数值。
  2. 将原本纵向排列的每日累计数据，转换为横向的列，以便于阅读和分析。

本文将分三步解决此问题：准备数据、计算累计值、进行行转列。

一、准备测试数据

首先，我们创建示例数据表并插入测试数据，以便后续演示。

-- 创建数据表
CREATE TABLE IF NOT EXISTS device_info (
    id INT,
    device_id INT,
    tday INT, -- 日期，格式如20240701
    amount INT, -- 当日上报数值
    first_day INT -- 设备首次产生数值的日期
);

-- 插入示例数据
INSERT INTO device_info (id, device_id, tday, amount, first_day) VALUES
(1, 1001, 20240701, 50, 20240701),
(2, 1002, 20240701, 100, 20240701),
(3, 1001, 20240702, 60, 20240701),
(4, 1002, 20240702, 110, 20240701),
(5, 1001, 20240703, 50, 20240701),
(6, 1002, 20240703, 50, 20240701);

二、核心步骤：使用窗口函数计算累计值

我们使用 SUM() OVER() 窗口函数，高效地计算每个设备从首次上报开始的每日累计数值。

实现思路：

• PARTITION BY device_id：按设备分组，分别计算。
• ORDER BY tday：按日期排序，进行累计。

  默认窗口范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从首行累计到当前行。 更多窗口函数用例可参考：窗口函数rows between 、range between的使用-CSDN博客，如果你的需求比较简单也可以通过同表的多次左连接来达成目的。

SELECT
  device_id,
  tday,
  DATEDIFF(DATE_FORMAT(tday, '%Y%m%d'), DATE_FORMAT(first_day, '%Y%m%d')) AS diff_day, -- 计算当前是第几天
  SUM(amount) OVER (PARTITION BY device_id ORDER BY tday) AS amount_tday_cumulative -- 计算累计值
FROM
  device_info;

查询结果：

说明：diff_day 表示当前日期与设备首次上报日期相差的天数。

三、关键转换：将纵表转为横表（行转列）

现在，我们需要把上一步中纵向的 diff_day 和 amount_tday_cumulative，转换为以 0day, 1day... 7day 为列的横向格式。

我们使用 公共表表达式 (CTE) 和 条件聚合 来实现这一转换。

实现思路：

1. 使用 CTE 表达式临时存储上一步计算出的累计结果。
2. 通过 CASE WHEN 语句，将不同 diff_day 的累计值投射到对应的新列中。
3. 使用 MAX() 函数和 GROUP BY device_id 来确保每个设备只输出一行。

WITH cumulative_days AS (
  -- 此为第二步中的SQL，用于生成累计数据
  SELECT
    device_id,
    tday,
    DATEDIFF(DATE_FORMAT(tday, '%Y%m%d'), DATE_FORMAT(first_day, '%Y%m%d')) AS diff_day,
    SUM(amount) OVER (PARTITION BY device_id ORDER BY tday) AS amount_tday_cumulative
  FROM
    device_info
)
-- 行转列查询
SELECT
  device_id,
  MAX(CASE WHEN diff_day = 0 THEN amount_tday_cumulative ELSE 0 END) AS 0day,
  MAX(CASE WHEN diff_day = 1 THEN amount_tday_cumulative ELSE 0 END) AS 1day,
  MAX(CASE WHEN diff_day = 2 THEN amount_tday_cumulative ELSE 0 END) AS 2day,
  MAX(CASE WHEN diff_day = 3 THEN amount_tday_cumulative ELSE 0 END) AS 3day,
  MAX(CASE WHEN diff_day = 4 THEN amount_tday_cumulative ELSE 0 END) AS 4day,
  MAX(CASE WHEN diff_day = 5 THEN amount_tday_cumulative ELSE 0 END) AS 5day,
  MAX(CASE WHEN diff_day = 6 THEN amount_tday_cumulative ELSE 0 END) AS 6day,
  MAX(CASE WHEN diff_day = 7 THEN amount_tday_cumulative ELSE 0 END) AS 7day
FROM
  cumulative_days
GROUP BY
  device_id;

最终输出结果：

总结与要点

1. 实现方法：本方案结合了 SQL窗口函数 进行高效累计计算，以及 条件聚合 实现行转列，逻辑清晰且性能较好。
2. 关于 MAX() 函数：在行转列步骤中，使用 MAX() 是为了在按设备分组后，从多个可能存在的行中提取出目标值。由于 CASE WHEN 已经确保了每个 diff_day 只有一行有非零值，MAX() 能准确提取该值。
3. 性能考虑：如果数据量庞大，可以考虑将CTE物化为临时表或实际表，并为关键字段（如 device_id, tday）建立索引。从长远看，最优方案是在业务设计阶段就考虑建设统计宽表，通过ETL任务实现数据冗余，以空间换时间，满足高效的报表查询需求。

注：2025-09-29 使用 DeepSeek 对原文章进行了润色处理