相当于 LISTAGG 的 Hive

Question

我在 oracle 中有一个这样的查询，但我想要一个与此等效的配置单元 SQL。有人可以指导我下面的等效配置单元是什么

'[' || LISTAGG(t3.measure_title, ';') 在组内(按 t3.measure_title 排序)|| ']' all_measure_desc.如何在 Hive 中重写。

我在 Oracle 中的查询是

SELECT 
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk,
    '[' || LISTAGG(t3.measure_title, ';') WITHIN GROUP (ORDER BY t3.measure_title) || ']' all_measure_desc
FROM qms_gic_lifecycle t1 
INNER JOIN dim_member t2 
on t1.member_id = t2.member_id
INNER JOIN dim_quality_measure t3 
on t1.quality_measure_id = t3.quality_measure_id
GROUP BY  
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk;

Answer 1

您可以使用 collect_list 或 collect_set，具体取决于应如何处理重复值。 collect_list 保留重复值，而 collect_set 消除重复值。

输出将是值的数组。

SELECT 
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk,
    COLLECT_LIST(t3.measure_title) as all_measure_desc
FROM qms_gic_lifecycle t1 
INNER JOIN dim_member t2 
on t1.member_id = t2.member_id
INNER JOIN dim_quality_measure t3 
on t1.quality_measure_id = t3.quality_measure_id
GROUP BY  
    t1.member_id,
    t2.first_name,
    t2.date_of_birth_sk;