SQL avec les fonctions FIRST_VALUE et LAST_VALUE pour obtenir les premières et dernières valeurs

Dans l’analyse de données ou la création de rapports avec SQL, il est souvent nécessaire d’extraire la première ou la dernière valeur d’un ensemble de données. Pour faciliter cette tâche, les fonctions FIRST_VALUE et LAST_VALUE sont très utiles. Cet article vous expliquera en détail comment manipuler efficacement vos données à l’aide de ces fonctions avec des exemples concrets.

Sommaire

Utilisation basique de la fonction FIRST_VALUE

La fonction FIRST_VALUE permet d’obtenir la première valeur dans une fenêtre ou une partition spécifique. Cela permet d’extraire facilement la première valeur de la première ligne dans un ensemble de données.

Syntaxe de base

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3) AS first_value
FROM 
    table_name;

Dans cet exemple, nous obtenons la première valeur de column1 après avoir partitionné les données par column2 et trié par column3.

Exemple d’utilisation

Par exemple, si vous souhaitez obtenir le nom du premier employé embauché dans chaque département, vous pouvez utiliser la requête suivante :

SELECT 
    department,
    employee_name,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date) AS first_hired
FROM 
    employees;

Cette requête retourne le nom du premier employé embauché dans chaque département.

Exemples avancés de la fonction FIRST_VALUE

En plus de son utilisation basique, la fonction FIRST_VALUE est utile dans des requêtes plus complexes et pour des analyses avancées. Voici quelques exemples basés sur des jeux de données réels.

Obtenir la première date de vente d’un produit

Si vous souhaitez obtenir la première date de vente d’un produit dans chaque magasin, vous pouvez utiliser la requête suivante :

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date) AS first_sale_date
FROM 
    sales;

Cette requête renvoie la première date de vente de chaque produit dans chaque magasin.

Obtenir la première note d’examen d’un étudiant

Lorsque vous gérez les données des résultats scolaires, vous pouvez obtenir la première note d’examen de chaque étudiant avec cette requête :

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date) AS first_exam_score
FROM 
    exam_results;

Cette requête renvoie la première note d’examen de chaque étudiant.

Obtenir le premier montant d’achat dans les données de transaction

Voici un exemple pour extraire le premier montant d’achat pour chaque client à partir des données de transaction :

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS first_purchase_amount
FROM 
    transactions;

Cette requête renvoie le premier montant d’achat de chaque client.

Utilisation basique de la fonction LAST_VALUE

La fonction LAST_VALUE permet d’obtenir la dernière valeur dans une fenêtre ou une partition spécifique. Cela permet d’extraire facilement la dernière valeur de la dernière ligne dans un ensemble de données.

Syntaxe de base

SELECT 
    column1,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

Dans cet exemple, nous obtenons la dernière valeur de column1 après avoir partitionné les données par column2 et trié par column3. Il est important d’utiliser la clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING pour spécifier toute la fenêtre.

Exemple d’utilisation

Par exemple, si vous souhaitez obtenir le nom du dernier employé embauché dans chaque département, vous pouvez utiliser la requête suivante :

SELECT 
    department,
    employee_name,
    LAST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hired
FROM 
    employees;

Cette requête renvoie le nom du dernier employé embauché dans chaque département.

Exemples avancés de la fonction LAST_VALUE

La fonction LAST_VALUE, comme la fonction FIRST_VALUE, est très utile dans l’analyse des données et la création de rapports. Voici quelques exemples basés sur des jeux de données réels.

Obtenir la dernière date de vente d’un produit

Si vous souhaitez obtenir la dernière date de vente d’un produit dans chaque magasin, vous pouvez utiliser la requête suivante :

SELECT 
    store_id,
    product_id,
    sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Cette requête renvoie la dernière date de vente de chaque produit dans chaque magasin.

Obtenir la dernière note d’examen d’un étudiant

Lorsque vous gérez les données des résultats scolaires, vous pouvez obtenir la dernière note d’examen de chaque étudiant avec cette requête :

