如何使用SQL计算[dbo].[festival]表啤酒销售额十分位分布

需求核心是按累计销售额拆分十分位（而非常规按人数分箱），实现步骤如下：

1. 聚合计算每位参与者的总消费金额，同一个票号的多笔订单合并计算
2. 统计活动总销售额，得出每个十分位对应的目标销售额（总销售额/10）
3. 按消费金额升序排序所有参与者，计算滚动累计消费额，为每个参与者标记所属的十分位
4. 按十分位聚合统计区间上下限、参与人数、区间总销售额后按升序输出

WITH participant_spend AS (
    -- 统计每个参与者的总消费
    SELECT 
        ticket_no,
        SUM(price * quantity) AS total_spend
    FROM [dbo].[festival]
    GROUP BY ticket_no
),
total_sales_calc AS (
    -- 计算总销售额与单十分位目标销售额
    SELECT 
        SUM(total_spend) AS total_amount,
        SUM(total_spend) / 10 AS per_decile_target
    FROM participant_spend
),
decile_marked AS (
    -- 按消费升序计算累计消费，标记所属十分位
    SELECT 
        ticket_no,
        total_spend,
        CEILING(
            SUM(total_spend) OVER (ORDER BY total_spend ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            / (SELECT per_decile_target FROM total_sales_calc)
        ) AS decile
    FROM participant_spend
)
-- 聚合输出最终结果
SELECT 
    decile AS [Decile],
    MIN(total_spend) AS [Lower end of range],
    MAX(total_spend) AS [Upper end of range],
    COUNT(ticket_no) AS [No of participants],
    SUM(total_spend) AS [Total sales in this decile]
FROM decile_marked
GROUP BY decile
ORDER BY decile ASC

如果需要和样例的输出格式完全对齐，可以对金额字段做格式化处理，比如SQL Server中使用FORMAT(MIN(total_spend), 'N2')、MySQL中使用ROUND(MIN(total_spend), 2)即可保留两位小数输出。

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