根据其他两列中的条件选择一个值

Question

我有下表：

| 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 |

我想了解每种饮料的最大销量所在的国家。优先考虑。如果没有为a出售的单位，我们从为b出售的最大单位中选择国家。

因此，对于芬达，我们将选择英国，而对于百事可乐，我们选择美国

我有这样的东西：

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;

有什么想法吗？

Answer 1

使用MAX()窗口函数检查每个beverage和FIRST_VALUE()窗口函数的units_a_sold中是否存在有效值，以选择正确的country:

SELECT DISTINCT beverage,
CASE 
WHEN MAX(units_a_sold) OVER (PARTITION BY beverage) > 0 
THEN FIRST_VALUE(country) OVER (PARTITION BY beverage ORDER BY units_a_sold DESC) 
ELSE FIRST_VALUE(country) OVER (PARTITION BY beverage ORDER BY units_b_sold DESC)
END country
FROM temp;

请参阅演示