Bagaimana untuk menggabungkan berbilang baris menjadi satu baris berdasarkan lajur dalam MySQL atau Hive

Question

Saya perlu menjana purata jualan untuk setiap tajuk antara 2019 dan 2021. Terdapat 2 jadual input:

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

Hasil yang diharapkan harus menghasilkan lajur berikut:

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

Saya menggunakan pertanyaan di bawah tetapi ia tidak menjana output yang dijangkakan

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

Cara menggabungkan baris dengan title_id tertentu untuk mendapatkan hasil yang diharapkan

Nota: Saya menjalankan pertanyaan dalam Hive

Answer 1

Gunakan pengagregatan bersyarat:

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;