Tous les produits
Search

Ce produit

    AnalyticDB:Fonctions de fenêtrage

    Centre de documentation

    AnalyticDB:Fonctions de fenêtrage

    Dernière mise à jour :Aug 10, 2026

    Les fonctions de fenêtrage calculent des valeurs sur un ensemble de lignes liées à la ligne actuelle, sans regrouper ces lignes en une seule ligne de sortie. Utilisez-les pour établir des classements par groupe, calculer des moyennes mobiles, des sommes cumulatives et comparer les lignes entre elles. Les fonctions de fenêtrage s'exécutent après la clause HAVING et avant la clause ORDER BY. Une fonction de fenêtrage est déclenchée par l'utilisation d'une clause OVER qui définit la fenêtre.

    AnalyticDB for MySQL prend en charge trois catégories de fonctions de fenêtrage : les fonctions d'agrégation, les fonctions de tri et les fonctions de valeur. Pour les fonctions d'agrégation utilisées comme fonctions de fenêtrage, consultez la section Fonctions d'agrégation.

    Fonctions prises en charge

    Fonction Catégorie Type de retour Description
    Toutes les fonctions d'agrégation Agrégation Variable Les fonctions d'agrégation agissent comme des fonctions de fenêtrage lorsqu'elles sont combinées avec une clause OVER
    CUME_DIST() Tri DOUBLE Distribution cumulative de chaque valeur au sein de la partition
    RANK() Tri BIGINT Classement avec des écarts pour les valeurs ex æquo
    DENSE_RANK() Tri BIGINT Classement sans écarts pour les valeurs ex æquo
    NTILE(n) Tri BIGINT Répartit les lignes dans n compartiments
    ROW_NUMBER() Tri BIGINT Numéro de ligne séquentiel au sein de la partition, commençant à 1
    PERCENT_RANK() Tri DOUBLE Classement sous forme de pourcentage : (r - 1) / (n - 1)
    FIRST_VALUE(x) Valeur Identique à l'entrée Valeur de la première ligne dans la partition de la fenêtre
    LAST_VALUE(x) Valeur Identique à l'entrée Valeur de la dernière ligne dans le cadre de la fenêtre
    LAG(x[, offset[, default]]) Valeur Identique à l'entrée Valeur issue de offset lignes précédant la ligne actuelle
    LEAD(x[, offset[, default]]) Valeur Identique à l'entrée Valeur issue de offset lignes suivant la ligne actuelle
    NTH_VALUE(x, offset) Valeur Identique à l'entrée Valeur de la ligne à la position offset au sein du cadre de la fenêtre

    Syntaxe

    function_name OVER ([PARTITION BY expr] ORDER BY expr [RANGE|ROWS BETWEEN start AND end])

    Un appel de fonction de fenêtrage comporte trois parties :

    • Règle de partitionnement (facultative) : Divise les lignes d'entrée en partitions indépendantes, de manière similaire à GROUP BY. Chaque partition est traitée séparément.

    • Règle de tri : Détermine l'ordre dans lequel les lignes sont traitées au sein de chaque partition.

    • Cadre de la fenêtre : Définit le sous-ensemble de lignes au sein de la partition sur lequel la fonction opère. Le cadre est ancré à la ligne actuelle.

    Modes de cadre de fenêtre

    Le cadre de la fenêtre peut être défini selon deux modes :

    Mode Définit Exemple d'utilisation
    ROWS Un nombre fixe de lignes par rapport à la ligne actuelle Totaux courants, moyennes mobiles
    RANGE Une plage de valeurs par rapport à la valeur de la ligne actuelle Fenêtres glissantes basées sur les valeurs

    Utilisez BETWEEN start AND end pour définir les limites du cadre :

    Limite Signification
    CURRENT ROW La ligne actuelle
    N PRECEDING N lignes précédant la ligne actuelle
    UNBOUNDED PRECEDING De la première ligne de la partition jusqu'à la ligne actuelle
    N FOLLOWING N lignes suivant la ligne actuelle
    UNBOUNDED FOLLOWING De la ligne actuelle jusqu'à la dernière ligne de la partition

    Le diagramme suivant illustre la relation entre les limites et la ligne actuelle au sein d'une partition :

          PARTITION
