Comment utiliser une fenêtre SQL pour créer un jeu de résultats

Les fenêtres et les fonctions de la fenêtre ont été introduits dans SQL: 1999. Avec une fenêtre, un utilisateur peut éventuellement partitionner un ensemble de données, éventuellement commander les lignes dans chaque partition, et spécifiez une collection de lignes (le châssis de la fenêtre) qui est associé à une ligne donnée.

Le cadre d'une rangée R de la fenêtre est un sous-ensemble de la partition contenant R. Par exemple, le cadre de fenêtre peut être constitué de toutes les lignes depuis le début de la partition jusqu'à et y compris R, basée sur la façon dont les lignes sont ordonnées dans la partition .

Une fonction de fenêtre calcule une valeur pour une ligne R, sur la base des lignes dans le cadre de R. fenêtre

Par exemple, supposons que vous avez une table de vente qui a colonnes de CustID, InvoiceNo et TotalSale. Votre directeur des ventes peut vouloir savoir ce que les ventes totales étaient à chaque client sur une plage spécifiée de numéros de facture. Vous pouvez obtenir ce qu'elle veut avec le code SQL suivant:

SELECT CustID, InvoiceNo, SUM (TotalSale) OVER (PARTITION BY CustIDORDER PAR InvoiceNoROWS BETWEENUNBOUNDED PRECEDINGAND CURRENT ROW) DE SALES-

La PLUS DE clause détermine comment les lignes de la requête sont cloisonnés avant d'être traités, dans ce cas par le SOMME fonction. Une partition est affecté à chaque client. Au sein de chaque partition sera une liste de numéros de facture, et associé à chacun d'entre eux sera la somme de tous les TotalSale les valeurs supérieures à la gamme de lignes spécifié, pour chaque client.

SQL: 2011 a ajouté plusieurs améliorations majeures à la fonctionnalité de la fenêtre d'origine, incorporant de nouveaux mots clés.

Comment partitionner une fenêtre dans des seaux avec NTILE

La NTILE fonction de fenêtre répartit une partition de la fenêtre commandé dans un nombre entier positif n des seaux, la numérotation des seaux de 1 à n. Si le nombre de lignes dans une partition m est pas divisible par n, puis, après la fonction de NTILE remplit les godets de façon égale, le reste de m / n, appelé r, est attribuée à la première r seaux, les rendant ainsi plus grande que les autres godets.

Supposons que vous souhaitez classer vos employés en salaire, en répartissant en cinq seaux, du plus haut au plus bas. Vous pouvez le faire avec le code suivant:

SELECT Prénom, Nom, NTILE (5) OVER (ORDER BY Salaire DESC) AS BUCKETFROM-employé

Si il y a, par exemple, 11 employés, chaque godet est rempli de deux sauf pour le premier godet, qui est rempli de trois. Le premier seau contiendra les trois personnes les mieux rémunérées, et le cinquième seau contiendra les deux employés les moins bien payés.

Comment fonctions de la fenêtre de nidification

Parfois, pour obtenir le résultat dont vous avez besoin, le plus simple est d'imbriquer une fonction au sein d'une autre. SQL: 2011 a ajouté la capacité de faire une telle imbrication des fonctions de la fenêtre.

A titre d'exemple, considérons un cas où un investisseur en bourse tente de déterminer si elle est un bon moment pour acheter un stock particulier. Pour obtenir une poignée sur ce, elle décide de comparer le prix actuel du stock au prix qu'il a vendu pour 100 sur les métiers immédiatement précédentes. Elle se demande, combien de fois dans les 100 métiers précédents elle a vendu pour moins que le prix actuel.

Pour atteindre une réponse, elle fait la requête suivante:

SaleTime SELECT, SUM (CASE WHEN SalePrice 
La fenêtre englobe les 100 lignes précédant la ligne actuelle, qui correspondent à des ventes de 100 immédiatement avant le moment actuel. Chaque fois qu'une ligne est évaluée, où la valeur de Prix ​​de vente est inférieur au prix le plus récent, 1 est ajouté à la somme.
Le résultat final est un nombre qui vous indique le nombre de ventes de la précédente cents qui ont été faites à un prix inférieur au prix actuel.
Comment évaluer des groupes de lignes
Parfois, la clé de tri que vous avez choisi de placer une partition afin auront doublons. Vous voudrez peut-être d'évaluer toutes les lignes qui ont la même clé de tri en tant que groupe. Dans de tels cas, vous pouvez utiliser le GROUPE option. Avec elle, vous pouvez compter groupes de lignes où les clés de tri sont identiques.
Voici un exemple:

SELECT CustomerID, SaleDate, SUM (InvoiceTotal) OVER (PARTITION BY CustomerIDORDER PAR SaleDateGROUPS ENTRE 2 ET 2 SUIVANT PRECEDENT) DE Clients-

Le cadre de la fenêtre dans cet exemple se compose d'un maximum de cinq groupes de lignes: deux groupes avant le groupe contenant la ligne en cours, le groupe contenant la ligne actuelle, et deux groupes suivants le groupe contenant la ligne actuelle. Les lignes de chaque groupe ont la même Date de vente, et le Date de vente associé à chaque groupe est différent du Date de vente des valeurs pour les autres groupes.