如何按优先级从两列筛选值：为每种饮品选销量最高国家

需求概述 #

为每种饮品类型选取销量最高的对应国家：

• 优先依据units_a_sold数据，选取该指标下销量最高的国家
• 若某饮品所有记录的units_a_sold均为0，则依据units_b_sold选取销量最高的国家

原始数据表 #

| beverage | country | units_a_sold | units_b_sold |
| -------- | ------- | ------------ | ------------ |
| fanta    | US      | 184          |          209 |
| fanta    | DE      | 0            |          400 |
| fanta    | UK      | 309          |          107 |
| pepsi    | US      | 0            |          890 |
| pepsi    | DE      | 0            |          345 |
| pepsi    | UK      | 0            |          193 |

待完善的SQL片段 #

WITH temp AS (
    SELECT *
        , RANK() OVER (PARTITION BY app_id ORDER BY units_a_sold DESC) rnk_a
        , RANK() OVER (PARTITION BY app_id ORDER BY units_B_sold DESC) rnk_b
    FROM table
)

SELECT DISTINCT beverage
    , CASE 
        WHEN units_a_sold > 0 THEN (...)

FROM temp
WHERE rnk = 1;

修正并完善后的SQL语句 #

方案一（简洁高效，推荐） #

该方案通过单一窗口函数整合优先级逻辑，直接生成符合需求的排名：

WITH temp AS (
    SELECT 
        beverage,
        country,
        units_a_sold,
        units_b_sold,
        -- 排序优先级：先区分是否有units_a销量，再按units_a降序，最后按units_b降序
        ROW_NUMBER() OVER (
            PARTITION BY beverage 
            ORDER BY 
                CASE WHEN units_a_sold > 0 THEN 1 ELSE 0 END DESC,
                units_a_sold DESC,
                units_b_sold DESC
        ) AS sales_rank
    FROM your_table_name -- 替换为实际表名
)
SELECT 
    beverage,
    country AS top_sales_country
FROM temp
WHERE sales_rank = 1;

代码说明

• 使用ROW_NUMBER()确保每个饮品仅返回一条结果（若需保留并列销量的多个国家，可替换为RANK()）
• 排序逻辑分层：
  1. 优先将units_a_sold>0的记录排在前面，保证优先使用units_a数据
  2. 对有units_a销量的记录，按units_a_sold降序排序
  3. 对无units_a销量的记录，按units_b_sold降序排序
• 最后筛选排名为1的记录，得到每个饮品的目标国家

方案二（基于原片段思路修正） #

如果希望保留原片段的双排名逻辑，可按如下方式完善：

WITH temp AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY beverage ORDER BY units_a_sold DESC) rnk_a,
        RANK() OVER (PARTITION BY beverage ORDER BY units_b_sold DESC) rnk_b
    FROM your_table_name -- 替换为实际表名
)
SELECT DISTINCT
    beverage,
    CASE
        -- 优先选取units_a销量排名第一且销量大于0的国家
        WHEN EXISTS (
            SELECT 1 FROM temp t2 
            WHERE t2.beverage = temp.beverage 
            AND t2.rnk_a = 1 
            AND t2.units_a_sold > 0
        ) THEN (
            SELECT country FROM temp t2 
            WHERE t2.beverage = temp.beverage 
            AND t2.rnk_a = 1 
            AND t2.units_a_sold > 0
        )
        -- 若units_a无有效销量，选取units_b销量排名第一的国家
        ELSE (
            SELECT country FROM temp t2 
            WHERE t2.beverage = temp.beverage 
            AND t2.rnk_b = 1
        )
    END AS top_sales_country
FROM temp
GROUP BY beverage;

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