LLM Quest Academy
← Quay lại Blog

SQL Window Functions: 10 use case thực tế cho analyst 2026

LLM Quest Editorial·20 thg 4, 2026·2 phút đọc
SQL Window Functions: 10 use case thực tế cho analyst 2026
SQL Window Functions: 10 use case thực tế cho analyst 2026

Window function là công cụ mạnh nhất + ít dùng nhất của SQL. Hiểu nó = viết được query mà 90% analyst phải dùng subquery 3 tầng để làm.

Cấu trúc cơ bản

FUNCTION() OVER (
  PARTITION BY col1  -- chia nhóm
  ORDER BY col2      -- thứ tự trong nhóm
  ROWS BETWEEN ...   -- range frame
)

PARTITION như GROUP BY nhưng không collapse row. Mỗi row vẫn giữ + có thêm metric tổng hợp bên cạnh.

10 use case thực tế

1. Rank customer theo revenue

SELECT
  customer_id,
  total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS rank_all,
  RANK() OVER (PARTITION BY country ORDER BY total_revenue DESC) AS rank_in_country
FROM customers;

RANK vs DENSE_RANK vs ROW_NUMBER: khác nhau khi tie. ROW_NUMBER tie random, RANK skip số, DENSE_RANK không skip.

2. Top 3 product mỗi category

WITH ranked AS (
  SELECT
    category, product_id, revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn <= 3;

Pattern này dùng cho top-N-per-group cực phổ biến.

3. Running total (revenue cộng dồn)

SELECT
  date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_sales;

Dùng cho YTD, MTD, running totals dashboards.

4. Moving average 7 ngày

SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS revenue_7d_avg
FROM daily_sales;

Smooth ra xu hướng, loại spike ngắn hạn.

5. So sánh row với previous period

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_diff,
  (revenue - LAG(revenue, 1) OVER (ORDER BY month)) / LAG(revenue, 1) OVER (ORDER BY month) * 100 AS mom_growth_pct
FROM monthly_revenue;

LAG = giá trị row trước. LEAD = row sau. Critical cho MoM/YoY analytics.

6. Percentile (phân vị)

SELECT
  user_id,
  session_duration,
  PERCENT_RANK() OVER (ORDER BY session_duration) AS percentile,
  NTILE(4) OVER (ORDER BY session_duration) AS quartile
FROM sessions;

Tìm user top 10% engagement, hoặc chia bucket quartile cho cohort analysis.

7. First/Last row trong partition

SELECT
  customer_id,
  order_id,
  order_date,
  FIRST_VALUE(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS first_order_date,
  LAST_VALUE(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_date
FROM orders;

Cẩn thận LAST_VALUE — default frame là UNBOUNDED PRECEDING TO CURRENT ROW, phải viết explicit UNBOUNDED FOLLOWING.

8. Fill forward missing values

SELECT
  date,
  COALESCE(
    stock_price,
    LAG(stock_price) IGNORE NULLS OVER (ORDER BY date)
  ) AS filled_price
FROM daily_stocks;

Khi data có gap (weekend, holiday), carry forward giá trị trước. Hỗ trợ Snowflake, BigQuery.

9. Session分ization

WITH with_gap AS (
  SELECT
    user_id, event_time,
    event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS gap
  FROM events
),
sessionized AS (
  SELECT *,
    SUM(CASE WHEN gap > INTERVAL '30 minutes' OR gap IS NULL THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM with_gap
)
SELECT user_id, session_id, MIN(event_time), MAX(event_time), COUNT(*) AS events
FROM sessionized
GROUP BY 1, 2;

Gom event thành session theo gap 30 phút. Pattern cực mạnh cho analytics.

10. Detect consecutive days

WITH diffs AS (
  SELECT
    user_id, date,
    date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) * INTERVAL '1 day' AS group_key
  FROM user_activity
)
SELECT user_id, MIN(date) AS streak_start, MAX(date) AS streak_end, COUNT(*) AS streak_days
FROM diffs
GROUP BY user_id, group_key
ORDER BY streak_days DESC;

Tìm streak dài nhất cho gamification analytics.

Performance tips

  • Window function không dùng index trực tiếp → cân nhắc materialize result
  • Frame clause ảnh hưởng perf rất nhiều: ROWS nhanh hơn RANGE
  • Postgres 14+ hỗ trợ IGNORE NULLS native; trước đó cần workaround
  • BigQuery và Snowflake tối ưu tốt hơn Postgres cho huge data

Kết luận

Window function biến SQL từ "query đơn giản" thành "analytics engine". Master 10 use case trên = cover 90% report analyst cần làm. Nếu đang dùng 3 CTE nested để làm việc này → refactor dùng window ngay.

Khoá Data Science & Analytics Level 1–2 dạy window functions + 5 boss project analytics thực chiến.

#SQL#window functions#analytics#data analyst
Advertisement