WINDOW Fonksiyonları

BigQuery'ye Giriş

Matthew Forrest

Field CTO

WINDOW fonksiyonları nedir

Yuvarlanan SUM ile bir WINDOW illüstrasyonu

WINDOW fonksiyonları ne zaman kullanılır

Türlerine göre gruplandırılmış WINDOW fonksiyonları

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

WINDOW yapısı, PARTITION ve 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(): Satır numarasını döndüren window fonksiyonu
OVER(): Pencere çerçevesini tanımlar
PARTITION BY customer_id: Veriyi müşteri bazında böler
ORDER BY order_date: Her bölüm içinde sıralar
order_sequence: Window işleminin sonucu

RANK ve PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- Her satır için sıralama değeri
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- Her satır için yüzdelik sırası
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

RANK ve PERCENT_RANK sorgusu sonuçları

LAG ve LEAD

SELECT
  product_id,
  -- Önceki satırın değerini döndürür
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- Sonraki satırın değerini döndürür
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Sorgu sonuçlarında LAG ve LEAD gösterimi

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

Satır tabanlı sınır seçenekleri:

UNBOUNDED PRECEDING: Tüm önceki satırlar
UNBOUNDED FOLLOWING: Tüm sonraki satırlar
[INT] ROWS PRECEDING: Belirli sayıda önceki satır
[INT] ROWS FOLLOWING: Belirli sayıda sonraki satır

QUALIFY

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

3 veya daha yüksek sıraya sahip değerleri bulmak için QUALIFY kullanan sorgu sonuçları

Toplama yapmadığımız için HAVING kullanılamaz

Haydi pratik yapalım!

BigQuery'ye Giriş