WINDOW-functies

Introductie tot BigQuery

Matthew Forrest

Field CTO

Wat zijn WINDOW-functies

Een illustratie van een WINDOW met een rollende SUM

Wanneer gebruik je WINDOW-functies

WINDOW-functies gegroepeerd per type

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

WINDOW-structuur, PARTITION en 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(): De windowfunctie die het rijnummer retourneert
OVER(): Definieert het windowframe
PARTITION BY customer_id: Groepeert data per klant
ORDER BY order_date: Sorteert data binnen elke partitie
order_sequence: Resultaat van de windowfunctie

RANK en PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- Ordinale rang per rij
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- Percentielrang per rij
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Resultaten van de RANK- en PERCENT_RANK-query

LAG en LEAD

SELECT
  product_id,
  -- Geeft de waarde van de vorige rij
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- Geeft de waarde van de volgende rij
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Afbeelding met LAG en LEAD in queryresultaten

RANGE BETWEEN en 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

Rij-gebaseerde grenzen:

UNBOUNDED PRECEDING: Alle rijen ervoor
UNBOUNDED FOLLOWING: Alle rijen erna
[INT] ROWS PRECEDING: Aantal rijen ervoor
[INT] ROWS FOLLOWING: Aantal rijen erna

QUALIFY

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

Resultaten van onze query met QUALIFY om waarden met een rang van 3 of hoger te vinden

HAVING kan niet, we aggregeren niet

Laten we oefenen!

Introductie tot BigQuery