2
votes

Condition de jointure SQL A ou B mais pas à la fois A et B

J'ai des données de ventes par année et par trimestre et pour la dernière année, je souhaite remplir les trimestres manquants avec la dernière valeur disponible.

Disons que nous avons une table source:

select r.year, k.quarter, t.sales
from (select distinct year        from [MyTable]) r cross join
     (select distinct quarter     from [MyTable]) k left join
     [MyTable] t
     on (r.year = t.year and k.quarter=t.quarter) or row_no=1

Résultats souhaités:

+------+---------+-------+------------------------+
| year | quarter | sales |                        |
+------+---------+-------+------------------------+
| 2018 |       1 |  4000 |                        |
| 2018 |       2 |  6000 |                        |
| 2018 |       3 |  5000 |                        |
| 2018 |       4 |  3000 |                        |
| 2019 |       1 |  8000 |                        |
| 2019 |       2 |  8000 | <repeat the last value |
| 2019 |       3 |  8000 | <repeat the last value |
| 2019 |       4 |  8000 | <repeat the last value |
+------+---------+-------+------------------------+

La tâche est donc de rendre cartésien année et trimestre et à gauche joignent à lui les ventes correspondantes ou les dernières.

Ce code m'amène presque là:

+------+---------+-------+--------+
| year | quarter | sales | row_no |
+------+---------+-------+--------+
| 2018 |       1 |  4000 |      5 |
| 2018 |       2 |  6000 |      4 |
| 2018 |       3 |  5000 |      3 |
| 2018 |       4 |  3000 |      2 |
| 2019 |       1 |  8000 |      1 |
+------+---------+-------+--------+

Comment corriger la dernière ligne (condition de jointure) pour que le 2018 ne soit pas doublé? p>

sql sql-server join

0 commentaires

3 Réponses :

3
votes

Une méthode utilise une application externe:

select y.year, q.quarter,
       coalesce(t.sales, tdefault.sales)
from (select distinct year from [MyTable]) y cross join
     (select distinct quarter from [MyTable]) q left join
     [MyTable] t
     on t.year = y.year and
        t.quarter = q.quarter cross join
     (select top (1) t.*
      from [MyTable] t
      order by t.year desc, t.quarter desc
     ) tdefault

Pour votre volume de données, cela devrait convenir.

Une méthode plus efficace - en supposant que vous n'attribuez que des valeurs à la fin - serait:

select y.year, q.quarter, t.sales
from (select distinct year from [MyTable]) y cross join
     (select distinct quarter from [MyTable]) q outer apply
     (select top (1) t.*
      from [MyTable] t
      where t.year < y.year or
            (t.year = y.year and t.quarter <= q.quarter)
      order by t.year desc, t.quarter desc
     ) t;

0 commentaires

1
votes

Une approche très différente utilisant un CTE et certaines fonctions de fenêtre. Cela ne nécessite pas 2 analyses de la table, ni une jointure triangulaire.

WITH VTE AS(
    SELECT *
    FROM (VALUES (2018,1,4000,5),
                 (2018,2,6000,4),
                 (2018,3,5000,3),
                 (2018,4,3000,2),
                 (2019,1,8000,1)) V([Year],[Quarter],sales, row_no)),
CTE AS(
    SELECT Y.Year,
           Q.Quarter,
           V.sales,
           V.row_no,
           COUNT(CASE WHEN V.sales IS NOT NULL THEN 1 END) OVER (ORDER BY Y.[Year], Q.[Quarter]
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM (VALUES(2018),(2019)) Y([Year])
         CROSS JOIN (VALUES(1),(2),(3),(4)) Q([Quarter])
         LEFT JOIN VTE V ON Y.[Year] = V.[Year] AND Q.[Quarter] = V.[Quarter])
SELECT C.[Year],
       C.[Quarter],
       MAX(C.sales) OVER (PARTITION BY C.Grp) AS Sales
FROM CTE C;

Cela ne fonctionnera que sur SQL Server 2012+ (comme ROWS BETWEEN a été introduit avec SQL Server 2012), cependant, j'espère que vous n'utilisez pas 2008, qui sont tous (presque) entièrement hors support.

0 commentaires

1
votes

Je ferais simplement JOIN :

SELECT TT.YEAR, TT.Quarter, COALESCE(T.SALES, T1.SALES) AS Sales 
FROM (SELECT DISTINCT T.YEAR, TT.Quarter
      FROM [MyTable] T CROSS JOIN
           ( SELECT DISTINCT TT.Quarter FROM [MyTable] TT ) TT
     ) TT LEFT JOIN 
     [MyTable] T 
     ON TT.YEAR = T.YEAR AND TT.Quarter = T.Quarter OUTER APPLY 
     ( SELECT TOP (1) T.*
       FROM [MyTable] T
       WHERE T.YEAR = TT.YEAR
       ORDER BY T.Quarter DESC
     ) T1;

MODIFIER: Je viens de mal lire la question du trimestre code supplémentaire > s donc, vous avez besoin de APPLY dans OUTER JOIN:

SELECT TT.YEAR, TT.Quarter, COALESCE(T.SALES, MAX(T.SALES) OVER (PARTITION BY TT.YEAR)) AS sales 
FROM (SELECT DISTINCT T.YEAR, TT.Quarter
      FROM [MyTable] T CROSS JOIN
           ( SELECT DISTINCT TT.Quarter FROM [MyTable] TT ) TT
     ) TT LEFT JOIN 
     [MyTable] T 
     ON TT.YEAR = T.YEAR AND TT.Quarter = T.Quarter;

3 commentaires

Êtes-vous sûr que le fait que les ventes de la dernière période soient accidentellement les plus élevées n'influence pas votre requête?

Disons que le temps a passé et que nous avons un quart supplémentaire dans la table source. Et si les ventes du T2 2019 = 7000 (donc moins que le T1 2019). Ensuite, la partition MAX par année produirait 8000 (valeur pour 2019 Q1), ce qui n'est pas ce que je veux. Je veux la dernière valeur disponible qui dans ce cas serait 2019 Q2.

@PrzemyslawRemin. . . Correct j'ai mal lu la question, oubliez le scénario que vous aviez. Je vous remercie.