百万级数据下，基于动作最后出现时间计算时间差的最优方案

针对你这个百万级数据的需求，我们得尽量避免全表扫描和重复计算，核心思路是利用窗口函数或预聚合减少数据处理量，同时通过索引优化把查询速度拉满。下面给出完全匹配你预期结果的优化方案，都是针对大表场景打磨过的：

关键前提：先加索引！ #

不管用哪种方案，第一步必须给(Action, Record_Created)创建联合索引——这是百万级数据查询提速的核心，能让数据库直接定位到每个Action的时间序列，不用傻乎乎扫全表：

CREATE INDEX idx_action_created ON your_table(Action, Record_Created DESC);

这个索引能把查询效率提升几十倍甚至上百倍，一定要先做。

最优实现方案（匹配你的预期结果） #

从你的示例结果反推，需求是：

• 对有多次出现的Action，计算它最后两次出现的时间差（按天算）
• 对仅出现一次的Action，计算它与所有Action中最晚出现时间的差值

下面是高效实现代码：

WITH ranked_actions AS (
    SELECT
        Action,
        Record_Created,
        -- 按Action分组，时间倒序排名，1是最新的记录
        ROW_NUMBER() OVER (PARTITION BY Action ORDER BY Record_Created DESC) AS rn,
        -- 全局最晚时间，一次计算全表复用
        MAX(Record_Created) OVER () AS global_last_created
    FROM your_table
),
action_last_two AS (
    SELECT
        Action,
        Record_Created,
        global_last_created,
        rn
    FROM ranked_actions
    WHERE rn <= 2 -- 只保留每个Action的最后两条记录，大幅减少后续处理量
)
SELECT
    Action,
    CASE
        -- 有两条记录时，计算最后两次的天数差
        WHEN COUNT(*) = 2 THEN DATEDIFF(MAX(CASE WHEN rn=1 THEN Record_Created END), MAX(CASE WHEN rn=2 THEN Record_Created END))
        -- 只有一条记录时，计算与全局最晚时间的天数差
        ELSE DATEDIFF(global_last_created, Record_Created)
    END AS Difference
FROM action_last_two
GROUP BY Action, global_last_created
ORDER BY Action;

为什么这个方案高效？ #

1. 窗口函数利用索引加速：ROW_NUMBER()和MAX() OVER ()会直接使用我们创建的联合索引，不需要全表扫描，百万级数据里几秒就能跑完
2. 数据量大幅压缩：WHERE rn <=2过滤后，每个Action最多只留2条记录，后续聚合处理的数据量最多是「2×不同Action数量」，远小于原始百万级数据
3. 避免重复计算：全局最晚时间只计算一次，全程复用，不会额外消耗资源

备选方案（按最后时间排序算相邻差） #

如果你的需求是把所有Action按最后出现时间排序，计算当前Action与下一个Action的时间差（比如Action3到Action4差0天，Action4到Action5差7天），可以用这个更轻量的方案：

WITH action_last_times AS (
    SELECT
        Action,
        MAX(Record_Created) AS last_created
    FROM your_table
    GROUP BY Action -- 先聚合把百万数据压缩到几百/几千条
),
ranked_last_times AS (
    SELECT
        Action,
        last_created,
        -- 取排序后下一个Action的最后时间
        LEAD(last_created) OVER (ORDER BY last_created DESC) AS next_last_created
    FROM action_last_times
)
SELECT
    Action,
    CASE
        WHEN next_last_created IS NOT NULL THEN DATEDIFF(last_created, next_last_created)
        ELSE DATEDIFF(last_created, (SELECT MIN(last_created) FROM action_last_times))
    END AS Difference
FROM ranked_last_times
ORDER BY last_created DESC;

这个方案适合不同Action数量较少的场景，聚合后的数据量极小，窗口函数计算几乎瞬间完成。

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