sql - 按日期分区一段时间 SQL BigQuery

Question

数据必须按 id 和 pageview_date 进行分区。因此，对于每个相应的 id - 代码应该在 edited_date 列中查找不晚于 pageview_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 (PARTITION BY pageview_date, id) 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

table_1 AS (
  SELECT
    id,
    pageview_date,
    ARRAY_AGG(edited_date) OVER (PARTITION BY id) AS edited_dates
  FROM sample
)

SELECT
  id,
  pageview_date,
  (SELECT MAX(d) FROM UNNEST(edited_dates) AS d WHERE d <= pageview_date) AS new_edited_date
FROM table_1
ORDER BY pageview_date

输出:

<表类="s-表"><头>idpageview_datenew_edited_date<正文>一个2022-02-272022-01-28一个2022-02-272022-01-28一个2022-03-012022-03-01一个2022-03-012022-03-01一个2022-03-052022-03-01