在BigQuery中高效实现透视表并统计唯一交易日期的方法

我有一张用户级交易数据表，记录用户每日购买不同产品的情况。需要按用户聚合唯一交易日期的次数，同时针对相同前缀的列执行透视操作，还要按2022年上下半年的日期条件筛选。

可重现数据与表结构 #

表结构 #

CREATE OR REPLACE TABLE transaction_tble (
  user_id char(10),
  transaction_date date,
  catg_prod1_amt int(10),
  catg_prod2_amt int(10),
  catg_prod3_amt int(10),
  sku_item1_qty int(10),
  sku_item2_qty int(10),
  sku_item3_qty int(10)
)

示例数据 #

user_id,transaction_date,sku_sale_prod1,sku_sale_prod2,sku_sale_prod3,catg_purch_prod3,catg_purch_prod4,catg_purch_prod5
A,1/14/2022,10,0,0,0,11,0
B,1/10/2022,0,18,0,5,11,7
A,1/19/2022,6,18,2,5,0,0
A,1/19/2022,10,18,1,5,11,7
B,1/19/2022,10,18,1,5,11,7
C,1/20/2022,10,18,1,5,11,7
C,1/20/2022,10,18,1,5,11,7
B,4/19/2022,10,18,1,5,11,7
A,5/23/2022,10,18,1,5,11,7
A,7/23/2022,10,18,1,5,11,7

我的尝试 #

我认为用透视（Pivot）是正确方向，但代码无法运行：

CREATE OR REPLACE TABLE trans_view (
    select 
        user_id,
        ( case when transaction_date between '2022-01-01' - INTERVAL 6 MONTH then count(distinct transaction_date) end) as 2022_h1_trans_cnt, 
        ( case when transaction_date between '2022-06-01' - INTERVAL 6 MONTH then count(distinct transaction_date) end) as 2022_h2_trans_cnt,

        -- using pivot here
        ( case 
            when transaction_date between '2022-01-01' - INTERVAL 6 MONTH
            and 
            PIVOT (
                count(distinct transaction_date)
                for sku in (
                    'sku_sale_prod1','sku_sale_prod2','sku_sale_prod3'
                )
            )
            then count(distinct transaction_date) 
            end
        ) as 2022_h1_trans_cnt_sku, 
        -- 
        ( case 
            when transaction_date between '2022-01-01' - INTERVAL 6 MONTH
            and 
            PIVOT (
                count(distinct transaction_date)
                for catg in (
                    'catg_purch_prod3','catg_purch_prod4','catg_purch_prod5'
                )
            )
            then count(distinct transaction_date) 
            end
        ) as 2022_h1_trans_cnt_catg
    from transaction_table
    group by user_id 
)

查阅BigQuery的Pivot文档后仍不清楚逻辑错误，求可行解决方法。

期望输出 #

目标视图结构 #

CREATE OR REPLACE VIEW transc_view as(
select
    user_id,
    2022_h1_trans_cnt,
    2022_h2_trans_cnt,
    2022_h1_trans_cnt_catg,
    2022_h2_trans_cnt_catg,
    2022_h1_trans_cnt_sku,
    2022_h2_trans_cnt_sku
from transaction_tble
group by user_id
)

示例输出数据 #

user_id,2022_1st_6mth_trns_cnt,2022_2nd_6mth_trns_cnt,2022_1st_6mth_trns_cnt_sku,2022_2nd_6mth_trns_cnt_sku,2022_1st_6mth_trns_cnt_catg,2022_2nd_6mth_trns_cnt_catg
A,4,1,3,,3,1
B,3,0,3,0,3,0
C,1,1,1,0,1,0

解决方案（BigQuery 兼容） #

核心思路是先将宽表转为长表，聚合后再转回宽表，避免错误嵌套Pivot：

完整SQL代码 #

