การแบ่งกลุ่มด้วย NTILE และ Cume_Dist

NTILE: กลุ่มขนาดเท่ากัน

SELECT id, total,
       NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;
-- 1 = bottom 25%, 2 = next, 3, 4 = top 25%

กลุ่มเปอร์เซ็นไทล์

SELECT id, score,
       NTILE(100) OVER (ORDER BY score DESC) AS percentile
FROM students;
-- percentile 1 = top 1%

PERCENT_RANK

SELECT id, score,
       PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM students;
-- 0 for the lowest, 1 for the highest.

CUME_DIST

SELECT id, score,
       CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM students;
-- "What share of students scored ≤ this one?"

สถิติเปอร์เซ็นไทล์ด้วย PERCENTILE_CONT

SELECT
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY total) AS median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total) AS p99
FROM orders;

PERCENTILE_DISC

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total)

NTILE บนพาร์ทิชัน

SELECT user_id, total,
       NTILE(4) OVER (PARTITION BY user_id ORDER BY total) AS user_quartile
FROM orders;

ฮิสโตแกรมผ่าน NTILE

SELECT bucket, MIN(total) AS bucket_min, MAX(total) AS bucket_max, COUNT(*)
FROM (
  SELECT NTILE(10) OVER (ORDER BY total) AS bucket, total
  FROM orders
) sub
GROUP BY bucket
ORDER BY bucket;

การรวมข้อมูลต่อกลุ่ม

SELECT user_quartile, COUNT(*) AS users, AVG(total) AS avg_total
FROM (
  SELECT user_id, NTILE(4) OVER (ORDER BY total) AS user_quartile, total
  FROM orders
) sub
GROUP BY user_quartile;

การรวมฟังก์ชันหน้าต่าง

SELECT id, total,
       NTILE(4) OVER w     AS quartile,
       PERCENT_RANK() OVER w AS pct_rank,
       AVG(total) OVER w     AS overall_avg
FROM orders
WINDOW w AS (ORDER BY total);

กรณีที่ NTILE ทำให้เข้าใจผิด

สรุปทบทวน

ตรวจสอบอย่างรวดเร็ว