如何用Starburst SQL识别因子在本周或与上周的变化？

我有多个时间序列（每个对应一个unit，示例聚焦单个unit），因子变化无规律（不同unit的因子变化时间不同）。数据每周更新，当因子发生变化时，该unit的整个时间序列都需要更新。我需要一种简洁高效的方法，识别每个unit中因子在本周内发生变化，或与上周因子存在差异的周数。以下是我当前的实现，但并非最优方案，使用Starburst SQL。

示例数据及原始实现 #

1. 创建示例数据表 #

DROP TABLE IF EXISTS factor_daily ;

CREATE TABLE factor_daily (
    unit varchar(2),
    factor int,
    effdate date
);

INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-02');
INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-03');
INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-04');
INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-05');
INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-08');
INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-09');
INSERT INTO factor_daily VALUES ('A', 1, date'2024-01-10');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-11');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-12');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-16');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-17');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-18');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-19');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-22');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-23');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-24');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-25');
INSERT INTO factor_daily VALUES ('A', 5, date'2024-01-26');
INSERT INTO factor_daily VALUES ('A', 10, date'2024-01-29');
INSERT INTO factor_daily VALUES ('A', 10, date'2024-01-30');
INSERT INTO factor_daily VALUES ('A', 10, date'2024-01-31');
INSERT INTO factor_daily VALUES ('A', 10, date'2024-02-01');
INSERT INTO factor_daily VALUES ('A', 10, date'2024-02-02');

2. 原始实现步骤 #

• 步骤1：添加周标识列，创建中间表factor_wk

DROP TABLE IF EXISTS factor_wk ;
CREATE TABLE factor_wk AS
SELECT *, date_trunc('week', effdate) AS dt_wk
FROM factor_daily;

• 步骤2：识别差异周数，创建中间表delta_factor_wkly

DROP TABLE IF EXISTS delta_factor_wkly ;
CREATE TABLE delta_factor_wkly AS
SELECT *,
        IF((factor_min_wk <> factor_max_wk) -- 本周内因子有变化
            OR (least(lag_factor_min_wk, lag_factor_max_wk) <> least(factor_min_wk, factor_max_wk)) -- 与上周因子不同
            , 1, 0) AS delta
FROM
(
    SELECT  *,
            lag(factor_min_wk) OVER (PARTITION BY unit ORDER BY dt_wk) AS lag_factor_min_wk,
            lag(factor_max_wk) OVER (PARTITION BY unit ORDER BY dt_wk) AS lag_factor_max_wk
    FROM
    (
        SELECT  unit, dt_wk,
                min(factor) AS factor_min_wk,
                max(factor) AS factor_max_wk
        FROM
        (
            SELECT  unit,
                    effdate,
                    factor,
                    date_trunc('week', effdate) AS dt_wk
            FROM factor_daily
        )
    GROUP BY 1, 2
    )
);

• 步骤3：关联表获取最终结果

SELECT l.*, r.delta 
FROM factor_wk AS l
LEFT JOIN
    delta_factor_wkly AS r
ON         l.unit = r.unit
                AND l.dt_wk = r.dt_wk
ORDER BY unit, effdate;

优化后的简洁方案 #

可以通过CTE（公共表表达式）简化逻辑，避免创建多个中间表，同时减少对原始表的扫描次数，直接在一次聚合和窗口计算中完成判断：

WITH weekly_factor AS (
    SELECT 
        unit,
        date_trunc('week', effdate) AS dt_wk,
        MIN(factor) AS min_factor,
        MAX(factor) AS max_factor,
        -- 获取上周的因子值（上周因子恒定时，min和max相等，取其一即可）
        LAG(MIN(factor)) OVER (PARTITION BY unit ORDER BY dt_wk) AS prev_week_factor
    FROM factor_daily
    GROUP BY unit, dt_wk
),
delta_week AS (
    SELECT 
        unit,
        dt_wk,
        -- 两种需要标记的情况：本周内因子变化，或与上周因子不同
        CASE 
            WHEN min_factor <> max_factor THEN 1
            WHEN prev_week_factor IS NOT NULL AND prev_week_factor <> min_factor THEN 1
            ELSE 0
        END AS delta
    FROM weekly_factor
)
SELECT 
    fd.*,
    dw.delta
FROM factor_daily fd
LEFT JOIN delta_week dw 
    ON fd.unit = dw.unit 
    AND date_trunc('week', fd.effdate) = dw.dt_wk
ORDER BY fd.unit, fd.effdate;

优化点说明 #

• 移除了不必要的中间表创建，用CTE串联逻辑，代码更简洁易读
• 周聚合时仅需获取上周的单个因子值（而非同时取min和max），因为上周因子恒定时两者相等，减少计算量
• 合并判断逻辑为清晰的CASE语句，语义更明确
• 仅扫描原始表一次，提升查询效率

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