Bucketing mit NTILE und Cume_Dist

NTILE: Gleich große Buckets

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

Perzentil-Buckets

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?"

Perzentilstatistiken mit 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 auf Partitionen

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

Histogramm mit 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;

Pro Bucket aggregieren

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;

Fensterfunktionen kombinieren

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);

Wenn NTILE irreführt

Zusammenfassung

Schnelltest