如何从PostgreSQL/ClickHouse分区表创建SCD2类型表？

我有一张按date类型列ds分区的表，包含多个字段。由于并非所有列每天都会变化，大多数行只是前一行的重复。我需要基于这张表创建SCD2类型的表，包含以下字段：

• dt_start：特定值组合开始生效的起始日期
• dt_end：该值组合生效的结束日期；若为当前有效值组合，dt_end为NULL

用户初步尝试的SQL代码：

select ds as dt_start,
       /*__*/(ds) over w1 as dt_end,
       /*all_columns in the table*/
from public.app
window w1 as (partition by user_id, country_id order by ds)
group by /*all_columns in the table*/

示例输入表结构及数据 #

CREATE TABLE public.app(
    ds date NULL,
    user_id int4 NULL,
    country_id int2 NULL,
    n_sessions_1d int2 NULL,
    n_sessions_3d int2 NULL,
    n_sessions_1w int2 NULL,
    n_sessions_2w int2 NULL,
    n_sessions_1m int2 NULL,
    total_time_spent_1d int4 NULL,
    total_time_spent_3d int4 NULL,
    total_time_spent_1w int4 NULL,
    total_time_spent_2w int4 NULL,
    total_time_spent_1m int4 NULL,
    is_subscription_1d int2 NULL,
    is_subscription_3d int2 NULL
)
PARTITION BY RANGE (ds);
CREATE INDEX idx ON ONLY public.app USING btree (user_id, country_id);
CREATE TABLE public.app_202409 PARTITION OF public.app
    FOR VALUES FROM ('2024-09-01') TO ('2024-09-30');

INSERT INTO public.app VALUES
 ('yesterday'::date,1,1,0,0,0,0,0,1,1,1,1,1,2,2)
,('today'::date,    1,1,0,0,0,0,0,1,1,1,1,1,2,2)
,('tomorrow'::date, 1,1,0,0,0,0,0,1,1,1,1,1,2,2)
--different user:
,('yesterday'::date,2,1,0,0,0,0,0,1,1,1,1,1,2,2)
,('today'::date,    2,1,1,0,0,0,0,1,1,1,1,1,2,2)--changed
,('tomorrow'::date, 2,1,1,0,0,0,0,1,1,1,1,1,2,2)
,('today'::date+2,  2,1,0,0,0,0,0,1,1,1,1,1,2,2)--changed back
,('today'::date+3,  2,1,0,0,0,0,0,1,1,1,1,1,2,2)
returning *;

期望输出 #

完整实现SQL #

WITH grouped_data AS (
    SELECT
        ds,
        user_id,
        country_id,
        n_sessions_1d,
        n_sessions_3d,
        n_sessions_1w,
        n_sessions_2w,
        n_sessions_1m,
        total_time_spent_1d,
        total_time_spent_3d,
        total_time_spent_1w,
        total_time_spent_2w,
        total_time_spent_1m,
        is_subscription_1d,
        is_subscription_3d,
        SUM(CASE WHEN prev_row IS NULL OR 
            (n_sessions_1d != prev_n_sessions_1d OR
             n_sessions_3d != prev_n_sessions_3d OR
             n_sessions_1w != prev_n_sessions_1w OR
             n_sessions_2w != prev_n_sessions_2w OR
             n_sessions_1m != prev_n_sessions_1m OR
             total_time_spent_1d != prev_total_time_spent_1d OR
             total_time_spent_3d != prev_total_time_spent_3d OR
             total_time_spent_1w != prev_total_time_spent_1w OR
             total_time_spent_2w != prev_total_time_spent_2w OR
             total_time_spent_1m != prev_total_time_spent_1m OR
             is_subscription_1d != prev_is_subscription_1d OR
             is_subscription_3d != prev_is_subscription_3d)
        THEN 1 ELSE 0 END) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS group_id
    FROM (
        SELECT
            *,
            LAG(n_sessions_1d) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_n_sessions_1d,
            LAG(n_sessions_3d) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_n_sessions_3d,
            LAG(n_sessions_1w) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_n_sessions_1w,
            LAG(n_sessions_2w) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_n_sessions_2w,
            LAG(n_sessions_1m) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_n_sessions_1m,
            LAG(total_time_spent_1d) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_total_time_spent_1d,
            LAG(total_time_spent_3d) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_total_time_spent_3d,
            LAG(total_time_spent_1w) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_total_time_spent_1w,
            LAG(total_time_spent_2w) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_total_time_spent_2w,
            LAG(total_time_spent_1m) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_total_time_spent_1m,
            LAG(is_subscription_1d) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_is_subscription_1d,
            LAG(is_subscription_3d) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_is_subscription_3d,
            LAG(ds) OVER (PARTITION BY user_id, country_id ORDER BY ds) AS prev_row
        FROM public.app
    ) t
),
group_ranges AS (
    SELECT
        group_id,
        user_id,
        country_id,
        MIN(ds) AS dt_start,
        LEAD(MIN(ds)) OVER (PARTITION BY user_id, country_id ORDER BY MIN(ds)) - INTERVAL '1 day' AS dt_end,
        MAX(n_sessions_1d) AS n_sessions_1d,
        MAX(n_sessions_3d) AS n_sessions_3d,
        MAX(n_sessions_1w) AS n_sessions_1w,
        MAX(n_sessions_2w) AS n_sessions_2w,
        MAX(n_sessions_1m) AS n_sessions_1m,
        MAX(total_time_spent_1d) AS total_time_spent_1d,
        MAX(total_time_spent_3d) AS total_time_spent_3d,
        MAX(total_time_spent_1w) AS total_time_spent_1w,
        MAX(total_time_spent_2w) AS total_time_spent_2w,
        MAX(total_time_spent_1m) AS total_time_spent_1m,
        MAX(is_subscription_1d) AS is_subscription_1d,
        MAX(is_subscription_3d) AS is_subscription_3d
    FROM grouped_data
    GROUP BY group_id, user_id, country_id
)
SELECT
    dt_start::date,
    CASE WHEN dt_end IS NOT NULL THEN dt_end::date ELSE NULL END AS dt_end,
    user_id,
    country_id,
    n_sessions_1d,
    n_sessions_3d,
    n_sessions_1w,
    n_sessions_2w,
    n_sessions_1m,
    total_time_spent_1d,
    total_time_spent_3d,
    total_time_spent_1w,
    total_time_spent_2w,
    total_time_spent_1m,
    is_subscription_1d,
    is_subscription_3d
FROM group_ranges
ORDER BY user_id, dt_start;

代码说明 #

• 分组标识生成：利用LAG函数获取每个user_id+country_id分组内前一行的字段值，与当前行对比，字段值变化时生成新分组ID，将连续相同值的行归为一组。
• 日期范围计算：按分组ID聚合，取分组内最小ds作为dt_start；通过LEAD函数获取下一个分组的起始日期，减1天得到当前分组的dt_end，最后一个分组的dt_end设为NULL。
• 字段值提取：同一分组内字段值完全一致，用MAX（或MIN）提取分组内任意一行的字段值即可。

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