SQL中按6个月窗口聚合多列去重计数的实现咨询

原代码的核心问题 #

1. 语法错误：

   • BigQuery创建表的语法应为CREATE OR REPLACE TABLE 表名 AS WITH ...，而非将WITH子句嵌套在表定义括号内
   • 字段列表多处缺少逗号（如purchase_date tx_date后、user_id, purch_yr后）
   • 重复选取字段（SELECT *, user_id, purch_yr中user_id已包含在*中）
   • 最终查询逻辑混乱，select *, user_id, sum(tbl2.cnt)存在重复字段且未正确分组

2. 逻辑偏差：
   原代码最后一步的sum(tbl2.cnt)未按用户或时间窗口分组，无法得到按6个月窗口统计的各用户唯一交易日期数。

修正后的解决方案 #

BigQuery 版本 #

CREATE OR REPLACE TABLE desired_output AS
WITH tbl1 AS (
    SELECT 
        user_id,
        purchase_date AS tx_date,
        EXTRACT(YEAR FROM purchase_date) AS purch_yr,
        -- 划分6个月窗口：上半年（Q1-Q2）为1，下半年（Q3-Q4）为2
        CASE 
            WHEN EXTRACT(QUARTER FROM purchase_date) IN (1, 2) THEN 1
            WHEN EXTRACT(QUARTER FROM purchase_date) IN (3, 4) THEN 2
        END AS half_period
    FROM trans
),
tbl2 AS (
    SELECT 
        user_id,
        purch_yr,
        half_period,
        COUNT(DISTINCT tx_date) AS unique_days_count
    FROM tbl1
    GROUP BY user_id, purch_yr, half_period
)
-- 按用户+年度+半年窗口展示统计结果
SELECT * FROM tbl2;

-- 若需按6个月窗口汇总所有用户的唯一日期总数，使用以下查询：
-- SELECT purch_yr, half_period, SUM(unique_days_count) AS total_unique_days
-- FROM tbl2
-- GROUP BY purch_yr, half_period;

SQL Server 版本 #

SQL Server无CREATE OR REPLACE TABLE语法，改用DROP TABLE IF EXISTS+SELECT ... INTO，日期函数略有调整：

DROP TABLE IF EXISTS desired_output;

WITH tbl1 AS (
    SELECT 
        user_id,
        purchase_date AS tx_date,
        YEAR(purchase_date) AS purch_yr,
        CASE 
            WHEN DATEPART(QUARTER, purchase_date) IN (1, 2) THEN 1
            WHEN DATEPART(QUARTER, purchase_date) IN (3, 4) THEN 2
        END AS half_period
    FROM trans
),
tbl2 AS (
    SELECT 
        user_id,
        purch_yr,
        half_period,
        COUNT(DISTINCT tx_date) AS unique_days_count
    FROM tbl1
    GROUP BY user_id, purch_yr, half_period
)
SELECT * INTO desired_output FROM tbl2;

-- 若需按窗口汇总总数：
-- SELECT purch_yr, half_period, SUM(unique_days_count) AS total_unique_days
-- INTO desired_output_summary
-- FROM tbl2
-- GROUP BY purch_yr, half_period;

说明 #

• 代码先将交易日期划分为「年度+半年窗口」（上半年/下半年）
• 再按user_id+purch_yr+half_period分组，统计每个用户在对应6个月窗口内的唯一交易日期数量
• 若需要汇总所有用户在每个6个月窗口的总唯一日期数，可使用注释中的分组查询

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