如何根据MySQL或Hive中的列将多行组合到单行中

Question

我需要在2019年至2021年之间每个标题的平均销售额。有2个输入表：

Title Table
Title_id Title_type Price_per
1        tv        10
2        book      50
3        cd        20
        
Transactions table(trans)
tran_id Title_id   Qty  year
1       3          2    2019
2       1          1    2019
3       3          5    2020
4       3          3    2020
5       1         10    2021

预期结果应生成以下列：

Title_id|Avg_sales_2019|Avg_sales_2020|Avg_sales_2021

title_id        avg_sales_2019  avg_sales_2020  avg_sales_2021
1               10.0            NULL            100.0
3               40.0            80.0            NULL

我使用以下查询，但它不会生成预期的输出，

select a.title_id,
case when a.year=2019 then avg end as Avg_sales_2019,
case when a.year=2020 then avg end as Avg_sales_2020,
case when a.year=2021 then avg end as Avg_sales_2021
from (Select t.title_id, x.year, AVG(t.Price_per*x.Qty) as avg 
from title t join trans x on t.title_id=x.title_id 
group by t.title_id,x.year) a;

title_id        avg_sales_2019  avg_sales_2020  avg_sales_2021
1               10.0            NULL            NULL
1               NULL            NULL            100.0
3               40.0            NULL            NULL
3               NULL            80.0            NULL

如何将特定title_id的行组合起来。要获取预期的结果

注意：我正在蜂巢中运行查询

Answer 1

使用条件聚合：

SELECT
    t.title_id,
    AVG(CASE WHEN x.year = 2019
             THEN t.Price_per * x.Qty ELSE 0 END) AS avg_sales_2019,
    AVG(CASE WHEN x.year = 2020
             THEN t.Price_per * x.Qty ELSE 0 END) AS avg_sales_2020,
    AVG(CASE WHEN x.year = 2021
             THEN t.Price_per * x.Qty ELSE 0 END) AS avg_sales_2021
FROM title t
LEFT JOIN trans x
    ON x.title_id = t.title_id
GROUP BY
    t.title_id
ORDER BY
    t.title_id;