Comment fusionner plusieurs lignes en une seule ligne basée sur une colonne dans MySQL ou Hive

Question

Je dois générer les ventes moyennes pour chaque titre entre 2019 et 2021. Il existe 2 tables de saisie :

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

Le résultat attendu devrait produire les colonnes suivantes :

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

J'ai utilisé la requête ci-dessous mais elle ne génère pas le résultat attendu

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

Comment combiner des lignes avec un title_id spécifique pour obtenir le résultat attendu

Remarque : j'exécute la requête dans Hive

Answer 1

Utiliser l'agrégation conditionnelle :

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;