按日期分区一段时间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

ID	PageView_Date	New_edited_date
A	2022-02-27	2022-01-28
A	2022-02-27	2022-27 2022-01-28
A	2022-03-01	2022-01
2022-2022-03-03	输出： 01	2022-03-01
A	2022-03-05	2022-03-01

Answer 2

我认为在这里有点交叉加入的子查询可能会更好。

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 s3.id, s3.pageview_date, s2.new_edited_date
from sample s3
inner join
(select s.id, s.pageview_date, max(s1.edited_date) new_edited_date
from sample s,
sample s1
where s.id = s1.id
and s1.edited_date <= s.pageview_date
group by 1,2) s2
on s3.id = s2.id
and s3.pageview_date = s2.pageview_date

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