SQL Group By分组查询如何获取分组外的品类总销量

实现思路 #

你要输出的4个字段，用窗口聚合函数实现最简洁，性能也最好，不需要写冗余的嵌套关联子查询：

• 门店：取门店维度表的门店名称字段
• 商品品类：取商品维度表的品类名称字段
• 指定门店对应品类销售件数：按门店+品类分组聚合事实表销量即可，这部分你已经写对了
• 指定品类全门店总销售件数：通过窗口函数按品类分区，跨门店汇总同品类销量即可

原有代码问题 #

你注释掉的子查询跑不出正确结果，核心问题有3个：

• 没有和外层查询的品类字段做关联，匹配不到当前行对应的品类
• 没有加和外层一致的时间过滤条件，统计出来的总销量会包含非目标时间段的数据
• 就算补全上面两个逻辑，相关子查询会重复扫描销售事实表，数据量大的时候跑数很慢

可直接运行的正确代码 #

SELECT 
    s.store_name AS [Drug Store],
    g.group_name AS [Category],
    SUM(f.quantity) AS [Sales pcs.],
    -- 按品类分区汇总，得到全门店同品类总销量
    SUM(SUM(f.quantity)) OVER(PARTITION BY g.group_name) AS [Total sales]
FROM 
    [dbo].[fct_cheque] AS f
INNER JOIN
    [dim_stores] AS s ON s.store_id = f.store_id
INNER JOIN
    dim_goods AS g ON g.good_id = f.good_id 
WHERE
    date_id BETWEEN '20170601' AND '20170630'
GROUP BY
    s.store_name, g.group_name

逻辑说明 #

• 外层GROUP BY s.store_name, g.group_name先把数据聚合到「单店+单品类」粒度，SUM(f.quantity)就是当前门店当前品类的销售件数
• 窗口函数SUM(SUM(f.quantity)) OVER(PARTITION BY g.group_name)在上述聚合结果的基础上，把所有相同品类的单店销量做加总，直接得到该品类在全门店的总销量
• 窗口计算会自动继承外层WHERE的时间过滤规则，不需要重复写时间判断，不会出现总销量和单店销量统计时间范围不一致的问题

只有在使用SQL Server 2005及更早的极老版本（不支持窗口函数）时，才需要用CTE先预聚合各品类全门店总销量，再和门店品类粒度的结果关联。目前绝大多数生产环境都支持窗口函数写法，优先用上面的代码，少扫一次事实表，查询效率高很多。

内容的提问来源于stack exchange，提问作者Николай Беляков