BigQuery SQL练习求助：按年月拆分的平均订阅金额计算需求解读

需求拆解 #

• 时间分组规则：必须把所有订阅数据按「年月」维度拆分，输出的年月格式固定为YYYY-MM（例如2024-06、2023-11），所有同一月份的订阅记录归为一组。
• 平均金额计算逻辑：这里的平均订阅金额不是单条订阅金额的简单平均值，而是当月所有订阅的总金额除以当月的订阅次数，即：
  平均订阅金额 = 当月订阅金额总和 ÷ 当月订阅次数
  • 当月订阅金额总和：该月份下所有订阅记录的金额累加值
  • 当月订阅次数：该月份下订阅记录的总条数（每一条记录算一次订阅）

BigQuery SQL实现示例 #

假设你的订阅表名为user_subscriptions，包含subscribe_time（订阅时间字段）、payment_amount（单条订阅金额字段），可以参考以下SQL写法：

SELECT
  -- 将日期转换为YYYY-MM格式的年月字符串
  FORMAT_DATE('%Y-%m', subscribe_time) AS year_month,
  -- 计算当月订阅总金额
  SUM(payment_amount) AS total_monthly_amount,
  -- 统计当月订阅次数
  COUNT(*) AS monthly_subscribe_count,
  -- 计算当月平均订阅金额
  SUM(payment_amount) / COUNT(*) AS avg_monthly_subscribe_amount
FROM user_subscriptions
-- 按年月分组
GROUP BY year_month
-- 按年月排序，方便查看
ORDER BY year_month;

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