Les fonctions de fenêtre dans InterSystems IRIS permettent de réaliser des analyses puissantes — comme des totaux cumulés, des classements (rankings) ou des moyennes mobiles — directement en SQL.
Ces fonctions s’appliquent à une "fenêtre" de lignes liées à la ligne courante, sans regrouper les résultats comme le fait GROUP BY.
Cela signifie que vous pouvez écrire des requêtes plus claires, plus rapides et plus faciles à maintenir — sans boucles, sans jointures, sans tables temporaires.

Dans cet article, nous allons comprendre le fonctionnement des fonctions de fenêtre SQL en abordant certaines tâches courantes d'analyse de données.

---

Introduction aux fonctions de fenêtre SQL dans InterSystems IRIS

Les fonctions de fenêtre SQL constituent un outil très puissant pour l’analyse de données.
Elles permettent de calculer des agrégats ou des classements sur plusieurs lignes, tout en conservant la visibilité de chaque ligne.
Que vous créiez des tableaux de bord, des rapports ou des traitements analytiques complexes, les fonctions de fenêtre simplifient la logique de vos requêtes et améliorent les performances.

Remarque : je ne suis pas un expert en fonctions de fenêtre, mais j'aimerais partager les informations et les ressources qui m'ont aidé à les comprendre. Vos suggestions ou corrections sont les bienvenues!

---

🚀 Les fonctions de fenêtre : pourquoi sont-elles importantes

Vous est-il déjà arrivé d’écrire plusieurs requêtes SQL — voire même des boucles procédurales — simplement pour calculer des totaux cumulés, des classements, ou des différences entre lignes?

Les fonctions de fenêtre vous permettent de faire tout cela en une seule requête SQL.

Elles intègrent des capacités analytiques avancées directement dans SQL — sans jointures supplémentaires, sans tables temporaires, et sans boucles procédurales.

---

🧠 Qu’est-ce qu’une fonction de fenêtre?

Une fonction de fenêtre effectue un calcul sur un ensemble de lignes liées d’une certaine manière à la ligne courante — cet ensemble est appelé une fenêtre.

Comme le montre la figure 1, contrairement à GROUP BY, les fonctions fenêtre ne regroupent pas les lignes. Elles vous permettent de calculer des agrégats tout en conservant chaque ligne visible.

Différences entre les agrégations et les fonctions de fenêtre

La syntaxe générale se présente comme suit:

window_function_name(...) OVER (
  PARTITION BY column_name
  ORDER BY column_name
  ROWS BETWEEN ...
)

Où:

• PARTITION BY définit des groupes de lignes (comme "par utilisateur" ou "par département").
• ORDER BY définit l'ordre des lignes dans chaque partition.
• ROWS BETWEEN ... définit le sous-ensemble de lignes visibles par la fonction — c’est ce qu’on appelle le cadre de la fenêtre window frame).

---

⚙️ Avantages des fonctions de fenêtre

Avant l'introduction des fonctions de fenêtre, les développeurs devaient souvent:

• Exécuter plusieurs requêtes pour obtenir des résultats intermédiaires.
• Utiliser des tables temporaires ou des sous-requêtes pour fusionner des agrégats partiels.
• Écrire du code procédural dans ObjectScript pour simuler un classement ou des totaux cumulés.

Les fonctions de fenêtre résolvent tout cela de manière élégante: une seule requête, aucune boucle, aucun état à gérer manuellement.

---

🧩 Exemple 1 — Total cumulé par client

Commençons par un exemple très simple : calculer le total cumulé des commandes de chaque utilisateur au fil du temps.

🛠️ Création et remplissage de la table pour cet exemple

CREATE TABLE Orders (
  OrderID INT,
  CustomerID INT,
  OrderDate DATE,
  OrderAmount DECIMAL(10,2)
)

INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount)
SELECT 1, 101, '2023-01-01', 100.00 UNION
SELECT 2, 101, '2023-01-05', 150.00 UNION
SELECT 3, 102, '2023-01-02', 200.00 UNION
SELECT 4, 101, '2023-01-10', 50.00 UNION
SELECT 5, 102, '2023-01-07', 100.00 

❌Sans fonctions de fenêtre — requêtes multiples

SELECT
  o1.CustomerID,
  o1.OrderDate,
  SUM(o2.OrderAmount) AS RunningTotal
FROM Orders o1
JOIN Orders o2
  ON o1.CustomerID = o2.CustomerID
  AND o2.OrderDate <= o1.OrderDate
GROUP BY o1.CustomerID, o1.OrderDate
ORDER BY o1.CustomerID, o1.OrderDate

Résultat:

CustomerID	OrderDate	RunningTotal
101	2023-01-01	100
101	2023-01-05	250
101	2023-01-10	300
102	2023-01-02	200
102	2023-01-07	300

---

Cela fonctionne, mais il est nécessaire d'utiliser une auto-jointure et un GROUP BY, ce qui devient coûteux pour les grands ensembles de données.
Les fonctions de fenêtre permettent d'écrire une requête SQL beaucoup plus claire.

✅ Au moyen des fonctions de fenêtre — une seule requête

SELECT
  CustomerID,
  OrderDate,
  SUM(OrderAmount) OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate
  ) AS RunningTotal
FROM Orders
ORDER BY CustomerID, OrderDate

Résultat:

CustomerID	OrderDate	RunningTotal
101	2023-01-01	100
101	2023-01-05	250
101	2023-01-10	300
102	2023-01-02	200
102	2023-01-07	300

---

Décomposons chaque composant de la syntaxe des fonctions de fenêtre:

• PARTITION BY CustomerID
  Garantit que le total cumulé est calculé séparément pour chaque utilisateur. Sans cela, la somme s'étendrait à tous les utilisateurs.

• ORDER BY OrderDate
  Définit la séquence des commandes pour chaque utilisateur, afin que le total cumulé s'accumule dans l'ordre chronologique.

• SUM(OrderAmount) OVER (...)
  Il s'agit de la fonction de fenêtre appliquée aux partitions. Dans ce cas, elle calcule la somme de OrderAmount pour chaque ligne, en les accumulant, y compris toutes les lignes précédentes dans la même partition (utilisateur) jusqu'à ce point.

Figure 2 - Évaluation de la fonction de fenêtre pour l'exemple 1

💡 Exemple 2 — Classement des employés par salaire

🛠️ Création et remplissage de la table pour cet exemple

CREATE TABLE Employees (
  EmployeeID INT,
  Department VARCHAR(50),
  Name VARCHAR(100),
  Salary DECIMAL(10,2)
)

INSERT INTO Employees (EmployeeID, Department, Name, Salary)
SELECT 1, 'Sales', 'Alice', 70000 UNION
SELECT 2, 'Sales', 'Bob', 65000 UNION
SELECT 3, 'HR', 'Carol', 60000 UNION
SELECT 4, 'HR', 'Dave', 62000 UNION
SELECT 5, 'Sales', 'Eve', 72000

❌ Sans fonctions de fenêtre — Boucles SQL dynamique et ObjectScript

ClassMethod RankEmployeesBySalary()
{
    Set tSQL = "SELECT Department, EmployeeID, Salary " _
              "FROM Employees ORDER BY Department, Salary DESC"
    Set tRS = ##class(%SQL.Statement).%ExecDirect(, tSQL)

    Set prevDept = ""
    Set rank = 0

    While tRS.%Next() {
        Set dept = tRS.%Get("Department")
        Set emp = tRS.%Get("EmployeeID")
        Set sal = tRS.%Get("Salary")

        If dept '= prevDept {
            Set rank = 1
        } Else {
            Set rank = rank + 1
        }

        Write "Dept: ", dept, " | Emp: ", emp, " | Rank: ", rank, " | Salary: ", sal, !
        Set prevDept = dept
    }
}

