【ClickHouse】空值问题

以你之姓@ 2022-12-25 15:48 506阅读 0赞

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

where toDateOrNull(dat\_dt)>='2020-11-01' and toDateOrNull(dat\_dt)<='2020-12-02'

**目录**

[1.建表时的空值问题][1.]

[2.查询时的空值问题][2.]

[3.关联问题][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][https_blog.csdn.net_jarry_cm_article_details_105988012]

[1.]: https://blog.csdn.net/jarry_cm/article/details/105988012#1.%E5%BB%BA%E8%A1%A8%E6%97%B6%E7%9A%84%E7%A9%BA%E5%80%BC%E9%97%AE%E9%A2%98
[2.]: https://blog.csdn.net/jarry_cm/article/details/105988012#2.%E6%9F%A5%E8%AF%A2%E6%97%B6%E7%9A%84%E7%A9%BA%E5%80%BC%E9%97%AE%E9%A2%98
[3.]: https://blog.csdn.net/jarry_cm/article/details/105988012#3.%E5%85%B3%E8%81%94%E9%97%AE%E9%A2%98
[https_blog.csdn.net_jarry_cm_article_details_105988012]: https://blog.csdn.net/jarry_cm/article/details/105988012