如何在BigQuery中按月份分组获取最常出现的商品类别？

可以通过窗口函数+分组统计实现批量按月份获取最频繁商品类别的需求，无需重复执行单月份查询。以下是适配Google BigQuery的SQL代码：

WITH monthly_category_counts AS (
  -- 统计每个月份下各类别的订单数量
  SELECT
    DATE_TRUNC(a.delivered_at, MONTH) AS delivery_month,
    b.category,
    COUNT(*) AS category_count
  FROM `bigquery-public-data.thelook_ecommerce.order_items` a
  JOIN `bigquery-public-data.thelook_ecommerce.products` b
    ON a.product_id = b.id
  WHERE
    a.status = 'Complete'
    AND a.delivered_at BETWEEN '2022-01-01' AND '2022-10-01'
  GROUP BY delivery_month, b.category
),
ranked_categories AS (
  -- 给每个月份内的类别按订单数降序排名
  SELECT
    delivery_month,
    category AS favourite,
    ROW_NUMBER() OVER (PARTITION BY delivery_month ORDER BY category_count DESC) AS rank
  FROM monthly_category_counts
)
-- 筛选每个月份排名第一的类别
SELECT
  FORMAT_DATE('%Y-%m', delivery_month) AS delivery_month,
  favourite
FROM ranked_categories
WHERE rank = 1
ORDER BY delivery_month;

关键说明： #

• DATE_TRUNC(a.delivered_at, MONTH)：将交付日期截断到月份级别，是实现按月份分组的核心。
• ROW_NUMBER() OVER (PARTITION BY delivery_month ORDER BY category_count DESC)：针对每个月份（PARTITION BY指定分组维度），按类别订单数降序分配排名，排名为1的就是当月出现次数最多的类别。
• 如果存在多个类别订单数并列第一的情况，可将ROW_NUMBER()替换为RANK()，这样会保留所有并列第一的类别。

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