如何按用户统计各产品列在半年区间内的Distinct交易日期数

非动态SQL实现（优先推荐） #

核心思路是先将多产品列拆分为行结构（Unpivot），统一统计每个用户、产品、半年区间的唯一交易日期数，再将结果转回列结构（Pivot）匹配期望输出。

完整SQL代码 #

WITH unpivoted_data AS (
    -- 将各产品列拆分为行，保留用户、日期、产品名称及交易金额
    SELECT 
        user_id,
        purchase_date,
        'prod1_amt' AS prod_name,
        prod1_amt AS prod_amt
    FROM trans
    UNION ALL
    SELECT 
        user_id,
        purchase_date,
        'prod2_amt' AS prod_name,
        prod2_amt AS prod_amt
    FROM trans
    UNION ALL
    SELECT 
        user_id,
        purchase_date,
        'prod3_amt' AS prod_name,
        prod3_amt AS prod_amt
    FROM trans
    UNION ALL
    SELECT 
        user_id,
        purchase_date,
        'prod4_amt' AS prod_name,
        prod4_amt AS prod_amt
    FROM trans
    UNION ALL
    SELECT 
        user_id,
        purchase_date,
        'prod5_amt' AS prod_name,
        prod5_amt AS prod_amt
    FROM trans
    UNION ALL
    SELECT 
        user_id,
        purchase_date,
        'prod6_amt' AS prod_name,
        prod6_amt AS prod_amt
    FROM trans
),
period_agg AS (
    -- 计算每个用户、产品、半年区间的唯一交易日期数
    SELECT
        user_id,
        prod_name,
        -- 生成半年区间标识（如2021_h1）
        EXTRACT(YEAR FROM purchase_date) || '_h' || CASE 
            WHEN EXTRACT(QUARTER FROM purchase_date) IN (1,2) THEN '1' 
            ELSE '2' 
        END AS period,
        COUNT(DISTINCT purchase_date) AS trns_cnt
    FROM unpivoted_data
    -- 可选：仅统计有实际交易（金额>0）的日期，若不需要则删除此条件
    WHERE prod_amt > 0
    GROUP BY user_id, prod_name, period
)
-- 将行结构转回列结构，匹配期望输出
SELECT
    user_id,
    -- 每个产品的各半年区间统计值
    MAX(CASE WHEN prod_name='prod1_amt' AND period='2021_h1' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_prod1_amt",
    MAX(CASE WHEN prod_name='prod1_amt' AND period='2021_h2' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_prod1_amt",
    MAX(CASE WHEN prod_name='prod1_amt' AND period='2022_h1' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_prod1_amt",
    MAX(CASE WHEN prod_name='prod1_amt' AND period='2022_h2' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_prod1_amt",
    
    MAX(CASE WHEN prod_name='prod2_amt' AND period='2021_h1' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_prod2_amt",
    MAX(CASE WHEN prod_name='prod2_amt' AND period='2021_h2' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_prod2_amt",
    MAX(CASE WHEN prod_name='prod2_amt' AND period='2022_h1' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_prod2_amt",
    MAX(CASE WHEN prod_name='prod2_amt' AND period='2022_h2' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_prod2_amt",
    
    MAX(CASE WHEN prod_name='prod3_amt' AND period='2021_h1' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_prod3_amt",
    MAX(CASE WHEN prod_name='prod3_amt' AND period='2021_h2' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_prod3_amt",
    MAX(CASE WHEN prod_name='prod3_amt' AND period='2022_h1' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_prod3_amt",
    MAX(CASE WHEN prod_name='prod3_amt' AND period='2022_h2' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_prod3_amt",
    
    MAX(CASE WHEN prod_name='prod4_amt' AND period='2021_h1' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_prod4_amt",
    MAX(CASE WHEN prod_name='prod4_amt' AND period='2021_h2' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_prod4_amt",
    MAX(CASE WHEN prod_name='prod4_amt' AND period='2022_h1' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_prod4_amt",
    MAX(CASE WHEN prod_name='prod4_amt' AND period='2022_h2' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_prod4_amt",
    
    MAX(CASE WHEN prod_name='prod5_amt' AND period='2021_h1' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_prod5_amt",
    MAX(CASE WHEN prod_name='prod5_amt' AND period='2021_h2' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_prod5_amt",
    MAX(CASE WHEN prod_name='prod5_amt' AND period='2022_h1' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_prod5_amt",
    MAX(CASE WHEN prod_name='prod5_amt' AND period='2022_h2' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_prod5_amt",
    
    MAX(CASE WHEN prod_name='prod6_amt' AND period='2021_h1' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_prod6_amt",
    MAX(CASE WHEN prod_name='prod6_amt' AND period='2021_h2' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_prod6_amt",
    MAX(CASE WHEN prod_name='prod6_amt' AND period='2022_h1' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_prod6_amt",
    MAX(CASE WHEN prod_name='prod6_amt' AND period='2022_h2' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_prod6_amt"
FROM period_agg
GROUP BY user_id
ORDER BY user_id;

关键步骤说明 #

1. Unpivot（列转行）：通过UNION ALL将6个产品列拆分为统一的prod_name和prod_amt行数据，避免重复编写统计逻辑。
2. 半年区间聚合：生成period标识（如2021_h1），按user_id、prod_name、period分组统计唯一交易日期数。
3. Pivot（行转列）：使用CASE WHEN + MAX组合将聚合后的行数据转回宽表结构，完全匹配你期望的输出列名。

动态SQL实现（学习参考） #

如果产品列数量较多，手动编写UNION ALL和CASE WHEN会繁琐，可通过动态SQL自动生成逻辑（以Snowflake为例，其他数据库语法略有差异）：

DECLARE
    prod_cols STRING;
    pivot_cols STRING;
BEGIN
    -- 自动获取所有产品列名
    SELECT LISTAGG(COLUMN_NAME, ', ') INTO prod_cols
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TRANS' AND COLUMN_NAME LIKE 'prod%_amt';

    -- 生成Unpivot部分的SQL
    SELECT LISTAGG(
        'SELECT user_id, purchase_date, ''' || COLUMN_NAME || ''' AS prod_name, ' || COLUMN_NAME || ' AS prod_amt FROM trans',
        ' UNION ALL '
    ) INTO prod_union_sql
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TRANS' AND COLUMN_NAME LIKE 'prod%_amt';

    -- 生成Pivot部分的CASE WHEN语句
    SELECT LISTAGG(
        'MAX(CASE WHEN prod_name=''' || COLUMN_NAME || ''' AND period=''2021_h1'' THEN trns_cnt ELSE 0 END) AS "2021_h1_trns_cnt_' || COLUMN_NAME || '",
        MAX(CASE WHEN prod_name=''' || COLUMN_NAME || ''' AND period=''2021_h2'' THEN trns_cnt ELSE 0 END) AS "2021_h2_trns_cnt_' || COLUMN_NAME || '",
        MAX(CASE WHEN prod_name=''' || COLUMN_NAME || ''' AND period=''2022_h1'' THEN trns_cnt ELSE 0 END) AS "2022_h1_trns_cnt_' || COLUMN_NAME || '",
        MAX(CASE WHEN prod_name=''' || COLUMN_NAME || ''' AND period=''2022_h2'' THEN trns_cnt ELSE 0 END) AS "2022_h2_trns_cnt_' || COLUMN_NAME || '"',
        ', '
    ) INTO pivot_cols
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'TRANS' AND COLUMN_NAME LIKE 'prod%_amt';

    -- 拼接完整SQL并执行
    EXECUTE IMMEDIATE '
        WITH unpivoted_data AS (' || prod_union_sql || '),
        period_agg AS (
            SELECT
                user_id,
                prod_name,
                EXTRACT(YEAR FROM purchase_date) || ''_h'' || CASE WHEN EXTRACT(QUARTER FROM purchase_date) IN (1,2) THEN ''1'' ELSE ''2'' END AS period,
                COUNT(DISTINCT purchase_date) AS trns_cnt
            FROM unpivoted_data
            WHERE prod_amt > 0
            GROUP BY user_id, prod_name, period
        )
        SELECT user_id, ' || pivot_cols || '
        FROM period_agg
        GROUP BY user_id
        ORDER BY user_id;
    ';
END;

说明 #

动态SQL通过读取元数据自动生成产品列的处理逻辑，适合产品列数量变动的场景，不同数据库（如MySQL、PostgreSQL）的动态语法需做调整（比如MySQL用PREPARE+EXECUTE）。

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