SQL. BigQuery. Find values from an array corresponding to another column

Question

I have a table with columns Date, UserID, EventID, Value, RangeOfValues.

The task is two determine a pair of values from the last columns between which is the value from the 4th columns. So, for example, if the user on the screenshot has value 326 in Value clmn it will be between 200 and 1000. I have a lot of users and need to extract such pairs for each of them. Can do this in python but have no ideas how to do this in bigquery (or even if it's possible).

Any advice would be appreciated!

The table looks like this

Martin Weitzmann · Accepted Answer · 2019-07-26 15:50:57Z

2

Yes, this is easily achievable using UNNEST() to turn the array into rows and then run simple sub-queries on them:

WITH test as (
  SELECT * FROM UNNEST([
    STRUCT(4 as value, [1, 3, 5, 7, 9, 100, 150, 40] as rangeOfValues)
    ,(15, [1, 3, 5, 7, 9, 100, 150, 40])
    ,(50, [1, 3, 5, 7, 9, 100, 150, 40])
    ,(160, [1, 3, 5, 7, 9, 100, 150, 40])
  ])
)

SELECT  
  value,
  (SELECT MAX(r) FROM UNNEST(rangeOfValues) r WHERE r<value ) nextLowest,
  (SELECT MIN(r) FROM UNNEST(rangeOfValues) r WHERE r>value ) nextBiggest
FROM test

answered Jul 26, 2019 at 15:50

Martin Weitzmann

4,75612 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL. BigQuery. Find values from an array corresponding to another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related