在Presto SQL中实现ID累计数组（Running Total）计算

一、目标结果示例 #

先明确最终生成的目标表数据格式（基于你提供的示例）：

二、基础累计逻辑实现 #

以SQL（MySQL为例）实现核心累计逻辑，通过窗口函数处理行与行的继承关系：

SELECT
    Date,
    IDs_Used_Today,
    New_IDs,
    CASE
        -- 第一行无前置数据，直接取当日New_IDs
        WHEN LAG(All_IDs_To_Date) OVER (ORDER BY Date) IS NULL THEN New_IDs
        -- 当日无新ID，继承前一行累计值
        WHEN New_IDs IS NULL THEN LAG(All_IDs_To_Date) OVER (ORDER BY Date)
        -- 有新ID则拼接前一行累计值与当日新ID
        ELSE CONCAT(LAG(All_IDs_To_Date) OVER (ORDER BY Date), ',', New_IDs)
    END AS All_IDs_To_Date
FROM your_table
ORDER BY Date;

三、删除旧行后的兼容方案 #

上面的逻辑依赖历史行存在，若删除旧行，LAG函数无法获取历史累计值，会导致累计断裂。需单独维护累计快照表来存储最新的累计状态：

1. 创建快照表 #

CREATE TABLE id_cumulative_snapshot (
    latest_date DATE PRIMARY KEY,
    all_ids_to_date VARCHAR(255) NOT NULL
);

2. 新增数据时的联动逻辑 #

插入每日数据时，先从快照表读取最新累计值，计算当日All_IDs_To_Date，同时更新快照表：

-- 假设新数据临时存在temp_table中
INSERT INTO your_table (Date, IDs_Used_Today, New_IDs, All_IDs_To_Date)
SELECT
    t.Date,
    t.IDs_Used_Today,
    t.New_IDs,
    CASE
        WHEN s.all_ids_to_date IS NULL THEN t.New_IDs
        WHEN t.New_IDs IS NULL THEN s.all_ids_to_date
        ELSE CONCAT(s.all_ids_to_date, ',', t.New_IDs)
    END AS All_IDs_To_Date
FROM temp_table t
LEFT JOIN id_cumulative_snapshot s ON 1=1;

-- 更新快照表，保留最新的累计值
REPLACE INTO id_cumulative_snapshot (latest_date, all_ids_to_date)
SELECT Date, All_IDs_To_Date FROM your_table ORDER BY Date DESC LIMIT 1;

3. 删除旧行后的处理 #

删除历史行后，快照表仍存储最后一次的累计值，后续插入新数据时直接读取快照表值拼接，累计逻辑不会断裂。

四、优化建议 #

• 若ID存在重复风险，拼接前可通过FIND_IN_SET判断新ID是否已在累计数组中，避免重复添加
• 建议用JSON类型存储累计数组（如MySQL的JSON），比字符串拼接更便于后续查找、去重操作，示例：

  CASE
      WHEN s.all_ids_to_date IS NULL THEN JSON_ARRAY(t.New_IDs)
      WHEN t.New_IDs IS NULL THEN s.all_ids_to_date
      ELSE JSON_MERGE_PRESERVE(s.all_ids_to_date, JSON_ARRAY(t.New_IDs))
  END AS All_IDs_To_Date
  

内容的提问来源于stack exchange，提问作者theOtherOne