Cloud

ntile

The ntile() function is a window function used to divide an ordered data set into a specified number of approximately equal groups or buckets. This function assigns each group a bucket number starting from one.

Syntax

The syntax for this function is:

NTILE(buckets) OVER (
    PARTITION BY partition_expression, ... ]
    [ORDER BY sort_expression [ASC | DESC], ...]
)

Parameters

  • buckets: Positive integer or an expression that evaluates to a positive integer for each partition. It specifies the number of groups into which the data should be divided.

Restrictions

  • buckets: Must be a positive integer. Redpanda SQL truncates non-integer constants to an integer.

Examples

The following example uses a simplified version of the film table from the Pagila database, containing only the title, length and rating columns.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating text
);

INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 'PG-13'),
  ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
  ('DANGEROUS UPTOWN', 121, 'PG'),
  ('KILL BROTHERHOOD', 54, 'G'),
  ('HALLOWEEN NUTS', 47, 'PG-13'),
  ('HOURS RAGE', 122, 'NC-17'),
  ('PIANIST OUTFIELD', 136, 'NC-17'),
  ('PICKUP DRIVING', 77, 'G'),
  ('INDEPENDENCE HOTEL', 157, 'NC-17'),
  ('PRIVATE DROP', 106, 'PG'),
  ('SAINTS BRIDE', 125, 'G'),
  ('FOREVER CANDIDATE', 131, 'NC-17'),
  ('MILLION ACE', 142, 'PG-13'),
  ('SLEEPY JAPANESE', 137, 'PG'),
  ('WRATH MILE', 176, 'NC-17'),
  ('YOUTH KICK', 179, 'NC-17'),
  ('CLOCKWORK PARADISE', 143, 'PG-13');

The following query uses the ntile() function to divide the films into four quartiles based on their length:

SELECT
    title,
    length,
    NTILE(4) OVER (ORDER BY length) AS quartile
FROM film;

The query returns:

        title        | length | quartile
---------------------+--------+----------
 HALLOWEEN NUTS      |     47 |        1
 KILL BROTHERHOOD    |     54 |        1
 PICKUP DRIVING      |     77 |        1
 ATTRACTION NEWTON   |     83 |        1
 PRIVATE DROP        |    106 |        1
 DANGEROUS UPTOWN    |    121 |        2
 HOURS RAGE          |    122 |        2
 SAINTS BRIDE        |    125 |        2
 FOREVER CANDIDATE   |    131 |        2
 PIANIST OUTFIELD    |    136 |        3
 SLEEPY JAPANESE     |    137 |        3
 MILLION ACE         |    142 |        3
 CLOCKWORK PARADISE  |    143 |        3
 CHRISTMAS MOONSHINE |    150 |        4
 INDEPENDENCE HOTEL  |    157 |        4
 WRATH MILE          |    176 |        4
 YOUTH KICK          |    179 |        4
(17 rows)