如何复用SELECT别名计算特定客户销量占门店总销量的占比？

需求说明 #

现有表结构：

TransactionID     Establishment    Client
TR001             Estab1           XXX
TR002             Estab1           YYY
...
TR00X             EstabN           XXX

需要统计各门店针对XX、YY、ZZ这几个特定客户的销量，以及该销量占对应门店总销量的百分比，期望结果：

Establishment    Client    Nbr_Sales    %Total_sales
Estab1           XXX       64           0.35%
Estab1           YYY       321          0.17%
Estab2           XXX       2325         25%
...

实现方法 #

方法1：子查询关联 #

把你已写好的门店总销量查询作为子查询，和特定客户销量的查询做关联，就能拿到对应门店的总销量来计算占比：

SELECT 
    t1.Establishment,
    t1.Client,
    t1.Nbr_Sales,
    ROUND((t1.Nbr_Sales / t2.tot_sales_by_estab) * 100, 4) AS %Total_sales
FROM (
    -- 特定客户的门店销量
    SELECT 
        Establishment, 
        Client, 
        Count(*) as Nbr_Sales
    FROM data_list 
    WHERE Client IN ('XX', 'YY', 'ZZ') -- 用IN替代OR更简洁
    GROUP BY Establishment, Client
) t1
JOIN (
    -- 各门店总销量
    SELECT 
        Establishment, 
        Count(*) AS tot_sales_by_estab
    FROM data_list 
    GROUP BY Establishment
) t2 ON t1.Establishment = t2.Establishment;

注：如果数据库中存在门店总销量为0的情况，建议用NULLIF(t2.tot_sales_by_estab, 0)替换t2.tot_sales_by_estab，避免触发除以0的错误。

方法2：窗口函数（更简洁） #

用窗口函数COUNT(*) OVER (PARTITION BY Establishment)直接计算每个门店的总销量，无需单独写子查询：

SELECT 
    Establishment,
    Client,
    Count(*) AS Nbr_Sales,
    ROUND((Count(*) * 100.0) / COUNT(*) OVER (PARTITION BY Establishment), 4) AS %Total_sales
FROM data_list
WHERE Client IN ('XX', 'YY', 'ZZ')
GROUP BY Establishment, Client;

注：这里给Count()乘以100.0是为了触发浮点运算，避免整数除法导致结果被截断为0的问题。

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