在Big Query中，如何有效地仅选择具有非零和非空列的行? [英] How to only SELECT rows with non-zero and non-null columns efficiently in Big Query?

问题描述

我在Big Query中有一个包含大量列的表.

I am having a table with large number of columns in Big Query.

表中的行很多，某些列值为0/0.0，并且为null.

The table has lot of rows with some column values as 0/0.0 and null.

例如

Row A      B    C     D     E   F
1   "abc"  0   null  "xyz"  0   0.0
2   "bcd"  1    5    "wed"  4   65.5

我只需要选择那些具有非零Integer，Float和非NULL值的行.基本上，我只需要上表中的第2行

I need to select only those rows which have non zero Integer, Float and non NULL values. Basically, I need just Row 2 in the above table

我知道我可以通过对每个列使用此查询来做到这一点

I know I can do this by using this query for each of the columns

SELECT * FROM table WHERE (B IS NOT NULL AND B is !=0) AND
.
.
.

但是我有很多列，很难为每个列编写这样的查询.有没有更好的方法来解决这个问题?

But I have lot of columns and writing query like this for each of the columns would be difficult. Is there any better approach to handle this?

推荐答案

下面是BigQuery标准SQL的示例

Below example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT "abc" a, 0 b, NULL c, "xyz" d, 0 e, 0.0 f UNION ALL
  SELECT "bcd", 1, 5, "wed", 4, 65.5 
)
SELECT *
FROM `project.dataset.table` t
WHERE NOT REGEXP_CONTAINS(TO_JSON_STRING(t), r':0[,}]|null[,}]')  

有输出

Row a   b   c   d   e   f    
1   bcd 1   5   wed 4   65.5    

这篇关于在Big Query中，如何有效地仅选择具有非零和非空列的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！