hadoop - Hive - 数组中相同的记录序列

Question

我有一个包含小时级别数据的表格。我想找到数组中所有小时的小时数以及 col1 和 col2 的值。输入表

+-----+-----+-----+
| hour| col1| col2|
+-----+-----+-----+
| 00  | 0.0 | a   |
| 04  | 0.1 | b   |
| 08  | 0.2 | c   |
| 12  | 0.0 | d   |
+-----+-----+-----+

我正在使用下面的查询来获取数组中的列值

查询:选择计数 (hr), map_values(str_to_map(concat_ws(',',collect_set(concat_ws(':',reflect('java.util.UUID','randomUUID'),cast(col1 as string))))))从表格；

我得到的输出，col2_arr 中的值与 col1_arr 的顺序不同。请建议我如何以相同的顺序获取不同列的数组/列表中的值。

+----------+-----------------+----------+
| count(hr)| col1_arr        | col2_arr | 
+----------+-----------------+----------+
| 4        | 0.0,0.1,0.2,0.0 | b,a,c,d  | 
+----------+----------------+-----------+

Required output:

+----------+-----------------+----------+
| count(hr)| col1_arr        | col2_arr | 
+----------+-----------------+----------+
| 4        | 0.0,0.1,0.2,0.0 | a,b,c,d  | 
+----------+----------------+-----------+

谢谢

Answer 1

select  count(*) as cnt 
       ,concat_ws(',',sort_array(collect_list(hour)))  as hour
       ,regexp_replace(concat_ws(',',sort_array(collect_list(concat_ws(':',hour,cast(col1 as string))))),'..:','') as col1
       ,regexp_replace(concat_ws(',',sort_array(collect_list(concat_ws(':',hour,col2)))),'..:','') as col2

from    mytable
;

---

+-----+-------------+-------------+---------+
| cnt |    hour     |    col1     |  col2   |
+-----+-------------+-------------+---------+
|   4 | 00,04,08,12 | 0,0.1,0.2,0 | a,b,c,d |
+-----+-------------+-------------+---------+