Fonctions de Fenêtre en PostgreSQL

Introduction aux Fonctions de Fenêtre

Les fonctions de fenêtre dans PostgreSQL sont des outils puissants qui permettent d'effectuer des calculs sur un ensemble de lignes en rapport avec la ligne courante dans une requête. Ces fonctions offrent la flexibilité de définir les lignes sur lesquelles opérer, appliquées selon des critères définis, que ce soit une plage spécifique ou un regroupement.

Définition et Syntaxe

Ces fonctions se définissent à l'aide d'une clause OVER, qui détermine comment partitionner et ordonner les lignes sur lesquelles la fonction s'applique. Cette distinction en fait un type particulier de fonction, contrastant avec les fonctions d'agrégation classiques. Voici un exemple de syntaxe simple pour une fonction de fenêtre :

SELECT employee_id, row_number() OVER (ORDER BY employee_id) AS row_num FROM employees;

Dans cette requête, nous utilisons employee_id pour attribuer un numéro de ligne unique à chaque employé. L'expression ORDER BY dans la clause OVER spécifie l'ordre dans lequel les lignes sont traitées. Le numéro de ligne généré est désigné par le nom de colonne row_num grâce à la partie AS row_num.

Types de Fonctions de Fenêtre

Voici un tableau résumant certaines des principales fonctions de fenêtre en PostgreSQL et leur syntaxe correspondante :

| Fonction | Syntaxe | Description | |----------------|------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------| | Rank() | SELECT column_name1, column_name2, ..., rank() OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name; | Renvoie le rang de la ligne actuelle. | | Row_number() | SELECT column_name1, column_name2, ..., row_number() OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name;| Renvoie le numéro de la ligne actuelle au sein de sa partition, en comptant à partir de 1. | | Dense_rank() | SELECT column_name1, column_name2, ..., dense_rank() OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name; | Renvoie le rang de la ligne actuelle, comptant les groupes. | | Ntile() | SELECT column_name1, column_name2, ..., ntile (Argument) OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name; | Renvoie un entier variant de 1 à la valeur d'argument. | | Cume_dist() | SELECT column_name1, column_name2, ..., cume_dist() OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name; | Renvoie la distribution cumulative. | | Percent_rank() | SELECT column_name1, column_name2, ..., percent_rank() OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;| Renvoie le rang relatif de la ligne actuelle. | | First_Value() | SELECT column_name1, column_name2, ..., first_value(value) OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;| Renvoie la valeur de la première ligne dans une fenêtre définie. | | Last_value() | SELECT column_name1, column_name2, ..., last_value(value) OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;| Renvoie la valeur de la dernière ligne dans une fenêtre définie. |

Exemples Pratiques

Prenons un exemple avec une table de personnel pour illustrer l'utilisation des fonctions Rank() et Row_number(). Voici un aperçu de la table :

| id | employee | address | contact | salary | |----|----------|----------|---------|--------| | 01 | John | Tokyo | +81 | 5500 | | 02 | Kate | Beijing | +186 | 5000 | | 03 | Tad | Mumbai | +91 | 2500 | | 04 | Martha | Tokyo | +727 | 3500 | | 05 | Will | Paris | +33 | 4000 | | 06 | Ruth | Dubai | +971 | 2500 | | 07 | Kaka | Mumbai | +213 | 4500 | | 08 | Tolf | Tokyo | +44 | 2000 |

Pour attribuer un rang aux employés en utilisant la fonction Rank(), on écrit :

SELECT id, salary, address, Rank() OVER (PARTITION BY address ORDER BY salary DESC) FROM staff;

Pour la décomposition de cette syntaxe : - SELECT choisit les colonnes id, salary et address de la table. - Rank() OVER calcule le rang des lignes. - (PARTITION BY address ORDER BY salary DESC) définit comment partitionner et ordonner les données. - FROM staff indique que ces opérations se font sur la table de personnel.

Voici le résultat de cette requête :

| id | salary | address | rank | |----|--------|---------|------| | 01 | 5500 | Tokyo | 1 | | 04 | 3500 | Tokyo | 2 | | 08 | 2000 | Tokyo | 3 | | 07 | 4500 | Mumbai | 1 | | 03 | 2500 | Mumbai | 2 | | 02 | 5000 | Beijing | 1 | | 05 | 4000 | Paris | 1 | | 06 | 2500 | Dubai | 1 |

Utilisation des Fonctions de Fenêtre dans le Développement Web

Les fonctions de fenêtre sont extrêmement utiles lorsque vous créez des applications web ou des startups. Par exemple, si vous développez un site de e-commerce, vous pourriez vouloir afficher les classements des produits selon leur popularité ou leurs ventes. Grâce à ces fonctions, vous pouvez facilement générer des classements dynamiques ou des rapports détaillés qui peuvent être intégrés directement dans vos interfaces utilisateur, améliorant ainsi l'expérience client et vous aidant à prendre des décisions stratégiques basées sur les données.