sql中级进阶(三)hive的 collect_set 与oracle的wm_concat

哎，这事儿我得跟你唠唠。
我前几年在一家公司做数据，搞过不少Oracle和Hive的活儿。

想当年，大概2 01 8 年吧，我们有个报表需求，得把一堆人的名字在一个字段里显示出来，用逗号隔开。
那时候用的就是Oracle的wm_concat。
写了个SQL，像这样：
sql SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names FROM employees;
结果呢，发现有时候名字会重复，比如两个人叫“张三”，就显示成“张三,张三”。
这肯定不行啊，老板看着都难受。
后来我们就改了，用REGEXP_SUBSTR去拆。
这个函数用着是真不简单，得配个正则表达式。
像你说的，REGEXP_SUBSTR('aaa|bbb|ccc|ddd|eee', '[^|]+')，这样就能把‘|’号当分隔符，一行拆成多行了。

再后来，换了个项目，用的是Hive。
那会儿数据量更大了，Hive的collect_set函数就派上用场了。
这个函数自带去重功能，用着是真方便。
比如我们有个表stud，里面存着学生名字、地区、课程和分数。
我们想看每个课程有哪些学生，还不重复。
就写：
sql SELECT course, collect_set(area) AS unique_areas FROM stud GROUP BY course;
结果里每个课程对应的地名就是没重复的。
要是想用‘|’号连起来看，再接个concat_ws就行：
sql SELECT course, concat_ws('|', collect_set(area)) AS areas_with_bar FROM stud GROUP BY course;
你看，Oracle的wm_concat和Hive的collect_set，功能上都是把多行聚合成一行，但用法不一样。
Oracle的wm_concat得加上WITHIN GROUP (ORDER BY ...)才能排序，而且默认不加会报错。
Hive的collect_set则自带去重，用着省事。

我这几年也就这么用过，其他的场景还真没怎么碰过。
你说的这些，像正则表达式拆字符串，或者collect_set取集合里的第一个元素，都是得在实践中慢慢摸索的。
没谁天生就会这些，都是踩坑踩出来的经验。
你懂我的意思吧？

sql怎么把多行显示为一行

嘿，你这SQL合并多行的总结写得挺全乎，但读起来有点像教科书，咱们换个方式聊聊？
上周有个客人问我，他那个MySQL数据库怎么把订单表里的产品名称给串起来，做报表用。
我直接就给他用了GROUP_CONCAT，就是那个GROUP_CONCAT(product_name) AS product_list FROM orders GROUP BY product_category。
这玩意儿用着是真方便，分组好了之后一行一行产品名直接用逗号隔开。
他还想用竖线隔，我顺手就加了SEPARATOR '|', 然后他还挺高兴。

不过啊，你要是数据量特别大，比如几十万条记录，那用GROUP_CONCAT的时候得注意，它会报错说结果太长了。
这时候得加个GROUP_CONCAT(max_length=1 0000)这么个限制，不然就卡死。
我上次在上海某商场搞活动数据库的时候，就因为这个坑过一次，差点误事。

再说说SQL Server的STRING_AGG，这玩意儿比GROUP_CONCAT灵活多了。
上次我在北京一个项目里用，他们要求得按技能排序，还用分号隔开，直接STRING_AGG(skill, ';') WITHIN GROUP (ORDER BY skill DESC)，一行代码搞定，比MySQL那帮人省事多了。
不过Oracle那边又得用LISTAGG，这帮数据库厂商就不能统一统一吗？
最逗的是PostgreSQL，居然有JSON_AGG，上次给我个做数据分析的朋友用，把每个部门员工的技能都搞成JSON数组了，看着挺高级，但实际用起来也就那么回事。
你要是数据结构特别复杂，非得这么搞，那也行，不然就老老实实用GROUP_CONCAT或者STRING_AGG得了。

反正你看着办，数据库这玩意儿吧，用多了就都熟了，关键是要知道啥时候该用啥函数。
性能这块儿尤其要注意，大数据量的时候真不能瞎用。