Clause OVER (). Quand et pourquoi est-ce utile?

Question

     USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
 

J'ai lu sur cet article et je ne comprends pas pourquoi j'en ai besoin. Que fait la fonction Over ? Que font Partitioning By ? Pourquoi ne puis-je pas faire une requête en écrivant Group By SalesOrderID ?

Answer 1

Vous pouvez utiliser GROUP BY SalesOrderID. La différence est, avec le GROUPE, vous pouvez seulement de l'ensemble des valeurs pour les colonnes qui ne sont pas inclus dans le GROUPE.

En revanche, en utilisant la fenêtre des fonctions d'agrégation à la place du GROUPE, vous pouvez récupérer les deux données agrégées et non des valeurs agrégées. Bien que vous ne faites pas que dans votre exemple de requête, vous pouvez les récupérer à la fois individuels OrderQty valeurs et leurs sommes, des comptes, des moyennes, etc. sur les groupes de même SalesOrderIDs.

Voici un exemple pratique de pourquoi fenêtré agrégats sont grands. Supposons que vous avez besoin de calculer le pourcentage d'un total de chaque valeur. Sans fenêtré agrégats vous devez d'abord générer une liste de valeurs agrégées et ensuite la rejoindre en arrière à l'original d'un ensemble de lignes, c'est à dire comme ceci:

SELECT
  orig.[Partition],
  orig.Value,
  orig.Value * 100.0 / agg.TotalValue AS ValuePercent
FROM OriginalRowset orig
  INNER JOIN (
    SELECT
      [Partition],
      SUM(Value) AS TotalValue
    FROM OriginalRowset
    GROUP BY [Partition]
  ) agg ON orig.[Partition] = agg.[Partition]

Maintenant, regardez comment vous pouvez faire de même avec une fenêtre d'agrégation:

SELECT
  [Partition],
  Value,
  Value * 100.0 / SUM(Value) OVER (PARTITION BY [Partition]) AS ValuePercent
FROM OriginalRowset orig

Beaucoup plus facile et plus propre, n'est-ce pas?

Answer 2

La clause OVER est puissante en ce sens que vous pouvez avoir des agrégats sur différentes plages ("fenêtrage"), que vous utilisiez GROUP BY ou non

Exemple: obtenez le nombre par SalesOrderID et le nombre de tous

 SELECT
    SalesOrderID, ProductID, OrderQty
    ,COUNT(OrderQty) AS 'Count'
    ,COUNT(*) OVER () AS 'CountAll'
FROM Sales.SalesOrderDetail 
WHERE
     SalesOrderID IN(43659,43664)
GROUP BY
     SalesOrderID, ProductID, OrderQty
 

Obtenez différents COUNT s, pas GROUP BY

 SELECT
    SalesOrderID, ProductID, OrderQty
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'CountQtyPerOrder'
    ,COUNT(OrderQty) OVER(PARTITION BY ProductID) AS 'CountQtyPerProduct',
    ,COUNT(*) OVER () AS 'CountAllAgain'
FROM Sales.SalesOrderDetail 
WHERE
     SalesOrderID IN(43659,43664)
 

Answer 3

Permet de m'expliquer avec un Exemple et vous serez en mesure de voir comment il fonctionne.

Supposons que Vous ayez Tableau Suivant DIM_EQUIPMENT

VIN         MAKE    MODEL   YEAR    COLOR

---

1234ASDF    Ford    Taurus  2008    White
1234JKLM    Chevy   Truck   2005    Green
5678ASDF    Ford    Mustang 2008    Yellow

Exécutez SQL ci-dessous

SELECT VIN,
  MAKE,
  MODEL,
  YEAR,
  COLOR ,
  COUNT(*) OVER (PARTITION BY YEAR) AS COUNT2
FROM DIM_EQUIPMENT

Le résultat serait comme ci-dessous

VIN         MAKE    MODEL   YEAR    COLOR     COUNT2
 ----------------------------------------------  
1234JKLM    Chevy   Truck   2005    Green     1
5678ASDF    Ford    Mustang 2008    Yellow    2
1234ASDF    Ford    Taurus  2008    White     2

Voir ce qui s'est passé.

Vous êtes en mesure de compter sans Group By sur l'ANNÉE et de la Correspondance avec la LIGNE.

Une autre FAÇON Intéressante d'obtenir le même résultat si comme ci-dessous à l'aide de la Clause, AVEC des œuvres aussi dans la ligne de VUE, et vous pouvez simplifier la requête particulièrement complexe, ce qui n'est pas le cas ici, mais depuis que je suis juste essayer de montrer l'utilisation de la

 WITH EQ AS
  ( SELECT YEAR AS YEAR2, COUNT(*) AS COUNT2 FROM DIM_EQUIPMENT GROUP BY YEAR
  )
SELECT VIN,
  MAKE,
  MODEL,
  YEAR,
  COLOR,
  COUNT2
FROM DIM_EQUIPMENT,
  EQ
WHERE EQ.YEAR2=DIM_EQUIPMENT.YEAR;

Answer 4

Si vous vouliez uniquement GROUPER PAR le SalesOrderID, vous ne pourriez pas inclure les colonnes ProductID et OrderQty dans la clause SELECT.

La clause PARTITION BY vous permet de diviser vos fonctions d'agrégat. Un exemple évident et utile serait de générer des numéros de ligne pour les lignes de commande d'une commande:

 SELECT
    O.order_id,
    O.order_date,
    ROW_NUMBER() OVER(PARTITION BY O.order_id) AS line_item_no,
    OL.product_id
FROM
    Orders O
INNER JOIN Order_Lines OL ON OL.order_id = O.order_id
 

(Ma syntaxe est peut-être légèrement différente)

Vous obtiendrez alors quelque chose comme:

 order_id    order_date    line_item_no    product_id
--------    ----------    ------------    ----------
    1       2011-05-02         1              5
    1       2011-05-02         2              4
    1       2011-05-02         3              7
    2       2011-05-12         1              8
    2       2011-05-12         2              1
 

Answer 5

La clause OVER lorsqu'il est combiné avec de la PARTITION PAR l'état que le précédent appel de fonction doit être fait de façon analytique en évaluant les lignes renvoyées de la requête. Pensez-y comme un inline instruction GROUP BY.

OVER (PARTITION BY SalesOrderID) est en indiquant que pour SUM, AVG, etc... de la fonction, de retour de la valeur SUR un sous-ensemble d'enregistrements renvoyés par la requête, et que la PARTITION sous-ensemble PAR la clé étrangère SalesOrderID.

Donc nous SOMME tous OrderQty enregistrement pour CHAQUE UNIQUE SalesOrderID, et que le nom de la colonne "Total".

Il est BEAUCOUP plus efficace que l'utilisation de plusieurs inline vue de trouver les mêmes informations. Vous pouvez mettre cette requête à l'intérieur d'une vue intégrée et filtre au Total.

SELECT ...,
FROM (your query) inlineview
WHERE Total < 200