Hàm WINDOW

Giới thiệu về BigQuery

Matthew Forrest

Field CTO

WINDOW functions là gì

Minh họa WINDOW với SUM cuộn

Khi nào dùng hàm WINDOW

Các hàm WINDOW được nhóm theo loại

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

Cấu trúc WINDOW, PARTITION và 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(): Hàm trả về số thứ tự dòng
OVER(): Xác định khung cửa sổ
PARTITION BY customer_id: Nhóm theo khách hàng
ORDER BY order_date: Sắp xếp trong từng partition
order_sequence: Kết quả của phép tính window

RANK và PERCENT_RANK

SELECT
  product_id,
  product_photos_qty,
  -- Hạng thứ tự cho từng dòng
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank,
  -- Hạng phần trăm cho từng dòng
  PERCENT_RANK() OVER(
    ORDER BY product_photos_qty
  ) as percent
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Kết quả truy vấn RANK và PERCENT_RANK

LAG và LEAD

SELECT
  product_id,
  -- Trả về giá trị của dòng trước
  LAG(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lag,
  product_photos_qty,
  -- Trả về giá trị của dòng sau
  LEAD(product_photos_qty) OVER(
    ORDER BY product_photos_qty
  ) as lead
FROM ecommerce.ecomm_products 
ORDER BY product_photos_qty DESC;

Hình minh họa kết quả truy vấn LAG và LEAD

RANGE BETWEEN và 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

Tùy chọn ranh giới theo hàng:

UNBOUNDED PRECEDING: Mọi hàng trước
UNBOUNDED FOLLOWING: Mọi hàng sau
[INT] ROWS PRECEDING: Số hàng cụ thể trước
[INT] ROWS FOLLOWING: Số hàng cụ thể sau

QUALIFY

SELECT
  product_id,
  product_photos_qty,
  RANK() OVER(
    ORDER BY product_photos_qty DESC
  ) as rank
FROM ecommerce.ecomm_products 
-- Lọc bằng QUALIFY
QUALIFY rank < 4
ORDER BY product_photos_qty DESC;

Kết quả truy vấn dùng QUALIFY để tìm các giá trị có hạng từ 3 trở lên

Không thể dùng HAVING vì không tổng hợp

Ayo berlatih!

Giới thiệu về BigQuery