DISTINCTES avec PARTITION PAR contre GROUPBY

J'ai trouvé quelques requêtes SQL dans une application que je suis en examinant comme ceci:

SELECT DISTINCT
Company, Warehouse, Item,
SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stock

Je suis tout à fait sûr que cela donne le même résultat que:

SELECT
Company, Warehouse, Item,
SUM(quantity) AS stock
GROUP BY Company, Warehouse, Item

Est-il un avantage (la performance, la lisibilité, une plus grande souplesse dans l'écriture de la requête, la facilité de maintenance, etc.) de l'aide de la première approche sur le tard?

Comme je l'avais mentalement à analyser la première requête pour un certain temps, il n'a pas de score bien avec la "compréhensibilité"...
Dans ce cas, le PARTITION BY semble juste mal utilisée
Je pense que cette question peut aider à clarifier la différence
J'ai vu cette question, mais il ne pas me donner tout les renseignements que je cherchais. Je voudrais savoir si il pourrait y certaines non évident (pour moi au moins) avantages pour l'utilisation de la première requête. Le deuxième aspect plus "naturel".
Ce qui en gros, je voulais dire, c'est à partir de ce que j'ai lu et je comprends, c'est que l'utilisation de GROUP BY et PARTITION BY ne sont pas vraiment interchangeables. Ils font tous les deux des choses différentes. Je me méfie de simple échange des requêtes les plus fréquentes, même quand ils semblent donner les mêmes résultats.

OriginalL'auteur Andris | 2013-12-04

  1. 11

    Performance:

    Gagnant: GROUP BY

    Certains très rudimentaire des essais sur une grande table avec des indexée colonnes ont montré que, au moins dans mon cas, les deux requêtes générées complètement différent de ce plan de requête. L'un pour PARTITION BY était significativement plus lente.

    La GROUP BY plan de requête comprend uniquement une analyse de la table et de l'opération d'agrégation, tandis que le PARTITION BY plan a deux boucles imbriquées auto-jointures. Le PARTITION BY a pris environ 2800ms sur la deuxième manche, le GROUP BY a fallu que 500ms.

    Lisibilité /Maintenabilité:

    Gagnant: GROUP BY

    Basée sur les opinions des intervenants ici la PARTITION BY est moins lisible pour la plupart des développeurs de sorte qu'il sera probablement aussi plus difficile à maintenir dans le futur.

    Flexibilité

    Gagnant: PARTITION BY

    PARTITION BY vous donne plus de flexibilité dans le choix des colonnes de regroupement. Avec GROUP BY vous pouvez avoir un seul ensemble de colonnes de regroupement pour toutes les colonnes agrégées. Avec DISTINCT + PARTITION BY vous pouvez avoir de colonne différent dans chaque partition. Également sur certains Sgbd, vous pouvez choisir parmi plus d'agrégation/fonctions analytiques dans le OVER clause.

    OriginalL'auteur Andris

  2. 2

    À l'aide de sum() comme une fonction analytique avec over partition by n'est pas nécessaire. Je ne pense pas qu'il y a une grande différence entre eux dans tous les sens. Dans oracle il y a beaucoup plus analytique de la fonction de la fonction d'agrégation. Je pense que ms-sql est le même cas. Et par exemple lag(), lead(), rank(), dense rank(), etc sont beaucoup plus difficiles à mettre en œuvre avec seulement group by.
    Bien sûr, cet argument n'est pas vraiment pour la défense de la première version...

    Peut-être y avait déjà plus de champs calculés dans le jeu de résultats qui ne sont pas réalisables avec group by.

    OriginalL'auteur Lajos Veres

  3. 0

    Bien que les deux requêtes semblent pour calculer la même chose quand vous regardez les colonnes, ils sont en fait la production de jeu complètement différent de lignes.

    Le premier à utiliser la fonction d'analyse seront de sortie exactement une ligne pour chaque ligne d'entrée. C'est pour CHAQUE stock d'informations, il sera de retour une ligne avec la quantité totale pour les associés de la société/l'entrepôt/élément. (par la façon dont le calcul de la moyenne aurait plus de sens pour moi, mais qui sait...)

    La seconde, qu'il pourra retourner une seule ligne pour chaque société/entrepôt/élément de combinaison.

    Donc, oui, dans cet exemple, la première requête semble un peu inutile... à moins que vous voulez calculer certains niveau de stock de statistique comme le stock actuel ratio de la quantité globale par la société/magasin/point (juste un exemple, je ne sais pas si elle a un sens économique!)

    Fonction analytique, ils sont très puissants mécanisme en SQL, en un certain sens, bien plus puissants que d'un groupe. Mais à utiliser avec précautions... Une règle simple pourrait être: si vous pouvez calculer à l'aide d'un groupe, ainsi, ne pas utiliser une fonction analytique 😉

    Dans la première requête, il y a un DISTINCT après SELECT de sorte qu'il renvoie une seule ligne pour chaque société/entrepôt/item comme le second.
    Ok, assez juste... encore le DISTINCT est appliquée à chaque ligne, et de la nécessité de prendre en compte chaque valeur: société/entrepôt/élément et de la somme(quantité). Si vous regardez l'exec plan (ok, elle dépend de votre base de données) la nette coût ajoute sur l'analyse de la requête, ce qui est déjà deux fois plus coûteux qu'un simple groupe.

    OriginalL'auteur SergeFantino