如何用SQL筛选2020年5月营收占比垫底2%的餐厅？

编写SQL查询，返回2020年5月顾客下单的餐厅中，营收占比垫底2%的餐厅列表，需包含餐厅ID及其总营收。总营收通过sum(order_total)计算，需将总营收划分为均匀分布的桶来确定垫底2%。

表结构说明 #

doordash_delivery表字段如下：

customer_placed_order_datetime: datetime
placed_order_with_restaurant_datetime:datetime
driver_at_restaurant_datetime:datetime
delivered_to_consumer_datetime:datetime
driver_id:int
restaurant_id:int
consumer_id:int
is_new:bool
delivery_region:varchar
is_asap:bool
order_total:float
discount_amount:int
tip_amount:float
refunded_amount:float

原查询的问题 #

你原写的CTE逻辑存在几个明显问题：

• 没加2020年5月的订单筛选条件，这是需求的核心范围限制
• 累计占比的计算逻辑混乱，cummlative_percentage的取值规则完全错误，没法准确统计累计营收占比
• 最后一步的row_number()和lead()用法没有明确的筛选目标，根本达不到筛选垫底2%餐厅的效果

正确查询语句 #

WITH restaurant_revenue AS (
    -- 计算2020年5月每个餐厅的总营收，同时算出所有符合条件餐厅的总营收
    SELECT
        restaurant_id,
        SUM(order_total) AS total_revenue,
        SUM(SUM(order_total)) OVER () AS overall_total_revenue
    FROM doordash_delivery
    -- 精准筛选2020年5月的订单
    WHERE customer_placed_order_datetime BETWEEN '2020-05-01' AND '2020-05-31 23:59:59'
    GROUP BY restaurant_id
),
ranked_restaurants AS (
    -- 计算每个餐厅的营收占比，按占比从小到大排序后计算累计占比
    SELECT
        restaurant_id,
        total_revenue,
        (total_revenue / overall_total_revenue) * 100 AS revenue_percentage,
        SUM((total_revenue / overall_total_revenue) * 100) OVER (ORDER BY revenue_percentage ASC) AS cumulative_percentage
    FROM restaurant_revenue
)
-- 筛选累计占比≤2%的餐厅；如果最后一个餐厅的累计占比刚超过2%，也需包含（确保覆盖完整的2%区间）
SELECT
    restaurant_id,
    total_revenue
FROM ranked_restaurants
WHERE cumulative_percentage <= 2
OR (
    cumulative_percentage > 2
    AND LAG(cumulative_percentage) OVER (ORDER BY cumulative_percentage ASC) < 2
);

逻辑说明 #

1. 时间筛选：通过WHERE子句锁定2020年5月的订单，确保数据范围完全符合需求
2. 营收统计：先算出每个餐厅的总营收，再用窗口函数SUM(SUM(order_total)) OVER ()得到所有目标餐厅的总营收，为后续占比计算做准备
3. 累计占比排序：按单个餐厅的营收占比从小到大排序，用窗口函数计算累计占比，这样排在最前面的就是营收最低的餐厅群
4. 垫底2%筛选：直接取累计占比≤2%的餐厅；如果最后一个符合条件的餐厅累计占比刚超过2%，也必须包含它，避免遗漏刚好卡在2%边界的餐厅

内容的提问来源于stack exchange，提问作者aryan awasthi 17UME048