窗口功能查找最大日期SQL BigQuery

Question

在下面的代码中，我试图在将其比较edited_date中的列中的值时，将其比较pageview_date列的值。目标是从edited_date中获取最大值，不迟于pageview_date的行值。

with sample as (
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-03-01') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-03-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-05') as pageview_date, DATE('2017-02-28') as edited_date
)
SELECT
  id,
  pageview_date,
  MAX(IF(edited_date <= pageview_date, edited_date, null)) OVER (ORDER BY pageview_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as new_edited_date
FROM sample

返回：

所需的输出：

id          pageview_date         new_edited_date
a           2022-02-27             2022-01-28
a           2022-02-27             2022-01-28
a           2022-03-01             2022-03-01
a           2022-03-01             2022-03-01
a           2022-03-05             2022-03-01

Answer 1

使用子选择代替窗口函数的方法：

with sample as (
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-02-27') as pageview_date, DATE('2022-03-01') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-03-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-01-28') as edited_date
  UNION ALL
  select 'a' as id, DATE('2022-03-05') as pageview_date, DATE('2017-02-28') as edited_date
)
SELECT
  s1.id,
  s1.pageview_date,
  (SELECT MAX(edited_date) FROM sample WHERE id = s1.id AND edited_date <= s1.pageview_date) as new_edited_date
FROM sample s1

输出：

id  pageview_date   new_edited_date
a   2022-02-27      2022-01-28
a   2022-02-27      2022-01-28
a   2022-03-01      2022-03-01
a   2022-03-01      2022-03-01
a   2022-03-05      2022-03-01