利用关联表start_date处理SQL中缺失的滞后值问题

现有两张表结构如下：

表1：ozon.campaign #

CREATE TABLE ozon.campaign 
(
    id int8 NOT NULL, 
    title text NOT NULL, 
    state text NULL, 
    adv_object_type text NULL, 
    start_date date NULL, 
    stop_date date NULL, 
    daily_budget int8 NULL, 
    api_account_id int4 NULL, 
    budget_all numeric NULL, 
    payment_type text NULL, 
    product_autopilot_strategy text NULL, 
    product_campaign_mode text NULL, 
    placement text NULL, 
    CONSTRAINT campaign_pk PRIMARY KEY (id)
);

表2：ozon.campaign_banner_reach_stat #

CREATE TABLE ozon.campaign_banner_reach_stat 
(
    campaign_id int8 NOT NULL,
    report_date date NOT NULL,
    reach numeric NULL,
    CONSTRAINT campaign_reach_stat_pk PRIMARY KEY (campaign_id, report_date)
);

已编写视图计算每日增量触达量（incremental_reach），逻辑为当日reach减去前一日reach，若差值为负则取0：

CREATE OR REPLACE VIEW ozon.campaign_banner_reach_stat_v
AS 
    SELECT 
        cbrs.campaign_id,
        cbrs.report_date,
        CASE
            WHEN (cbrs.reach - lag(cbrs.reach) OVER (PARTITION BY cbrs.campaign_id ORDER BY cbrs.report_date)) < 0::numeric 
                THEN 0::numeric
                ELSE cbrs.reach - lag(cbrs.reach) OVER (PARTITION BY cbrs.campaign_id ORDER BY cbrs.report_date)
        END AS incremental_reach
    FROM 
        ozon.campaign_banner_reach_stat cbrs;

优化需求 #

当campaign_banner_reach_stat表中缺失前一日数据时，满足以下任一条件则直接取当日reach作为增量触达量，而非返回null：

• 当日记录的report_date等于关联campaign表的start_date
• campaign表的start_date为空，且当日是该campaign在统计表中的首条记录

示例数据 #

campaign表数据

campaign_banner_reach_stat表数据

期望视图结果 #

解决方案 #

需要关联campaign表，同时结合lag()和row_number()函数判断首条记录，调整后的视图SQL如下：

CREATE OR REPLACE VIEW ozon.campaign_banner_reach_stat_v
AS 
SELECT 
    cbrs.campaign_id,
    cbrs.report_date,
    CASE
        -- 差值为负时返回0
        WHEN (cbrs.reach - COALESCE(lag(cbrs.reach) OVER (PARTITION BY cbrs.campaign_id ORDER BY cbrs.report_date), 0)) < 0::numeric
            THEN 0::numeric
        -- 无前置数据时，判断是否符合取当日reach的条件
        WHEN lag(cbrs.reach) OVER (PARTITION BY cbrs.campaign_id ORDER BY cbrs.report_date) IS NULL THEN
            CASE
                WHEN c.start_date IS NOT NULL AND cbrs.report_date = c.start_date THEN cbrs.reach
                WHEN c.start_date IS NULL AND row_number() OVER (PARTITION BY cbrs.campaign_id ORDER BY cbrs.report_date) = 1 THEN cbrs.reach
                ELSE 0::numeric -- 兜底逻辑，可按需调整
            END
        -- 正常场景：当日reach减前一日reach
        ELSE cbrs.reach - lag(cbrs.reach) OVER (PARTITION BY cbrs.campaign_id ORDER BY cbrs.report_date)
    END AS incremental_reach
FROM 
    ozon.campaign_banner_reach_stat cbrs
LEFT JOIN 
    ozon.campaign c ON cbrs.campaign_id = c.id;

逻辑说明 #

1. 关联campaign表：通过LEFT JOIN获取每个campaign的start_date，确保即使无对应campaign记录也能保留统计数据（若需严格关联可改为INNER JOIN）。
2. 识别无前置数据：用lag(cbrs.reach) IS NULL判断当前记录是否缺失前一日数据。
3. 首条记录判断：通过row_number() OVER (PARTITION BY campaign_id ORDER BY report_date) = 1定位campaign在统计表中的第一条记录。
4. 优先级处理：先处理差值为负的场景，再处理无前置数据的特殊规则，最后执行正常计算，确保所有场景覆盖。

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