Window functions

การวิเคราะห์ข้อมูลธุรกิจด้วย SQL

Michel Semaan

Data Scientist

Window functions - ภาพรวม

Window functions: ทำการคำนวณข้ามชุดของแถวที่เกี่ยวข้องกับแถวปัจจุบัน
ตัวอย่าง
- คำนวณผลรวมสะสม
- ดึงค่าจากแถวก่อนหน้าหรือถัดไป

Running total

Running total: ผลรวมสะสมของค่าก่อนหน้าของตัวแปร

ตัวอย่าง

x    x_rt
---  ----
1    1
2    3
3    6
4    11
5    16

Running total ของการลงทะเบียน - คิวรี

WITH reg_dates AS (
  SELECT
    user_id,
    MIN(order_date) AS reg_date
  FROM orders
  GROUP BY user_id),

  registrations AS (
  SELECT
    DATE_TRUNC('month', reg_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS regs
  FROM reg_dates
  GROUP BY delivr_month)


SELECT
  delivr_month,
  regs,
  SUM(regs) OVER (ORDER BY delivr_month ASC) AS regs_rt
FROM registrations
ORDER BY delivr_month ASC LIMIT 3;

Running total ของการลงทะเบียน - ผลลัพธ์

delivr_month  regs  regs_rt
------------  ----  -------
2018-06-01    123    123
2018-07-01    140    263
2018-08-01    157    420

Lagged MAU - คิวรี

WITH maus AS (
  SELECT
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    COUNT(DISTINCT user_id) AS mau
  FROM orders
  GROUP BY delivr_month)


SELECT
  delivr_month,
  mau,
  COALESCE(
    LAG(mau) OVER (ORDER BY delivr_month ASC),
  1) AS last_mau
FROM maus
ORDER BY delivr_month ASC
LIMIT 3;

Lagged MAU - ผลลัพธ์

delivr_month  mau  last_mau
------------  ---  --------
2018-06-01    123  1
2018-07-01    226  123
2018-08-01    337  226

Window functions

การวิเคราะห์ข้อมูลธุรกิจด้วย SQL