Syntomic

5201 字约 14 分钟

数仓里大部分表都是按天分区的增量表。要算「过去 30 天的成交额」这类指标，最自然的写法是每天把最近 30 个分区扫一遍。问题在于：今天扫的 30 个分区里，有 29 个昨天已经扫过了。这篇文章介绍几种增量计算的办法，避免重复扫描，提高计算效率。

现象：每天重复扫描同样的分区

先区分两种常见的时间窗口：

  • 滑动窗口：近 N 天，起点和终点每天一起往后挪，比如「近 30 天成交额」；

  • 累计窗口：起点固定，终点每天往后挪，比如「本月至今成交额」。

最直接的实现是把需要的分区都扫一遍，再按条件聚合（变量 ${bizdate} 表示业务日期，${bizdate-nd} 表示 N 天前）：

sql
SELECT  pk
        -- 滑动窗口：开始和结束时间每天变化
        ,SUM(IF(dt > '${bizdate-nd}' AND dt <= '${bizdate}', m, 0)) AS m_sum_nd
        ,MAX(IF(dt > '${bizdate-nd}' AND dt <= '${bizdate}', m, 0)) AS m_max_nd
        -- 累计窗口：开始时间固定，结束时间变化
        ,SUM(IF(dt > '${startdate}' AND dt <= '${bizdate}', m, 0)) AS m_sum_td
        ,MAX(IF(dt > '${startdate}' AND dt <= '${bizdate}', m, 0)) AS m_max_td
FROM    a_1d
-- 假定 startdate 晚于 bizdate-nd
WHERE   dt > '${bizdate-nd}'
AND     dt <= '${bizdate}'
GROUP BY pk;

这样做有两个明显的浪费：

  • 每天反复扫描大量相同的分区，产出慢、成本高；

  • 同一个作业里混着不同周期的指标时，为了保证长周期指标的产出，短周期指标也被跟着重复计算。

下面结合阿里云 MaxCompute 讨论几种解法。

递推：今天的 N 天 = 昨天的 N 天 + 今天 − N 天前

能递推的指标

如果指标存在递推公式，就可以用昨天的结果加上今天的变化得到今天的结果，不必回头扫描。

累计窗口最简单，求和与最大值都能直接递推：

∑i=0nai=∑i=0n−1ai+anmax⁡0≤i≤nai=max⁡(max⁡0≤i≤n−1ai, an)\sum_{i=0}^{n} a_i = \sum_{i=0}^{n-1} a_i + a_n \qquad \max_{0 \le i \le n} a_i = \max\left(\max_{0 \le i \le n-1} a_i,\ a_n\right)

滑动窗口的求和也可以：新的一天进入窗口，最老的一天离开窗口：

∑i=1nai=∑i=0n−1ai+an−a0\sum_{i=1}^{n} a_i = \sum_{i=0}^{n-1} a_i + a_n - a_0
滑动求和：昨天的结果加上新的一天、减去离开窗口的一天，每天只读 3 个分区

这样每天只需要读 3 个分区，而不是 30 个；窗口越长，省得越多。

天下没有免费的午餐，递推也有代价：

  • 作业要依赖自己前一天的结果（自依赖），补数据时只能按天串行执行；

  • 遇到紧急需求，往往还要先用暴力扫描初始化一遍历史数据，开发成本更高。

写法一：JOIN

把今天的增量、昨天的结果和 N 天前的增量按主键关联起来：

sql
SELECT  COALESCE(today.pk, yst.pk) AS pk
        -- 近 N 天：昨天的 N 天 + 今天 - N 天前的那一天
        ,COALESCE(yst.m_sum_nd, 0) + COALESCE(today.m_1d, 0) - COALESCE(nd.m_1d, 0) AS m_sum_nd
        -- 累计至今：昨天的累计 + 今天
        ,COALESCE(yst.m_sum_td, 0) + COALESCE(today.m_1d, 0) AS m_sum_td
        ,GREATEST(COALESCE(yst.m_max_td, today.m_1d), COALESCE(today.m_1d, yst.m_max_td)) AS m_max_td
FROM    (
            SELECT  pk, m_1d
            FROM    a_1d
            WHERE   dt = '${bizdate}'
        ) today
