Clickhouse实战--开窗函数的使用(1)

当我们需要求同比、环比，或则分组计算数据的最大值，最小值时，就需要用到窗口函数。Clickhouse也提供了很多的window函数。本文就来学习一下Clickhouse的window函数的使用。

官方对窗口函数的说明

下表是官方对窗口函数的一个说明。官方对Clickhouse开窗函数的详细说明，可以参考这里的文档。

窗口函数的使用范式

分析函数 over（partition by 列名 order by 列名 ）

分析函数的分类：

• 排名类：rank() ，row_number()，dense_rank()
• 聚合函数：sum,avg,count,max,min等
• 其他类：lag,lead,ntile

窗口函数的说明

开窗函数实战

创建一张用来测试的表，如下：

CREATE TABLE  test_db.empsalary on cluster perftest_2shards_1replicas
(
    `depName` String COMMENT '部门名',
    `empNo` Long COMMENT '员工号',
    `name`   String COMMENT  '员工名',
    `salary` Long COMMENT '薪水',
    `hobby`   Array(String) COMMENT '爱好'
)
ENGINE = MergeTree()
ORDER BY (depName, name)
PARTITION BY (depName)
SETTINGS index_granularity = 8192;

通过下面的命令导入测试数据：

insert into test_db.empsalary values ('sales',     1,  'Alice',  5000, ['game',  'ski']);
insert into test_db.empsalary values ('personnel', 2,  'Olivia', 3900, ['game',  'ski']);
insert into test_db.empsalary values ('sales',     3,  'Ella',   4800, ['skate', 'ski']);
insert into test_db.empsalary values ('sales',     4,  'Ebba',   4800, ['game',  'ski']);
insert into test_db.empsalary values ('personnel', 5,  'Lilly',  3500, ['climb', 'ski']);
insert into test_db.empsalary values ('develop',   7,  'Astrid', 4200, ['game',  'ski']);
insert into test_db.empsalary values ('develop',   8,  'Saga',   6000, ['kajak', 'ski']);
insert into test_db.empsalary values ('develop',   9,  'Freja',  4500, ['game',  'kajak']);
insert into test_db.empsalary values ('develop',   10, 'Wilma',  5200, ['game',  'ski']);
insert into test_db.empsalary values ('develop',   11, 'Maja',   5200, ['game',  'farming']);

在进行窗口函数实战前，先要设置一个参数：

set allow_experimental_window_functions = 1

排名函数的基本使用

通过排名函数可以轻松的获取分组中的属于某个排名的数据行。

select depName,name,salary,rank() over (partition by depName order by salary desc) rank  from test_db.empsalary;

得到的结果如下：

─depName───┬─name───┬─salary─┬─rank─┐
│ develop   │ Saga   │   6000 │    1 │
│ develop   │ Maja   │   5200 │    2 │
│ develop   │ Wilma  │   5200 │    2 │
│ develop   │ Freja  │   4500 │    4 │
│ develop   │ Astrid │   4200 │    5 │
│ personnel │ Olivia │   3900 │    1 │
│ personnel │ Lilly  │   3500 │    2 │
│ sales     │ Alice  │   5000 │    1 │
│ sales     │ Ebba   │   4800 │    2 │
│ sales     │ Ella   │   4800 │    2 │
└───────────┴────────┴────────┴──────┘

可以看到，通过depName分区后，在分区内部，使用rank()函数对数据按salary字段进行了排序。

几个排名函数的效果比较

SELECT
    depName,
    name,
    salary,
    rank() OVER (PARTITION BY depName ORDER BY salary DESC) AS rank,
    row_number() OVER (PARTITION BY depName ORDER BY salary DESC) AS row_number,
    dense_rank() OVER (PARTITION BY depName ORDER BY salary DESC) AS dense_rank
FROM test_db.empsalary

该SQL语句输出的结果如下：

┌─depName─┬─name───┬─salary─┬─rank─┬─row_number─┬─dense_rank─┐
│ develop │ Saga   │   6000 │    1 │          1 │          1 │
│ develop │ Maja   │   5200 │    2 │          2 │          2 │
│ develop │ Wilma  │   5200 │    2 │          3 │          2 │
│ develop │ Freja  │   4500 │    4 │          4 │          3 │
│ develop │ Astrid │   4200 │    5 │          5 │          4 │
│ personnel │ Olivia │   3900 │    1 │          1 │          1 │
│ personnel │ Lilly │   3500 │    2 │          2 │          2 │
│ sales   │ Alice │   5000 │    1 │          1 │          1 │
│ sales   │ Ebba  │   4800 │    2 │          2 │          2 │
│ sales   │ Ella  │   4800 │    2 │          3 │          2 │
└─────────┴───────┴────────┴──────┴────────────┴────────────┘

可以看到，对于分区字段和排序字段重复的数据行，处理的方式不同：

• rank()函数：其排名数字会重复，而且会跳过排名。比如：1 2 2 4这个排名，3被跳过了。
• row_number()函数：即使分区字段和排序字段相同，排名数字也不会跳过，得到的是一个连续的排名。
• dense_rank()函数：当分区字段和排序字段相同时，排名数字会相同，但不会跳过排名。例如：1 2 2 3 4，其中排名为2的数据重复，但不会跳过3这个排名。

找出每个部门薪水排在前3的员工

找出每个部门薪水排在前3名的所有员工，注意：是所有员工。

解题思路：要找出每个部门薪水排在前3的所有员工，就首先需要按部门对员工进行分组。然后对组内的员工进行按薪水降序排序，再取前3名。

另外要注意，这里是找出所有的员工，包括重复的，所以应该使用dense_rank()函数，这样就不会溜掉薪水相同的员工名单。

SELECT
	depName, 
	empNo, 
	name, 
	salary,
	dense_rank
FROM (
  SELECT
		depName, 
		empNo, 
		name, 
		salary,
    dense_rank() OVER (PARTITION BY depName ORDER BY salary DESC) as dense_rank
  FROM empsalary) tmp
WHERE
  dense_rank <= 3

执行以上sql后，得到的结果如下：

─depName───┬─empNo─┬─name───┬─salary─┬─dense_rank─┐
│ develop   │     8 │ Saga   │   6000 │          1 │
│ develop   │    11 │ Maja   │   5200 │          2 │
│ develop   │    10 │ Wilma  │   5200 │          2 │
│ develop   │     9 │ Freja  │   4500 │          3 │
│ personnel │     2 │ Olivia │   3900 │          1 │
│ personnel │     5 │ Lilly  │   3500 │          2 │
│ sales     │     1 │ Alice  │   5000 │          1 │
│ sales     │     4 │ Ebba   │   4800 │          2 │
│ sales     │     3 │ Ella   │   4800 │          2 │
└───────────┴───────┴────────┴────────┴────────────┘

小结

​ 本文介绍了Clickhouse的窗口函数，并通过几个例子介绍了其窗口函数的基本使用。后面会继续说明其他函数的使用。

最后

以上就是忐忑舞蹈为你收集整理的Clickhouse实战--开窗函数的使用(1)的全部内容，希望文章能够帮你解决Clickhouse实战--开窗函数的使用(1)所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。