Fungsi WINDOW

Pengantar BigQuery

Matthew Forrest

Field CTO

Apa itu fungsi WINDOW

Ilustrasi WINDOW dengan SUM berjalan

Kapan menggunakan fungsi WINDOW

Fungsi WINDOW dikelompokkan menurut jenis

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

Struktur WINDOW, PARTITION, dan 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(): Fungsi window yang mengembalikan nomor baris
OVER(): Mendefinisikan bingkai window
PARTITION BY customer_id: Mengelompokkan data per pelanggan
ORDER BY order_date: Mengurutkan data di dalam tiap partisi
order_sequence: Hasil operasi fungsi window

RANK dan PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- Peringkat ordinal untuk tiap baris
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- Peringkat persentil untuk tiap baris
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Hasil kueri RANK dan PERCENT_RANK

LAG dan LEAD

SELECT
  product_id,
  -- Mengambil nilai dari baris sebelumnya
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- Mengambil nilai dari baris berikutnya
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Gambar yang menampilkan LAG dan LEAD pada hasil kueri

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

Opsi batas berbasis baris:

UNBOUNDED PRECEDING: Semua baris sebelum
UNBOUNDED FOLLOWING: Semua baris setelah
[INT] ROWS PRECEDING: Jumlah baris tertentu sebelum
[INT] ROWS FOLLOWING: Jumlah baris tertentu setelah

QUALIFY

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

Hasil kueri yang menggunakan QUALIFY untuk menemukan nilai dengan peringkat 3 atau lebih tinggi

Tidak bisa memakai HAVING karena tidak ada agregasi

Ayo berlatih!

Pengantar BigQuery