1 concat_ws
select
concat_ws(',', 1, 2, null, 3), --1,2,3
concat_ws(1, 2, null, 3), --213，第一个字符是分隔符！！
concat_ws(1, 2, 3), --213 , 第一个字符是分隔符！！
concat_ws('#', 1, 2, null, 3), --1#2#3，第一个字符是分隔符！！
concat_ws('#', 1, 2, 3), --1#2#3
concat_ws('#', 1, 2, 3, 1, 2) as c10, --1#2#3#1#2
-- 输入需要是一个字段，collect_list(1, 2, 3) as c11; -- BuiltinFunctionException: Sql 1 Can not match expected 3 params for expression org.apache.spark.sql.catalyst.expressions.aggregate.CollectList
-- 前面只写一个逗号，报错，concat_ws(, 1, 2, 3, 1, 2) as c11 -- SqlParseException: Sql 1 Encountered "( ," at line 8, column 14.Please check [concat_ws(, 1, 2, 3, 1, 2) as c11].
2 concat
select
concat(',', 1, 2, 3), --,123
concat(',', 1, 2, null, 3) --NULL
3 collect_list/collect_set/sort_array/array_contains
select
month,
--concat_ws(',', qty) as qty_ws --可能原因:org.apache.calcite.runtime.CalciteContextException: Sql 1: From line 3, column 20 to line 3, column 22: Expression 'qty' is not being grouped
collect_list(qty) as qty_list,
collect_set(qty) as qty_set,
size(collect_list(qty)) as qty_list_size,
sort_array(collect_list(qty)) as qty_list_sort,
sort_array(collect_set(qty)) as qty_set_sort,
array_contains(collect_set(qty), 4) as contain4
from
(
select
date_format(order_day, 'yyyy-MM') as month,
qty
from
(
select
11 as order_id,
'2022-01-02 ' as order_day,
1 as qty
union all
select
12 as order_id,
'2022-02-02' as order_day,
2 as qty
union all
select
9 as order_id,
'2022-01-02' as order_day,
1 as qty
union all
select
8 as order_id,
'2022-02-06' as order_day,
2 as qty
union all
select
13 as order_id,
'2022-03-02' as order_day,
3 as qty
union all
select
16 as order_id,
'2022-04-02' as order_day,
6 as qty
union all
select
14 as order_id,
'2022-04-02' as order_day,
4 as qty
union all
select
15 as order_id,
'2022-04-02' as order_day,
5 as qty
union all
select
16 as order_id,
'2022-05-02' as order_day,
6 as qty
) t1
) t2
group by
month
order by
month;
4 str_to_map/map_keys/map_values
select
concat_ws('#', order_id, order_day, qty) as c1,
concat_ws(
',',
concat_ws(':', 'order_id', order_id),
concat_ws(':', 'order_day', order_day),
concat_ws(':', 'qty', qty)
) as str,
str_to_map(
concat_ws(
',',
concat_ws(':', 'order_id', order_id),
concat_ws(':', 'order_day', order_day),
concat_ws(':', 'qty', qty)
),
',',
':'
) as map_type,
size(
str_to_map(
concat_ws(
',',
concat_ws(':', 'order_id', order_id),
concat_ws(':', 'order_day', order_day),
concat_ws(':', 'qty', qty)
),
',',
':'
)
) as map_size,
map_keys(
str_to_map(
concat_ws(
',',
concat_ws(':', 'order_id', order_id),
concat_ws(':', 'order_day', order_day),
concat_ws(':', 'qty', qty)
),
',',
':'
)
) as map_keys_arr,
map_values(
str_to_map(
concat_ws(
',',
concat_ws(':', 'order_id', order_id),
concat_ws(':', 'order_day', order_day),
concat_ws(':', 'qty', qty)
),
',',
':'
)
) as map_values_arr
from
(
select
11 as order_id,
'2022-01-02 ' as order_day,
1 as qty
union all
select
12 as order_id,
'2022-02-02' as order_day,
2 as qty
union all
select
9 as order_id,
'2022-01-02' as order_day,
1 as qty
union all
select
8 as order_id,
'2022-02-06' as order_day,
2 as qty
union all
select
13 as order_id,
'2022-03-02' as order_day,
3 as qty
union all
select
16 as order_id,
'2022-04-02' as order_day,
6 as qty
union all
select
14 as order_id,
'2022-04-02' as order_day,
4 as qty
union all
select
15 as order_id,
'2022-04-02' as order_day,
5 as qty
union all
select
16 as order_id,
'2022-05-02' as order_day,
6 as qty
) t1
Hive 3.0.1版本
cwiki.apache.org/confluence/… Hive官方函数文档
