Windows

Par exemple, si le cadre de la Window est donné par l’expression RANGE 5 PRECEDING, une Window mobile sera générée qui inclut les lignes précédentes du groupe dont la valeur est inférieure à celle de la ligne actuelle de pas plus de 5.

Example 1. Utilisation des plages Windows

SELECT
    id,
    salary,
    SUM(salary) OVER() AS s1,
    SUM(salary) OVER(ORDER BY salary) AS s2,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
    SUM(salary) OVER(ORDER BY salary
                     RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
    SUM(salary) OVER(ORDER BY salary RANGE 1 PRECEDING) AS s8
FROM
    employee

ID  SALARY     S1      S2      S3      S4      S5      S6      S7      S8
-------------------------------------------------------------------------
3     8.00  49.00    8.00    8.00   49.00   49.00   17.00   17.00    8.00
4     9.00  49.00   17.00   17.00   41.00   49.00   29.00   37.00   17.00
1    10.00  49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
5    10.00  49.00   37.00   37.00   32.00   49.00   20.00   29.00   29.00
2    12.00  49.00   49.00   49.00   12.00   49.00   12.00   12.00   12.00

Vous pouvez utiliser les fonctions FIRST_VALUE et LAST_VALUE pour voir quelles valeurs seront incluses dans la plage. Cela aide à voir les plages de Windows et à vérifier si les paramètres sont définis correctement.

Windows en ligne

Un terme de Window est spécifié en unités physiques, les chaînes de caractères. Par exemple, si le cadre de la Window est spécifié par l’expression ROWS 5 PRECEDING, la Window comprendra jusqu’à 6 chaînes de caractères : la chaîne actuelle et les cinq précédentes (l’ordre est déterminé par la construction ORDER BY).

Example 1. Utilisation des Windows de portée

SELECT
    id,
    salary,
    SUM(salary) OVER() AS s1,
    SUM(salary) OVER(ORDER BY salary) AS s2,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS s3,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS s4,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS s5,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s6,
    SUM(salary) OVER(ORDER BY salary
                     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s7,
    SUM(salary) OVER(ORDER BY salary ROWS 1 PRECEDING) AS s8
FROM
    employee

ID SALARY      S1      S2      S3      S4      S5      S6      S7      S8
-------------------------------------------------------------------------
3    8.00   49.00    8.00    8.00   49.00   49.00   17.00   17.00    8.00
4    9.00   49.00   17.00   17.00   41.00   49.00   19.00   27.00   17.00
1   10.00   49.00   37.00   27.00   32.00   49.00   20.00   29.00   19.00
5   10.00   49.00   37.00   37.00   22.00   49.00   22.00   32.00   20.00
2   12.00   49.00   49.00   49.00   12.00   49.00   12.00   22.00   22.00