窗口函数

1.窗口函数解析：
其相较于聚合函数的区别就是，聚合函数是按照某个特性，如对一类元组进行求和，将求和的结果变成一项进行呈现，即是多变一的过程。

而窗口函数则是在得到这个求和结果后，把这个结果赋给这一类元组的所有元组中去，即仍保留了多个元组的情况

使用实例：

#任务：算出每个区域的销售额占自己所在的市的比例和占全国的比例
SELECT *,sales_value / SUM(sales_value) OVER() AS '占国家比例', 
sales_value / SUM(sales_value) OVER(PARTITION BY city) AS '占所在市的比例'
FROM sales
ORDER BY id;

SUM(sales_value) OVER (PARTITION BY CITY):即可得到所有城市的销售额总额，且这个得到的总额可以用于做所有城市中的地区销售额所占的比例的运算。

 

总结：在使用窗口函数时，都会跟上OVER(...)对表按照自己的需要进行处理,例如:1.PARTITION BY 属性：根据某种属性进行分类     2.ORDER BY 属性:根据某种属性进行排序

 

2.一些常用的窗口函数类型：

第一类：序号函数

(1).ROW_NUMBER():按照所给定的分组和排序方式对每个分组都从1开始进行依次编号，并输出这个分组的对应编号

实例：

#例子：查询goods表中每个商品分类下降排序的各个商品的信息
SELECT ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num
FROM goods;

(2).RANK()

RANK()与ROW_NUMBER()的区别：排序时如果碰到两个相等的值，则RANK的排序是两人名次一样(如都是第2名，那么再下一个人#就从第4名开始继续往下)

ROW_NUMBER则是(其中一个人为第2名，另一个人为第3名)
实例：

#举例：使用RANK()函数,查询goods表中每个商品分类下降排序的各个商品的信息
SELECT RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num
FROM goods;

(3).DENSE_RANK()

DENSE_RANK()与RANK()的区别:RANK的排序是两人名次一样(如都是第2名那么再下一个人就从第4名开始继续往下)
而DENSE_RANK则是两人名次一样(如都是第2名那么再下一个人就从第3名开始继续往下)

实例：

#举例：使用DENSE_RANK()函数获取goods数据表中各类别的价格从高到低的各商品信息
SELECT DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num
FROM goods;

 

第二类：分布函数

(1).PERCENT_RANK()函数

PERCEMT_RANK() = RANK() - 1 / ROWS() - 1; (RANK()即当前元组所在编号,ROWS()即为这个分组一共有多少个元组)

实例：

#举例：计算goods表中名称为"女装/女士精品类别下商品的PERCENT_RANK的值"
SELECT RANK() OVER w AS r
PERCENT_RANK() OVER w AS pr,
FROM goods
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

WINDOW w AS (PARTITION BY category_id ORDER BY price DESC)即：使w与(PARTITION BY category_id ORDER BY price DESC)等价

 

(2).CUME_DIST()函数：

CUME_DIST():即当前的元组在其所在的整个分组中所占的位置或者说是小于等于它的元组所占的比例(越大则占据在越前面,第1则为1,最后则为1/ROWS())

#举例：查询goods表中小于或等于当前价格的比例
SELECT CUME_DIST() OVER (PARTITION BY category_id ORDER BY price) AS cd
FROM goods;

 

第三类：前后函数

(1).LAG(expr,n)函数

LAG(expr,n):expr为要查的要输出的属性,n表示前n个,即获得目前位置的第前n个元组的expr属性的值

#举例：查询goods表中当前商品的前一个商品的价格
SELECT LAG(price,1) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods;

 

(2).LEAD(expr,n)函数

LEAD(expr,n):expr为要查的要输出的属性,n表示后n个,即获得目前位置的第后n个元组的expr属性的值

#举例：查询goods表中当前商品的后一个商品的价格
SELECT LEAD(price,1) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods;

 

第四类：首尾函数

(1).FIRST_VALUE(expr):返回所在表的第一个值

#举例：查询goods表中第一个商品的价格
SELECT FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods;

(2).LAST_VALUE(expr):返回所在表的最后一个值

#举例：查询goods表中最后一个商品的价格
SELECT LAST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods;

 

第五类：其他函数

(1).NTH_VALUE(expr,n):返回所在表的第n个值

#举例：查询goods表中第3个商品的价格
SELECT NTH_VALUE(price,3) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods;

(2).NTILE(n)函数:将所在表分为n组

#举例：将goods表中的商品按价格分为3组
SELECT NTILE(3) OVER (PARTITION BY category_id ORDER BY price) AS pre_price
FROM goods;