SELECT 
    student_id,
    exam_date,
    score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Cette requête renvoie la dernière note d’examen de chaque étudiant.

Obtenir le dernier montant d’achat dans les données de transaction

Voici un exemple pour extraire le dernier montant d’achat pour chaque client à partir des données de transaction :

SELECT 
    customer_id,
    transaction_date,
    amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Cette requête renvoie le dernier montant d’achat de chaque client.

Combinaison des fonctions FIRST_VALUE et LAST_VALUE

En combinant les fonctions FIRST_VALUE et LAST_VALUE, vous pouvez obtenir simultanément la première et la dernière valeur d’un ensemble de données. Cela permet, par exemple, de comparer les valeurs au début et à la fin d’une période pour une analyse plus approfondie.

Obtenir la première et la dernière date de vente d’un produit

Voici un exemple de requête pour obtenir la première et la dernière date de vente d’un produit dans chaque magasin :

SELECT 
    store_id,
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Cette requête renvoie la première et la dernière date de vente de chaque produit dans chaque magasin.

Obtenir la première et la dernière date d’embauche des employés

Si vous souhaitez obtenir les premières et dernières dates d’embauche des employés de chaque département, vous pouvez utiliser la requête suivante :

SELECT 
    department,
    employee_name,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Cette requête renvoie les noms des employés embauchés en premier et en dernier dans chaque département.

Obtenir le premier et le dernier montant d’achat pour chaque client

Voici une requête pour obtenir le premier et le dernier montant d’achat pour chaque client à partir des données de transaction :

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Cette requête renvoie le premier et le dernier montant d’achat de chaque client.

Utilisation des fonctions comme fonctions de fenêtre

Les fonctions FIRST_VALUE et LAST_VALUE peuvent être utilisées comme des fonctions de fenêtre, ce qui permet d’analyser et de manipuler les données de manière plus flexible. En tant que fonctions de fenêtre, elles permettent de calculer les premières et dernières valeurs pour chaque ligne d’un ensemble de résultats.

Concept de base des fonctions de fenêtre

Les fonctions de fenêtre effectuent des calculs sur un ensemble de résultats et les appliquent à un groupe spécifique de lignes dans une fenêtre. La fenêtre est définie par la clause PARTITION BY et ORDER BY.

Syntaxe de base

