CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
atsang01
atsang01

Reputation: 227

BigQuery Challenge: How can I correctly assign the value by next available value and keep it until the next update?

Come up with a quite challenging BigQuery question here. So basically, I have to assign the next available value to session1's code (in this case session 1 should be the next available value -> 123. However, we want to keep the code value at 234 in session4 until it gets another update.

Here's what I have:

timestamp session user_id code
ts1 1 User A NULL
ts2 2 User A NULL
ts3 2 User A 123
ts4 3 User A NULL
ts5 3 User A 234
ts6 4 User A NULL

And the desired output table:

timestamp session user_id code
ts1 1 User A 123
ts2 2 User A 123
ts3 2 User A 123
ts4 3 User A 234
ts5 3 User A 234
ts6 4 User A 234

Thanks everyone for the help!

Upvotes: 0

Views: 70

Answers (2)

atsang01
atsang01

Reputation: 227

JayTiger's answer is a much cleaner, but here's what I came up with that can be used as an alternative:

SELECT *EXCEPT (Code),
       IFNULL(
               (FIRST_VALUE(LatestCodeBySession IGNORE NULLS)
                            OVER (PARTITION BY user_id ORDER BY event_timestamp ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)),
               (LAST_VALUE(LatestCodeBySession IGNORE NULLS)
                           OVER (PARTITION BY user_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)))
           AS Code

LatestCodeBySession: `LAST_VALUE(Code IGNORE NULLS) OVER (PARTITION BY session ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)`

Upvotes: 0

Jaytiger
Jaytiger

Reputation: 12254

You might consider below approach.

SELECT *, 
       COALESCE(
         FIRST_VALUE(code IGNORE NULLS) OVER w0,
         LAST_VALUE(code IGNORE NULLS) OVER w1
       ) AS new_code
  FROM sample_table
WINDOW w AS (PARTITION BY user_id ORDER BY timestamp),
       w0 AS (w RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
       w1 AS (w RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Query results

enter image description here

Upvotes: 2

Related Questions