Triage

La clause ORDER BY peut être utilisée avec ou sans sectionnement. La clause ORDER BY dans OVER spécifie l’ordre dans lequel la fonction Window traitera les lignes. Cet ordre ne doit pas nécessairement être le même que l’ordre de sortie des lignes.

Pour les fonctions d’agrégation standard, la clause ORDER BY à l’intérieur de la clause OVER entraîne le renvoi de résultats d’agrégation partiels au fur et à mesure du traitement des enregistrements.

Example 1. Trier avec OVER

SELECT
  id,
  salary,
  SUM(salary) OVER (ORDER BY salary) AS cumul_salary
FROM employee
ORDER BY salary;

Résultat

id salary cumul_salary
-- ------ ------------
3    8.00         8.00
4    9.00        17.00
1   10.00        37.00
5   10.00        37.00
2   12.00        49.00

Dans ce cas, cumul_salary renvoie une agrégation partielle/accumulative (fonction SUM). Il peut sembler étrange que la valeur 37,00 soit répétée pour les identifiants 1 et 5, mais c’est normal.Le tri (ORDER BY) des clés les regroupe, et l’agrégat est calculé une fois (mais en additionnant les deux valeurs 10.00 en une fois). Pour éviter cela, vous pouvez ajouter un champ ID à la fin de la clause ORDER BY.

Ceci est dû au fait qu’il n’y a pas de cadre de window, qui par défaut, avec ORDER BY, est constitué de toutes les lignes depuis le début de la section jusqu’à la ligne actuelle et des lignes égales à la ligne actuelle dans la clause ORDER BY (c’est-à-direL’INTERVALLE ENTRE LA LIGNE PRÉCÉDENTE NON BORNÉE ET LA LIGNE ACTUELLE). Sans ORDER BY, le cadre par défaut est constitué de toutes les lignes de la section.Plus de détails sur (cadres de window) seront expliqués plus tard.

Vous pouvez utiliser plusieurs Windows avec des tris différents, et compléter l’offre ORDER BY avec les options ASC / DESC et NULLS {FIRST | LAST}.

Avec les sections, la proposition ORDER BY fonctionne de la même manière, mais à la limite de chaque section, les agrégats sont éliminés.

Toutes les fonctions d’agrégation peuvent utiliser la proposition ORDER BY sauf LIST().

L’exemple suivant montre le montant du prêt, le montant des remboursements cumulés et le solde des remboursements.

Example 2. Utilisation de OVER(ORDER BY …) pour les totaux cumulatifs

SELECT
  payments.id AS id,
  payments.bydate AS bydate,
  credit.amount AS credit_amount,
  payments.amount AS pay,
  SUM(payments.amount) OVER(ORDER BY payments.bydate) AS s_amount,
  SUM(payments.amount) OVER(ORDER BY payments.bydate,
                                     payments.id) AS s_amount2,
  credit.amount - SUM(payments.amount) OVER(ORDER BY payments.bydate,
                                                     payments.id) AS balance
FROM credit
JOIN payments ON payments.credit_id = credit.id
WHERE credit.id = 1
ORDER BY payments.bydate

Résultat

ID BYDATE     CREDIT_AMOUNT PAY    S_AMOUNT S_AMOUNT2 BALANCE
-- ---------- ------------- ------ -------- --------- ----------
1  15.01.2015 1000000       100000  100000  100000    900000
2  15.02.2015 1000000       150000  250000  250000    750000
3  15.03.2015 1000000       130000  400000  380000    620000
4  15.03.2015 1000000        20000  400000  400000    600000
5  15.04.2015 1000000       200000  600000  600000    400000
6  15.05.2015 1000000       150000  750000  750000    250000
7  15.06.2015 1000000       150000 1000000  900000    100000
8  15.06.2015 1000000       100000 1000000 1000000         0

Cadre de window

L’ensemble des lignes d’une section sur lesquelles la fonction Window opère est appelé le cadre de Window (window frames). Le cadre de Window détermine quelles lignes doivent être prises en compte pour la ligne courante lors de l’évaluation d’une fonction Window.

Un cadre de Window se compose de trois parties : une unité (unit), une limite de début et une limite de fin. Les mots-clés RANGE ou ROWS peuvent être utilisés comme unité et ceux-ci définissent comment les limites de la Window vont fonctionner. Les limites de la Window sont définies par les expressions suivantes

<expr> PRECEDING
<expr> FOLLOWING
CURRENT ROW

