如何在窗口函数中基于观测日期获取截至当日最低流量日期

问题场景 #

每日导入文件后，在monitoring_table表中记录行数规模，原查询语句如下：

SELECT
  observation_date,
  flow_name,
  volume,
FROM
  `monitoring_table`

需求为获取截至当前日期的最低流量对应的日期，但当前使用NTH_VALUE仅能获取全时段最低流量日期：

NTH_VALUE(observation_date, 1) OVER (ORDER BY volume) AS day_of_lowest_volume,

添加ROWS BETWEEN子句仅能限制流量计算范围，无法基于observation_date限定窗口范围。

解决方案 #

要实现基于日期范围的窗口限制，需先通过ORDER BY observation_date定义窗口范围为「截至当前日期的所有行」，再在该范围内筛选出最低流量对应的日期。以下是具体实现语句：

SELECT
  observation_date,
  flow_name,
  volume,
  FIRST_VALUE(observation_date) OVER (
    PARTITION BY flow_name -- 若需按流量名称单独统计则保留，全局统计可移除
    ORDER BY 
      -- 先将等于截至当前日期最小流量的行排在最前
      CASE WHEN volume = MIN(volume) OVER (
        PARTITION BY flow_name
        ORDER BY observation_date ASC
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) THEN 0 ELSE 1 END,
      observation_date ASC -- 若有多个日期流量相同，取最早的日期
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS day_of_lowest_volume
FROM
  `monitoring_table`

逻辑说明 #

1. 定义日期窗口范围：通过MIN(volume) OVER (...)计算截至当前日期的最小流量，其中ORDER BY observation_date ASC + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW确保窗口仅包含当前日期及之前的所有数据。
2. 筛选最低流量日期：外层FIRST_VALUE窗口中，先将流量等于截至当前日期最小值的行优先级设为最高，再按日期升序排列，取第一个日期即为目标结果。

若无需按flow_name分组统计，直接移除所有PARTITION BY flow_name子句即可。

内容的提问来源于stack exchange，提问作者Random it guy