NTILE(n) OVER() 窗口函数详解

NTILE(n) OVER() 窗口函数详解

NTILE(n) OVER() 是SQL中的一种窗口函数，用于将结果集划分为大致相等的n个组（或称为"分位数"），并为每一行分配一个组号（从1到n）。

基本语法

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

参数说明

• n: 指定要将结果集分成的组数
• PARTITION BY: 可选，用于将结果集分成多个分区，NTILE会在每个分区内独立计算
• ORDER BY: 可选，决定行的排序顺序，这对NTILE的结果至关重要

工作原理

1. 首先根据PARTITION BY子句（如果有）将数据分成多个分区
2. 在每个分区内，根据ORDER BY子句（如果有）对行进行排序
3. 将每个分区内的行尽可能均匀地分配到n个组中
4. 为每一行分配一个组号（1到n）

使用示例

示例1：基本用法

SELECT 
    employee_id, 
    salary,
    NTILE(4) OVER(ORDER BY salary DESC) AS quartile
FROM employees;

这将按工资降序排列员工，并将他们分成4个组（四分位数）。

示例2：带分区

SELECT 
    department_id,
    employee_id, 
    salary,
    NTILE(3) OVER(PARTITION BY department_id ORDER BY salary DESC) AS tercile
FROM employees;

这将按部门分区，然后在每个部门内按工资降序排列员工，并将每个部门的员工分成3个组。

注意事项

1. 如果总行数不能被n整除，前面的组会比后面的组多一行。例如，11行分成4组，组的大小将是3,3,3,2。

2. 如果没有ORDER BY子句，NTILE的分配是未定义的（不同数据库可能表现不同）。

3. NTILE常用于分析场景，如将客户按消费金额分成高、中、低组别。

实际应用场景

• 将学生成绩分成A、B、C、D等级
• 将客户按价值分成不同层级
• 将产品按销售额分成不同表现组
• 将员工绩效分成不同等级

NTILE提供了一种简单的方法来执行这种分桶操作，而不需要手动计算百分位数或复杂的条件逻辑。