CODEWITHSUNDEEP
sqlgoogle-bigquery
Tim Martin
Tim Martin

Reputation: 2509

BigQuery: Get row + information about nearest previous row where column x has some value in previous row

Suppose I have a table with the following schema

name       | type
----------------------
id         | STRING
timestamp  | TIMESTAMP
event_type | STRING
some_value | STRING
...

I want to get all the events of type 'x'. However, I also want an additional parameter for every row returned. This parameter is a boolean that should be TRUE if the nearest event WHERE event_type='y' has some_value='necessary value'.

For example, assuming the following rows are ordered by timestamp ascending:

event_type |  some_value
------------------------
y          | 'true value'
x          | 'not relevant'
y          | 'false value'
x          | 'not relevant 2'
y          | 'true value'
y          | 'false value'
x          | 'not relevant3'
x          | 'not relevant4'

I would get the following rows back from my query:

event_type |  some_value     | previous_true
-------------------------------------
x          | 'not relevant'  | TRUE
x          | 'not relevant2' | FALSE
x          | 'not relevant3' | FALSE
x          | 'not relevant4' | FALSE

I thought a join might do the trick but I can't figure out how that would work. LAG also seemed like a good idea at first, but then I realized that LAG will take the previous row regardless of what it is and I'm not sure how I would use that.

Upvotes: 0

Views: 3903

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

With BigQuery Standard SQL - try below
(make sure to uncheck Use Legacy SQL checkbox under Show Options) 

WITH YourTable AS (
  SELECT 1 AS ts, 'y' AS event_type, 'true value' AS some_value UNION ALL
  SELECT 2 AS ts, 'x' AS event_type, 'not relevant' AS some_value UNION ALL
  SELECT 3 AS ts, 'y' AS event_type, 'false value' AS some_value UNION ALL
  SELECT 4 AS ts, 'x' AS event_type, 'not relevant2' AS some_value UNION ALL
  SELECT 5 AS ts, 'y' AS event_type, 'true value' AS some_value UNION ALL
  SELECT 6 AS ts, 'y' AS event_type, 'false value' AS some_value UNION ALL
  SELECT 7 AS ts, 'x' AS event_type, 'not relevant3' AS some_value UNION ALL
  SELECT 8 AS ts, 'x' AS event_type, 'not relevant4' AS some_value
)
SELECT 
  event_type, 
  some_value,
  (SELECT some_value = 'true value' FROM YourTable 
    WHERE event_type = 'y' AND ts < a.ts
    ORDER BY ts DESC LIMIT 1
    ) AS previous_true
FROM YourTable AS a
WHERE event_type = 'x'
ORDER BY ts

Result is :

event_type  some_value      previous_true    
x           not relevant    true     
x           not relevant2   false    
x           not relevant3   false    
x           not relevant4   false

For BigQuery Legacy SQL - try 

SELECT
  event_type, some_value, 
  previous_true = 'true value' AS previous_true
FROM (
  SELECT
    ts, event_type, some_value, 
    FIRST_VALUE(some_value) OVER(PARTITION BY grp ORDER BY ts) AS previous_true
  FROM (
    SELECT 
      ts, event_type, some_value, 
      SUM(step) OVER(ORDER BY ts) AS grp
    FROM (
      SELECT 
        ts, event_type, some_value, 
        IF(event_type = 'x' , 0, 1) AS step
      FROM 
        (SELECT 1 AS ts, 'y' AS event_type, 'true value' AS some_value),
        (SELECT 2 AS ts, 'x' AS event_type, 'not relevant' AS some_value),
        (SELECT 3 AS ts, 'y' AS event_type, 'false value' AS some_value),
        (SELECT 4 AS ts, 'x' AS event_type, 'not relevant2' AS some_value),
        (SELECT 5 AS ts, 'y' AS event_type, 'true value' AS some_value),
        (SELECT 6 AS ts, 'y' AS event_type, 'false value' AS some_value),
        (SELECT 7 AS ts, 'x' AS event_type, 'not relevant3' AS some_value),
        (SELECT 8 AS ts, 'x' AS event_type, 'not relevant4' AS some_value)
    )
  )
)
WHERE event_type = 'x'
ORDER BY ts

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Here is one method: You can use a max scan on "y" to get the id of the nearest y for each "x". Then use a join for the calculation:

select t.*,
       (case when some_value = 'necessary value' then 1 else 0 end) as previous_true
from (select t.*,
             max(case when event_type = 'y' then id end) over (order by timestamp) as yid
      from t
     ) t join
     t ty
     on ty.id = t.yid
where t.event_type = 'x';

I'm not sure about the exact role of id and timestamp. This version assumes that id is uniformly increasing with respect to timestamp. Alternatively, you could use timestamp -- but it is unclear if that is sufficient for the join.

Upvotes: 0

Related Questions