hive count与collect_ws等函数结合使用特殊结果

这里，我们需要先了解到 concat 、concat_ws 等函数在一些特殊场景的执行结果。

concat

• 参数只有 null

hive> select concat(null);
NULL

• 参数中包含有 null

hive> select concat('1',null);
NULL

concat_ws

• 需拼接数组为 null

hive> select concat_ws(',', null);

• 需拼接数组为空数组

hive> select concat_ws(',', array());

结合上述内容，我们来看下面和 count 函数结合使用的 case。（这里我们已知 count 函数计算时会计入 null 的行）

当 count 去聚合 concat_ws 空数组

with t as (
    select concat_ws(',',array())
)
select count(1) from t;
-- 或下面代码
with t as (
    select concat_ws(',',collect_list(null))
)
select count(1) from t;

执行结果为 1，而非我们预期的 0。

这里可以理解为 concat_ws 空数组或 null 的结果是有值的，只不过是空字符串，那么 count 一条空字符串自然是 1。

当 count 去聚合 concat_ws 空记录

with t1 as (
    select * from (
        select 'a' as flag, 1 as tmp_col
    ) tmp_t where flag != 'a'
)
select count(*) from (
    select concat_ws(',',collect_list(flag))
    from t1
) t2
;

这里显然 t1 表的结果是空的，没数据。那么 collect_list(flag) 就相当于空数组，等效于 array()，那么再被 concat_ws 计算的结果就是空字符串。最后 count 自然为 1。

基于 group by 进行 concat_ws，之后再 count

with t1 as (
    select * from (
        select 'a' as flag, 1 as tmp_col
    ) tmp_t where flag != 'a'
)
 
select count(*) from (
    select concat_ws(',',collect_list(flag))
    from t1
    group by tmp_col
) t2
;

上面 sql 执行结果为 0。

这里 t1 表结果是空，即 tmp_col 字段没有任何有效值，虽然 collect_list 仍是空数组，但因为有 group by 的限制，则子表 t2 是一个空表，而非一行空字符串。所以 count 结果为 0。