SQL如何按STORE_NUMBER字段分组并对分组后各门店对应指标求和

核心调整逻辑 #

• 新增GROUP BY子句，将所有非聚合的维度字段（AGENCY、MASTER_CHAIN、CHAIN、STORE_NUMBER、WK_ENDING）纳入分组，保证每个门店唯一对应一行结果
• 所有数值类指标外层增加SUM()聚合函数，实现同门店下所有商品粒度数据的累加
• 百分比类指标不能直接聚合行级计算结果，需先分别求和分子、分母的总数值后再做除法计算，避免统计结果错误
• 现有WHERE条件已经过滤了DEAGY=10、DECHN='WM'，CASE语句中重复的判断条件可以直接删除，简化代码

修改后完整代码 #

SELECT 
    s.DEAGY AS 'AGENCY', 
    l.cmschn AS 'MASTER_CHAIN', 
    s.DECHN AS 'CHAIN', 
    s.DESTR AS 'STORE_NUMBER', 
    s.DEWEDT AS 'WK_ENDING',  
    SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY ELSE 0 END) AS  'OB_UNITS', 
    SUM(CASE WHEN sm.DEIO='I' THEN -1 * sm.DEQTY ELSE 0 END) AS 'RET_UNITS',
    SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY ELSE 0 END) - SUM(CASE WHEN sm.DEIO='I' THEN -1 * sm.DEQTY ELSE 0 END) AS 'NET_UNITS',
    CAST(SUM(CASE WHEN sm.DEIO='I' THEN -1 * sm.DEQTY ELSE 0 END) AS MONEY) 
    / CAST(SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY ELSE 0 END) AS MONEY) * 100 AS '%RET_UNITS',
    SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY * s.DERSP ELSE 0 END) AS OB_BILLING,
    SUM(CASE WHEN sm.DEIO='I' THEN (-1 * SM.DEQTY) * sm.DERSP ELSE 0 END) AS RET_BILLING,
    SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY * s.DERSP ELSE 0 END) - SUM(CASE WHEN sm.DEIO='I' THEN (-1 * sm.DEQTY) * sm.DERSP ELSE 0 END) AS 'NET_BILLING',
    CAST(SUM(CASE WHEN sm.DEIO='I' THEN (-1 * SM.DEQTY) * sm.DERSP ELSE 0 END) AS MONEY) 
    / CAST(SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY * s.DERSP ELSE 0 END) AS MONEY) * 100 AS '%RET_BILLING',
    SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY * s.DERPRC ELSE 0 END) AS OB_MSRP, 
    SUM(CASE WHEN sm.DEIO='I' THEN ( -1 * sm.DEQTY) * sm.DERPRC ELSE 0 END) AS RET_MSRP,
    SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY * s.DERPRC ELSE 0 END) - SUM(CASE WHEN sm.DEIO='I' THEN (-1 * sm.DEQTY) * sm.DERPRC ELSE 0 END) AS 'NET_MSRP',
    CAST(SUM(CASE WHEN sm.DEIO='I' THEN ( -1 * sm.DEQTY) * sm.DERPRC ELSE 0 END) AS MONEY) 
    / CAST(SUM(CASE WHEN s.DEIO='O' THEN s.DEQTY * s.DERPRC ELSE 0 END) AS MONEY) * 100 AS '%RET_MSRP'
INTO ##temp6
FROM REPIT.SMPDTA.SMPDTLM s
JOIN REPIT.LEVYDTA.LDSCHNM l
ON s.DEAGY=l.CMAGY AND s.DECHN=l.CMCHN
JOIN LEVYDTA.SMPRTNM sm
ON s.DEAGY=sm.DEAGY AND s.DECHN=sm.DECHN AND s.DESTR=sm.DESTR AND s.DEWEDT=sm.DEWEDT
WHERE s.DEWEDT = 20210807 AND s.DEAGY=10 AND s.DECHN='WM'
GROUP BY s.DEAGY, l.cmschn, s.DECHN, s.DESTR, s.DEWEDT;

补充说明 #

如果需要处理分母为0导致的除法报错，可以在比率计算逻辑中增加NULLIF判断，比如SUM(...) / NULLIF(SUM(...),0)，避免除0错误。

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