FULL OUTER JOIN (
            SELECT  pk, m_sum_nd, m_sum_td, m_max_td
            FROM    a_nd
            WHERE   dt = '${bizdate-1d}'
        ) yst
ON      today.pk = yst.pk
LEFT JOIN (
            SELECT  pk, m_1d
            FROM    a_1d
            WHERE   dt = '${bizdate-nd}'
        ) nd
ON      COALESCE(today.pk, yst.pk) = nd.pk;

写法直观，但 Join 本身代价不小，而且多张表要依次关联，维度和周期一多，提升就不明显了。

写法二：UNION ALL

换个思路：三份数据各自补齐成相同的列，UNION ALL 拼在一起，再按主键汇总一次。三个子查询可以并行，效率更高：

sql
SELECT  pk
        ,SUM(m_sum_nd + m_1d - m_np1d) AS m_sum_nd
FROM    (
            -- 昨天的近 N 天
            SELECT pk, m_sum_nd, 0 AS m_1d, 0 AS m_np1d
            FROM   a_nd
            WHERE  dt = '${bizdate-1d}'
            UNION ALL
            -- 今天新增
            SELECT pk, 0 AS m_sum_nd, m_1d, 0 AS m_np1d
            FROM   a_1d
            WHERE  dt = '${bizdate}'
            UNION ALL
            -- N 天前离开窗口的那一天
            SELECT pk, 0 AS m_sum_nd, 0 AS m_1d, m_1d AS m_np1d
            FROM   a_1d
            WHERE  dt = '${bizdate-nd}'
        ) a
GROUP BY pk;

缺点是字段要逐个对齐，指标一多就很繁琐。这种机械的活很适合交给大模型，下面是我用过的提示词：

prompt

You are a SQL expert that knows use progressive method to calculate past periods metircs. For example, when you need calculate past 30 days metric each day, you can use recurrence formula: use yesterday 30 days metric plus today 1 day metric substract 30 days ago 1 day metric Pay attention to use variable like ${bizdate-30}, not use date functions. Use the following format: Question: "Question here" Answer: "SQL Query to run" Here an example: Question: "Suppose we have two tables: CREATE TABLE a_1d ( pk STRING ,m_1d BIGINT ,n_1d BIGINT ,dt STRING ); CREATE TABLE a_nd ( pk STRING ,m_7d BIGINT ,n_7d BIGINT ,m_30d BIGINT ,n_30d BIGINT ,dt STRING ); How to calculate passed 7, 30 days metrics using SUM aggregation progressively by a_1d and a_nd? Answer: SELECT pk ,SUM(m_7d + m_1d - m_8d) AS m_7d ,SUM(n_7d + n_1d - n_8d) AS n_7d ,SUM(m_30d + m_1d - m_31d) AS m_30d ,SUM(n_30d + n_1d - n_31d) AS n_30d FROM ( SELECT pk ,m_nd ,n_nd ,0 AS m_1d ,0 AS n_1d ,0 AS m_8d ,0 AS n_8d ,0 AS m_31d ,0 AS n_31d FROM a_nd WHERE dt = '${bizdate-1}' UNION ALL SELECT pk ,0 AS m_nd ,0 AS n_nd ,m_1d ,n_1d ,0 AS m_8d ,0 AS n_8d ,0 AS m_31d ,0 AS n_31d FROM a_1d WHERE dt = '${bizdate}' UNION ALL SELECT pk ,0 AS m_nd ,0 AS n_nd ,0 AS m_1d ,0 AS n_1d ,m_1d AS m_8d ,n_1d AS n_8d ,0 AS m_31d ,0 AS n_31d FROM a_1d WHERE dt = '${bizdate-7}' UNION ALL SELECT pk ,0 AS m_nd ,0 AS n_nd ,0 AS m_1d ,0 AS n_1d ,0 AS m_8d ,0 AS n_8d ,m_1d AS m_31d ,n_1d AS n_31d FROM a_1d WHERE dt = '${bizdate-30}' ) a GROUP BY pk;" Question: "Suppose we have two tables: {1d table DDL} and {nd table DDL} How to calculate passed {periods} days metrics using SUM aggregation progressively by {1d table name} and {nd table name} ? " Answer: "

不能递推的指标：把窗口里的明细带上

