BigQuery中获取日期区间最后日期对应列值及求和问题排查

原查询的核心问题在于：子查询中使用LAST_VALUE窗口函数时，会为每一行生成截至当前行的最后一个非空accumulated_value，导致accumulated_value_last_date字段存在多个不同值；外层按该字段分组后，会将数据拆分成多个分组，最终输出多条结果，不符合你想要的单条汇总结果的需求。

正确查询写法 #

写法一：直接聚合+子查询获取最后日期对应值 #

SELECT
  MAX(date) AS last_date,
  -- 直接取最后日期对应的accumulated_value
  (SELECT accumulated_value FROM `my_table` WHERE date = MAX(date)) AS accumulated_value_last_date,
  SUM(daily_growth) AS total_daily_growth
FROM `my_table`
WHERE date BETWEEN '2023-12-18' AND '2023-12-20'

写法二：窗口函数标记最后行+全局求和 #

WITH interval_data AS (
  SELECT
    date,
    accumulated_value,
    daily_growth,
    -- 计算整个区间的最后日期
    MAX(date) OVER () AS interval_last_date,
    -- 计算整个区间的daily_growth总和
    SUM(daily_growth) OVER () AS total_daily_growth
  FROM `my_table`
  WHERE date BETWEEN '2023-12-18' AND '2023-12-20'
)
-- 筛选出最后日期的行，获取对应的accumulated_value
SELECT
  interval_last_date AS last_date,
  accumulated_value AS accumulated_value_last_date,
  total_daily_growth
FROM interval_data
WHERE date = interval_last_date

原查询问题详解 #

原子查询中，LAST_VALUE(accumulated_value IGNORE NULLS) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)的逻辑是：对每一行，从区间起始行到当前行中取最后一个非空的accumulated_value。假设你的表数据中，2023-12-18、19、20的accumulated_value分别为100、150、200，那么子查询输出的accumulated_value_last_date会依次是100、150、200；外层按该字段分组后，会生成3条结果，每条对应一个日期的accumulated_value和单天的daily_growth，这与你想要的整个区间汇总结果完全不符。

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