Les clauses ROWS et RANGE nécessitent que la clause ORDER BY soit spécifiée.Si la clause ORDER BY n’est pas présente, alors pour les fonctions d’agrégation, le cadre de la Window est constitué de toutes les lignes de la partition.Si la clause ORDER BY est spécifiée, le cadre de la Window se compose par défaut de toutes les lignes depuis le début de la partition jusqu’à la ligne actuelle, plus toutes les lignes suivantes qui sont égales à la ligne actuelle selon la clause ORDER BY,c’est-à-dire `Etendue entre la rangée précédente non bornée et la rangée actuelle'.

La clause ROWS limite les lignes dans une section en spécifiant un nombre fixe de lignes précédant ou suivant la ligne courante.Alternativement, la clause RANGE restreint logiquement les lignes d’une section en spécifiant une plage de valeurs relative à la valeur de la ligne courante.Les lignes précédentes et suivantes sont déterminées en fonction de l’ordre donné dans la clause ORDER BY.

Si le cadre de la Window est défini avec une clause RANGE, la clause ORDER BY ne peut contenir qu’une seule expression et l’expression doit être de type numérique, DATE, TIME ou TIMESTAMP.Pour <expr> PRECEDING, l’expression expr est soustraite de l’expression dans ORDER BY et pour <expr> FOLLOWING, elle est ajoutée.Pour CURRENT ROW, l’expression dans ORDER BY est utilisée telle quelle.

Ensuite, toutes les lignes (dans une section) situées entre les limites sont considérées comme faisant partie du cadre de la Window résultante.
Si le cadre de la Window est défini avec une clause ROWS, il n’y a aucune restriction sur le nombre et les types d’expressions placées sur la clause ORDER BY.Dans ce cas, la clause <expr> PRECEDING indique le nombre de lignes précédant la ligne courante, respectivement la clause <expr> FOLLOWING indique le nombre de lignes suivant la ligne courante.

Le UNBOUNDED PRECEDING et le UNBOUNDED FOLLOWING fonctionnent de la même manière pour les clauses ROWS et RANGE.La mention UNBOUNDED PRECEDING indique que la Window commence à la première ligne de la section. Le UNBOUNDED PRECEDING ne peut être spécifié que comme point de départ de la Window.La mention UNBOUNDED FOLLOWING indique que la Window se termine par la dernière ligne de la section. La clause UNBOUNDED FOLLOWING ne peut être spécifiée que comme point final de la Window.

L’expression CURRENT ROW indique que la Window commence ou se termine sur la ligne courante lorsqu’elle est utilisée conjointement avec la clause ROWS,ou que la Window se termine à la valeur courante lorsqu’elle est utilisée avec la clause RANGE.Le CURRENT ROW peut être spécifié à la fois comme un point de départ et un point d’arrivée.

La clause BETWEEN est utilisée en conjonction avec le mot clé ROWS ou RANGE pour spécifier le point de limite inférieur (début) ou supérieur (fin) de la Window.La limite supérieure ne peut être plus petite que la limite inférieure.

Note	Si seul le point de départ de la Window est spécifié, le point d’arrivée de la Window est considéré comme la `LIGNE ACTUELLE`. Par exemple, si `ROWS 1 PRECEDING` est spécifié, cela revient à spécifier `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW`.

Certaines fonctions de la Window ignorent l’expression du cadre :

ROW_NUMBER, LAG et LEAD fonctionnent toujours comme ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
DENSE_RANK, RANK, PERCENT_RANK et CUME_DIST travailler comme RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
FIRST_VALUE, LAST_VALUE et NTH_VALUE travailler sur le cadre, mais RANGE fonctionne de manière identique ROWS.

Ainsi, les options ROWS et RANGE offrent une certaine souplesse dans le réglage de la taille de la Window flottante. Les options suivantes sont les plus courantes :

La borne inférieure est fixe (elle coïncide avec la première ligne du groupe ordonné) et la borne supérieure est rampante (elle coïncide avec la ligne actuelle du groupe ordonné). Dans ce cas, nous obtenons un total cumulé (agrégat cumulé). Dans ce cas, la taille de la Window change (elle s’agrandit d’un côté) et la Window elle-même se déplace en raison de l’agrandissement. La situation inverse est également possible, lorsque la frontière inférieure est rampante et que la frontière supérieure est fixe. Dans ce cas, la Window s’effilera.
Si les limites supérieure et inférieure sont fixes par rapport à la ligne courante, par exemple, 1 ligne avant la ligne courante et 2 après la ligne courante, nous avons un agrégat glissant. Dans ce cas, la taille de la Window est fixe et la Window elle-même coulisse.