Skip to main content

### 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;
```