ntile - ClickHouse Documentation

将分区内已排序的行尽可能均匀地划分为指定数量的桶 (组) ，并返回当前行所属的桶编号。桶的编号从 1 开始。对于每个分区，各行会按顺序分配到各个桶中：如果行数不能被桶数整除，则靠前的桶比靠后的桶多分配一行。语法

ntile (buckets)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column]
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING] | [window_name])
FROM table_name
WINDOW window_name as ([PARTITION BY grouping_column] [ORDER BY sorting_column] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

参数 buckets 必须是一个常量正整数。必须指定 ORDER BY 子句。窗口帧必须覆盖整个分区 (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ；如果未显式指定，默认使用的也是这个窗口帧。有关窗口函数语法的更多信息，请参见：Window Functions - Syntax。 返回值

当前行在其分区内所属的桶编号。UInt64。

示例以下示例按薪资降序将玩家划分为四个桶。

Query

CREATE TABLE salaries
(
    `team` String,
    `player` String,
    `salary` UInt32,
    `position` String
)
Engine = Memory;

INSERT INTO salaries FORMAT Values
    ('Port Elizabeth Barbarians', 'Gary Chen', 195000, 'F'),
    ('New Coreystad Archdukes', 'Charles Juarez', 190000, 'F'),
    ('Port Elizabeth Barbarians', 'Michael Stanley', 150000, 'D'),
    ('New Coreystad Archdukes', 'Scott Harrison', 150000, 'D'),
    ('Port Elizabeth Barbarians', 'Robert George', 195000, 'M'),
    ('South Hampton Seagulls', 'Douglas Benson', 150000, 'M'),
    ('South Hampton Seagulls', 'James Henderson', 140000, 'M');

Query

SELECT player, salary,
       ntile(4) OVER (ORDER BY salary DESC, player ASC) AS bucket
FROM salaries;

Response

   ┌─player──────────┬─salary─┬─bucket─┐
│ Gary Chen       │ 195000 │      1 │
│ Robert George   │ 195000 │      1 │
│ Charles Juarez  │ 190000 │      2 │
│ Douglas Benson  │ 150000 │      2 │
│ Michael Stanley │ 150000 │      3 │
│ Scott Harrison  │ 150000 │      3 │
│ James Henderson │ 140000 │      4 │
   └─────────────────┴────────┴────────┘

这里共有七行和四个桶，因此前面三个桶各包含两行，最后一个桶包含一行。