开窗函数(二)

之前的文章里,白茶曾经描述过关于开窗函数的内容,本期我们来继续这个话题。

通过之前的介绍,相信大家也知道了,我们经常使用的开窗函数除了排名函数以外,还有聚合函数。

语法

<开窗函数> over (partition by <用于分组的列> order by <用于排序的列>)

本期呢,会给大家展示聚合函数在开窗函数中的应用。

聚合函数:sum,avg,count,max,min。

使用实例

案例数据:

在白茶本机的数据库中存在名为“CaseData”的数据库。"Dim_Product"产品表。

例子1:使用Sum函数,对Price进行聚合,按照ProductGroup进行分组。

SELECT ProductGroup,ProductName,SUM(price) OVER (PARTITION BY ProductGroup ORDER BY price) AS Sum_Price
FROM Dim_Product

结果如下:

从结果上我们可以看出,这段代码按照ProductGroup的分组方式,遵循Price从小到大的顺序进行累积。

某些场合,我们可以用来计算累计求和,例如:YTD。

如果我们直接使用Sum的话,结果是什么样?我们看一下。

代码如下:

SELECT ProductGroup,ProductName,SUM(price) AS Sum_Price
FROM Dim_Product
GROUP BY ProductName,ProductGroup

结果如下:

数据对比原Price其实是没有变化的,如果我们将ProductName去掉,则会按照Group进行汇总。

例子2:使用Avg函数,对Price进行聚合,按照ProductGroup进行分组。

SELECT *,AVG(price) OVER (PARTITION BY ProductGroup ORDER BY price) AS Avg_Price
FROM Dim_Product

结果如下:

从结果上我们可以看出,这段代码按照ProductGroup的分组方式,进行平均值计算。

如果没明白的话,白茶换个说法相信小伙伴不会陌生:DAX中的移动平均。

例子3:使用Count函数,对Price进行聚合,按照ProductGroup进行分组。

SELECT *,COUNT(price) OVER (PARTITION BY ProductGroup ORDER BY price) AS Count_Price
FROM Dim_Product

结果如下:

从结果上我们可以看出,这段代码按照ProductGroup的分组方式,进行行数计算。

当然,这个结果与Rank函数有点类似,区别在于Rank函数不保留后面的排位序数,而Count是不保留前面的排位序数。

Rank:1、2、3、3、5。

Count:1、2、4、4、5。

例子4:使用Max函数,对Price进行聚合,按照ProductGroup进行分组。

SELECT *,MAX(price) OVER (PARTITION BY ProductGroup ORDER BY price) AS Max_Price
FROM Dim_Product

结果如下:

可能看到这里小伙伴会有疑惑,这不是没变化么?

别急,我们与例子5一起说。

例子5:使用Min函数,对Price进行聚合,按照ProductGroup进行分组。

SELECT *,MIN(price) OVER (PARTITION BY ProductGroup ORDER BY price) AS Min_Price
FROM Dim_Product

结果如下:

Max函数在开窗函数使用中,会进行迭代比较取最大值。因为本身就是按照Price进行排序的,所以大家看不出来差距。

Min函数是迭代取最小值,按照Price升序,结果对比非常明细。

总结:

函数名称 开窗适用场景
SUM 适用于累计求和,例如:YTD
AVG 适用于移动平均的计算
COUNT 适用于排名,注意与Rank的区别
MAX 组内取最大值
MIN 组内取最小值

这里是白茶,一个PowerBI的初学者。