hive concat_ws,collect_set的使用

-- 创建测试数据
drop table if exists jdt_dev.idmtmp_src_data_table_a_d;
CREATE table jdt_dev.idmtmp_src_data_table_a_d as select *
from (
select B.order_id,A.* from 
(select 'tom' as pin, '12:05:30' as order_time, 'no1' as order_id
union all 
select 'tom' as pin, '12:30:30' as order_time, 'no2' as order_id
)B -- 订单表
left join (
select 'tom' as pin,'page1' as page,  'ts2' as ts,'1' as seq, '12:00:30' as click_time  -- pin+页面+访次+访序+点击时间
union all 
select 'tom' as pin,'page2' as page,  'ts2' as ts,'4' as seq, '12:09:30' as click_time
union all 
select 'tom' as pin,'page4' as page,  'ts2' as ts,'5' as seq, '12:20:30' as click_time
union all 
select 'tom' as pin,'page2' as page,  'ts2' as ts,'2' as seq, '12:01:30' as click_time
union all 
select 'tom' as pin,'page3' as page,  'ts2' as ts,'3' as seq, '12:05:30' as click_time
union all

select 'tim' as pin,'page5' as page,  'ts2' as ts,'6' as seq, '12:30:30' as click_time
)A -- 流量点击表
on A.pin  = B.pin and A.click_time <= B.order_time 
order by order_id,click_time ASC  
)T1
;
-- 查看订单明细
select * from jdt_dev.idmtmp_src_data_table_a_d ;

-- 按照订单聚合页面和访序
select order_id,concat("{",
concat_ws(',' ,collect_list(concat(page,':[',ts_set,']')))
,"}")
from (
    -- 访序排序聚合 
    select 
        order_id,
        page,
        -- concat_ws(',' ,collect_list(seq) ) as ts_set_src,  --原始拼接数据
        -- --中间拼接数据
        -- concat_ws(',',
        --   sort_array(
        --      collect_list(
        --       concat_ws(':',lpad(seq,10,'0'),seq)
        --      )
        --   )
        --  ) as ts_set_mid,
        regexp_replace(
         concat_ws(',',
          sort_array(
             collect_list(
              concat_ws(':',lpad(seq,10,'0'),seq) 
             )
          )
         ),
      '\\d+\:','') as ts_set --最终拼接数据
    from jdt_dev.idmtmp_src_data_table_a_d
    group by order_id,page
)A 
group by order_id
;
-- 按照订单聚合页面
select 
    order_id,
    concat_ws(',' ,collect_list(page)) as page_set
from jdt_dev.idmtmp_src_data_table_a_d
group by order_id

-- 解决collect_list 排序问题
-- select order_id,
--     concat_ws(',',
--           sort_array(
--              collect_list(
--               concat_ws(':',lpad(seq,10,'0'),page)
--              )
--           )
--          ),
--       regexp_replace(
--          concat_ws(',',
--           sort_array(
--              collect_list(
--               concat_ws(':',lpad(seq,10,'0'),page)
--              )
--           )
--          ),
--       '\\d+\:','')
-- from jdt_dev.idmtmp_src_data_table_a_d
-- group by order_id

详细函数说明，引用链接🔗 hive中的拼接函数（concat,group_concat,concat_ws,collect_set）_hive concat_小码良的博客-CSDN博客

1、concat
concat() 函数用于将多个字符串连接成一个字符串。
concat(string s1, string s2, string s3,…)表示通过s1、s2和s3连接一起
举例：

+----+--------+
| id | name   |
+----+--------+
|  1 | city   |
+----+--------+
1
2
3
4
5
CONCAT(id,’:’,name) = 1:city

2、concat_ws
concat_ws() 指定参数之间的分隔符
concat_ws(separator,str1,str2,…)中第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为null，则结果为null。函数会忽略任何分隔符参数后的null 值。但是concat_ws()不会忽略任何空字符串。 (然而会忽略所有的 null）
举例：数据如1
concat_ws(’_’,id,name)
结果：1_city

3、group_concat函数
group_concat() 函数返回一个字符串结果，该结果由分组中的值连接组合而成

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
[SEPARATOR str_val])
1
2
3
在 MySQL 中，你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序，可以使用 ORDER BY 子句。
SEPARATOR 是一个字符串值，它被用于插入到结果值中。缺省为一个逗号 (",")，可以通过指定 SEPARATOR “” 完全地移除这个分隔符。
可以通过变量 group_concat_max_len 设置一个最大的长度。在运行时执行的句法如下： SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
如果最大长度被设置，结果值被剪切到这个最大长度。如果分组的字符过长，可以对系统参数进行设置：SET @@global.group_concat_max_len=40000;
举例：

+---------+---------+-----------+
| uid      |      age    |     address    |
+---------+---------+-----------+
| 100001  |      21    |      深圳    |
| 100001  |   22    |    青岛      |
| 100002  |   23    |      重庆    |
| 100002  |   24    |      上海    |
+---------+---------+-----------+
1
2
3
4
5
6
7
8
select uid,group_concat(distinct age order by age desc SEPARATOR '_') 
from user_info where uid in('100001','100002') group by uid;
1
2
结果

100001        21_22
100002        23_24
1
2
4、collect_set
concat_ws常常结合group by与collect_set使用
concat_ws(SEPARATOR ,collect_set(column))与group_concat()函数作用相似
因为hive版本问题，低版本可能没有group_concat函数，只能用其他来代替。
————————————————
版权声明：本文为CSDN博主「小码良」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zll_1234/article/details/106329025