Day5-SQL高级处理(Datawhale)

---

目录

一、窗口函数概念

1.1 窗口函数概念及基本的使用方法

二、窗口函数类别

1. 专用窗口函数

2. 聚合函数 

三、窗口函数

计算移动平均值

四、GROUPING运算符

1. ROLLUP

五、练一练

练习1

练习2

练习3

总结

---

一、窗口函数概念

1.1 窗口函数概念及基本的使用方法

窗口函数也称OLAP(OnLine Analytical Processing)函数，对数据库数据进行实时分析处理

窗口函数可以有选择的对某一部分数据进行汇总、计算和排序

<窗口函数> OVER ( PARTITION BY <列名>
                 ORDER BY <排序用列名>)

PARTITION BY 用来分组，选择要看哪个窗口，类似于GROUP BY 子句的分组功能，但是不具备GROUP BY的汇总功能， 不会改变原始表中记录的行数，也就是只有将数据分组展示的功能。

ORDER BY 用来排序，决定窗口内，按照哪种规则来排序

SELECT product_name,
    product_type,
    sale_price,
    RANK() OVER (PARTITION BY product_type
                 ORDER BY sale_price) AS ranking
FROM product​

根据返回的结果，我们可以看到窗口函数实现了在product_type上根据sale_price对商品进行了分组排序

二、窗口函数类别

可以分为两类：

• SUM、MAX、MIN等聚合函数
• RANK、DENSE_RANK、ROW_NUMBER等排序

1. 专用窗口函数

• RANK()

是跳跃排序，两个第二名下来就是第四名

• DENSE_RANK()

是连续的排序，两个第二名仍然跟着第三名

• ROW_NUMBER()

是没有重复值的排序(即使两条记录相同，序号也不重复的)，不会有同名次

SELECT product_name,
	   product_type,
	   sale_price,
	   RANK() OVER (ORDER BY sale_price) AS ranking,
		 DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking,
		 ROW_NUMBER() OVER (ORDER BY sale_price) AS row_n
FROM product

2. 聚合函数 

SELECT product_name,
	   product_type,
	   sale_price,
	   SUM(sale_price) OVER (ORDER BY product_id) AS cumsum,
	   AVG(sale_price) OVER (ORDER BY product_id) AS average
FROM product

当前所在行及之前所有行的合计或均值

窗口函数注意事项：

• 原则上，窗口函数只能在SELECT子句中使用
• 窗口函数OVER中的ORDER BY子句并不会影响最终结果的排序，只是用来决定窗口函数按何种顺序计算

 

三、窗口函数计算移动平均值

窗口函数可以指定详细的汇总范围，该汇总范围称为框架。（***实用性高***）

<窗口函数> OVER (ORDER BY <排序用列名>
                ROWS N PRECEDING )
                
