ฟังก์ชัน WINDOW

BigQuery เบื้องต้น

Matthew Forrest

Field CTO

ฟังก์ชัน WINDOW คืออะไร

ภาพประกอบ WINDOW ที่มีการคำนวณ SUM แบบ rolling

เมื่อไหร่ควรใช้ฟังก์ชัน WINDOW

ฟังก์ชัน WINDOW จำแนกตามประเภท

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

โครงสร้าง WINDOW, PARTITION และ 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(): ฟังก์ชัน window ที่คืนค่าหมายเลขแถว
OVER(): กำหนดกรอบ window
PARTITION BY customer_id: จัดกลุ่มข้อมูลตาม customer
ORDER BY order_date: เรียงข้อมูลภายในแต่ละ partition
order_sequence: ผลลัพธ์จากการดำเนินการของฟังก์ชัน window

RANK และ 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;

ผลลัพธ์ของคิวรี RANK และ PERCENT_RANK

LAG และ 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;

ภาพแสดงผลลัพธ์คิวรีของ LAG และ LEAD

RANGE BETWEEN และ 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

ตัวเลือกการกำหนดขอบเขตแบบแถว:

UNBOUNDED PRECEDING: แถวทั้งหมดก่อนหน้า
UNBOUNDED FOLLOWING: แถวทั้งหมดถัดไป
[INT] ROWS PRECEDING: จำนวนแถวที่ระบุก่อนหน้า
[INT] ROWS FOLLOWING: จำนวนแถวที่ระบุถัดไป

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;

ผลลัพธ์ของคิวรีที่ใช้ QUALIFY เพื่อกรองค่าที่มี rank 3 หรือสูงกว่า

ใช้ HAVING ไม่ได้เนื่องจากไม่ได้ทำการรวมกลุ่ม (aggregate)

มาฝึกกันเถอะ!

BigQuery เบื้องต้น