+─────────────────+  <- UNBOUNDED PRECEDING (start of partition)
|                 |
|=================|  <- N PRECEDING  -+
|   rows before   |                   |
|   current row   |                   |  FRAME
|~~~~~~~~~~~~~~~~~|  <- CURRENT ROW   |
|   rows after    |                   |
|   current row   |                   |
|=================|  <- N FOLLOWING  -+
|                 |
+─────────────────+  <- UNBOUNDED FOLLOWING (end of partition)

    Comportement par défaut du cadre de fenêtre

    • LAST_VALUE : Le cadre par défaut se termine à CURRENT ROW. Par conséquent, LAST_VALUE renvoie par défaut la valeur de la ligne actuelle, et non celle de la dernière ligne de la partition. Pour obtenir la véritable dernière valeur, spécifiez explicitement ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

    Spécifiez toujours explicitement le cadre de la fenêtre afin d'éviter des résultats inattendus.

    Configurer la table d'exemple

    Les exemples de cette rubrique utilisent une table testwindow. Exécutez les instructions suivantes pour la créer et la remplir :

    CREATE TABLE testwindow (
  year    INT,
  country VARCHAR(20),
  product VARCHAR(20),
  profit  INT
) DISTRIBUTED BY HASH(year);

INSERT INTO testwindow VALUES (2000, 'Finland', 'Computer',   1500);
INSERT INTO testwindow VALUES (2001, 'Finland', 'Phone',        10);
INSERT INTO testwindow VALUES (2000, 'Germany', 'Calculator',   75);
INSERT INTO testwindow VALUES (2000, 'Germany', 'Calculator',   75);
INSERT INTO testwindow VALUES (2001, 'Germany', 'Calculator',   79);
INSERT INTO testwindow VALUES (2001, 'USA',     'Calculator',   50);
INSERT INTO testwindow VALUES (2001, 'USA',     'Computer',   1500);

    Vérifiez les données :

    SELECT * FROM testwindow;
    +------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2000 | Germany | Calculator |     75 |
