按Order ID分组统计含食品订单满足条件占比的SQL实现求助

订单数据整理 #

先把收银系统的订单数据整理为清晰表格：

问题1：饮品数量≥食品数量的订单占比 #

可以用CTE（公共表表达式）先统计每个订单的食品、饮品总数量，再筛选计算占比：

WITH order_summary AS (
    SELECT
        "Order ID" AS order_id,
        SUM(CASE WHEN Category LIKE 'Food -> %' THEN Qty ELSE 0 END) AS total_food_qty,
        SUM(CASE WHEN Category LIKE 'Drinks -> %' THEN Qty ELSE 0 END) AS total_drinks_qty
    FROM your_table_name
    GROUP BY "Order ID"
)
SELECT
    ROUND(
        (COUNT(CASE WHEN total_drinks_qty >= total_food_qty THEN 1 END) * 100.0) 
        / COUNT(CASE WHEN total_food_qty > 0 THEN 1 END),
        0
    ) AS drinks_ge_food_percentage
FROM order_summary;

逻辑说明：

1. order_summary按订单分组，用CASE语句分别统计食品总数量（匹配Food ->开头的类别）和饮品总数量（匹配Drinks ->开头的类别）。
2. 外层查询中：
   • 分子统计含食品且饮品数量≥食品数量的订单数；
   • 分母统计所有含食品的订单数；
   • 计算占比并四舍五入到整数，结果和示例一致为33%。

问题2：未订购饮品的食品订单占比 #

基于同样的order_summary，直接统计饮品数量为0的食品订单占比：

WITH order_summary AS (
    SELECT
        "Order ID" AS order_id,
        SUM(CASE WHEN Category LIKE 'Food -> %' THEN Qty ELSE 0 END) AS total_food_qty,
        SUM(CASE WHEN Category LIKE 'Drinks -> %' THEN Qty ELSE 0 END) AS total_drinks_qty
    FROM your_table_name
    GROUP BY "Order ID"
)
SELECT
    ROUND(
        (COUNT(CASE WHEN total_food_qty > 0 AND total_drinks_qty = 0 THEN 1 END) * 100.0) 
        / COUNT(CASE WHEN total_food_qty > 0 THEN 1 END),
        0
    ) AS no_drinks_percentage
FROM order_summary;

逻辑说明：

1. 分子统计含食品且饮品数量为0的订单数；
2. 分母为所有含食品的订单数；
3. 计算占比后四舍五入，结果和示例一致为33%。

替代方案：子查询实现 #

如果不使用CTE，也可以用子查询嵌套，逻辑完全一致：

-- 问题1的子查询写法
SELECT
    ROUND(
        (COUNT(CASE WHEN total_drinks_qty >= total_food_qty THEN 1 END) * 100.0) 
        / COUNT(CASE WHEN total_food_qty > 0 THEN 1 END),
        0
    ) AS drinks_ge_food_percentage
FROM (
    SELECT
        "Order ID" AS order_id,
        SUM(CASE WHEN Category LIKE 'Food -> %' THEN Qty ELSE 0 END) AS total_food_qty,
        SUM(CASE WHEN Category LIKE 'Drinks -> %' THEN Qty ELSE 0 END) AS total_drinks_qty
    FROM your_table_name
    GROUP BY "Order ID"
) AS order_summary;

注意：将your_table_name替换为你实际使用的表名即可。

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