如何在BigQuery中按月统计每月前20名供应商的销售总额

首先注意你的item_price字段使用逗号作为小数分隔符，计算前需要先转换为标准数值格式避免出错，以下是可直接运行的BigQuery查询语句：

WITH monthly_vendor_sales AS (
  -- 先计算每个供应商每月的总销售额
  SELECT
    DATE_TRUNC(date, MONTH) AS month,
    vendor,
    SUM(CAST(REPLACE(item_price, ',', '.') AS FLOAT64)) AS vendor_monthly_total
  FROM
    `替换为你的实际表名`
  GROUP BY
    month, vendor
),
ranked_vendor AS (
  -- 对每月的供应商按销售额降序排名
  SELECT
    month,
    vendor_monthly_total,
    ROW_NUMBER() OVER (PARTITION BY month ORDER BY vendor_monthly_total DESC) AS sales_rank
  FROM
    monthly_vendor_sales
)
-- 统计每月前20名供应商的销售额总和
SELECT
  FORMAT_DATE("%Y-%m", month) AS month,
  ROUND(SUM(vendor_monthly_total)) AS `sum_of_only_top20_vendor's_sales`
FROM
  ranked_vendor
WHERE
  sales_rank <= 20
GROUP BY
  month
ORDER BY
  month ASC

说明 #

• 如果你的业务规则里销售额相同的供应商都算同一名次，需要保留并列排名，可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()
• 如果确认item_price都是整数，只需要把CAST(REPLACE(item_price, ',', '.') AS FLOAT64)换成CAST(item_price AS INT64)即可
• 最终输出的月份格式为YYYY-MM，销售额默认四舍五入取整，和你提供的预期格式一致

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