【FAQ】Hive collect_set()无序、collect_list()有序和concat_ws()使用

雨点打透心脏的1/2处 2023-01-23 10:56 1阅读 0赞

## 需求来源 ##

对列值分组，并按一定顺序排序，最后多行合并一行，合并值左到右逆序排列。

## 问题描述 ##

select
      t.id,
      concat_ws(',', collect_set(t.class))
    from
      temp t
    group by
      t.id

原始temp数据

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>loongshaw</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>loongshaw</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>loongshaw</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>loongshaw</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

期望值

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>loongshaw</td> 
   <td>1,2,3,4</td> 
  </tr> 
 </tbody> 
</table>

实际结果，class合并后并不是有序

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>loongshaw</td> 
   <td>1,3,2,4</td> 
  </tr> 
 </tbody> 
</table>

## 解决方式 ##

collect\_set无序集合，改成collect\_list或sort\_array进行排序。

concat_ws(',', sort_array(collect_set(t.class), false))

sort\_array(e: column, asc: boolean)将array中元素排序(自然排序)，默认asc。

结果，class合并后有序

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>loongshaw</td> 
   <td>1,2,3,4</td> 
  </tr> 
 </tbody> 
</table>

concat_ws(',',collect_list(t.class))

结果，class合并后有序

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>loongshaw</td> 
   <td>1,2,3,4</td> 
  </tr> 
 </tbody> 
</table>

## 参考文章 ##

[Spark API 全集(2):Spark SQL 函数全集][Spark API _2_Spark SQL]  
[Hive中concat函数和concat\_ws函数的区别][Hive_concat_concat_ws]  
[Hive collect\_set()排序和concat\_ws()的使用][Hive collect_set_concat_ws]

[Spark API _2_Spark SQL]: https://liam-blog.ml/2018/03/23/spark-sql-functions-api/
[Hive_concat_concat_ws]: https://blog.csdn.net/henrrywan/article/details/86543202
[Hive collect_set_concat_ws]: https://blog.csdn.net/qq_37250067/article/details/93768254