<窗口函数> OVER (ORDER BY <排序用列名>
                ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING("之前")，将框架指定为"截止到之前n行"，加上自身行

FOLLOWING("之后")，将框架指定为"截止到之后n行"，加上自身行

BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为"之前1行"+"之后1行"+”自身“

往前两位的移动平均值

SELECT product_id,
	   product_name,
	   sale_price,
	   AVG(sale_price) OVER (ORDER BY product_id
	   			ROWS 2 PRECEDING) AS moving_avg
FROM product

我们会发现窗口函数来进行累计求和是非常简单的事情，比我们之前使用关联子查询和JOIN要简单的多。

往前1位往后1位的移动平均值

SELECT product_id,
	   product_name,
	   sale_price,
	   AVG(sale_price) OVER (ORDER BY product_id
	   			ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM product

四、GROUPING运算符

1. ROLLUP

计算合计及小计。常规的GROUP BY 只能得到每个分类的小计，如果需要计算分类的合计，可以使用ROLLUP关键字

SELECT product_type,
	   regist_date,
	   SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type,
	 regist_date
WITH ROLLUP

五、练一练

练习1

以下SQL运行的最终结果是，求得当前所在行及之前的最大值

SELECT product_id,
	   product_name,
	   sale_price,
	   MAX(sale_price) OVER (ORDER BY product_id) AS CURRENT_MAX_PRICE
FROM product

练习2

使用product表，计算出按照regist_date升序进行排序的各日期的sale_price的总额。排序是需要将登记日期为NULL的“运动T恤”排在第一位（将他看作比其他日期早）

原表的数据如上，按照regist_date的升序进行排序，如果是想要将相同的日期合并，则直接使用GROUP BY 和ORDER BY就可以实现，对于升序来讲NULL值会直接排在第一位。结果如下：

SELECT regist_date,
       SUM(sale_price)
FROM product
GROUP BY regist_date

如果我们使用的是窗口函数，我们会发现相同的日期不会合并

SELECT  regist_date,
	SUM(sale_price) OVER (PARTITION BY regist_date 
			      ORDER BY regist_date) AS sale_sum
FROM product

我们尝试直接使用ORDER BY查看结果

---直接使用ORDER BY 
SELECT  regist_date,
	sale_price 
FROM product
ORDER BY regist_date

比较上面的两个结果跟我们发现使用不合并的情况下使用窗口函数和直接使用ORDER BY 产生的一样的效果，原因是PARTITION BY只有分组的效果，不会进行合并，并且我们将regist_date作为了结果字段。

依据我对这个题目的理解，我认为日期是需要合并的，也就是使用GROUP BY是我们想要的结果。

练习3

思考题：

1. 窗口函数不指定PARTITION BY的效果是什么？

指定PARTITION BY

SELECT product_name,
    product_type,
    sale_price,
    RANK() OVER (PARTITION BY product_type
                 ORDER BY sale_price) AS ranking
FROM product

不指定PARTITION BY

SELECT product_name,
    product_type,
    sale_price,
    RANK() OVER (ORDER BY sale_price) AS ranking
FROM product 

通过上面的两个例子，我们会发现使用了PARTITION BY会有一个分组的小组，而没有使用的则是在整个表上进行动作的，其实相当于我们没有窗口函数的效果，但是排序的序号必须有包含 ORDER BY 的 OVER 子句

 

2. 窗口函数为什么只能在SELECT子句中使用？实际上在ORDER BY子句使用并不会报错？

对比两种情况：

第一种：SELECT 和 ORDER BY 都用窗口函数。

这种情况下，由于ORDER BY的执行顺序靠后，我们可以直接使用ORDER BY ranking，得到的是一样的结果，不需要再写一遍窗口函数。

SELECT product_name,
    product_type,
    sale_price,
    RANK() OVER (PARTITION BY product_type
                 ORDER BY sale_price) AS ranking
FROM product
ORDER BY RANK() OVER (PARTITION BY product_type
                 ORDER BY sale_price)

我们将ORDER BY 的窗口函数替换为字段名

SELECT product_name,
    product_type,
    sale_price,
    RANK() OVER (PARTITION BY product_type
                 ORDER BY sale_price) AS ranking
FROM product
ORDER BY ranking

 第二种：SELECT中不用窗口函数，ORDER BY中使用窗口函数

这种效果其实跟上面的一致，但是存在几个问题，第一个是我们没有办法很清楚的知道排序的顺序是怎么样的，另外，这种在排序中分组排序后再排序的应用场景几乎没有，只有当极特殊的情况，我们需要这种排序并且不想显示排序的顺序的时候，我觉得可以使用

SELECT product_name,
    product_type,
    sale_price
FROM product
ORDER BY RANK() OVER (PARTITION BY product_type
                 ORDER BY sale_price)

综合以上两种情况，在大多数的情况下，我们基本不会在ORDER BY中使用窗口函数，大多数情况下会在SELECT中使用窗口函数 。

---

总结

1. 窗口函数对于我们移动求值非常有用

2. 求累计值时我们用窗口函数更方便