如何在SQL中筛选出每月销量最高的单个品类数据？

需求 #

筛选出每个月销量最高的单个品类。

现有代码（仅支持查询单月数据） #

client.query('''SELECT year,
                       month,
                       category,
                       num_of_product
                FROM (SELECT EXTRACT(year from created_at) as year,
                             EXTRACT(month from created_at) as month,
                             b.category,
                             COUNT(b.category) AS num_of_product,
                      FROM `bigquery-public-data.thelook_ecommerce.order_items` as a
                      INNER JOIN `bigquery-public-data.thelook_ecommerce.products` as b
                       ON a.product_id=b.id
                      WHERE status='Complete'
                       AND created_at BETWEEN \"2022-01-01\" AND \"2022-09-30\"
                      GROUP BY year, month, b.category)
                WHERE month=1
                ORDER BY year ASC, month ASC, num_of_product DESC''') \
      .to_dataframe()

已尝试方案（仅能获取最高销量数值，丢失品类信息） #

client.query('''SELECT *
                FROM (SELECT year,
                             month,
                             MAX(num_of_product) OVER (PARTITION BY year, month) as num_of_productz
                      FROM (SELECT EXTRACT(year from created_at) as year,
                                   EXTRACT(month from created_at) as month,
                                   b.category,
                                   COUNT(b.category) AS num_of_product,
                            FROM `bigquery-public-data.thelook_ecommerce.order_items` as a
                            INNER JOIN `bigquery-public-data.thelook_ecommerce.products` as b
                             ON a.product_id=b.id
                            WHERE status='Complete'
                             AND created_at BETWEEN \"2022-01-01\" AND \"2022-09-30\"
                            GROUP BY year, month, b.category)
                      GROUP BY year, month, num_of_product)
                GROUP BY year, month, num_of_productz
                ORDER BY year ASC, month ASC''') \
      .to_dataframe()

预期结果 #

year | month | category  | num_of_product
2022 | 1     | Intimates | 132

实际结果 #

year | month | num_of_product
2022 | 1     | 132

正确解决方案 #

使用ROW_NUMBER()窗口函数，按year和month分区，按销量降序排序，筛选出每个分区的第一行，即可同时拿到品类和对应最高销量：

client.query('''SELECT year, month, category, num_of_product
                FROM (
                    SELECT 
                        EXTRACT(year from created_at) as year,
                        EXTRACT(month from created_at) as month,
                        b.category,
                        COUNT(b.category) AS num_of_product,
                        ROW_NUMBER() OVER (PARTITION BY EXTRACT(year from created_at), EXTRACT(month from created_at) ORDER BY COUNT(b.category) DESC) as rn
                    FROM `bigquery-public-data.thelook_ecommerce.order_items` as a
                    INNER JOIN `bigquery-public-data.thelook_ecommerce.products` as b
                        ON a.product_id=b.id
                    WHERE status='Complete'
                        AND created_at BETWEEN "2022-01-01" AND "2022-09-30"
                    GROUP BY year, month, b.category
                )
                WHERE rn = 1
                ORDER BY year ASC, month ASC''') \
      .to_dataframe()

说明 #

• 内层查询先按年月+品类分组计算销量，同时用ROW_NUMBER()给每个年月分组内的品类按销量降序编号，销量最高的品类编号为1。
• 外层查询筛选出编号为1的记录，就是每个月销量最高的品类及对应销量。
• 之前的方案用MAX()窗口函数只能拿到最大值，但无法关联到对应的品类，而ROW_NUMBER()可以保留所有字段并完成分组排序筛选。

内容的提问来源于stack exchange，提问作者Jason Rich Darmawan