使用SQL窗口函数实现分组排序累计结果

请编写一个MySQL脚本，该脚本需要执行以下操作：

1. 已有数据表，包含以下字段：每日日期 tday（精确到天）、当天上报 amount 数值、产生数值的设备编号 device_id、第一次产生数值的时间 first_day（精确到天）。
2. 假设设备每天都有数值，并使用这些字段来统计数值。
3. 每天定时执行一次SQL语句，将统计结果输出到控制台。
4. 输出结果应包括以下字段：统计日期、设备编号、设备使用当天数值、设备第一次产生数值后的第一天的累计数值、设备第一次产生数值后的第二天的累计数值、设备第一次产生数值后的第三天的累计数值，以此类推到设备第一次产生数值后在第七天的累计数值。
5. 脚本应具有良好的可读性和可维护性，注释清晰，易于理解。

咨询AI后也并不能得到很好的回答，上面的描述里统计累计量其实并不复杂，一些AI可以给出到存储过程以及SQL的窗口函数相近的方法。但是上面的描述里隐藏了一个使用窗口函数后将列显示转为行显示的一种隐藏的含义。

这里使用窗口函数 SUM() OVER() 来实现，如果你的需求比较简单也可以通过同表的左连接几次来达成目的。更多函数用例可参考：窗口函数rows between 、range between的使用-CSDN博客

一、创建数据表并填充数据

CREATE TABLE IF NOT EXISTS jeeinn_info (  
    id INT,
    device_id INT, 
    tday INT, 
    amount INT,  
    first_day INT
);  
  
INSERT INTO jeeinn_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() 进行统计

请注意MySQL的版本支持窗口函数，SUM() OVER() 默认使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 窗口条件来求和

SELECT
  device_id,
  tday,
  DATEDIFF(DATE(tday), DATE(first_day)) AS diff_day,
  SUM( amount ) OVER ( PARTITION BY device_id ORDER BY tday ) AS amount_tday_cumulative
FROM
  jeeinn_info

结果如下

device_id	tday	diff_day	amount_tday_cumulative
1001	20240701	0	50
1001	20240702	1	110
1001	20240703	2	160
1002	20240701	0	100
1002	20240702	1	210
1002	20240703	2	260

三、将纵表转为横表（行转列）

使用下 CTE 表达式，直接出结果

WITH cumulative_days AS (
SELECT
  device_id,
  tday,
  DATEDIFF(DATE(tday), DATE(first_day)) AS diff_day,
  SUM( amount ) OVER ( PARTITION BY device_id ORDER BY tday ) AS amount_tday_cumulative
FROM
  jeeinn_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

结果如下：

device_id	0day	1day	2day	3day	4day	5day	6day	7day
1001	50	110	160	0	0	0	0	0
1002	100	210	260	0	0	0	0	0

注：如果数据较多，可以增加相应的 where 条件。或者考虑将 CTE 的表进行实体化，更有条件的应该在业务层直接实现最终的表冗余设计