Résultat:

USER>Do ##class(tmp.Teste1).RankEmployeesBySalary()
Dept: HR | Emp: 4 | Rank: 1 | Salary: 62000
Dept: HR | Emp: 3 | Rank: 2 | Salary: 60000
Dept: Sales | Emp: 5 | Rank: 1 | Salary: 72000
Dept: Sales | Emp: 1 | Rank: 2 | Salary: 70000
Dept: Sales | Emp: 2 | Rank: 3 | Salary: 65000

---

✅ Avec des fonctions de fenêtre — un SQL déclaratif

SELECT
  Department,
  EmployeeID,
  Salary,
  RANK() OVER (
    PARTITION BY Department
    ORDER BY Salary DESC
  ) AS SalaryRank
FROM Employees
ORDER BY Department, SalaryRank

Résultat:

Department	EmployeeID	Salary	SalaryRank
HR	4	62000	1
HR	3	60000	2
Sales	5	72000	1
Sales	1	70000	2
Sales	2	65000	3

---

Décomposons chaque élément de la syntaxe de la fonction de fenêtre:

• PARTITION BY Department
  Garantit que le classement est calculé séparément dans chaque département. Sans cette clause, les employés seraient classés à l'échelle de l'ensemble de l'entreprise, sans tenir compte du périmètre départemental.

• ORDER BY Salary DESC
  Trie les employés de chaque département par ordre décroissant de salaire. Cela détermine l'ordre de classement: les salaires les plus élevés obtiennent les numéros de classement les plus bas.

• RANK() OVER (...)
  Applique la fonction de classement à la liste triée de chaque département. Elle attribue un classement à chaque employé en fonction de son salaire, au moyen duquel les égalités reçoivent le même classement et les écarts apparaissent dans la séquence.

Figure 3 - Évaluation de la fonction de fenêtre pour l'exemple 2

---

🧩 Exemple 3 — Moyenne mobile des ventes quotidiennes

Illustrons le fonctionnement de ROWS BETWEEN au moyen d'une moyenne mobile.

🛠️ Création et remplissage de la table pour cet exemple

CREATE TABLE DailySales (
  SaleDate DATE,
  Amount DECIMAL(10,2)
)

INSERT INTO DailySales (SaleDate, Amount)
SELECT '2023-01-01', 100 UNION 
SELECT '2023-01-02', 150 UNION
SELECT '2023-01-03', 200 UNION
SELECT '2023-01-04', 250 UNION
SELECT '2023-01-05', 300

❌ Sans fonctions de fenêtre — requêtes multiples et boucles ObjectScript

ClassMethod MovingAverageWithoutWindow()
{
    // Requête sur toutes les ventes classées par date
    Set sql = "SELECT SaleDate, Amount FROM DailySales ORDER BY SaleDate"
    Set stmt = ##class(%SQL.Statement).%New()
    Set status = stmt.%Prepare(sql)
    If $$$ISERR(status) {
        Write "SQL Prepare failed: ", status, !
        Quit
    }

    Set rset = stmt.%Execute()

    // Stocker les lignes en mémoire pour un retour en arrière
    Set rowCount = 0
    While rset.%Next() {
        Set rowCount = rowCount + 1
        Set sales(rowCount, "Date") = rset.%Get("SaleDate")
        Set sales(rowCount, "Amount") = rset.%Get("Amount")
    }

    // Passer en boucle et calculer la moyenne mobile sur 3 jours
    For i = 1:1:rowCount {
        Set total = 0
        Set count = 0

        For j = i-2:1:i {
            If j >= 1 {
                Set total = total + sales(j, "Amount")
                Set count = count + 1
            }
        }

        Set movingAvg = total / count
        Write "Date: ", sales(i, "Date"), " | Amount: ", sales(i, "Amount"), " | MovingAvg: ", $FN(movingAvg, "", 2), !
    }
}

