Fenêtrage dans Redshift

Introduction à Redshift

Jason Myers

Principal Architect

Fonctions de fenêtre

Agir sur une fenêtre (partition) de données avec une valeur pour chaque ligne de cette fenêtre
Les fonctions d'agrégation regroupent des lignes, pas les fonctions de fenêtre
Définies avec une clause OVER

Trois notions clés

Partitionnement — créer des groupes de lignes (PARTITION BY)
Ordonnancement — ordre dans chaque partition (ORDER BY)
Encadrement — optionnel, ajoute des contraintes sur les lignes.

Utiliser une fenêtre pour calculer une moyenne

SELECT division_id,
       sale_date,
       revenue,

       -- Calculer la moyenne des revenus
       AVG(revenue) OVER (
           -- Par division pour chaque année et mois
           PARTITION BY division_id,
                        DATE_PART('year', sale_date)
                        DATE_PART('month', sale_date),
       ) AS month_avg_revenue

  FROM orders
 ORDER BY division_id, 
          sale_date DESC;

Utiliser une fenêtre pour calculer une moyenne (suite)

division_id | sale_date  | revenue | dept_month_avg_revenue
============|============|=========|=======================
      1     | 2024-01-23 | 350460 | 225500 
      1     | 2024-01-09 | 100540 | 225500 
      1     | 2023-12-15 | 231000 | 231000 
      1     | 2023-11-12 | 124000 | 68000 
      1     | 2023-11-07 | 75000  | 68000 
      1     | 2023-11-01 | 5000   | 68000 
      2     | 2024-01-10 | 500    | 500 
      2     | 2023-12-11 | 1000   | 16166.666666666667 
      2     | 2023-12-08 | 37000  | 16166.666666666667 
      2     | 2023-12-01 | 10500  | 16166.666666666667

Utiliser LAG pour des fenêtres d'un mois à l'autre

LAG et LEAD permettent d'obtenir la donnée d'une ligne au‑dessus (avant) ou au‑dessous (après) dans la fenêtre selon la clause ORDER BY

SELECT division_id,
       DATE_PART('year', sale_date) AS sales_year,
       DATE_PART('month', sale_date) AS sales_month,

       -- Compter les enregistrements dans la fenêtre
       COUNT(*) AS current_month_sales,

       -- Compter les enregistrements de la fenêtre précédente
       LAG(COUNT(*), 1) OVER (

          -- Pour chaque division
          PARTITION BY division_id 
              -- Ordonné par année et mois
              ORDER BY DATE_PART('year', sale_date),
                       DATE_PART('month', sale_date)
       ) AS prior_month_sales

Utiliser LAG pour des fenêtres d'un mois à l'autre (suite)

  FROM sales_data
 -- Veillez à regrouper par toutes les clauses de fenêtre
 GROUP BY division_id, 
          sales_year, 
          sales_month
 ORDER BY division_id, 
          sales_year DESC, 
          sales_month DESC;

Utiliser LAG pour des fenêtres d'un mois à l'autre (résultats)

division_id	sales_year	sales_month	current_month_sales	prior_month_sales
1	2024	1	2	1
1	2023	12	1	3
1	2023	11	3	null
2	2024	1	1	3
2	2023	12	3	null

Classer des données à l'intérieur des fenêtres

RANK permet de classer une valeur dans la fenêtre selon la clause ORDER BY, en commençant à 1

SELECT division_id,
       sale_date,
       revenue,
       -- Calculer le rang de chaque vente dans la fenêtre
       RANK() OVER (
           -- Pour chaque division 
           PARTITION BY division_id 
               -- Utiliser revenue pour le classement
               ORDER BY revenue desc
       ) as division_sales_rank
  FROM sales_data
 -- Les trier par rang et par division
 ORDER BY division_id, division_sales_rank;

Classer des données à l'intérieur des fenêtres (résultats)

division_id	sale_date	revenue	division_sales_rank
1	2024-01-23	350460	1
1	2023-12-15	231000	2
1	2023-11-12	124000	3
1	2024-01-09	100540	4
1	2023-11-07	75000	5
1	2023-11-01	5000	6
2	2023-12-08	37000	1
2	2023-12-01	10500	2
2	2023-12-11	1000	3
2	2024-01-10	500	4

Passons à la pratique !

Introduction à Redshift