Presto中按时间戳聚合事件统计物品存量的SQL实现

一、Presto环境下的SQL实现 #

你之前用FULL JOIN合并两表事件的逻辑存在缺陷：当同一时间戳下存在多条同类型/不同类型事件时，会产生笛卡尔积导致计数错误。正确的实现需要先通过UNION ALL打平两类事件，再按规则聚合计算，步骤如下：

1. 构造统一事件流：CREATED事件标记增量为+1，DELETED事件标记增量为-1
2. 按时间戳分组聚合，计算每个时间戳的总增量，直接过滤掉总增量为0的记录（即新增删除完全抵消的时点）
3. 按时间戳升序排序，通过窗口函数累计计算截至每个时点的总存量

对应可直接运行的SQL代码如下：

WITH all_events AS (
    -- 打平所有新增事件
    SELECT timestamp, 1 AS delta
    FROM created
    UNION ALL
    -- 打平所有删除事件
    SELECT timestamp, -1 AS delta
    FROM deleted
),
ts_delta AS (
    -- 按时间戳聚合净增量，过滤掉增量为0的时点
    SELECT 
        timestamp,
        SUM(delta) AS net_delta
    FROM all_events
    GROUP BY timestamp
    HAVING SUM(delta) != 0
)
-- 累计求和得到各时点存量
SELECT
    timestamp,
    SUM(net_delta) OVER (ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "number of items"
FROM ts_delta
ORDER BY timestamp;

执行上述代码即可匹配预期结果，其中2021-11-19 09:00:00.000时点新增和删除各1条，净增量为0，会被自动过滤不会出现在结果中，完全覆盖给出的所有边界规则。

二、SQL实现这类时序聚合计算的局限性 #

• 事件顺序容错性差：这类累计计算强依赖事件的时间顺序，如果仅靠时间戳排序，当出现时间戳精度不足（比如同毫秒下有多条事件）、数据乱序（比如早发生的事件晚写入）的情况，没有额外的顺序标记（比如Kafka偏移量、事件自增ID）的话，很容易出现存量计算错误，且问题很难排查。
• 大规模数据下性能差：Presto本身是无状态的查询引擎，每次执行查询都需要从Kafka拉取全量历史事件重新计算，当事件量级达到千万、亿级时，全量扫描+窗口计算的延迟会非常高，甚至无法完成查询，没法做到低延迟的实时统计。
• 复杂时序规则适配成本极高：如果要新增迟到事件修正、会话窗口统计、异常事件过滤这类常见时序需求，纯SQL实现会非常臃肿，多层嵌套后不仅可读性差，性能还会进一步下降；类似“迟到1小时以上的事件要回滚修正之前所有存量结果”这类需求，纯SQL基本无法高效实现。
• 结果一致性无法保证：Kafka数据是持续流入的，不同时间执行同一个查询，拉取到的数据集范围不同，得到的存量结果可能存在差异，没有办法保证固定时点统计结果的一致性，也很难回溯历史某一时刻的准确存量。
• 多维度扩展困难：如果后续需要按物品分类、操作人等不同维度拆分统计存量，SQL的复杂度会线性上升，资源消耗也会成倍增加，维护成本很高。

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