SQL如何按国家分组取每组收入最高SKU（分组取首行）

需求实现SQL方案 #

方案1：支持窗口函数的数据库（适用MySQL8.0+、PostgreSQL、Spark SQL、Hive等绝大多数主流数据库） #

该方案可读性和性能最优，逻辑如下：

1. 先统计每个国家下每个SKU的美元计价总收入
2. 按国家分组，对组内SKU按总收入降序排名
3. 筛选每个国家排名第一的SKU

WITH product_revenue AS (
    SELECT 
        Country,
        "In-app Product" AS sku,
        SUM(Price) AS total_revenue
    FROM cleandataset
    -- 过滤仅保留美元计价的交易，符合需求要求
    WHERE Currency = 'USD'
    GROUP BY Country, "In-app Product"
),
ranked_sku AS (
    SELECT 
        Country,
        sku,
        total_revenue,
        -- 按国家分区，收入从高到低排名
        ROW_NUMBER() OVER (PARTITION BY Country ORDER BY total_revenue DESC) AS revenue_rank
        -- 若需返回同国家所有收入并列第一的SKU，可将ROW_NUMBER()替换为RANK()
    FROM product_revenue
)
SELECT Country, sku, total_revenue
FROM ranked_sku
WHERE revenue_rank = 1;

方案2：兼容不支持窗口函数的低版本数据库（如MySQL5.x） #

通过关联子查询的方式实现相同效果：

SELECT 
    t.Country,
    t."In-app Product" AS sku,
    t.total_revenue
FROM (
    -- 计算每个国家每个SKU的总收入
    SELECT 
        Country,
        "In-app Product",
        SUM(Price) AS total_revenue
    FROM cleandataset
    WHERE Currency = 'USD'
    GROUP BY Country, "In-app Product"
) t
INNER JOIN (
    -- 计算每个国家的最高收入值
    SELECT 
        Country,
        MAX(total_revenue) AS max_revenue
    FROM (
        SELECT 
            Country,
            SUM(Price) AS total_revenue
        FROM cleandataset
        WHERE Currency = 'USD'
        GROUP BY Country, "In-app Product"
    ) t_inner
    GROUP BY Country
) t_max 
ON t.Country = t_max.Country AND t.total_revenue = t_max.max_revenue;

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