滑动窗口的最大值没法递推，那就把窗口里每一天的值都存下来。可以用 MAP 之类的复杂类型保存「日期 → 当天的值」，每天做两件事：

  1. 增量维护：去掉已经离开窗口的那一天，加上今天；

  2. 在复杂类型上聚合：用复杂类型函数对窗口内的值求最大值。

sql
SELECT  pk
        ,ARRAY_MAX(MAP_VALUES(MAP_FILTER(m_map, (k, v) -> k > '${bizdate-nd}'))) AS m_max_nd
        ,m_map
FROM    (
            SELECT  COALESCE(today.pk, yst.pk) AS pk
                    -- 维护近 N 天的 map：去掉过期的日期，加上今天
                    ,MAP_CONCAT(
                        MAP_FILTER(COALESCE(yst.m_map, MAP()), (k, v) -> k > '${bizdate-nd}'),
                        MAP('${bizdate}', COALESCE(today.m_1d, 0))
                    ) AS m_map
            FROM    (
                        SELECT pk, m_map
                        FROM   a_nd
                        WHERE  dt = '${bizdate-1d}'
                    ) yst
            FULL JOIN (
                        SELECT pk, m_1d
                        FROM   a_1d
                        WHERE  dt = '${bizdate}'
                    ) today
            ON      yst.pk = today.pk
        ) mid;

缺点是复杂类型的写法门槛较高；指标多了，每个都要带一份 N 天的明细，存储和计算也会变重。

把三种做法放在一起比一比：拖动「今天是第几天」，看每种做法要读哪些分区，算出来的近 N 天求和与最大值对不对。第 9 天有一个峰值，留意它离开窗口的那一天。

近 N 天指标：扫描、递推与明细每天算一次近 N 天的求和与最大值：三种做法各读哪些分区，算得对不对。
  1. 暴力扫描
  2. 递推
  3. 带上明细

近 7 天 · 第 20 天 · 从「递推」看起

运行后可调整参数，观察变化

渐进计算：让平台替你做

改代码的投入产出比还是太低。有没有不改代码的办法？MaxCompute 的渐进计算带来了曙光：在原来暴力扫描的 SQL 前加一行参数就行。

sql
-- 开启渐进计算
SET odps.progressive.enable=true;

-- 下面是和开头完全相同的暴力扫描 SQL
SELECT  pk
        ,SUM(IF(dt > '${bizdate-nd}' AND dt <= '${bizdate}', m, 0)) AS m_sum_nd
        ,MAX(IF(dt > '${bizdate-nd}' AND dt <= '${bizdate}', m, 0)) AS m_max_nd
        ,SUM(IF(dt > '${startdate}' AND dt <= '${bizdate}', m, 0)) AS m_sum_td
        ,MAX(IF(dt > '${startdate}' AND dt <= '${bizdate}', m, 0)) AS m_max_td
FROM    a_1d
WHERE   dt > '${bizdate-nd}'
AND     dt <= '${bizdate}'
GROUP BY pk;

原理大致是：

  • 第一次运行时，产出可以复用的中间结果，比如按天、按月预先聚合好的表；

  • 之后每次运行，只计算当天的增量，再和中间结果合并出最终结果。


优点

缺点

渐进计算

不用改业务逻辑；中间表的合并、拆分和生命周期由平台管理

首次运行要生成中间表，耗时较长，最好在周期开始前先跑一次；通用优化的效果可能不如手写递推；用的人少，遇到问题较难排查

更进一步：定义即研发

再往前一步，是在指标定义阶段就把计算方式交给平台。比如 Dataphin 的规范建模：开发者只声明原子指标、统计周期和统计粒度，平台自动生成计算代码，递推、渐进这类优化也由平台统一完成。

总结

方法

每天扫描

改动

适用

暴力扫描

N 个分区

无

窗口短、数据量小

递推（JOIN / UNION ALL）

3 个分区

改写 SQL，作业变成自依赖

求和、计数等可递推的指标

复杂类型保存明细

2 个分区

改写 SQL，存储变大

最大值等不可递推的指标

渐进计算

平台决定

加一行参数

不想改逻辑，平台支持

这些技巧本质上都是同一个思想：只处理变化的部分。我更希望它们最终都不用开发者操心，而是集成在开发平台里，开发者只需要关心业务逻辑。这个思想在流计算里走得更远，相关的讨论见 我理解的流批一体。

参考资料


全文评论

0

正在加载评论…