partition over by date for a period of time SQL BigQuery

Question

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

score 3 · Answer 1 · answered Jun 10 '22 at 13:39

3

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

answered Jun 10 '22 at 13:39

dikesh

2,977
2
16
26

While this logic works perfectly fine on sample, it has performance issues when I run it on the full set. I upvoted the answer! – Chique_Code Jun 10 '22 at 18:34
@Chique_Code I was expecting that since there can be n number of `edited_date` values for each `id` and doing on it `ARRAY_AGG` for each row would become expensive. What is the size of data? – dikesh Jun 11 '22 at 05:37
the size of data is about 40GB. 300M of rows roughly. – Chique_Code Jun 13 '22 at 18:43

score 1 · Answer 2 · answered Jun 10 '22 at 13:05

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

This logic works fine on a sample, it has performance issues when I run it on the full set. I upvoted the answer! It runs forever and never finishes :/ — Chique_Code, Jun 10 '22 at 18:35

partition over by date for a period of time SQL BigQuery

2 Answers2