Partitionnement

Comme pour les fonctions d’agrégation, qui peuvent travailler seules ou par rapport à un groupe, les fonctions de Window peuvent également travailler pour des groupes, qui sont appelés "partitions" ou partitions.

Syntaxe

<window function>(...) OVER (PARTITION BY <expr> [, <expr> ...])

Pour chaque ligne, la fonction window évalue uniquement les lignes qui appartiennent à la même section que la ligne actuelle.

L’agrégation sur un groupe peut produire plus d’une ligne, donc les résultats de la requête principale sont ajoutés à l’ensemble résultant créé par la section, en utilisant la même liste d’expressions que pour la section.

En continuant avec l’exemple de l’employé, au lieu de compter le pourcentage du salaire de chaque employé par rapport au salaire total de l’employé, comptons le pourcentage du salaire total de l’employé du même département :Pour chaque ligne, la fonction de fenêtrage évalue uniquement les lignes qui appartiennent à la même section que la ligne actuelle.

Example 1. Partitionnement avec OVER

select
  id,
  department,
  salary,
  salary / sum(salary) OVER (PARTITION BY department) percentage
from employee
order by id;

Résultat

id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.3448
2  SALES       12.00     0.6000
3  SALES        8.00     0.4000
4  R & D        9.00     0.3103
5  R & D       10.00     0.3448

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