Fonctions WINDOW

Introduction à BigQuery

Matthew Forrest

Field CTO

Qu'est-ce que les fonctions WINDOW

Illustration d'une WINDOW avec une somme glissante

Quand utiliser les fonctions WINDOW

Fonctions WINDOW regroupées par type

¹ https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9

Structure WINDOW, PARTITION et ORDER BY

SELECT
  customer_id,
  order_date,
  order_total,
  ROW_NUMBER() OVER(

    PARTITION BY customer_id

    ORDER BY order_date

  ) AS order_sequence
FROM orders;

ROW_NUMBER(): Fonction window qui renvoie le numéro de ligne
OVER(): Définit la fenêtre
PARTITION BY customer_id: Regroupe par client
ORDER BY order_date: Trie les données à l'intérieur de chaque partition
order_sequence: Résultat de l'opération window

RANK et PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- Ordinal rank for each row
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- Percentile rank for each row
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Résultats de la requête RANK et PERCENT_RANK

LAG et LEAD

SELECT
  product_id,
  -- Returns value from previous row
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- Returns value from next row
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Image illustrant LAG et LEAD dans les résultats de requête

RANGE BETWEEN et CURRENT ROW

SELECT
  order_id,
  order_timestamp,
  SUM(cost) OVER(
    ORDER BY order_timestamp 
    ROWS BETWEEN 2 PRECEDING 
    AND CURRENT ROW) as rolling_avg
FROM sales_data
ORDER BY order_timestamp

Options de délimitation par lignes :

UNBOUNDED PRECEDING: Toutes les lignes avant
UNBOUNDED FOLLOWING: Toutes les lignes après
[INT] ROWS PRECEDING: Nombre précis de lignes avant
[INT] ROWS FOLLOWING: Nombre précis de lignes après

QUALIFY

SELECT
  product_id,
  product_photos_qty,
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank
FROM ecommerce.ecomm_products 
-- Filter using QUALIFY
QUALIFY rank < 4
ORDER BY product_photos_qty DESC;

Résultats de notre requête utilisant QUALIFY pour trouver les valeurs de rang 3 ou plus

Impossible d'utiliser HAVING car il n'y a pas d'agrégation

Passons à la pratique !

Introduction à BigQuery