1
votes

Extraire plusieurs enregistrements de différents groupes dans Oracle en fonction de la valeur dans une colonne différente

Cela peut sembler un peu compliqué, alors je vais essayer d'être bref. Essentiellement, j'ai besoin d'exécuter une requête qui regroupe les résultats en fonction de COL1 , et à partir de là, il regarde la valeur maximale dans COL2 . Une fois qu'il a déterminé quelle est la valeur la plus élevée dans COL2 , il doit alors examiner la valeur dans COL3 , puis extraire tous les enregistrements du groupe qui ont la même valeur. Il y a d'autres colonnes dans la table dont j'ai besoin de valeurs, mais elles ne sont pas utilisées dans la logique de requête.

  COL1    COL2     COL3
1 12345   100      A
2 12345   (null)   A
6 12346   100      B
7 12346   20       B

Donc, dans cet exemple, pour les lignes avec la valeur COL1 de 12345 , je voudrais les lignes 1 et 2 (il n'a besoin que d'une seule instance de la valeur la plus élevée, peu importe ce que sont les valeurs du reste des lignes). Pour COL1 avec la valeur de 12346 j'ai besoin de lignes avec B dans COL3.

Voici un exemple de sortie dans un format un peu plus lisible:

SOME_TABLE

  COL1    COL2     COL3
1 12345   100      A
2 12345   (null)   A
3 12345   50       B
4 12346   0        A
5 12346   (null)   A
6 12346   100      B
7 12346   20       B

J'ai essayé de nombreuses requêtes et permutations différentes des requêtes, mais sans succès. Je ne sais pas à quel point il serait constructif de publier un tas de requêtes qui ne fonctionnent pas. Les deux plus grandes approches que j'ai essayées incluent une instruction GROUP BY régulière, puis aussi PARTITION BY sur COL1 .

Je dois également noter que j'ai essayé de l'implémenter du côté C # de l'application, et également d'utiliser la boucle dans le script SQL mais les résultats étaient soit mitigés, soit les performances étaient incroyablement médiocre.

Je n'ai rien trouvé d'utile dans SO ou dans la "documentation" d'Oracle.

sql oracle oracle11g

0 commentaires

3 Réponses :

0
votes

Vous pouvez utiliser des fonctions fenêtrées:

+-----+--------+-------+------+
| ID  | COL1   | COL2  | COL3 |
+-----+--------+-------+------+
|  2  | 12345  |       | A    |
|  1  | 12345  |  100  | A    |
|  7  | 12346  |   20  | B    |
|  6  | 12346  |  100  | B    |
+-----+--------+-------+------+

db démo fiddle

Sortie:

WITH cte AS (SELECT t.*, MAX(col2) OVER(PARTITION BY col1) m FROM t)
SELECT *
FROM t
WHERE (COL1,COL3) IN (SELECT col1, col3 FROM cte WHERE col2 = m);

7 commentaires

Il échouera pour les données 3 12345 100 B . Affinez votre O.

C'est probablement une question stupide, mais devrais-je remplacer t par quelque chose? J'ai essayé d'utiliser FROM some_table t pour la partie WITH , mais je n'ai pas de chance d'exécuter la requête.

@Dortimer t est le nom de votre table et vous devez donc changer chaque occurrence de t avec some_table . Vous en avez 3 t. * , FROM t dans cte et FROM t dans la requête principale.

Désolé pour la réponse tardive, mais @Serg avait raison dans son commentaire. Cette requête n'a pas généré les résultats souhaités.

@Dortimer Comment pouvez-vous vouloir sélectionner une valeur lorsque vous avez une cravate évidente? dbfiddle.uk/…

@LukaszSzozda avec un ensemble de données plus complexe, il a extrait plusieurs valeurs différentes de Col3, au lieu d'une seule valeur unique partagée entre les lignes résultantes.

@Dortimer Comment aimeriez-vous gérer les cravates? Si vous avez une égalité dans la valeur maximale, vous devez préférer une valeur à une autre. Vous devez définir une logique qui indique explicitement quelle valeur doit être prise. Ou vous voulez simplement utiliser ORDER BY .. et saisir le premier et laisser l'optimiseur de requêtes le faire. Mais en procédant de cette façon, vous obtiendrez des résultats indéterministes sur plusieurs exécutions.

0
votes

Un exemple de résultat attendu peut aider. Essayez ceci:

    select col1, col3, max(col3)
    from table
    group by col1, col3

1 commentaires

Mise à jour de la question avec le résultat attendu dans un format plus clair.

1
votes

Vous voulez toutes les lignes où les valeurs col3 correspondent au maximum pour col1 . Une sous-requête corrélée semble être la manière la plus naturelle d'exprimer ceci:

select t.*
from t
where t.col3 = (select max(t2.col3) keep (dense_rank first order by col2 desc)
                from t t2
                where t2.col1 = t.col1
               );

La longue expression keep dit simplement "garder la première valeur de col3 code> pour la plus grande valeur de col2.

2 commentaires

Cela a fait l'affaire. La performance est également assez phénoménale.

@Dortimer. . . keep me surprend toujours en termes de performances. La syntaxe est si longue mais les performances sont rapides.