Filter partitions in window - computing event recency in BigQuery

user_3531907 0
Viewed 2599

Is there any way I can emulate the behavior of FILTER (http://modern-sql.com/feature/filter) in standard SQL BigQuery?

What I would need to do is:

SELECT MAX(date) FILTER (WHERE event_happend = 1) OVER ( PARTITION BY user_id ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM ...

In essence I need to work out the most recent date a certain event occurred prior to the date of the current row. Column event_happened takes values 0 and 1 and I need the most recent date on which the event occurred (event_happened = 1) prior to the date of the current row.

google-bigquery
3 Answers

Is there any way I can emulate the behavior of FILTER?

#standardSQL
SELECT
  MAX(IF(event_happend = 1, date, null))
  OVER (
    PARTITION BY user_id
    ORDER BY date ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  )
  FROM
    ...
user_52219440

Try this:

#standardSQL
WITH SampleData AS (
  SELECT 1 AS user_id, DATE '2017-11-02' AS date, 1 AS event_happend UNION ALL
  SELECT 1, DATE '2017-11-03', 0 UNION ALL
  SELECT 1, DATE '2017-11-04', 1 UNION ALL
  SELECT 1, DATE '2017-11-04', 1 UNION ALL
  SELECT 1, DATE '2017-11-05', 0 UNION ALL
  SELECT 2, DATE '2017-11-10', 1 UNION ALL
  SELECT 2, DATE '2017-11-11', 0 UNION ALL
  SELECT 2, DATE '2017-11-20', 0 UNION ALL
  SELECT 2, DATE '2017-11-21', 1
)
SELECT
  user_id,
  date,
  MAX(IF(event_happend = 1, date, NULL)) OVER (
    PARTITION BY user_id ORDER BY UNIX_DATE(date)
    RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
  ) AS max_date
FROM SampleData;

I included user_id and date to see what is going on. Note that using RANGE is important here--if you use ROWS, it may be the case that the previous row in the window has the same date value. By using RANGE with 1 PRECEDING, you can enforce that all rows in the window have a date value that is less than the current one.

user_62533470

The solution provided by Mikhail Berlyant works well for the question asked. I had a slightly different problem where the column being aggregated was not the same as the ORDER BY statement in the OVER clause. The aggregation function I needed was First_value, but it works as well with some others as long you can specify IGNORE NULLS. Example:

ColumnToBeFiltered | Value      | PartitionColumn | OrderingColumn
FOO                | APPLE      | A               | 1
BAR                | BANANA     | A               | 2
FOO                | ORANGE     | A               | 3
FOO                | CHERRY     | B               | 8
BAR                | MANGO      | B               | 10
BAR                | POMELO     | B               | 9

For each partition, if you want to get the first Value based on the OrderingColumn when the filtered column is "BAR", here is how I solved it:

FIRST_VALUE(IF (columnToBeFiltered = 'BAR', Value, null) IGNORE NULLS) 
OVER (PARTITION BY PartitionColumn ORDER BY OrderingColumn)
AS FirstFilteredValue

It will return Banana for each row of partition A and Pomelo for each row of partition B.

ColumnToBeFiltered | Value    | PartitionColumn | OrderingColumn | FirstFilteredValue
FOO                | APPLE    | A               | 1              | BANANA
BAR                | BANANA   | A               | 2              | BANANA
FOO                | ORANGE   | A               | 3              | BANANA
FOO                | CHERRY   | B               | 8              | POMELO
BAR                | MANGO    | B               | 10             | POMELO
BAR                | POMELO   | B               | 9              | POMELO

I hope it can help others.

user_113099940
Related