玩转MySQL(第二部分):窗口函数详解
最编程
2024-07-23 08:02:12
...
开窗函数的本质是聚合运算,但比聚合函数更灵活;每条记录都会执行并返回一条结果记录,而聚合函数+group by的返回结果记录会减少。
书写格式:
聚合函数/专用窗口函数 over([partition by 分区字段] [order by 排序字段][滑动窗口])
备注:
- 聚合函数用于求值,专用窗口函数用于排名,又叫序号函数;
- partition by指定分区,order by指定排序,[滑动窗口]指定窗口范围;
- 专用窗口函数条件下指定的滑动窗口不起作用;
- 适用场景:专用窗口函数用于排名,聚合函数用于按特定分区累计加总、求移动均值等。
功能说明:
-
聚合函数+over()
说明:无任何指定,默认计算所有行。
-- 查询所有员工的平均工资
select *,avg(sal) over() 平均工资 from emp;
-
聚合函数+over(partition by 分区字段)
说明:仅指定分区,默认计算分区内的所有行。
-- 计算各部门的平均工资
select *,avg(sal) over(partition by deptno) 部门平均工资 from emp;
-
聚合函数+over(partition by 分区字段 order by 排序字段)
说明:仅指定分区和排序,默认计算分区内的第一行到当前行。
-- 查询各部门按入职时间顺序的累计工资总和
select *,sum(sal) over(partition by deptno order by hiredate) 累计工资总和 from emp;
-
聚合函数+over(partition by 分区字段 order by 排序字段 滑动窗口)
说明:指定分区、排序和滑动窗口,会计算分区中滑动窗口内的行。
-- 查询各部门员工工资按从高到低排序后的移动平均值
select *,avg(sal) over(partition by deptno order by sal desc rows between 1 preceding and 1 following) 移动平均值
from emp;
-
序号函数:rank()、dense_rank()、row_number()
说明:同样可以指定分区partition by和排序order by
rank:序号不连续有重复;
dense_rank:序号连续有重复;
row_number:序号连续无重复。
select *,row_number() over(partition by deptno order by sal desc) 排名1,
dense_rank() over(partition by deptno order by sal desc) 排名2,
rank() over(partition by deptno order by sal desc) 排名3
from emp;
推荐阅读
-
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实现低通滤波,模糊一副图像
-
玩转Android应用安装:详解使用adb install命令的安装步骤(第二部分)
-
C++中的转换函数详解:第二部分 - 应用与用法
-
BI-SQL进阶教程:窗口函数讲解(第二部分)
-
玩转MySQL(第二部分):窗口函数详解
-
MySQL中的窗口函数:求和 OVER 语句详解
-
用MySQL实现窗口函数操作详解
-
深入学习Hive:第四部分 - 函数详解 (一节:单行函数、高级聚合函数、explode函数与窗口函数)
-
MySQL中的窗口函数详解
-
玩转Oracle:详解常用over窗口函数操作