【ClickHouse】空值问题

今天查询遇到个问题，一直报''空值错误，查询条件中，加入日期空值处理解决了

where toDateOrNull(dat_dt)>='2020-11-01' and toDateOrNull(dat_dt)<='2020-12-02'

目录

1.建表时的空值问题

2.查询时的空值问题

3.关联问题

---

       我们再ClickHouse环境下，SQL很多语法是和HIVE、Spark环境下不同的。以下从三个方面说明CK下空值的问题。

1.建表时的空值问题

如果我们建表时，不特殊说明空值，比如：

CREATE TABLE test.table1(
id String,
name String
) ENGINE = MergeTree PARTITION BY id ORDER BY id SETTINGS index_granularity = 8192

这种情况下，如果将包含空值的数据，读入到表中时，会报错。

DB::Exception: Expression returns value NULL, that is out of range of type String, at: null) 

因此，为了防止这种情况的发生，我们一般会这样建表：

注意：这里的主键是不可以包含空值的，如果把主键也加Nullable会报错

CREATE TABLE test.table1(
id String,
name Nullable(String)
) ENGINE = MergeTree PARTITION BY id ORDER BY id SETTINGS index_granularity = 8192

2.查询时的空值问题

        上面说了建表的问题，接下来要实例一下，当我们表已经建好，且表数据已经有了，一列数据既包含null，又包含''这类空值，这个时候，如果不注意语法，会报错，如果包含这两类数据，不能使用coalesce，如下：

select coalesce(paymentterm, 0) as paymentterm_a,
       count(distinct orderid) as ornum
  from ckdb.test
 where d = '2020-05-08'
 group by paymentterm_a

报错如下：错误原因是paymentterm是string类型，不可以改成int类型

Code: 386, e.displayText() = DB::Exception: There is no supertype for types String, UInt8 because some of them are String/FixedString and some of them are not (version 19.17.6.36 (official build))

这里有一个小的知识点：

group by后面的名称，可以写select中的逻辑，也可以写as为的别名，下面使用case when改写上面的内容：

--方式一
select case when paymentterm is null or paymentterm = '' then 'null' else paymentterm end as paymentterm,
       count(distinct orderid) as ornum
  from ckdb.test
 where d = '2020-05-08'
 group by paymentterm
 
--方式二
select case when paymentterm is null or paymentterm = '' then 'null' else paymentterm end as paymentterm,
       count(distinct orderid) as ornum
  from ckdb.test
 where d = '2020-05-08'
 group by case when paymentterm is null or paymentterm = '' then 'null' else paymentterm end
 
--方式三
select coalesce(paymentterm,'null') as paymentterm,
       count(distinct orderid) as ornum
  from ckdb.test
 where d = '2020-05-08'
 group by coalesce(paymentterm,'null')
 

这两种方式都是可以达到效果的 

3.关联问题

如下场景，需要使用a表关联b表，把a和b都有的id剔除，在hive中我们一般这样实现：

select a.* 
  from a 
  left join b 
    on a.id = b.id 
 where b.id is null

不过这种方式，在CK中是有问题的，要借用coalesce来完成

select a.* 
  from a 
  left join b 
    on a.id = b.id 
 where coalesce(b.id,0) = 0

转载：https://blog.csdn.net/jarry_cm/article/details/105988012