[clickhouse] Clickhouse之開窗函式篇

千千寰宇發表於2024-05-24
函式

0 概述

0.1 序

  • clickhouse : 21.3.4.25

0.2 開窗函式

  • 視窗函式可讓使用者對與當前行相關的一組行執行計算。使用者可以執行的一些計算與使用聚合函式執行的計算類似，但視窗函式不會導致行被分組為單個輸出 - 仍會返回各個行。
  • clickhouse的開窗函式
  • clickhouse開窗函式的功能，不過當前為試驗階段，不建議在生產環境中使用，可以先學習一下準備著，等官網去掉了實驗標記就可以愉快的使用啦~
  • 詳見官網：https://clickhouse.com/docs/en/sql-reference/window-functions
  • 在部分clickhouse版本中尚未預設開啟窗函式功能，可以透過引數設定開啟：
SETTINGS allow_experimental_window_functions = 1

0.3 資料準備

建立新表

CREATE TABLE default.employee_salary_l (
	month Date,
	name String ,
	department String,
	salary UInt32
) ENGINE = MergeTree()
partition by month
ORDER BY month;

樣例資料

INSERT INTO default.employee_salary_l VALUES
('2020-01-01', 'Ali', 'Sales', 6000),
('2020-01-01', 'Bob', 'Sales', 6000),
('2020-01-01', 'Cindy', 'Sales', 5000),
('2020-01-01', 'Davd', 'Finance', 8000),
('2020-01-01', 'Elena', 'Sales', 9000),
('2020-01-01', 'Fancy', 'Finance', 10000),
('2020-01-01', 'George', 'Finance', 10000),
('2020-01-01', 'Haffman', 'Marketing', 6000),
('2020-01-01', 'Ilaja', 'Marketing', 7000),
('2020-01-01', 'Joey', 'Sales', 8000);
  • 查詢樣例資料
select * from default.employee_salary_l;


/month      /name   /department /salary
2020-01-01	Ali	    Sales	    6000
2020-01-01	Bob	    Sales	    6000
2020-01-01	Cindy	Sales	    5000
2020-01-01	Davd	Finance	    8000
2020-01-01	Elena	Sales	    9000
2020-01-01	Fancy	Finance	    10000
2020-01-01	George	Finance	    10000
2020-01-01	Haffman	Marketing	6000
2020-01-01	Ilaja	Marketing   7000
2020-01-01	Joey	Sales	    8000

1 開窗函式的實驗

1.1 開窗排序

rank() 函式 : 分組排序(允許並列排名、序號順延)

rank方法允許並列排名，後續排名序號往後順延。比如，有兩個第一，則接著後面就是第三了。我們先看一下下面的sql語句：

  • 方式1：
select 
	name, department, month, salary,
	rank() OVER (partition by department ORDER BY salary desc) AS rank
from default.employee_salary_l
SETTINGS allow_experimental_window_functions = 1
  • 方式2：
select 
	name, department, month, salary,
	rank() OVER w AS rank
from default.employee_salary_l
WINDOW w AS (partition by department ORDER BY salary desc)
SETTINGS allow_experimental_window_functions = 1
  • 【解釋】：在該sql語句中
  • WINDOW w AS (partition by department ORDER BY salary desc)這一行就是定義一個分組窗
  • partition by department是指按照部門進行分組
  • ORDER BY salary desc則是在分組內對salary按照從大到小進行排序，這個排序主要是為了讓rank()函式依據該結果得到排序後的序號
  • 排序後的查詢結果：
┌─name────┬─department─┬──────month─┬─salary─┬─rank─┐
│ Fancy   │ Finance    │ 2020-01-01 │  10000 │    1 │
│ George  │ Finance    │ 2020-01-01 │  10000 │    1 │
│ Davd    │ Finance    │ 2020-01-01 │   8000 │    3 │
│ Ilaja   │ Marketing  │ 2020-01-01 │   7000 │    1 │
│ Haffman │ Marketing  │ 2020-01-01 │   6000 │    2 │
│ Elena   │ Sales      │ 2020-01-01 │   9000 │    1 │
│ Joey    │ Sales      │ 2020-01-01 │   8000 │    2 │
│ Ali     │ Sales      │ 2020-01-01 │   6000 │    3 │
│ Bob     │ Sales      │ 2020-01-01 │   6000 │    3 │
│ Cindy   │ Sales      │ 2020-01-01 │   5000 │    5 │
└─────────┴────────────┴────────────┴────────┴──────┘

從結果可以看到，rank函式得到的就是每個成員在各自部門分組內的排序序號，而序號也是不連續的。

dense_rank() 函式 : 分組排序(允許並列排名、序號不順延)

  • dense_rank()方法允許出現並列排名，但是後續排名序號不順延，也就是會出現連續的序號。

如下所示為dense_rank的排序sql語句和結果：

  • 方法1：
select 
	name, department, month, salary,
	dense_rank() OVER (partition by department ORDER BY salary desc) AS dense_rank
from default.employee_salary_l
SETTINGS allow_experimental_window_functions = 1
  • 方法2:
select 
	name, department, month, salary,
	dense_rank() OVER w AS dense_rank
from default.employee_salary_l
WINDOW w AS (partition by department ORDER BY salary desc)
SETTINGS allow_experimental_window_functions = 1
  • 查詢結果
