Compte tenu de l'ensemble de données ci-dessous, je souhaite exécuter une requête pour mettre en évidence les «paires» qui ne consistent pas en une «gauche» et une «droite».

+---------+-----------+---------------+----------------------+
| Pair_Id | Pair_Name | Individual_Id | Individual_Direction |
+---------+-----------+---------------+----------------------+
|       1 | A         | A1            | Left                 |
|       1 | A         | A2            | Right                |
|       2 | B         | B1            | Right                |
|       2 | B         | B2            | Left                 |
|       3 | C         | C1            | Left                 |
|       3 | C         | C2            | Left                 |
|       4 | D         | D1            | Right                |
|       4 | D         | D2            | Left                 |
|       5 | E         | E1            | Left                 |
|       5 | E         | E2            | Right                |
+---------+-----------+---------------+----------------------+

Dans ce cas, la paire 3 «C» a deux gauches. Par conséquent, je chercherais à afficher ce qui suit:

+---------+-----------+---------------+----------------------+
| Pair_Id | Pair_Name | Individual_Id | Individual_Direction |
+---------+-----------+---------------+----------------------+
|       3 | C         |  C1           | Left                 |
|       3 | C         |  C2           | Left                 |
+---------+-----------+---------------+----------------------+
1
JHarley1 3 sept. 2020 à 13:11

3 réponses

Meilleure réponse

Vous pouvez simplement utiliser not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.pair_id = t.pair_id and
                        t2.Individual_Direction <> t.Individual_Direction
                 ) ;

Avec un index sur (pair_id, Individual_Direction), cela devrait non seulement être la solution la plus concise mais aussi la plus rapide.

Si vous voulez être sûr qu'il y a des paires (ce qui précède renvoie des singletons):

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.pair_id = t.pair_id and
                        t2.Individual_Direction <> t.Individual_Direction
                 ) and
      exists (select 1
              from t t2
              where t2.pair_id = t.pair_id and
                    t2.Individual_ID <> t.Individual_ID
            );

Vous pouvez également le faire à l'aide des fonctions de fenêtre:

select t.*
from (select t.*,
             count(*) over (partition by pair_id) as cnt,
             min(status) over (partition by pair_id) as min_status,
             max(status) over (partition by pair_id) as max_status
      from t
     ) t
where cnt > 1 and min_status <> max_status;
3
Gordon Linoff 3 sept. 2020 à 11:09

Il devrait y avoir une manière élégante d'utiliser la fonction de fenêtre que ce que j'ai écrit:

WITH ranked AS
(
 SELECT *, RANK() OVER(ORDER BY Pair_Id, Pair_Name, Individual_Direction) AS r
     FROM pairs
),
    counted AS
(
SELECT Pair_Id, Pair_Name, Individual_Direction,r, COUNT(r) as times FROM ranked 
GROUP BY Pair_Id, Pair_Name, Individual_Direction, r
HAVING COUNT(r) > 1
)
SELECT ranked.Pair_Id, ranked.Pair_Name, ranked.Individual_Id, ranked.Individual_Direction FROM ranked
RIGHT JOIN counted 
ON ranked.Pair_Id=counted.Pair_Id 
AND ranked.Pair_Name=counted.Pair_Name 
AND ranked.Individual_Direction=counted.Individual_Direction
0
RandyMcKay 3 sept. 2020 à 10:45

Une option utilise l'agrégation:

WITH cte AS (
    SELECT Pair_Name
    FROM yourTable
    WHERE Individual_Direction IN ('Left', 'Right')
    GROUP BY Pair_Name
    HAVING MIN(Individual_Direction) = MAX(Individual_Direction)
)

SELECT *
FROM yourTable
WHERE Pair_Name IN (SELECT Pair_Name FROM cte);

La clause HAVING utilisée ci-dessus affirme qu'une paire correspondante a à la fois une direction minimale et une direction maximale qui sont identiques. Cela implique qu'une telle paire n'a qu'une seule direction.

Comme c'est le cas avec la réponse de Gordon, un index sur (Pair_Name, Individual_Direction) peut améliorer les performances:

CREATE INDEX idx ON yourTable (Pair_Name, Individual_Direction);
3
Tim Biegeleisen 3 sept. 2020 à 11:05