如何根據 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 的行以獲得預期結果

注意：我正在 Hive 中執行查詢

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;