原汁原味的ClickHouse窗口函数来啦!
由于公众号不再按时间线推送,如果不想错过精彩内容,请在关注公众号后,点击右上角 ... 设为星标,感谢支持。
在今年2月6号线上举行的 ClickHouse China Spring Meetup 中,朵夫为我们带来了 ClickHouse Features 2021 的分享,其中有非常多强大的新特性,幻灯片的下载地址如下:
https://presentations.clickhouse.tech/meetup50/new_features/
在众多的新特性中,我对开窗函数、自定义UDF、ZooKeeper优化等几项特别感兴趣,后续我也打算分别用几篇文章来展开说明。
今天主要想聊一下在分享中提到的 ClickHouse 原生的开窗函数,在此之前,我曾经专门写过两篇文章介绍如何在 CH 中变相实现开窗函数的功能,传送门如下:
使用ClickHouse快速实现同比、环比分析
如何在ClickHouse中实现RANK OVER排序
现在 ClickHouse 提供了正宗的实现,功能上使用起来真是比先前的奇技淫巧简单太多了。
这里我继续沿用先前文章的场景用例,对比看一看现在实现起来是多么的简便。
首先准备测试表:
CREATE TABLE test_data engine = Memory AS
WITH( SELECT ['A','A','A','A','B','B','B','B','B','A','59','90','80','80','65','75','78','88','99','70'])AS dict
SELECT dict[number%10+1] AS id, dict[number+11] AS val FROM system.numbers LIMIT 10
在此之前,如果要实现 row_number 和 dense_rank 的分组查询,需要借助arrayEnumerate 和 arrayEnumerateDense 这类数组函数,代码量巨大且嵌套复杂:
SELECT
id,
val,
row_number,
dense_rank,
uniq_rank
FROM
(
SELECT
id,
groupArray(val) AS arr_val,
arrayEnumerate(arr_val) AS row_number,
arrayEnumerateDense(arr_val) AS dense_rank,
arrayEnumerateUniq(arr_val) AS uniq_rank
FROM
(
SELECT *
FROM test_data
ORDER BY val ASC
)
GROUP BY id
)
ARRAY JOIN
arr_val AS val,
row_number,
dense_rank,
uniq_rank
ORDER BY
id ASC,
row_number ASC,
dense_rank ASC
而在新版本中(我使用的是 21.3.1 ),实现相同的功能只需要下面这样:
SELECT
id,
val,
rank() OVER w AS rank,
dense_rank() OVER w AS dense_rank,
row_number() OVER w AS row_number,
count(*) OVER w AS count,
sum(toInt32(val)) OVER w AS sum_v,
avg(toInt32(val)) OVER w AS avg_v,
max(toInt32(val)) OVER w AS max_v
FROM test_data
WINDOW w AS (PARTITION BY id ORDER BY val ASC range unbounded preceding)
ORDER BY id ASC
SETTINGS allow_experimental_window_functions = 1
┌─id─┬─val─┬─rank─┬─dense_rank─┬─row_number─┬─count─┬─sum_v─┬─────────────avg_v─┬─max_v─┐
│ A │ 59 │ 1 │ 1 │ 1 │ 1 │ 59 │ 59 │ 59 │
│ A │ 70 │ 2 │ 2 │ 2 │ 2 │ 129 │ 64.5 │ 70 │
│ A │ 80 │ 3 │ 3 │ 3 │ 4 │ 289 │ 72.25 │ 80 │
│ A │ 80 │ 3 │ 3 │ 4 │ 4 │ 289 │ 72.25 │ 80 │
│ A │ 90 │ 5 │ 4 │ 5 │ 5 │ 379 │ 75.8 │ 90 │
│ B │ 65 │ 1 │ 1 │ 1 │ 1 │ 65 │ 65 │ 65 │
│ B │ 75 │ 2 │ 2 │ 2 │ 2 │ 140 │ 70 │ 75 │
│ B │ 78 │ 3 │ 3 │ 3 │ 3 │ 218 │ 72.66666666666667 │ 78 │
│ B │ 88 │ 4 │ 4 │ 4 │ 4 │ 306 │ 76.5 │ 88 │
│ B │ 99 │ 5 │ 5 │ 5 │ 5 │ 405 │ 81 │ 99 │
└────┴─────┴──────┴────────────┴────────────┴───────┴───────┴───────────────────┴───────┘
10 rows in set. Elapsed: 0.003 sec.
可以看到,ClickHouse 现在支持了原生的:
分析函数 rank()、dense_rank()、row_number()
开窗函数 over(),且开窗函数也支持分组子句 partition by、排序子句 order by 和窗口子句 range/row
由于默认窗口子句是 range ,所以下面的写法是等价的:
PARTITION BY id ORDER BY val ASC range unbounded preceding
和
PARTITION BY id ORDER BY val ASC
接着我们再来看一看同比/环比功能,现在可以如何实现。
在此之前,实现同比/环比需要借助 neighbor 函数实现:
WITH toDate('2019-01-01') AS start_date
SELECT
toStartOfMonth(start_date + (number * 32)) AS date_time,
(number + 1) * 100 AS money,
neighbor(money, -12) AS prev_year,
neighbor(money, -1) AS prev_month
FROM numbers(16)
在新的版本中,虽然目前也还未实现 lead/lag 函数,但通过开窗函数的窗口子句就能变相实现该功能:
SELECT
date_time,
money,
any(money) OVER (ORDER BY money ASC ROWS BETWEEN 12 PRECEDING AND 12 PRECEDING) AS prev_year,
any(money) OVER (ORDER BY money ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_month
FROM
(
WITH toDate('2019-01-01') AS start_date
SELECT
toStartOfMonth(start_date + (number * 32)) AS date_time,
(number + 1) * 100 AS money
FROM numbers(16)
)
SETTINGS allow_experimental_window_functions = 1
┌──date_time─┬─money─┬─prev_year─┬─prev_month─┐
│ 2019-01-01 │ 100 │ 0 │ 0 │
│ 2019-02-01 │ 200 │ 0 │ 100 │
│ 2019-03-01 │ 300 │ 0 │ 200 │
│ 2019-04-01 │ 400 │ 0 │ 300 │
│ 2019-05-01 │ 500 │ 0 │ 400 │
│ 2019-06-01 │ 600 │ 0 │ 500 │
│ 2019-07-01 │ 700 │ 0 │ 600 │
│ 2019-08-01 │ 800 │ 0 │ 700 │
│ 2019-09-01 │ 900 │ 0 │ 800 │
│ 2019-10-01 │ 1000 │ 0 │ 900 │
│ 2019-11-01 │ 1100 │ 0 │ 1000 │
│ 2019-12-01 │ 1200 │ 0 │ 1100 │
│ 2020-01-01 │ 1300 │ 100 │ 1200 │
│ 2020-02-01 │ 1400 │ 200 │ 1300 │
│ 2020-03-01 │ 1500 │ 300 │ 1400 │
│ 2020-04-01 │ 1600 │ 400 │ 1500 │
└────────────┴───────┴───────────┴────────────┘
如上所示,这里是利用窗口子句,将 range 换成 row ,通过如下的句式实现:
any(value) over (.... rows between <offset> preceding and <offset> preceding), or following
这么使用下来,ClickHouse 开窗函数的语法和其他数据库中的用法基本无异,果然 CH 又变强大了呢
。
好了今天的分享就到这里吧,开窗函数目前完整的官方描述参见下面的地址:
https://github.com/ClickHouse/ClickHouse/blob/master/docs/en/sql-reference/window-functions/index.md#experimental-window-function
下一篇: 玩转API测试的神奇之旅:深度体验与反思
推荐阅读
-
OpenCV 高级图形用户界面(9)中更改指定窗口位置的函数 moveWindow 的使用--代码示例
-
OpenCV 高级图形用户界面(6)中获取指定窗口图像矩形区域的函数 getWindowImageRect 的使用--代码示例
-
14-傅里叶变换的代码实现-一、numpy实现傅里叶变换和逆傅里叶变换 1.numpy实现傅里叶变换numpy.fft.fft2实现傅里叶变换,返回一个复数数组(complex ndarray),也就是频谱图像numpy.fft.fftshift将零频率分量移到频谱中心(将左上角的低频区域,移到中心位置) 20*np.log(np.abs(fshift))设置频谱的范围。可以理解为,之前通过傅里叶变换得到复数的数组,是不能通过图像的方法展示出来的,需要转换为灰度图像(映射到[0,255]区间)需要注意的是1> 傅里叶得到低频、高频信息,针对低频、高频处理能够实现不同的目的2> 傅里叶过程是可逆的,图像经过傅里叶变换、逆傅里叶变换后,能够恢复到原始图像3> 在频域对图像进行处理,在频域的处理会反映在逆变换图像上 # 将绘制的图显示在窗口 %matplotlib qt5 import cv2 import numpy as np import matplotlib.pyplot as plt img = cv2.imread(r"image\lena.bmp",cv2.IMREAD_GRAYSCALE) # 傅里叶变换 f = np.fft.fft2(img) # 移动中心位置 fshift = np.fft.fftshift(f) # 调整值范围 result = 20*np.log(np.abs(fshift)) plt.subplot(1,2,1) plt.imshow(img,cmap=plt.cm.gray) plt.title("original") plt.axis("off") plt.subplot(1,2,2) plt.imshow(result,cmap=plt.cm.gray) plt.title("result") plt.axis("off") plt.show 傅里叶变换的频谱图像: 2.numpy实现逆傅里叶变换numpy.fft.ifft2实现逆傅里叶变换,返回一个复数数组(complex ndarray)numpy.fft.ifftshiftfftshift函数的逆函数,将中心位置的低频,重新移到左上角iimg = np.abs(逆傅里叶变化结果)设置值的范围,映射到[0,255]区间 # 将绘制的图显示在窗口 %matplotlib qt5 import cv2 import numpy as np import matplotlib.pyplot as plt img = cv2.imread(r"image\boat.bmp",cv2.IMREAD_GRAYSCALE) # 傅里叶变换 f = np.fft.fft2(img) fshift = np.fft.fftshift(f) # 逆傅里叶变换 ishift = np.fft.ifftshift(fshift) iimg = np.fft.ifft2(ishift) iimg = np.abs(iimg) plt.subplot(1,2,1) plt.imshow(img,cmap=plt.cm.gray) plt.title("original") plt.axis("off") plt.subplot(1,2,2) plt.imshow(iimg,cmap=plt.cm.gray) plt.title("iimg") plt.axis("off") plt.show 将一副图像,进行傅里叶变换和逆傅里叶变换后,进行对比(一样的) 实例:通过numpy实现高通滤波,保留图像的边缘信息 获取图像的形状rows,cols = img.shape获取图像的中心点crow,ccol = int(rows/2),int(cols/2)将频谱图像的中心区域(低频区域)设置为0(黑色)fshift[crow-30:crow+30,ccol-30:ccol+30] = 0 # 将绘制的图显示在窗口 %matplotlib qt5 import cv2 import numpy as np import matplotlib.pyplot as plt img = cv2.imread(r"image\boat.bmp",cv2.IMREAD_GRAYSCALE) # 傅里叶变换 f = np.fft.fft2(img) fshift = np.fft.fftshift(f) # 高通滤波 rows,cols = img.shape crow,ccol = int(rows/2),int(cols/2) fshift[crow-30:crow+30,ccol-30:ccol+30] = 0 # 逆傅里叶变换 ishift = np.fft.ifftshift(fshift) iimg = np.fft.ifft2(ishift) iimg = np.abs(iimg) plt.subplot(1,2,1) plt.imshow(img,cmap=plt.cm.gray) plt.title("original") plt.axis("off") plt.subplot(1,2,2) plt.imshow(iimg,cmap=plt.cm.gray) plt.title("iimg") plt.axis("off") plt.show 使用numpy实现高通滤波的实验结果: 二、opencv实现傅里叶变换和逆傅里叶变换 1.opencv实现傅里叶变换 返回结果 = cv2.dft(原始图像,转换标识)1> 返回结果:是双通道的,第一个通道是结果的实数部分,第二个通道是结果的虚数部分2> 原始图像:输入图像要首先转换成np.float32(img)格式3> 转换标识:flags = cv2.DFT_COMPLEX_OUTPUT,输出一个复数阵列numpy.fft.fftshift将零频率分量移到频谱中心(将左上角的低频区域,移到中心位置)调整频谱的范围,将上面频谱图像的复数数组,转换为可以显示的灰度图像(映射到[0,255]区间)返回值 = 20*np.log(cv2.magnitude(参数1,参数2))1> 参数1:浮点型X坐标值,也就是实部2> 参数2:浮点型Y坐标值,也就是虚部 # 将绘制的图显示在窗口 %matplotlib qt5 import cv2 import numpy as np import matplotlib.pyplot as plt img = cv2.imread(r"image\lena.bmp",cv2.IMREAD_GRAYSCALE) # 傅里叶变换 dft = cv2.dft(np.float32(img),flags = cv2.DFT_COMPLEX_OUTPUT) # 移动中心位置 dftShift = np.fft.fftshift(dft) # 调整频谱的范围 result = 20*np.log(cv2.magnitude(dftShift[:,:,0],dftShift[:,:,1])) plt.subplot(1,2,1) plt.imshow(img,cmap=plt.cm.gray) plt.title("original") plt.axis("off") plt.subplot(1,2,2) plt.imshow(result,cmap=plt.cm.gray) plt.title("result") plt.axis("off") plt.show 傅里叶变换的频谱图像: 2.opencv实现逆傅里叶变换返回结果 = cv2.idft(原始数据)1> 返回结果:取决于原始数据的类型和大小2> 原始数据:实数或者复数均可numpy.fft.ifftshiftfftshift函数的逆函数,将中心位置的低频,重新移到左上角调整频谱的范围,映射到[0,255]区间返回值 = cv2.magnitude(参数1,参数2)1> 参数1:浮点型X坐标值,也就是实部2> 参数2:浮点型Y坐标值,也就是虚部 # 将绘制的图显示在窗口 %matplotlib qt5 import cv2 import numpy as np import matplotlib.pyplot as plt img = cv2.imread(r"image\lena.bmp",cv2.IMREAD_GRAYSCALE) # 傅里叶变换 dft = cv2.dft(np.float32(img),flags = cv2.DFT_COMPLEX_OUTPUT) dftShift = np.fft.fftshift(dft) # 逆傅里叶变换 ishift = np.fft.ifftshift(dftShift) iimg = cv2.idft(ishift) iimg = cv2.magnitude(iimg[:,:,0],iimg[:,:,1]) plt.subplot(1,2,1) plt.imshow(img,cmap=plt.cm.gray) plt.title("original") plt.axis("off") plt.subplot(1,2,2) plt.imshow(iimg,cmap=plt.cm.gray) plt.title("inverse") plt.axis("off") plt.show 将一副图像,进行傅里叶变换和逆傅里叶变换后,进行对比(一样的) 实例:通过opencv实现低通滤波,模糊一副图像
-
原汁原味的ClickHouse窗口函数来啦!
-
掌握Hive的强大工具:实用且高效的窗口函数详解
-
SparkSQL中的窗口函数基础,你不得不知的关键点
-
理解与运用MySQL中的窗口函数
-
必备的 Hive SQL 窗口函数及其在大厂面试中的常见问题
-
如何轻松借助ClickHouse进行同比增长和环比计算(利用窗口函数)
-
ClickHouse窗口函数详解全面指南