Alternative à l'utilisation de GROUP BY sans agrégats pour récupérer distinctes “meilleur” résultat

Je suis en train de récupérer le "Meilleur" entrée possible à partir d'une table SQL.

Considérer un tableau contenant des émissions de télévision:
l'id, le titre, l'épisode, is_hidef, is_verified
par exemple:

id title         ep hidef verified
1  The Simpsons  1  True  False
2  The Simpsons  1  True  True
3  The Simpsons  1  True  True
4  The Simpsons  2  False False
5  The Simpsons  2  True  False

Il peut y avoir des doublons de lignes pour un seul titre et l'épisode qui peut ou peut ne pas avoir des valeurs différentes pour les champs booléens. Il peut y avoir plus de colonnes contenant des informations supplémentaires, mais c'est sans importance.

Je veux un résultat qui me donne le meilleur de ligne (donc is_hidef et is_verified sont à la fois "vrai" si possible) pour chaque épisode. Pour les lignes considérés comme des "égaux" je veux que la ligne la plus récente (ordre naturel, ou par un abitrary colonne datetime).

3  The Simpsons  1  True  True
5  The Simpsons  2  True  False

Dans le passé, j'aurais utilisé la requête suivante:

SELECT * FROM shows WHERE title='The Simpsons' GROUP BY episode ORDER BY is_hidef, is_verified

Cela fonctionne sous MySQL et SQLite, mais va à l'encontre de la SQL spec (GROUPE EN exigeant aggragates etc etc). Je ne suis pas vraiment intéressé à entendre à nouveau pourquoi MySQL est si mauvais pour permettre à celui-ci; mais je suis très intéressé à trouver une solution alternative qui permettra de travailler sur d'autres moteurs de trop (les points de bonus si vous pouvez me donner l'ORM de django code).

Merci =)

"les points de bonus si vous pouvez me donner l'ORM de django code" Quel genre de bonus allons-nous parler?
Mon impérissable grâce? Sérieusement tho: il n'y a pas de véritable récompense :p
Je ne pense pas qu'il y est de manière - sans l'aide d'un JOIN - dire les Moteurs de base de données qui permettent à ce genre de regroupement, de ce que l'aléatoire de lignes que vous voulez.
Je n'ai pas l'esprit d'une solution qui utilise une jointure. Je suis l'espoir d'une solution qui n'utilise pas de GROUPE si (ou si c'est le cas, pour se conformer avec le SQL spec).
est-ce pour SQL(T-Sql) ou MySql?

OriginalL'auteur Tyris | 2011-01-17

1

Ce qui est essentiellement une forme de la groupwise-maximum-avec-des liens de problème. Je ne pense pas qu'il y est une solution conforme à la norme SQL. Une solution de ce genre effectuer bien:
```
SELECT  s2.id
,       s2.title
,       s2.episode
,       s2.is_hidef
,       s2.is_verified
FROM    (
        select  distinct title
        ,       episode
        from    shows
        where   title = 'The Simpsons' 
        ) s1
JOIN    shows s2
ON      s2.id = 
        (
        select  id
        from    shows s3
        where   s3.title = s1.title
                and s3.episode = s1.episode
        order by
                s3.is_hidef DESC
        ,       s3.is_verified DESC
        limit   1
        )
```
Mais étant donné le coût de la lisibilité, je m'en tiendrais à votre requête d'origine.

Semble réalisable... je vais jeter un oeil à l'article au plus tard ce soir aussi. Toute idée de ce que la différence de rendement (positif ou négatif) par rapport à ma requête peut être? Je vais avoir un jeu avec celui-ci quand j'ai la chance d'accepter votre réponse.
Le lien que vous avez fourni a été très utile et j'ai façonné une solution de votre post et le lien. Je vais devoir utiliser raw() dans django, mais c'est très bien, que je suppose.
Cela fonctionne pour moi. Mais la réflexion des stagiaires de la performance, c'est trop lourd. En prenant beaucoup de temps à exécuter pour 45 dossiers sur le 'Group By'. Est-il possible que nous puissions optimiser la requête ci-dessus?

OriginalL'auteur Andomar
2

D'une certaine façon similaire à Andomar mais celle-ci fonctionne vraiment.
```
select C.*
FROM
(
    select min(ID) minid
    from (
        select distinct title, ep, max(hidef*1 + verified*1) ord
        from tbl
        group by title, ep) a
    inner join tbl b on b.title=a.title and b.ep=a.ep and b.hidef*1 + b.verified*1 = a.ord
    group by a.title, a.ep, a.ord
) D inner join tbl C on D.minid = C.id
```
Le premier niveau tie-break convertit bits (SQL Server) ou MySQL booléen pour une valeur de nombre entier à l'aide de *1, et les colonnes sont ajoutées à produire le "meilleur" de la valeur. Vous pouvez leur donner de poids, par exemple, si hidef > vérifié, puis utilisez hidef*2 + vérifiée*1 qui peut produire des 3,2,1 ou 0.

Le 2ème niveau ressemble parmi ceux de la "meilleure" scénario et extrait le code minimal (ou certains autres tie-break de la colonne). C'est essentiel pour réduire un multi-match de l'ensemble des résultats à un seul enregistrement.

Dans ce cas particulier (schéma de la table), la sélection externe utilise le direct clé pour récupérer les enregistrements correspondants.

Merci pour la réponse cyberkiwi. Je suis allé avec Andomar de réponse pour le moment, mais votre méthode de pondération peut s'avérer utile dans l'avenir. Si je pouvais le taux de ce post, je voudrais... mais je n'ai pas assez rep =(
Quelle est la part de ma requête ne fonctionne pas?
La partie qui dit "LIMIT 1". La question était de savoir "...fonctionne sous MySQL et SQLite, mais va à l'encontre de la SQL spec..." alors j'ai pensé à une solution conforme à la norme ANSI SQL92 a été demandée. La LIMITE n'est pas
Je n'avais pas conscience de la LIMITE n'était pas conforme aux normes =( .. je pourrais avoir un peu plus de jouer avec les deux solutions. Puisque la LIMITE est prise en charge dans toutes les bases de données, je suis probablement jamais utiliser cette avec je peut aller avec celui-là. Merci pour l'info =)
Ouais, ma réponse dit qu'il n'est pas conforme aux normes. Je ne sais pas de manière à résoudre les liens au sein de la Norme SQL. Votre solution n'a pas (c'est à dire répéter les rangs 2 et 3)

OriginalL'auteur RichardTheKiwi

Vous devez vous connecter pour publier un commentaire.