┌─name────┬─department─┬──────month─┬─salary─┬─dense_rank─┐
│ Fancy   │ Finance    │ 2020-01-01 │  10000 │          1 │
│ George  │ Finance    │ 2020-01-01 │  10000 │          1 │
│ Davd    │ Finance    │ 2020-01-01 │   8000 │          2 │
│ Ilaja   │ Marketing  │ 2020-01-01 │   7000 │          1 │
│ Haffman │ Marketing  │ 2020-01-01 │   6000 │          2 │
│ Elena   │ Sales      │ 2020-01-01 │   9000 │          1 │
│ Joey    │ Sales      │ 2020-01-01 │   8000 │          2 │
│ Ali     │ Sales      │ 2020-01-01 │   6000 │          3 │
│ Bob     │ Sales      │ 2020-01-01 │   6000 │          3 │
│ Cindy   │ Sales      │ 2020-01-01 │   5000 │          4 │
└─────────┴────────────┴────────────┴────────┴────────────┘

row_number() : 分組排序(不允許並列排名:=排序行號)

  • row_number()不允許並列排名，所有序號需連續排列。

其實準確的說，row_number()方法並不是一個嚴格意義的排序方法，它的本質是獲得每一行的行號，但在某些排序場景中還是可以用到該方法的。
比如，當常規排序後只想要保留第一條資料（並列的也只取一個），那麼就可以用row_number()來解決這樣的問題了。
需要注意的是由於row_number()不允許出現並列的序號，那麼：對於並列的兩行資料，重複執行的話結果行號可能會不一樣。

  • 方法1：
select 
	name, department, month, salary,
	row_number() OVER (partition by department ORDER BY salary desc) AS row_number
from default.employee_salary_l
SETTINGS allow_experimental_window_functions = 1
  • 方法2：
select 
	name, department, month, salary,
	row_number() OVER w AS row_number
from default.employee_salary_l
WINDOW w AS (partition by department ORDER BY salary desc)
SETTINGS allow_experimental_window_functions = 1
  • 查詢結果：
┌─name────┬─department─┬──────month─┬─salary─┬─row_number─┐
│ Fancy   │ Finance    │ 2020-01-01 │  10000 │          1 │
│ George  │ Finance    │ 2020-01-01 │  10000 │          2 │
│ Davd    │ Finance    │ 2020-01-01 │   8000 │          3 │
│ Ilaja   │ Marketing  │ 2020-01-01 │   7000 │          1 │
│ Haffman │ Marketing  │ 2020-01-01 │   6000 │          2 │
│ Elena   │ Sales      │ 2020-01-01 │   9000 │          1 │
│ Joey    │ Sales      │ 2020-01-01 │   8000 │          2 │
│ Ali     │ Sales      │ 2020-01-01 │   6000 │          3 │
│ Bob     │ Sales      │ 2020-01-01 │   6000 │          4 │
│ Cindy   │ Sales      │ 2020-01-01 │   5000 │          5 │
└─────────┴────────────┴────────────┴────────┴────────────┘

1.2 開窗聚合

  • 開窗功能除了用來進行組內排序，還經常用來進行組內的資料統計，比如求和、均值、最大值等。下面我們按部門對薪資進行統計分析。

常規聚合

  • 常規聚合一般包含計算資料條數、最小值、最大值、總數、平均值等。

實現如下：

  • 使用方法
select 
	name, department, month, salary,
	count(*) OVER w AS count,
	sum(salary) OVER w AS sum_wage,
	avg(salary) OVER w AS avg_wage,
	max(salary) OVER w AS max_wage,
	min(salary) OVER w AS min_wage
from employee_salary_1
WINDOW w AS (partition by department)
SETTINGS allow_experimental_window_functions = 1

觀察一下sql語句我們會發現，在分組窗的定義語句中，只有partition by，卻沒有了剛剛說的order by。這是因為在統計聚合中，我們無需用到排序方法，因此在分組窗中也就無需對指定列排序了。

  • 統計結果：

為了結果看起來規整刪掉了部分小數。

┌─name────┬─department─┬──────month─┬─salary─┬─count─┬─sum_wage─┬─avg_wage─┬─max_wage─┬─min_wage─┐
│ Davd    │ Finance    │ 2020-01-01 │   8000 │     3 │    28000 │     9333 │    10000 │     8000 │
│ Fancy   │ Finance    │ 2020-01-01 │  10000 │     3 │    28000 │     9333 │    10000 │     8000 │
│ George  │ Finance    │ 2020-01-01 │  10000 │     3 │    28000 │     9333 │    10000 │     8000 │
│ Haffman │ Marketing  │ 2020-01-01 │   6000 │     2 │    13000 │     6500 │     7000 │     6000 │
│ Ilaja   │ Marketing  │ 2020-01-01 │   7000 │     2 │    13000 │     6500 │     7000 │     6000 │
│ Ali     │ Sales      │ 2020-01-01 │   6000 │     5 │    34000 │     6800 │     9000 │     5000 │
│ Bob     │ Sales      │ 2020-01-01 │   6000 │     5 │    34000 │     6800 │     9000 │     5000 │
│ Cindy   │ Sales      │ 2020-01-01 │   5000 │     5 │    34000 │     6800 │     9000 │     5000 │
│ Elena   │ Sales      │ 2020-01-01 │   9000 │     5 │    34000 │     6800 │     9000 │     5000 │
│ Joey    │ Sales      │ 2020-01-01 │   8000 │     5 │    34000 │     6800 │     9000 │     5000 │
└─────────┴────────────┴────────────┴────────┴───────┴──────────┴──────────┴──────────┴──────────┘

開窗累計 | TODO

除了常規的統計值，在實際工作中，可能會碰到需要計算累計和的場景，例如計算累計分佈。這時，我們就需要按行去累計某個指定指標值了。

  • 視窗引數設定實現累計

視窗函式rows between 、range between的使用 - CSDN

1.3 同比/環比

1.4 range between

X 參考文獻

  • windows functions - clickhouse
  • clickhouse--開窗函式(window function)的用法 - CSDN

相關文章