如何在BigQuery中按月重置累计利润求和（截至每月15日）

需要计算截至每月15日的月度累计利润，要求累计求和（running total）每月重置，即每月1日时Profit_Cumulative列的累计值从零开始重新计算。现有代码生成的累计求和会跨月延续，不符合预期。

期望结果 #

当前错误结果 #

问题原因 #

现有代码的窗口函数仅按product_categories分区，未将年份和月份纳入分区条件，导致累计求和跨月延续，无法实现每月重置的效果。

修正后的代码 #

WITH a AS (
  SELECT
    DATE_TRUNC(DATE(created_at), DAY) AS date_,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    SAFE_SUBTRACT(retail_price, cost) AS profit,
    products.category AS product_category
  FROM
    `bigquery-public-data.thelook_ecommerce.order_items` orderitems
  INNER JOIN
    `bigquery-public-data.thelook_ecommerce.products` products
  ON
    orderitems.product_id = products.id
  WHERE
    created_at >= '2022-06-01 00:00:00 UTC'
    AND created_at <= '2022-08-15 23:59:59 UTC'
  GROUP BY
    date_, year, month, day, product_category, profit
),
b AS (
  SELECT
    date_ AS Date,
    year,
    month,
    day,
    product_category AS Product_Categories,
    SUM(profit) AS Profit
  FROM
    a
  WHERE
    day <= 15
  GROUP BY
    date_, year, month, day, product_category
  ORDER BY
    date_, year, month, day, product_category
)
SELECT
  Date,
  Product_Categories,
  Profit,
  SUM(Profit) OVER(
    PARTITION BY year, month, Product_Categories 
    ORDER BY Date
  ) AS Profit_Cumulative
FROM
  b

关键改动说明 #

1. 在窗口函数的PARTITION BY中新增year和month，让累计求和按年份-月份-品类的组合分区，确保每个月的累计值从零开始计算，实现每月重置的效果。
2. 简化了最终查询的输出字段，去掉了冗余的year、month、day（若不需要展示可直接省略）。

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