[2008 R2] Utilisation de TOP au lieu d'une CTE [Résolu]

[Kropernic]

Bonjour,

J'aimerais un complément d'explication sur un truc.

Je plante le décor...

[DECOR]
Il s'agit d'une DB modélisant la gestion de gifts (gift-card et gift-cheque) pour une chaine de magasins.

Le truc c'est que je récupère les données d'une vieille DB sans aucune contrainte ni rien du tout bourrées d'incohérences (d'ailleurs elle n'est faite que d'une table qui n'est même pas en 1NF )
Il a été décidé en concertation avec mon chef de tout importer dans la nouvelle DB (correctement normalisée celle-là) et d'éliminer les incohérences après l'importation.

J'ai donc dans la nouvelle DB une table T_SEND_TO_STORE_STS qui contient les envois de gifts depuis l'entrepôt vers les magasins dont la structure complète importe peu. Les seules colonnes intéressantes sont GFT_ID, STR_ID et STS_DATE.

- GFT_ID étant de type INT (et faisant référence à la clef primaire de la table T_GIFT_GFT) qui est l'identifiant du gift
- STR_ID étant de type INT (et faisant référence à la clef primaire de la table T_STORE_STR) qui est l'identifiant du magasin
- STS_DATE étant de type DATETIME qui est la date de l'envoi du gift vers le magasin

Du coup, après importation des données, je me retrouve avec des gifts ayant été envoyés deux fois de suite vers un magasin. Genre 2 personnes bossaient en même temps sur l'ancienne appli et zou, ça fait 2 envois pour le même gift dans la DB. J'vous raconte pas mon étant quand j'ai découvert ce genre de chose en analysant les données que je devais importer... Quelle horreur ! (il y a même des gifts qui sont détruits 2 fois ! )
[/DECOR]

Bref ! Ce que je souhaite, c'est obtenir l'envoi le plus récent pour un gift et un magasin donné.

J'ai donc la requête suivante (dont je sais que vous n'y verrai pas d'objection) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH T1(GFT_ID, STS_DATE)
AS(
SELECT    
        GFT_ID, MAX(STS_DATE)
FROM
        DBO.T_SEND_TO_STORE_STS
WHERE
        GFT_ID = 1500822
    AND STR_ID = 14
GROUP BY
        GFT_ID
)
SELECT 
        * 
FROM 
        DBO.T_SEND_TO_STORE_STS STS
            INNER JOIN T1 
                ON    STS.GFT_ID = T1.GFT_ID 
                AND STS.STS_DATE = T1.STS_DATE

Cela me donne bien le résultat escompté.

Par contre, je ne comprends pas ce qui m'empêche de faire ceci :

Code :

1
2
3
4
5
6
7
8
9
SELECT    TOP 1
        *
FROM
        DBO.T_SEND_TO_STORE_STS
WHERE
        GFT_ID = 1500822
    AND STR_ID = 14
ORDER BY
        STS_DATE DESC

J'avais déjà évoqué le sujet dans une autre discussion et il y avait une histoire de ne pas mélangé le logique et le physique mais je ne comprends toujours pas...
En triant sur la date de manière descendante et en prenant la première ligne, quel risque y a-t-il ?

[Rei Ichido]

Quand le résultat est unitaire, il n'y en a pas. Par contre le TOP 1 est inapplicable dès lors qu'on veut procéder en masse (ou alors il faut faire des requêtes scalaires, et on se complique souvent la vie).

Du coup en général je préfère l'utilisation de fonction de fenêtrage (row_number typiquement), c'est modulable et me permet de tout gérer avec le même genre de syntaxe, et ça évite l'auto-jointure.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH T1(GFT_ID, STS_DATE)
AS(
SELECT    
        *, ROW_NUMBER() OVER(PARTITION BY gft_id ORDER BY sts_date DESC) AS rang
FROM
        DBO.T_SEND_TO_STORE_STS
WHERE           -- possibilité de ne pas avoir de restriction 
        GFT_ID = 1500822
    AND STR_ID = 14
)
SELECT 
        * 
FROM T1
WHERE T1.rang = 1

[Kropernic]

Ok ok,

Je crois que je vais appliquer votre méthode. J'aime assez et ça m'exercera à l'utilisation des fonctions de fenêtrage que je n'utilise jamais...

