如何在单SQL查询中结合GROUP BY与COUNT DISTINCT窗口函数？

需求概述 #

• 基于原始销售数据表，按Week、Item、Channel维度聚合，得到该维度下的销量总和SUM_QTY
• 计算CNT_DSNT_STR_WEEK_ITEM_CHNL：仅统计Qty>0的记录中，每个Week+Item+Channel组合下的唯一门店数量
• 计算CNT_DSNT_STR_WEEK_ITEM：仅统计Qty>0的记录中，每个Week+Item组合下的唯一门店数量（跨Channel汇总）

原始表结构与示例数据 #

CREATE TABLE sales_data (
    Week INT,
    Item VARCHAR(50),
    Channel VARCHAR(50),
    Store VARCHAR(50),
    QTY INT
);

INSERT INTO sales_data VALUES
(1, 'A', 'Online', 'S1', 10),
(1, 'A', 'Online', 'S1', 5),
(1, 'A', 'Offline', 'S1', 8),
(1, 'A', 'Offline', 'S2', 3),
(2, 'B', 'Online', 'S3', 0),
(2, 'B', 'Online', 'S4', 12);

预期结果表 #

问题场景 #

用户尝试用窗口函数编写单查询实现需求时，遇到编译错误（多数SQL方言不支持窗口函数内使用COUNT(DISTINCT)），示例错误SQL如下：

SELECT
    Week,
    Item,
    Channel,
    SUM(QTY) AS SUM_QTY,
    COUNT(DISTINCT CASE WHEN QTY>0 THEN Store END) OVER (PARTITION BY Week, Item, Channel) AS CNT_DSNT_STR_WEEK_ITEM_CHNL,
    COUNT(DISTINCT CASE WHEN QTY>0 THEN Store END) OVER (PARTITION BY Week, Item) AS CNT_DSNT_STR_WEEK_ITEM
FROM sales_data
GROUP BY Week, Item, Channel;

常见报错：窗口函数中不允许使用DISTINCT

解决方案 #

可以通过单查询实现，根据SQL引擎支持情况选择以下两种方式：

方式1：支持窗口函数COUNT(DISTINCT)的引擎（如BigQuery、PostgreSQL 13+） #

直接在窗口函数中使用COUNT(DISTINCT)，语法简洁：

SELECT
    Week,
    Item,
    Channel,
    SUM(QTY) AS SUM_QTY,
    COUNT(DISTINCT CASE WHEN QTY > 0 THEN Store END) AS CNT_DSNT_STR_WEEK_ITEM_CHNL,
    COUNT(DISTINCT CASE WHEN QTY > 0 THEN Store END) OVER (PARTITION BY Week, Item) AS CNT_DSNT_STR_WEEK_ITEM
FROM sales_data
GROUP BY Week, Item, Channel;

方式2：不支持窗口函数COUNT(DISTINCT)的引擎（如MySQL、PostgreSQL <13） #

通过关联子查询实现跨Channel的门店去重统计：

SELECT
    sd.Week,
    sd.Item,
    sd.Channel,
    SUM(sd.QTY) AS SUM_QTY,
    COUNT(DISTINCT CASE WHEN sd.QTY > 0 THEN sd.Store END) AS CNT_DSNT_STR_WEEK_ITEM_CHNL,
    (SELECT COUNT(DISTINCT Store) FROM sales_data sd2 WHERE sd2.Week = sd.Week AND sd2.Item = sd.Item AND sd2.QTY > 0) AS CNT_DSNT_STR_WEEK_ITEM
FROM sales_data sd
GROUP BY sd.Week, sd.Item, sd.Channel;

逻辑说明 #

• SUM(QTY)直接按Week+Item+Channel聚合得到销量总和
• CNT_DSNT_STR_WEEK_ITEM_CHNL用COUNT(DISTINCT)结合条件过滤，统计当前维度下的有效门店数
• CNT_DSNT_STR_WEEK_ITEM通过子查询或窗口函数，跨Channel汇总当前Week+Item下的有效门店数

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