hive中collect_list和concat_ws函数对null值的处理

hive中collect_list和concat_ws函数对null值的处理

!!! note
结论：
- collect_list函数会过滤掉为null的值，然后将其余非空的值组合成list。如果数据值都为null，则结果为空数组。
- concat_ws(';',collect_list(col_name))，当col_name的值都为null时，collect_list结果为空数组，concat_ws的结果为’'空字符串，而不是null

1. 验证

• 测试数据

CREATE TABLE table1(customer_no STRING, stock_fee double, advertisement_fee double);

INSERT INTO table1 VALUES 
("C0001", 0.1, 1221.12)
,("C0001", NULL, 1130.52)
,("C0001", 0.1, 1084.72)
,("C0001", NULL, 1738.32)
,("C0001", NULL, 1817.67)
,("C0001", NULL, 3271.45)
,("C0002", NULL, 2587.76)
,("C0002", NULL, 1397.60)
,("C0002", NULL, 2871.75)
,("C0002", NULL, 812.08)
,("C0002", NULL, 1643.39)
,("C0003", 2635.23, 2587.76)
,("C0003", 1253.69, 1397.60)
,("C0003", 253.69, 2871.75)
,("C0003", 1528.12, 812.08)
,("C0003", 412.53, 1643.39);

• 验证collect_list的结果

SELECT  customer_no
       ,collect_list(cast(stock_fee as string))
FROM table1
WHERE customer_no = 'C0002'
GROUP BY  customer_no;

-- 结果如下，为空数组，[]表示一个空数组
+--------------+------+
| customer_no  | _c1  |
+--------------+------+
| C0002        | []   |
+--------------+------+

-- []的结果和如下结果相同，因此验证了上述结果是空数组
select array();
+------+
| _c0  |
+------+
| []   |
+------+

当值全部为null，结果为空数组

SELECT  customer_no
       ,collect_list(cast(stock_fee as string))
FROM table1
WHERE customer_no = 'C0001'
GROUP BY  customer_no;

-- 结果如下，
+--------------+----------------+
| customer_no  |      _c1       |
+--------------+----------------+
| C0001        | ["0.1","0.1"]  |
+--------------+----------------+

当值中既有null，又有非null值，结果为非null的集合

SELECT  customer_no
       ,collect_list(cast(stock_fee as string))
FROM table1
WHERE customer_no = 'C0003'
GROUP BY  customer_no;

-- 结果如下
+--------------+----------------------------------------------------+
| customer_no  |                        _c1                         |
+--------------+----------------------------------------------------+
| C0003        | ["2635.23","1253.69","253.69","1528.12","412.53"]  |
+--------------+----------------------------------------------------+

• 验证concat_ws的结果

SELECT  customer_no
       ,concat_ws(';',collect_list(cast(stock_fee as string)))
FROM table1
WHERE customer_no = 'C0001'
GROUP BY  customer_no;

-- 结果如下
+--------------+----------+
| customer_no  |   _c1    |
+--------------+----------+
| C0001        | 0.1;0.1  |
+--------------+----------+

SELECT  customer_no
       ,ccw
       ,if(ccw = '','T','F')    AS is_empty
       ,if(ccw is null,'T','F') AS is_null
FROM
(
    SELECT  customer_no
           ,concat_ws(';',collect_list(cast(stock_fee AS string))) AS ccw
    FROM table1
    WHERE customer_no = 'C0002'
    GROUP BY  customer_no
) AS tmp;

-- 结果如下
+--------------+------+-----------+----------+
| customer_no  | ccw  | is_empty  | is_null  |
+--------------+------+-----------+----------+
| C0002        |      | T         | F        |
+--------------+------+-----------+----------+

如果collect_list的结果为空字符传，则concat_ws的结果为空字符串。