SELECT 
    column1,
    FIRST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value,
    LAST_VALUE(column1) OVER (PARTITION BY column2 ORDER BY column3 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value
FROM 
    table_name;

Dans cette syntaxe, les données sont partitionnées par column2 et triées par column3 pour obtenir les premières et dernières valeurs de column1.

Exemple d’utilisation : fonction de fenêtre dans les données de vente

Par exemple, si vous souhaitez obtenir la première et la dernière date de vente de chaque magasin, vous pouvez utiliser la requête suivante :

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Cette requête renvoie les premières et dernières dates de vente pour chaque magasin.

Exemple d’utilisation : données des résultats scolaires

Dans les données des résultats scolaires, si vous souhaitez obtenir les premières et dernières notes d’examen de chaque étudiant, vous pouvez utiliser cette requête :

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Cette requête renvoie les premières et dernières notes d’examen de chaque étudiant.

Partitionnement et utilisation de ORDER BY

Un des avantages puissants des fonctions FIRST_VALUE et LAST_VALUE est qu’elles permettent de partitionner et de trier les données. Cela permet d’obtenir les premières et dernières valeurs au sein de groupes spécifiques.

Concept de base du partitionnement

Le partitionnement divise les données en groupes basés sur certains critères, permettant des calculs indépendants dans chaque groupe. Le partitionnement se fait via la clause PARTITION BY.

Concept de base de ORDER BY

La clause ORDER BY permet de trier les lignes dans chaque partition dans un ordre spécifique, ce qui permet de déterminer précisément les premières et dernières valeurs.

Exemple d’utilisation du partitionnement et de ORDER BY

Voici une requête pour obtenir les premières et dernières dates de vente de chaque produit dans chaque magasin :

SELECT 
    store_id,
    product_id,
    sale_date,
    FIRST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY store_id, product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Cette requête renvoie les premières et dernières dates de vente pour chaque produit dans chaque magasin.

Exemple avancé avec les données des étudiants

Dans les données des résultats scolaires, voici comment obtenir les premières et dernières notes d’examen de chaque étudiant :

SELECT 
    student_id,
    exam_date,
    score,
    FIRST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_exam_score,
    LAST_VALUE(score) OVER (PARTITION BY student_id ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_exam_score
FROM 
    exam_results;

Cette requête renvoie les premières et dernières notes d’examen de chaque étudiant.

Exemple avancé avec les données de transaction

Voici comment obtenir les premiers et derniers montants d’achat pour chaque client à partir des données de transaction :

SELECT 
    customer_id,
    transaction_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Cette requête renvoie les premiers et derniers montants d’achat de chaque client.

Exercices et réponses

Pour approfondir votre compréhension des fonctions FIRST_VALUE et LAST_VALUE, nous avons préparé quelques exercices. Chaque exercice est accompagné d’une solution que vous pouvez essayer par vous-même.

Exercice 1 : Obtenir les premières et dernières dates d’embauche pour chaque département

Écrivez une requête pour obtenir les premières et dernières dates d’embauche dans chaque département à partir de la table employees suivante :

-- Table employees
+---------+------------+------------+
| emp_id  | department | hire_date  |
+---------+------------+------------+
| 1       | Sales      | 2020-01-01 |
| 2       | Sales      | 2021-05-10 |
| 3       | HR         | 2019-03-15 |
| 4       | HR         | 2020-07-23 |
| 5       | IT         | 2021-01-05 |
+---------+------------+------------+

Solution

SELECT 
    department,
    FIRST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire_date,
    LAST_VALUE(hire_date) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_hire_date
FROM 
    employees;

Exercice 2 : Obtenir les premières et dernières dates de vente pour chaque produit

Écrivez une requête pour obtenir les premières et dernières dates de vente pour chaque produit à partir de la table sales suivante :

-- Table sales
+---------+------------+------------+
| sale_id | product_id | sale_date  |
+---------+------------+------------+
| 1       | 101        | 2022-01-01 |
| 2       | 102        | 2022-01-05 |
| 3       | 101        | 2022-02-01 |
| 4       | 103        | 2022-01-10 |
| 5       | 102        | 2022-03-01 |
+---------+------------+------------+

Solution

SELECT 
    product_id,
    FIRST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_sale_date,
    LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM 
    sales;

Exercice 3 : Obtenir les premiers et derniers montants d’achat pour chaque client

Écrivez une requête pour obtenir les premiers et derniers montants d’achat pour chaque client à partir de la table transactions suivante :

-- Table transactions
+-------------+----------+--------+--------------+
| transaction_id | customer_id | amount | transaction_date |
+-------------+----------+--------+--------------+
| 1           | 1001     | 200    | 2023-01-01   |
| 2           | 1002     | 150    | 2023-01-05   |
| 3           | 1001     | 300    | 2023-02-01   |
| 4           | 1003     | 250    | 2023-01-10   |
| 5           | 1002     | 400    | 2023-03-01   |
+-------------+----------+--------+--------------+

Solution

SELECT 
    customer_id,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_purchase_amount,
    LAST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase_amount
FROM 
    transactions;

Conclusion

Dans cet article, nous avons appris à utiliser les fonctions SQL FIRST_VALUE et LAST_VALUE pour obtenir les premières et dernières valeurs d’un ensemble de données. Nous avons abordé les utilisations de base, des exemples avancés, l’utilisation des fonctions comme fonctions de fenêtre, et l’importance du partitionnement et de ORDER BY. En maîtrisant ces techniques, vous serez en mesure d’effectuer des analyses de données et de créer des rapports plus efficaces. N’hésitez pas à les appliquer à vos jeux de données réels.