在BigQuery中使用RANGE BETWEEN时如何强制窗口结束于当前行

我在BigQuery中有一张表，包含date日期字段和hit字段，多行可共享同一date，但date+hit组合唯一。我需要创建一个回溯30天的窗口，按date和hit排序后窗口结束于当前行。但RANGE BETWEEN子句仅支持按单个值排序，使用ORDER BY date RANGE BETWEEN 30 AND CURRENT ROW会将当前行同日期的所有后续行纳入窗口，比如行5、6的lastNonDirect预期为'twitter'，实际返回'bing'（因为同日期的行7被包含进窗口了）。我试过LAST_VALUE(source) OVER(ORDER BY UNIX_DATE(date) + hit / 100000)，但当当前hit值大于30天前的hit值时这个方法会失效。

原数据表 #

测试SQL及结果 #

测试SQL #

WITH t1 AS (
SELECT 
  1 as origOrder, DATE '2024-01-01' as Date, 'google' as Source, 1 as Hit, 'google' as lastNonDirectClick, 'google' as currentCode UNION ALL
SELECT 
  2, DATE '2024-01-01', NULL, 2, 'google', 'google' UNION ALL
SELECT 
  3, DATE '2024-01-02', 'facebook', 3, 'facebook', 'bing' UNION ALL
SELECT 
  4, DATE '2024-01-02', NULL, 4,'facebook','bing' UNION ALL
SELECT 
5 ,DATE '2024-01-02','twitter',5,'twitter','bing' UNION ALL
SELECT 
6 ,DATE '2024-01-02',NULL ,6,'twitter','bing' UNION ALL
SELECT 
7 ,DATE '2024-01-02','bing',7,'bing','bing' UNION ALL
SELECT 
8 ,DATE '2024-01-31',NULL ,8,'bing','bing' UNION ALL
SELECT 
9 ,DATE '2024-02-10' ,'instagram',9,'instagram' ,'instagram' UNION ALL
SELECT 
10 ,DATE '2024-02-10',NULL ,10,'instagram' ,'instagram')
########################################################################################
SELECT
  origOrder,
  Date,
  SOURCE,
  Hit,
  LAST_VALUE(SOURCE IGNORE NULLS) OVER(
    ORDER BY
      UNIX_DATE(date) 
    RANGE BETWEEN
       30 PRECEDING
       AND CURRENT ROW) AS lastNonDirect
FROM
  t1
ORDER BY
  origOrder

结果表 #

解决方案 #

要实现按date和hit排序、回溯30天且窗口仅包含当前行及之前数据的需求，可以将date转换为天数后乘以一个足够大的数（确保hit不会溢出），再加上hit生成唯一排序键，然后用RANGE BETWEEN定义窗口范围：

WITH t1 AS (
SELECT 
  1 as origOrder, DATE '2024-01-01' as Date, 'google' as Source, 1 as Hit, 'google' as lastNonDirectClick, 'google' as currentCode UNION ALL
SELECT 
  2, DATE '2024-01-01', NULL, 2, 'google', 'google' UNION ALL
SELECT 
  3, DATE '2024-01-02', 'facebook', 3, 'facebook', 'bing' UNION ALL
SELECT 
  4, DATE '2024-01-02', NULL, 4,'facebook','bing' UNION ALL
SELECT 
5 ,DATE '2024-01-02','twitter',5,'twitter','bing' UNION ALL
SELECT 
6 ,DATE '2024-01-02',NULL ,6,'twitter','bing' UNION ALL
SELECT 
7 ,DATE '2024-01-02','bing',7,'bing','bing' UNION ALL
SELECT 
8 ,DATE '2024-01-31',NULL ,8,'bing','bing' UNION ALL
SELECT 
9 ,DATE '2024-02-10' ,'instagram',9,'instagram' ,'instagram' UNION ALL
SELECT 
10 ,DATE '2024-02-10',NULL ,10,'instagram' ,'instagram')
SELECT
  origOrder,
  Date,
  SOURCE,
  Hit,
  LAST_VALUE(SOURCE IGNORE NULLS) OVER(
    ORDER BY
      UNIX_DATE(Date) * 100000 + Hit
    RANGE BETWEEN
       (UNIX_DATE(Date) - 30) * 100000 PRECEDING
       AND CURRENT ROW) AS lastNonDirect
FROM
  t1
ORDER BY
  origOrder

说明 #

• UNIX_DATE(Date) * 100000 + Hit作为排序键，确保每个(Date, Hit)组合对应唯一数值，且顺序与Date升序、Hit升序完全一致。
• 窗口起始值设为(UNIX_DATE(Date) - 30) * 100000，既保证仅包含当前日期往前30天内的行，又不会将同日期中Hit值更大的后续行纳入当前窗口。
• 执行后行5、6的lastNonDirect会正确返回'twitter'，不会被同日期的行7覆盖。

内容的提问来源于Stack Exchange，提问作者Benjamin Mason