partition over by date for a period of time SQL BigQuery-CodePudding

Data has to be partitioned by id as well as by pageview_date. So for each corresponding id - code should look for the latest date available in the column edited_date that is no later than pageview_date field itself. But it has to look for all values that are available before the pageview_date NOT ONLY for what the records are for each given day. Here is data and the code:

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

The output is:

Desired output is:

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

CodePudding user response：

I think bit of cross-join and corelated subquery may work better here.

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

CodePudding user response：

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

Output:

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