DEV Community

Michael
Michael

Posted on • Originally published at gbase8.cn

GBase 8a OLAP: Data Bucketing with NTILE

#gbase #database #数据库

NTILE is a window function in GBase 8a that divides rows within a partition into a specified number of buckets and assigns a bucket number to each row. When the number of rows cannot be evenly divided, the earlier buckets receive one extra row each. This article demonstrates NTILE usage across several common scenarios.

Syntax 

NTILE(num) OVER (
    [PARTITION BY col1, col2, ...]
    ORDER BY col1 [ASC|DESC], col2 [ASC|DESC], ...
)
Enter fullscreen mode Exit fullscreen mode
  • num: The number of buckets to create (must be positive). Buckets are numbered starting from 1.
  • If the rows in a partition don't divide evenly by num, the remainder rows are distributed one per bucket starting from bucket 1.

Sample Data 

CREATE TABLE td(id INT, type INT, val DECIMAL(20,3));
INSERT INTO td VALUES
    (1,1,111), (1,1,222), (2,1,333), (2,2,444),
    (3,2,555), (3,2,666), (4,2,777),
    (5,1,NULL), (50,2,NULL);
Enter fullscreen mode Exit fullscreen mode

Standard NTILE with PARTITION BY and ORDER BY

Partition by type, order by id, split into 2 buckets. type=1 has 4 rows → 2 per bucket. type=2 has 5 rows → bucket 1 gets 3 rows, bucket 2 gets 2 rows.

SELECT id, type, val,
       NTILE(2) OVER (PARTITION BY type ORDER BY id) AS bucket
FROM td;
Enter fullscreen mode Exit fullscreen mode

Output:

id | type | val      | bucket
---|------|----------|-------
 1 |    1 | 111.000  | 1
 1 |    1 | 222.000  | 1
 2 |    1 | 333.000  | 2
 5 |    1 |    NULL  | 2
 2 |    2 | 444.000  | 1
 3 |    2 | 555.000  | 1
 3 |    2 | 666.000  | 1
 4 |    2 | 777.000  | 2
50 |    2 |    NULL  | 2
Enter fullscreen mode Exit fullscreen mode

NTILE Without PARTITION BY

With 9 rows in total and 2 buckets, bucket 1 gets 5 rows and bucket 2 gets 4 rows.

SELECT id, type, val,
       NTILE(2) OVER (ORDER BY id) AS bucket
FROM td;
Enter fullscreen mode Exit fullscreen mode

Output:

id | type | val      | bucket
---|------|----------|-------
 1 |    1 | 111.000  | 1
 1 |    1 | 222.000  | 1
 2 |    1 | 333.000  | 1
 2 |    2 | 444.000  | 1
 3 |    2 | 555.000  | 1
 3 |    2 | 666.000  | 2
 4 |    2 | 777.000  | 2
 5 |    1 |    NULL  | 2
50 |    2 |    NULL  | 2
Enter fullscreen mode Exit fullscreen mode

NTILE Without ORDER BY

Omitting ORDER BY splits data in natural storage order; results are non‑deterministic and have no practical meaning.

SELECT id, type, val,
       NTILE(2) OVER (PARTITION BY type) AS bucket
FROM td;
Enter fullscreen mode Exit fullscreen mode

NTILE Without PARTITION BY or ORDER BY

The entire table is split into 2 buckets arbitrarily.

SELECT id, type, val,
       NTILE(2) OVER () AS bucket
FROM td;
Enter fullscreen mode Exit fullscreen mode

NTILE is widely used in OLAP scenarios such as equal‑frequency binning, percentile grouping, and workload distribution. In a gbase database, it provides a simple yet powerful way to segment data for further analysis, making your analytical queries both concise and insightful.

Top comments (0)