基于多列排除行

Question

我正在尝试排除所有总列都有空白值的行。

示例数据：

market   item_id    gross_1     gross_2   gross_3   period
POLAND   1111       1           2         3         20190301
ESTONIA  2222       blank       2         3         20190302
POLAND   3333       1           blank     3         20190303
POLAND   3333       1           blank     blank     20190304
POLAND   4444       blank       blank     blank     20190305
POLAND   5555       1           2         3         20190306`

我希望收到：

market   item_id    gross_1     gross_2   gross_3   period
POLAND   1111       1           2         3         20190301
ESTONIA  2222       blank       2         3         20190302
POLAND   3333       1           blank     3         20190303
POLAND   3333       1           blank     blank     20190304
POLAND   5555       1           2         3         20190306`

我发现了这样的东西，但它在雪花中不起作用：

WHERE NOT ROW(gross_1, gross_2, gross_3) IS NULL

Answer 1

使用coalesce()：

select * from tablename
where coalesce(gross_1, gross_2, gross_3) is not null

Answer 2

这取决于"空白"是什么意思。 如数据所示：

select t.*
from t
where not (gross_1 = 'blank' and gross_2 = 'blank' and gross_3  = 'blank')

如果"空白"表示空字符串，则：

select t.*
from t
where not (gross_1 = '' and gross_2 = '' and gross_3 = '')

如果它表示NULL，则：

select t.*
from t
where not (gross_1 is null and gross_2 is null and gross_3 is null)

所有这些都可以用or来改写：

select t.*
from t
where gross_1 is not null or gross_2 is not null or gross_3 is not null