Je suis très mauvais pour expliquer, alors laissez-moi essayer d'exposer mon problème. J'ai un tableau qui ressemble à ce qui suit:

 Source    Value    User
========  =======  ======
  old1       1      Phil
  new        2      Phil
  old2       3      Phil
  new        4      Phil
  old1       1      Mike
  old2       2      Mike
  new        1      Jeff
  new        2      Jeff

Ce que je dois faire, c'est créer une requête qui obtient des valeurs pour les utilisateurs en fonction de la source et de la valeur. Il devrait suivre cette règle:

Pour chaque utilisateur, obtenez la valeur la plus élevée. Cependant, ignorez la «nouvelle» source si «old1» ou «old2» existe pour cet utilisateur.

Donc, sur la base de ces règles, ma requête doit renvoyer ce qui suit à partir de ce tableau:

 Value    User
=======  ======
   3      Phil
   2      Mike
   2      Jeff

J'ai trouvé une requête qui se rapproche de ce qui est demandé:

SELECT      MAX([Value]), [User]
FROM
(
    SELECT  CASE [Source]
                WHEN 'old1' THEN 1
                WHEN 'old2' THEN 1
                WHEN 'new'  THEN 2
            END                 AS [SourcePriority],
            [Value],
            [User]
    FROM    #UserValues
) MainPriority
WHERE       [SourcePriority] = 1
GROUP BY    [User]
UNION
SELECT      MAX([Value]), [User]
FROM
(
    SELECT  CASE [Source]
                WHEN 'old1' THEN 1
                WHEN 'old2' THEN 1
                WHEN 'new'  THEN 2
            END                 AS [SourcePriority],
            [Value],
            [User]
    FROM    #UserValues
) SecondaryPriority
WHERE       [SourcePriority] = 2
GROUP BY    [User]

Cependant, cela renvoie les résultats suivants:

 Value    User
=======  ======
   3      Phil
   4      Phil
   2      Mike
   2      Jeff

Évidemment, cette valeur supplémentaire pour Phil = 4 n'est pas souhaitée. Comment dois-je tenter de résoudre cette requête? Je comprends également que c'est une solution assez compliquée et qu'elle peut probablement être plus facilement résolue par une utilisation appropriée des agrégats, mais je ne suis pas encore trop familier avec les agrégats, ce qui m'a conduit à recourir à une union. Je recherche essentiellement de l'aide pour créer la solution la plus propre possible.

Voici le code SQL si quelqu'un voulait remplir la table lui-même pour l'essayer:

CREATE TABLE #UserValues
(
    [Source] VARCHAR(10),
    [Value]  INT,
    [User]   VARCHAR(10)
)
INSERT INTO #UserValues VALUES
('old1', 1, 'Phil'),
('new',  2, 'Phil'),
('old2', 3, 'Phil'),
('new',  4, 'Phil'),
('old1', 1, 'Mike'),
('old2', 2, 'Mike'),
('new',  1, 'Jeff'),
('new',  2, 'Jeff')
4
Jon Warren 20 nov. 2018 à 20:25

4 réponses

Meilleure réponse

Vous pouvez utiliser les priorités order by avec row_number():

select top (1) with ties uv.*
from #UserValues uv
order by row_number() over (partition by [user] 
                            order by (case when source = 'old2' then 1 when source = 'old1' then 2 else 3 end), value desc 
                           );

Cependant, si vous n'avez que source limité à 3, vous pouvez également faire:

. . . 
order by row_number() over (partition by [user] 
                            order by (case when source = 'new' then 2 else 1 end), value desc 
                           )
1
Yogesh Sharma 20 nov. 2018 à 17:32

Vous pouvez le résoudre assez facilement sans recourir aux fonctions de fenêtre. Dans ce cas, vous avez besoin de la valeur maximale où ((pas nouveau) OU (il n'y a pas d'entrée old1 ou old2)).

Voici une requête qui fonctionne correctement avec vos exemples de données:

SELECT
    MAX(U1.[Value]) as 'Value'
    ,U1.[User]
FROM
    #UserValues U1
WHERE
    U1.[Source] <> 'new' 
    OR NOT EXISTS (SELECT * FROM #UserValues U2 WHERE U2.[User] = U1.[User] AND U2.[Source] IN ('old1','old2'))
GROUP BY U1.[User]
2
Shawn Pence 20 nov. 2018 à 17:39

Je pense que vous devriez envisager de mettre en place une table XREF pour définir quelle source est quelle priorité, pour une éventuelle priorisation plus compliquée à l'avenir. Je le fais avec une table temporaire:

CREATE TABLE #SourcePriority
(
    [Source]         VARCHAR(10),
    [SourcePriority] INT
)
INSERT INTO #SourcePriority VALUES
('old1', 1),
('old2', 1), 
('new',  2)

Vous pouvez également créer une vue pour rechercher la SourcePriority dans la table d'origine. Je le fais avec une implémentation possible CTE + comment rechercher la priorité la plus élevée avec la valeur la plus élevée:

;WITH CTE as (
    SELECT s.[SourcePriority], u.[Value], u.[User]
    FROM   #UserValues as u
        INNER JOIN #SourcePriority as s on u.[Source] = s.[Source]
)
SELECT MAX (v.[Value]) as [Value], v.[User]
FROM (
    SELECT MIN ([SourcePriority]) as [TopPriority], [User]
    FROM   cte
    GROUP BY [User]
    ) as s
    INNER JOIN cte as v
        ON s.[User] = v.[User] and s.[TopPriority] = v.[SourcePriority]
GROUP BY v.[User]
1
Dávid Laczkó 20 nov. 2018 à 18:33

Je pense que tu veux:

select top (1) with ties uv.*
from (select uv.*,
             sum(case when source in ('old1', 'old2') then 1 else 0 end) over (partition by user) as cnt_old
      from #UserValues uv
     ) uv
where cnt_old = 0 or source <> 'new'
order by row_number() over (partition by user order by value desc);
0
Gordon Linoff 20 nov. 2018 à 18:39