| 2000 | Germany | Calculator |     75 |
| 2001 | Germany | Calculator |     79 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
+------+---------+------------+--------+

    Notes d'utilisation

    Contraintes générales du cadre

    Les combinaisons BETWEEN start AND end suivantes ne sont pas valides :

    Combinaison invalide Message d'erreur
    start = UNBOUNDED FOLLOWING Window frame start cannot be UNBOUNDED FOLLOWING
    end = UNBOUNDED PRECEDING Window frame end cannot be UNBOUNDED PRECEDING
    start = CURRENT ROW, end = N PRECEDING Window frame starting from CURRENT ROW cannot end with PRECEDING
    start = N FOLLOWING, end = N PRECEDING Window frame starting from FOLLOWING cannot end with PRECEDING
    start = N FOLLOWING, end = CURRENT ROW Window frame starting from FOLLOWING cannot end with CURRENT ROW

    Contraintes du mode RANGE

    En mode RANGE, seules les limites UNBOUNDED sont prises en charge :

    Combinaison invalide Message d'erreur
    start ou end = N PRECEDING Window frame RANGE PRECEDING is only supported with UNBOUNDED
    start ou end = N FOLLOWING Window frame RANGE FOLLOWING is only supported with UNBOUNDED

    Fonctions d'agrégation

    Toutes les fonctions d'agrégation deviennent des fonctions de fenêtrage lorsque vous ajoutez une clause OVER. La fonction calcule son résultat sur les lignes contenues dans la fenêtre glissante actuelle, plutôt que de regrouper toutes les lignes en une seule.

    L'exemple suivant calcule une somme glissante des prix des commandes par date pour chaque vendeur :

    SELECT
  clerk,
  orderdate,
  orderkey,
  totalprice,
  SUM(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey;

    L'exemple suivant calcule une somme cumulative courante du profit pour chaque pays :

    SELECT
  year,
  country,
  profit,
  SUM(profit) OVER (
    PARTITION BY country
    ORDER BY year
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM testwindow;
    +------+---------+--------+---------------+
| year | country | profit | running_total |
+------+---------+--------+---------------+
| 2001 | USA     |     50 |            50 |
| 2001 | USA     |   1500 |          1550 |
| 2000 | Germany |     75 |            75 |
| 2000 | Germany |     75 |           150 |
| 2001 | Germany |     79 |           229 |
| 2000 | Finland |   1500 |          1500 |
| 2001 | Finland |     10 |          1510 |
+------+---------+--------+---------------+

    Sans clause ORDER BY ni clause de cadre, l'agrégation couvre l'intégralité de la partition :

    SELECT country, SUM(profit) OVER (PARTITION BY country) AS total_profit
FROM testwindow;
    +---------+--------------+
| country | total_profit |
+---------+--------------+
| Germany |          229 |
| Germany |          229 |
| Germany |          229 |
| USA     |         1550 |
| USA     |         1550 |
| Finland |         1510 |
| Finland |         1510 |
+---------+--------------+

    CUME_DIST

    CUME_DIST()

    Renvoie la distribution cumulative de chaque valeur au sein d'une partition : la fraction de lignes dont les valeurs sont inférieures ou égales à la valeur de la ligne actuelle.

    • Type de retour : DOUBLE

    • Les valeurs ex æquo reçoivent la même valeur de distribution.

    Exemple :

    SELECT
  year,
  country,
  product,
  profit,
  CUME_DIST() OVER (PARTITION BY country ORDER BY profit) AS cume_dist
FROM testwindow;
    +------+---------+------------+--------+--------------------+
| year | country | product    | profit | cume_dist          |
+------+---------+------------+--------+--------------------+
| 2001 | USA     | Calculator |     50 |                0.5 |
| 2001 | USA     | Computer   |   1500 |                1.0 |
| 2001 | Finland | Phone      |     10 |                0.5 |
| 2000 | Finland | Computer   |   1500 |                1.0 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2001 | Germany | Calculator |     79 |                1.0 |
+------+---------+------------+--------+--------------------+

    RANK

    RANK()

    Renvoie le rang de chaque ligne au sein de sa partition, triée selon l'expression ORDER BY. Le rang correspond au nombre de lignes précédant la ligne actuelle plus un. Les valeurs ex æquo obtiennent le même rang, et le rang suivant présente un écart correspondant, ce qui crée des trous dans la séquence.

    • Type de retour : BIGINT

    Exemple :

    SELECT
  year,
  country,
  product,
  profit,
  RANK() OVER (PARTITION BY country ORDER BY profit) AS rank
FROM testwindow;
    +------+---------+------------+--------+------+
| year | country | product    | profit | rank |
+------+---------+------------+--------+------+
| 2001 | Finland | Phone      |     10 |    1 |
| 2000 | Finland | Computer   |   1500 |    2 |
| 2001 | USA     | Calculator |     50 |    1 |
| 2001 | USA     | Computer   |   1500 |    2 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2001 | Germany | Calculator |     79 |    3 |
+------+---------+------------+--------+------+

    Dans la partition Allemagne, les deux lignes où profit = 75 obtiennent toutes deux le rang 1, et la ligne suivante passe directement au rang 3.

    DENSE_RANK

    DENSE_RANK()

    Renvoie le rang de chaque ligne au sein de sa partition. Contrairement à RANK(), les valeurs ex æquo ne génèrent pas d'écarts : le rang suivant suit toujours de manière consécutive.

    • Type de retour : BIGINT

    Exemple :

    SELECT
  year,
  country,
  product,
  profit,
  DENSE_RANK() OVER (PARTITION BY country ORDER BY profit) AS dense_rank
FROM testwindow;
    +------+---------+------------+--------+------------+
| year | country | product    | profit | dense_rank |
+------+---------+------------+--------+------------+
| 2001 | Finland | Phone      |     10 |          1 |
| 2000 | Finland | Computer   |   1500 |          2 |
| 2001 | USA     | Calculator |     50 |          1 |
| 2001 | USA     | Computer   |   1500 |          2 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2001 | Germany | Calculator |     79 |          2 |
+------+---------+------------+--------+------------+

    Dans la partition Allemagne, les deux lignes où profit = 75 obtiennent toutes deux le rang 1, et la ligne suivante reçoit le rang 2 (sans écart).

    NTILE

    NTILE(n)

    Répartit les lignes de chaque partition dans n compartiments, numérotés de 1 à n. Si les lignes ne se divisent pas uniformément, les lignes supplémentaires sont distribuées une par compartiment en commençant par le compartiment 1.

    Par exemple, 6 lignes dans 4 compartiments : 1, 1, 2, 2, 3, 4.

    • Type de retour : BIGINT

    Exemple (2 compartiments par pays) :

    SELECT
  year,
  country,
  product,
  profit,
  NTILE(2) OVER (PARTITION BY country ORDER BY profit) AS bucket
FROM testwindow;
    +------+---------+------------+--------+--------+
| year | country | product    | profit | bucket |
+------+---------+------------+--------+--------+
| 2001 | USA     | Calculator |     50 |      1 |
| 2001 | USA     | Computer   |   1500 |      2 |
| 2001 | Finland | Phone      |     10 |      1 |
| 2000 | Finland | Computer   |   1500 |      2 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2001 | Germany | Calculator |     79 |      2 |
+------+---------+------------+--------+--------+

    ROW_NUMBER

    ROW_NUMBER()

    Attribue un entier séquentiel unique à chaque ligne au sein de sa partition, en commençant par 1. Contrairement à RANK(), aucune ligne ne partage le même numéro.

    • Type de retour : BIGINT

    Exemple :

    SELECT
  year,
  country,
  product,
  profit,
  ROW_NUMBER() OVER (PARTITION BY country) AS row_num
FROM testwindow;
    +------+---------+------------+--------+---------+
| year | country | product    | profit | row_num |
+------+---------+------------+--------+---------+
| 2001 | USA     | Calculator |     50 |       1 |
| 2001 | USA     | Computer   |   1500 |       2 |
| 2000 | Germany | Calculator |     75 |       1 |
| 2000 | Germany | Calculator |     75 |       2 |
| 2001 | Germany | Calculator |     79 |       3 |
| 2000 | Finland | Computer   |   1500 |       1 |
| 2001 | Finland | Phone      |     10 |       2 |
+------+---------+------------+--------+---------+

    PERCENT_RANK

    PERCENT_RANK()

    Renvoie le rang relatif de chaque ligne sous forme de valeur comprise entre 0 et 1, en utilisant la formule (r - 1) / (n - 1), où r représente le RANK() de la ligne actuelle et n le nombre total de lignes dans la partition.

    • Type de retour : DOUBLE

    Exemple :

    SELECT
  year,
  country,
  product,
  profit,
  PERCENT_RANK() OVER (PARTITION BY country ORDER BY profit) AS pct_rank
FROM testwindow;
    +------+---------+------------+--------+----------+
| year | country | product    | profit | pct_rank |
+------+---------+------------+--------+----------+
| 2001 | Finland | Phone      |     10 |      0.0 |
| 2000 | Finland | Computer   |   1500 |      1.0 |
| 2001 | USA     | Calculator |     50 |      0.0 |
| 2001 | USA     | Computer   |   1500 |      1.0 |
| 2000 | Germany | Calculator |     75 |      0.0 |
| 2000 | Germany | Calculator |     75 |      0.0 |
| 2001 | Germany | Calculator |     79 |      1.0 |
+------+---------+------------+--------+----------+

    FIRST_VALUE

    FIRST_VALUE(x)

    Renvoie la valeur de la première ligne au sein de la partition de la fenêtre.

    • Type de retour : Identique au type de l'argument d'entrée

    Exemple :

    SELECT
  year,
  country,
  product,
  profit,
  FIRST_VALUE(profit) OVER (PARTITION BY country ORDER BY profit) AS first_profit
FROM testwindow;
    +------+---------+------------+--------+--------------+
| year | country | product    | profit | first_profit |
+------+---------+------------+--------+--------------+
| 2000 | Germany | Calculator |     75 |           75 |
| 2000 | Germany | Calculator |     75 |           75 |
| 2001 | Germany | Calculator |     79 |           75 |
| 2001 | USA     | Calculator |     50 |           50 |
| 2001 | USA     | Computer   |   1500 |           50 |
| 2001 | Finland | Phone      |     10 |           10 |
| 2000 | Finland | Computer   |   1500 |           10 |
+------+---------+------------+--------+--------------+

    LAST_VALUE

    LAST_VALUE(x)

    Renvoie la valeur de la dernière ligne au sein du cadre de la fenêtre. Le cadre de fenêtre par défaut est ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Par conséquent, LAST_VALUE renvoie par défaut la valeur de la ligne actuelle, et non celle de la dernière ligne de la partition.

    Pour renvoyer la véritable dernière valeur de la partition, ajoutez une clause de cadre explicite :

    LAST_VALUE(x) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

    • Type de retour : Identique au type de l'argument d'entrée

    Exemple 1 — cadre par défaut (renvoie la valeur de la ligne actuelle) :

    SELECT
  year,
  country,
  product,
  profit,
  LAST_VALUE(profit) OVER (PARTITION BY country ORDER BY profit) AS last_val
FROM testwindow;
    +------+---------+------------+--------+----------+
| year | country | product    | profit | last_val |
+------+---------+------------+--------+----------+
| 2001 | USA     | Calculator |     50 |       50 |
| 2001 | USA     | Computer   |   1500 |     1500 |
| 2001 | Finland | Phone      |     10 |       10 |
| 2000 | Finland | Computer   |   1500 |     1500 |
| 2000 | Germany | Calculator |     75 |       75 |
| 2000 | Germany | Calculator |     75 |       75 |
| 2001 | Germany | Calculator |     79 |       79 |
+------+---------+------------+--------+----------+

    Exemple 2 — cadre explicite couvrant toute la partition (renvoie la valeur de la dernière ligne) :

    SELECT
  year,
  country,
  product,
  profit,
  LAST_VALUE(profit) OVER (
    PARTITION BY country
    ORDER BY profit
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_val
FROM testwindow;
    +------+---------+------------+--------+----------+
| year | country | product    | profit | last_val |
+------+---------+------------+--------+----------+
| 2001 | Finland | Phone      |     10 |     1500 |
| 2000 | Finland | Computer   |   1500 |     1500 |
| 2000 | Germany | Calculator |     75 |       79 |
| 2000 | Germany | Calculator |     75 |       79 |
| 2001 | Germany | Calculator |     79 |       79 |
| 2001 | USA     | Calculator |     50 |     1500 |
| 2001 | USA     | Computer   |   1500 |     1500 |
+------+---------+------------+--------+----------+

    LAG

    LAG(x[, offset[, default_value]])

    Renvoie la valeur de la ligne située offset lignes avant la ligne actuelle au sein de la partition.

    Paramètre Description Valeur par défaut
    x Colonne ou expression à évaluer —
    offset Nombre de lignes à remonter ; 0 fait référence à la ligne actuelle 1
    default_value Valeur renvoyée lorsque offset est NULL ou dépasse la taille de la partition NULL

    • Type de retour : Identique au type de l'argument d'entrée

    Exemple (remonter d'une ligne) :

    SELECT
  year,
  country,
  product,
  profit,
  LAG(profit) OVER (PARTITION BY country ORDER BY profit) AS prev_profit
FROM testwindow;
    +------+---------+------------+--------+-------------+
| year | country | product    | profit | prev_profit |
+------+---------+------------+--------+-------------+
| 2001 | USA     | Calculator |     50 |        NULL |
| 2001 | USA     | Computer   |   1500 |          50 |
| 2000 | Germany | Calculator |     75 |        NULL |
| 2000 | Germany | Calculator |     75 |          75 |
| 2001 | Germany | Calculator |     79 |          75 |
| 2001 | Finland | Phone      |     10 |        NULL |
| 2000 | Finland | Computer   |   1500 |          10 |
+------+---------+------------+--------+-------------+

    LEAD

    LEAD(x[, offset[, default_value]])

    Renvoie la valeur de la ligne située offset lignes après la ligne actuelle au sein de la partition.

    Paramètre Description Valeur par défaut
    x Colonne ou expression à évaluer —
    offset Nombre de lignes à avancer ; 0 fait référence à la ligne actuelle 1
    default_value Valeur renvoyée lorsque offset est NULL ou dépasse la taille de la partition NULL

    • Type de retour : Identique au type de l'argument d'entrée

    Exemple (avancer d'une ligne) :

    SELECT
  year,
  country,
  product,
  profit,
  LEAD(profit) OVER (PARTITION BY country ORDER BY profit) AS next_profit
FROM testwindow;
    +------+---------+------------+--------+-------------+
| year | country | product    | profit | next_profit |
+------+---------+------------+--------+-------------+
| 2000 | Germany | Calculator |     75 |          75 |
| 2000 | Germany | Calculator |     75 |          79 |
| 2001 | Germany | Calculator |     79 |        NULL |
| 2001 | Finland | Phone      |     10 |        1500 |
| 2000 | Finland | Computer   |   1500 |        NULL |
| 2001 | USA     | Calculator |     50 |        1500 |
| 2001 | USA     | Computer   |   1500 |        NULL |
+------+---------+------------+--------+-------------+

    NTH_VALUE

    NTH_VALUE(x, offset)

    Renvoie la valeur de la ligne à la position offset au sein du cadre de la fenêtre. L'offset commence à 1.

    • Si offset est NULL ou dépasse le nombre de lignes dans le cadre, NULL est renvoyé.

    • Si offset vaut 0 ou est négatif, une erreur est renvoyée.

    • Type de retour : Identique au type de l'argument d'entrée

    Exemple (première ligne de chaque partition, équivalent à FIRST_VALUE) :

    SELECT
  year,
  country,
  product,
  profit,
  NTH_VALUE(profit, 1) OVER (PARTITION BY country ORDER BY profit) AS nth_val
FROM testwindow;
    +------+---------+------------+--------+---------+
| year | country | product    | profit | nth_val |
+------+---------+------------+--------+---------+
| 2001 | Finland | Phone      |     10 |      10 |
| 2000 | Finland | Computer   |   1500 |      10 |
| 2001 | USA     | Calculator |     50 |      50 |
| 2001 | USA     | Computer   |   1500 |      50 |
| 2000 | Germany | Calculator |     75 |      75 |
| 2000 | Germany | Calculator |     75 |      75 |
| 2001 | Germany | Calculator |     79 |      75 |
+------+---------+------------+--------+---------+

    Étapes suivantes