Google Big Query: how to filter output to show only rows that have at least 1 non-zero value?

user_13124211 0
Viewed 688

I am working on a dataset that looks like this:

enter image description here

As a result of this query:

SELECT "campaign_1" campaign_name, 0 clicks, 5 impressions, 1000 cost UNION ALL
SELECT "campaign_2", 1, 5, 0 UNION ALL
SELECT "campaign_3", 0, 0, 0

My goal is to show all rows in this table that have at least 1 non-zero value. In other words, my desired output is:

enter image description here

To solve my own problem, I tried to use a big WHERE clause including names of every column, listing each in the following format: WHERE column_1 != 0 AND column_2 != 0 .... This didn't work, because it also eliminated first 2 rows, since they also contain 0s.

I have searched StackOverflow for existing answers to this problem, and while I found a solution to a similar problem by Mikhail Berlyant here, this particular solution does not apply to this scenario. Therefore, I posted this as a new question.

Thank you in advance for sharing your knowledge and your help!

google-bigquery where-clause sql
3 Answers

If the values cannot be negative or null (as shown in your sample data), you can do:

where column_1 + column_2 + ... > 0

Or you can use greatest():

where greatest(column_1, column_2, ...) > 0
user_106767160

In case if for some reason you want to reuse solution from post you referenced in your question - you can use below variation of it

#standardSQL
SELECT *
FROM `project.dataset.table` t
WHERE REGEXP_CONTAINS(TO_JSON_STRING(t), r':[^0][,}]')

with output (if to apply to sample data from your question)

enter image description here

The potential benefit for you using this solution is in case if you have more than just 3-5 columns/metrics - in this case you don't need to call them out explicitly in your query

Also note: above assumes that campaign's name has more than one character

user_52219440

Would you not in this case use an OR operator to between all your conditions. In that scenario you would still eliminate campaign_3 while retaining 1 and 2.

WHERE column_1 != 0 OR column_2 != 0 .... But what GMB outlined seems much simpler and less prone to error.

user_144009980
Related