Résultat:

USER>Do ##class(tmp.Teste1).MovingAverageWithoutWindow()
Date: 66475 | Amount: 100 | MovingAvg: 100.00
Date: 66476 | Amount: 150 | MovingAvg: 125.00
Date: 66477 | Amount: 200 | MovingAvg: 150.00
Date: 66478 | Amount: 250 | MovingAvg: 200.00
Date: 66479 | Amount: 300 | MovingAvg: 250.00

✅ Avec des fonctions de fenêtre — un SQL déclaratif

SELECT
  SaleDate,
  Amount,
  AVG(Amount) OVER (
    ORDER BY SaleDate
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS MovingAvg
FROM DailySales
ORDER BY SaleDate

Cela permet de calculer la moyenne du jour actuel et des deux jours précédents, soit une moyenne mobile sur trois jours.

Résultat:

SaleDate	Amount	MovingAvg
2023-01-01	100	100
2023-01-02	150	125
2023-01-03	200	150
2023-01-04	250	200
2023-01-05	300	250

---

Décomposons chaque élément de la syntaxe de la fonction de fenêtre:

• ORDER BY SaleDate
  Définit l'ordre chronologique des ventes, ce qui est essentiel pour calculer une moyenne mobile en fonction du temps.

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  Spécifie le cadre de la fenêtre : la ligne actuelle et les deux lignes précédentes. Cela crée une fenêtre mobile de 3 jours pour le calcul de la moyenne.

• AVG(Amount) OVER (...)
  Applique la fonction de fenêtre pour calculer la moyenne de la somme Amount dans le cadre défini. Pour chaque ligne, elle inclut le jour actuel et les deux jours précédents dans le calcul.

Figure 4 - Évaluation de la fonction fenêtre pour l'exemple 3

Notez qu'il n'y a pas de PARTITION BY dans cet exemple. En effet, notre but est de calculer la moyenne mobile sur l'ensemble des ventes quotidiennes, et non séparément par catégorie, région ou utilisateur.

Utiliser PARTITION BY permet de diviser les données en groupes indépendants, ce qui fait que la moyenne mobile se réinitialise à l’intérieur de chaque groupe.
Mais ici, comme nous souhaitons une moyenne mobile continue dans le temps — en traitant l’ensemble des données comme une seule séquence chronologique — nous omettons PARTITION BY afin de conserver la fenêtre intacte. Mais vous pouvez bien sûr en faire l'aide si cela correspond à vos besoins en matière de requêtes.

---

🏁 Point clé à retenir

Les fonctions de fenêtre permettent d’exprimer, en une seule instruction SQL élégante, ce qui nécessitait auparavant plusieurs requêtes ou même du code procédural.

Ils rendent vos requêtes plus lisibles, votre code plus facile à maintenir et vos analyses plus rapides, et tout cela sans quitter SQL.

---

🧭 Conclusion

Les fonctions de fenêtre dans InterSystems IRIS offrent un moyen puissant et élégant d'effectuer des analyses complexes directement dans SQL. Que vous calculiez des totaux cumulés, attribuiez des classements ou calculiez des moyennes mobiles, ces fonctions simplifient votre logique et améliorent les performances, tout en conservant la lisibilité et la facilité de maintenance de vos requêtes.

En utilisant les fonctions de fenêtre, vous obtenez des informations plus approfondies à partir de vos données sans avoir recours à du code procédural ou à des jointures complexes. Elles ne sont pas seulement pratiques, elles constituent une avancée majeure dans notre façon d'envisager l'analyse SQL.

Vous trouverez plus de détails et d'autres fonctions de fenêtre dans la [Référence des fonctions de fenêtre dans la documentation IRIS.

---

Cet article a été rédigé avec l’aide d’outils d’intelligence artificielle afin de clarifier les concepts et d’en améliorer la lisibilité.