HIVE GROUP_CONCAT与ORDER BY [英] HIVE GROUP_CONCAT with ORDER BY
问题描述
我有一张桌子
我希望这样的输出(将结果组合到一个记录中,并且group_concat应该按值DESC对结果进行排序).
I expect the output like this (group concat the results in one record, and the group_concat should sort the results by value DESC).
这是我尝试过的查询,
SELECT id,
CONCAT('{',CONCAT_WS(',',GROUP_CONCAT(CONCAT('"',key, '":"',value, '"'))), '}') AS value
FROM
table_name
GROUP BY id
我希望目标表中的值应按源表值排序(降序).
I want the value in the destination table should be sorted (descending order) by source table value.
为此,我尝试执行 GROUP_CONCAT(... ORDER BY值).
像Hive一样不支持此功能.还有其他方法可以在蜂巢中实现这一目标吗?
Looks like Hive does not support this. Is there any other way to achieve this in hive?
推荐答案
尝试此查询.
Hive不支持GROUP_CONCAT函数,但是您可以使用collect_list函数来实现类似的功能.另外,您将需要使用分析窗口函数,因为Hive在collect_list函数内部不支持ORDER BY子句
Hive does not support the GROUP_CONCAT function, but instead you can use the collect_list function to achieve something similar. Also, you will need to use analytic window functions because Hive does not support ORDER BY clause inside the collect_list function
select
id,
-- since we have a duplicate group_concat values against the same key
-- we can pick any one value by using the min() function
-- and grouping by the key 'id'
-- Finally, we can use the concat and concat_ws functions to
-- add the commas and the open/close braces for the json object
concat('{', concat_ws(',', min(g)), '}')
from
(
select
s.id,
-- The window function collect_list is run against each row with
-- the partition key of 'id'. This will create a value which is
-- similar to the value obtained for group_concat, but this
-- same/duplicate value will be appended for each row for the
-- same key 'id'
collect_list(s.c) over (partition by s.id
order by s.v desc
rows between unbounded preceding and unbounded following) g
from
(
-- First, form the key/value pairs from the original table.
-- Also, bring along the value column 'v', so that we can use
-- it further for ordering
select
id,
v,
concat('"', k, '":"', v, '"') as c
from
table_name -- This it th
)
s
)
gs
-- Need to group by 'id' since we have duplicate collect_list values
group by
id
这篇关于HIVE GROUP_CONCAT与ORDER BY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!