CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Harshith S H
Harshith S H

Reputation: 13

How to use any_value in bigquery

I am looking to derive the columns 2 and 3 from 1 and 4 using sql, need advise

enter image description here

output logics for col_2 and col_3

  1. any of the values in col_1 group by col_4 is True , then col_2 is true col_3 is false
  2. all the values in col_1 group by col_4 is false, then col_2 is false and col_3 is true
  3. all the values in col_1 group by col_4 is true , then col_2 is false and col_3 is false

Upvotes: 0

Views: 368

Answers (2)

Jaytiger
Jaytiger

Reputation: 12274

You seems to want logical_or() and logical_and() functions in BigQuery.

WITH sample_table AS (
  SELECT col_4, col_1
    FROM UNNEST(SPLIT('aaaaabbbccc', '')) col_4 WITH offset
    JOIN UNNEST([false, true, false, true, false, false, false, false, true, true, true]) col_1 WITH offset
   USING (offset)
)
SELECT *,
       CASE
         WHEN LOGICAL_AND(col_1) OVER (PARTITION BY col_4) IS TRUE THEN STRUCT(false AS col2, false AS col_3)
         WHEN LOGICAL_OR(col_1) OVER (PARTITION BY col_4) IS FALSE THEN (false, true)
         ELSE (true, false)
       END.*
  FROM sample_table;

Query results

enter image description here

Upvotes: 2

Aishwary Shukla
Aishwary Shukla

Reputation: 460

One way would be to count true and false values for each col_4 key inside a CTE or a subquery (whichever way works for you). Then perform a join with the original table. That way, you can then derive col_2 and col_3 directly by checking the counts of True and False in the final select statement.

Upvotes: 1

Related Questions