CREATE OR REPLACE VIEW transc_view AS
WITH date_segmented AS (
    -- 标记每条记录所属的2022年上下半年
    SELECT
        user_id,
        transaction_date,
        CASE
            WHEN transaction_date BETWEEN '2022-01-01' AND '2022-06-30' THEN 'h1'
            WHEN transaction_date BETWEEN '2022-07-01' AND '2022-12-31' THEN 'h2'
            ELSE NULL
        END AS date_period,
        -- 提取sku类产品的交易状态（有购买量则标记为1）
        sku_sale_prod1, sku_sale_prod2, sku_sale_prod3,
        -- 提取catg类产品的交易状态（有购买量则标记为1）
        catg_purch_prod3, catg_purch_prod4, catg_purch_prod5
    FROM transaction_tble
    WHERE EXTRACT(YEAR FROM transaction_date) = 2022
),
unpivoted_sku AS (
    -- 将sku列转成长表，筛选有交易的记录
    SELECT
        user_id,
        transaction_date,
        date_period,
        'sku' AS prod_type
    FROM date_segmented
    UNPIVOT (
        qty FOR sku IN (sku_sale_prod1, sku_sale_prod2, sku_sale_prod3)
    )
    WHERE qty > 0
),
unpivoted_catg AS (
    -- 将catg列转成长表，筛选有交易的记录
    SELECT
        user_id,
        transaction_date,
        date_period,
        'catg' AS prod_type
    FROM date_segmented
    UNPIVOT (
        amt FOR catg IN (catg_purch_prod3, catg_purch_prod4, catg_purch_prod5)
    )
    WHERE amt > 0
),
combined_data AS (
    -- 合并sku和catg的长表，去重（同一用户同一日期同一类型只算一次交易）
    SELECT DISTINCT user_id, transaction_date, date_period, prod_type
    FROM (
        SELECT * FROM unpivoted_sku
        UNION ALL
        SELECT * FROM unpivoted_catg
    )
),
aggregated AS (
    -- 按用户、日期区间、产品类型统计唯一交易日期数
    SELECT
        user_id,
        date_period,
        prod_type,
        COUNT(DISTINCT transaction_date) AS trans_count
    FROM combined_data
    GROUP BY user_id, date_period, prod_type
),
-- 统计总交易次数（不分产品类型）
total_trans AS (
    SELECT
        user_id,
        date_period,
        COUNT(DISTINCT transaction_date) AS total_count
    FROM date_segmented
    GROUP BY user_id, date_period
)
-- 最终透视得到目标结构
SELECT
    t.user_id,
    -- 总交易次数
    MAX(CASE WHEN t.date_period = 'h1' THEN t.total_count ELSE 0 END) AS 2022_1st_6mth_trns_cnt,
    MAX(CASE WHEN t.date_period = 'h2' THEN t.total_count ELSE 0 END) AS 2022_2nd_6mth_trns_cnt,
    -- SKU类交易次数
    MAX(CASE WHEN a.date_period = 'h1' AND a.prod_type = 'sku' THEN a.trans_count ELSE 0 END) AS 2022_1st_6mth_trns_cnt_sku,
    MAX(CASE WHEN a.date_period = 'h2' AND a.prod_type = 'sku' THEN a.trans_count ELSE 0 END) AS 2022_2nd_6mth_trns_cnt_sku,
    -- 品类类交易次数
    MAX(CASE WHEN a.date_period = 'h1' AND a.prod_type = 'catg' THEN a.trans_count ELSE 0 END) AS 2022_1st_6mth_trns_cnt_catg,
    MAX(CASE WHEN a.date_period = 'h2' AND a.prod_type = 'catg' THEN a.trans_count ELSE 0 END) AS 2022_2nd_6mth_trns_cnt_catg
FROM total_trans t
LEFT JOIN aggregated a ON t.user_id = a.user_id AND t.date_period = a.date_period
GROUP BY t.user_id
ORDER BY t.user_id;

代码说明 #

1. date_segmented：标记每条记录属于2022年H1/H2，过滤非2022年数据
2. unpivoted_sku/unpivoted_catg：将宽表的sku/catg列转为长表，筛选有实际购买的记录
3. combined_data：合并两类数据并去重，确保同一用户同一日期同一类型只算一次交易
4. aggregated：按用户、日期区间、产品类型统计唯一交易日期数
5. total_trans：统计用户在各区间的总交易次数
6. 最后用CASE语句实现透视，输出目标格式的结果

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