Split rows into buckets

A

Analytic functions for splitting rows into buckets.

WITH d AS (
    SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 12 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 23 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 24 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 33 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 41 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 42 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 43 AS id FROM DUAL
)
SELECT
    d.*,
    --
    NTILE(2)    OVER (ORDER BY group_name, id) AS half,
    NTILE(3)    OVER (ORDER BY group_name, id) AS third,
    NTILE(4)    OVER (ORDER BY group_name, id) AS fourth,
    NTILE(10)   OVER (ORDER BY group_name, id) AS tenth,
    --
    WIDTH_BUCKET(ROWNUM,
        4,              -- split rows between 4
        8 + 1,          -- ... and 8
        3               -- ... into 3 buckets
    ) AS bucket
FROM d;