Pour info, voici l'ancienne discussion à laquelle je faisais référence et le message de sqlpro auquel je pensais en disant qu'il ne faut pas mélanger logique et physique.

J'ai quand même toujours du mal à comprendre pourquoi dans le cas de cette requête-ci il n'y a pas de risque mais bien dans l'autre.

La sous requête sur laquelle je voulais utilisé l'opérateur top dans l'autre discussion n'est sensé retourné qu'une ligne également (enfin je crois... je ne pige pas encore bien l'opérateur APPLY).

Non ?

[aieeeuuuuu]

Envoyé par Kropernic

J'ai quand même toujours du mal à comprendre pourquoi dans le cas de cette requête-ci il n'y a pas de risque mais bien dans l'autre.

Ici vous avez un ORDER BY pour "assurer" l'ordre des lignes (plus ou moins, car si vous avez deux lignes avec la même date, vous ne pourrez pas savoir a priori laquelle des deux sera retournée, et ce ne sera pas forcément la même à chaque exécution...)

Dans l’autre thead, vous ne spécifiez pas de clause ORDER BY mais vous "comptiez" sur l'index cluster pour assurer un ordre dans le résultat, ce qui n'est absolument pas garantit, même si ça semble être le cas a première vue lorsqu'on exécute des requêtes simples.

[Kropernic]

Envoyé par aieeeuuuuu

Ici vous avez un ORDER BY pour "assurer" l'ordre des lignes (plus ou moins, car si vous avez deux lignes avec la même date, vous ne pourrez pas savoir a priori laquelle des deux sera retournée, et ce ne sera pas forcément la même à chaque exécution...)

Saleté de sac de billes !
Mais si deux utilisateurs arrivent à me faire ça, je veux bien manger mon chapeau ! C'est quand même précis à la millisecondes prêt... Sinon je veux bien mettre en DATETIME2

Envoyé par aieeeuuuuu

Dans l’autre thead, vous ne spécifiez pas de clause ORDER BY mais vous "comptiez" sur l'index cluster pour assurer un ordre dans le résultat, ce qui n'est absolument pas garantit, même si ça semble être le cas a première vue lorsqu'on exécute des requêtes simples.

Ah bin voilà ! Avec ce petit mot d'explication en plus, tout se met en place et devient logique !

Un grand merci !

[aieeeuuuuu]

Envoyé par Kropernic

Mais si deux utilisateurs arrivent à me faire ça, je veux bien manger mon chapeau ! C'est quand même précis à la millisecondes prêt... Sinon je veux bien mettre en DATETIME2

Contrairement à ce qu'on pourrait penser, le type DATETIME n'est pas précis à la milliseconde :

Code :

1
2
3
4
5
6
7
8
9
 
 
SELECT 
	CASE 
		WHEN CAST('2012-01-01T00:00:00.005' AS DATETIME)= CAST('2012-01-01T00:00:00.007' AS DATETIME)
		THEN 1
		ELSE 0
	END
-- affiche 1

Mais je pense en effet que votre chapeau a encore de beaux jours devant lui...

Cependant, pour vous assurer un résultat déterministe, vous pouvez ajouter l'identifiant en deuxième critère de tri.

[Kropernic]

Envoyé par aieeeuuuuu

Contrairement à ce qu'on pourrait penser, le type DATETIME n'est pas précis à la milliseconde :

Code :

Mais je pense en effet que votre chapeau a encore de beaux jours devant lui...

Oui oui, je sais mais bon, j'ai arrondi ^^.

Mais bon, pour le coup, DATETIME2 me tente assez bien. Je n'ai jamais travaillé avec ce type de donnée. Il y a des choses particulières auxquelles il faut faire attention ou bien c'est juste un DATETIME plus précis (et avec une plus grande range d'après ce que j'ai lu) ?

Envoyé par aieeeuuuuu

Cependant, pour vous assurer un résultat déterministe, vous pouvez ajouter l'identifiant en deuxième critère de tri.

Pourquoi pas...

[aieeeuuuuu]

datetime2 peut être plus précis et respecte les normes , ce qui n'est pas le cas de DATETIME.

La MSDN indique d'utiliser DATETIME2 dans les nouveaux développement, donc a moins que vous n'ayez besoin d’être compatible avec des anciennes versions de SQL Server, mieux vaut utiliser DATETIME2