如何在SQL查询中按周拆分并添加交易数量汇总列？

要实现需求，需要完成时段过滤、按门店+周分组统计、行转列展示三个核心步骤，以下是具体实现：

1. 通用SQL实现（兼容多数数据库） #

SELECT 
    a.restid AS StoreNo,
    -- 按财年周生成对应列，可根据实际周数扩展
    COUNT(DISTINCT CASE WHEN td.fiscalweekno = 'W01' THEN tld.dw_gc_header END) AS "Week 1",
    COUNT(DISTINCT CASE WHEN td.fiscalweekno = 'W02' THEN tld.dw_gc_header END) AS "Week 2",
    COUNT(DISTINCT CASE WHEN td.fiscalweekno = 'W03' THEN tld.dw_gc_header END) AS "Week 3",
    COUNT(DISTINCT CASE WHEN td.fiscalweekno = 'W04' THEN tld.dw_gc_header END) AS "Week 4"
FROM 
    tbc.tbcdbv.tld_fact_v1 tld
LEFT JOIN 
    tbcdb.align_dim a ON a.dw_restid = tld.dw_restid
LEFT JOIN 
    tbcdbv.time_day_dim_v1 td ON tld.dw_day = td.dw_day
WHERE 
    td.fiscalyearno = 'Y2022'
    -- 过滤晚8点到凌晨3点的交易，需替换为实际交易时间字段
    AND (EXTRACT(HOUR FROM tld.transaction_time) >= 20 OR EXTRACT(HOUR FROM tld.transaction_time) <= 3)
GROUP BY 
    a.restid
ORDER BY 
    a.restid;

2. 关键细节说明 #

• 时段过滤：需确保tld_fact_v1表包含交易具体时间字段（如transaction_time），若数据库不支持EXTRACT(HOUR)，替换为对应函数：
  • MySQL：HOUR(tld.transaction_time)
  • SQL Server：DATEPART(HOUR, tld.transaction_time)
  • Oracle：EXTRACT(HOUR FROM CAST(tld.transaction_time AS TIMESTAMP))
• 周字段适配：若time_day_dim_v1中的周编号字段不是fiscalweekno，替换为实际字段（如week_no、fiscal_week）
• 动态扩展周列：若财年周数较多，手动编写CASE WHEN繁琐，可使用数据库专属的行转列语法简化：
  • PostgreSQL：使用crosstab函数
  • BigQuery/SQL Server：使用PIVOT语法
  • MySQL：通过存储过程生成动态SQL

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