ClickHouse窗口函数nth_value详解：获取有序窗口中的第N个值

郎轶诺

于 2025-05-30 09:09:58 发布

阅读量374

点赞数 5

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/gitblog_00084/article/details/148325809

ClickHouse窗口函数nth_value详解：获取有序窗口中的第N个值

ClickHouse ClickHouse® 是一个免费的大数据分析型数据库管理系统。项目地址: https://gitcode.com/gh_mirrors/cli/ClickHouse

什么是nth_value函数

nth_value是ClickHouse提供的一个强大的窗口函数，它能够在有序的窗口框架中返回第N行的值。这个函数特别适合需要分析数据集中特定排名位置值的场景，比如查找第三高薪资、第五畅销产品等。

函数语法

nth_value(x, offset) OVER (
    [PARTITION BY grouping_column] 
    [ORDER BY sorting_column]
    [ROWS | RANGE frame_spec]
)

参数说明：

x：需要获取值的列名
offset：指定要获取的行位置（正整数）

工作原理

nth_value函数会按照以下步骤执行：

首先根据PARTITION BY子句对数据进行分组（如果存在）
在每个分组内，按照ORDER BY指定的列进行排序
在排序后的数据中，查找第offset行的值
返回该行中x列的值

注意：如果第offset行不存在或者值为NULL，函数将返回NULL。

实际应用示例

让我们通过一个足球运动员薪资的示例来理解nth_value的用法：

数据准备

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

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

查询第三高薪资的球员

SELECT 
    player, 
    salary, 
    nth_value(player, 3) OVER(ORDER BY salary DESC) AS third_highest_salary 
FROM salaries;

结果分析

┌─player──────────┬─salary─┬─third_highest_salary─┐
│ Gary Chen       │ 195000 │                      │
│ Robert George   │ 195000 │                      │
│ Charles Juarez  │ 190000 │ Charles Juarez       │
│ Scott Harrison  │ 180000 │ Charles Juarez       │
│ Douglas Benson  │ 150000 │ Charles Juarez       │
│ James Henderson │ 140000 │ Charles Juarez       │
│ Michael Stanley │ 100000 │ Charles Juarez       │
└─────────────────┴────────┴──────────────────────┘

结果解读：

前两行（第1、2高薪资）的third_highest_salary列为空，因为此时还没有确定第三高薪资
从第三行开始，third_highest_salary列显示"Charles Juarez"，即第三高薪资的球员
后续所有行都保持这个值，因为第三高薪资已经确定

高级用法

分组查询

我们可以按球队分组，找出每个球队薪资第三高的球员：

SELECT 
    team,
    player,
    salary,
    nth_value(player, 3) OVER(PARTITION BY team ORDER BY salary DESC) AS team_third_highest
FROM salaries;

结合其他窗口函数

nth_value可以与其他窗口函数结合使用，例如计算与第三高薪资的差异：

SELECT 
    player,
    salary,
    nth_value(salary, 3) OVER(ORDER BY salary DESC) AS third_salary,
    salary - nth_value(salary, 3) OVER(ORDER BY salary DESC) AS diff_from_third
FROM salaries;