学习笔记-分析函数

1 分析函数
1.1 使用分析函数前后对比
假设我现在有这样一个数据表，它显示了某购物网站在每个城市每个区的销售额：

id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);

INSERT INTO sales(city,county,sales_value)
VALUES
('北京','海淀',10.00),
('北京','朝阳',20.00),
('上海','黄埔',30.00),
('上海','长宁',10.00);

SELECT * FROM sales;

图片.png

需求：现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率。
如果用分组和聚合函数，就需要分好几步来计算。
```select sales.CITY,
sales.COUNTY,
sales.SALES_VALUE/t.sum_v BL_IN_CITY
from sales
RIGHT JOIN (select CITY,SUM(SALES_VALUE) sum_v from sales group by CITY
union all
select 'QG',SUM(SALES_VALUE) sum_v from sales ) t
ON sales.city = t.city
--自己写的SQL，且还少了计算总销售额的比率。因为一个SQL里无法完成这么多需求
图片.png



同样的查询，如果用窗口函数，就简单多了。我们可以用下面的代码来实现：
      ```SELECT city AS 城市, 
             county AS 区, 
             sales_value AS 区销售额,
             SUM(sales_value) OVER(PARTITION BY city) AS 市销售额,
             sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率,
             SUM(sales_value) OVER() AS 总销售额,
             sales_value/SUM(sales_value) OVER() AS 总比率
        FROM sales
    ORDER BY city, 
             county;

窗口函数就相当于把一张表中的数据按照OVER中的分类方式进行分类，然后再将单行数据和分类统计的结果进行计算。
在这种需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。
1.2 分析函数分类
窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表：
图片.png
1.3 语法结构
分析函数的语法结构是
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）
1.4 分类讲解
创建测试数据

             (
                          id          INT PRIMARY KEY AUTO_INCREMENT,
                          category_id INT,
                          category    VARCHAR(15),
                          NAME        VARCHAR(30),
                          price       DECIMAL(10,2),
                          stock       INT,
                          upper_time  DATETIME
             );

INSERT INTO goods(category_id,category,NAME,price,stock,upper_time) VALUES
(1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'),
(1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'),
(2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'),
(2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'),
(2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'),
(2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'),
(2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'),
(2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00');

1.4.1 序号函数
1．ROW_NUMBER()函数
ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
举例：查询 goods 数据表中每个商品分类下价格降序排列的各个商品信息。

    ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,id, 
    category_id, 
    category, 
    NAME, 
    price, 
    stock
FROM goods;

图片.png

举例：查询 goods 数据表中每个商品分类下价格最高的3种商品信息。

FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
            id, category_id, category, NAME, price, stock
    FROM goods) t
WHERE row_num &lt;= 3;

2．RANK()函数
使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。
举例：使用RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。

RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;

图片.png

3．DENSE_RANK()函数
DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。
举例：使用DENSE_RANK()函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。

DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num,
id, category_id, category, NAME, price, stock
FROM goods;

图片.png

1.4.2 分布函数
1．PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
(rank - 1) / (rows - 1)
其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数。
举例：计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。

PERCENT_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS pr,
id, category_id, category, NAME, price, stock
FROM goods
WHERE category_id = 1;

图片.png

2．CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
举例：查询goods数据表中小于或等于当前价格的比例。

id, category, NAME, price
FROM goods;

图片.png

1.4.3 前后函数
1．LAG(expr,n)函数
LAG(expr,n)函数返回当前行的前n行的expr的值。
举例：查询goods数据表中前一个商品价格与当前商品价格的差值。

FROM (
	SELECT id, category, NAME, price,
	LAG(price,1) OVER(PARTITION BY category_id ORDER BY price) AS pre_price
 FROM goods ) t;

图片.png

2．LEAD(expr,n)函数
LEAD(expr,n)函数返回当前行的后n行的expr的值。
举例：查询goods数据表中后一个商品价格与当前商品价格的差值。

FROM (
	SELECT id, category, NAME, price,
	LEAD(price,1) OVER(PARTITION BY category_id ORDER BY price) AS behind_price
 FROM goods ) t;

图片.png

1.4.4 首尾函数
1．FIRST_VALUE(expr)函数
FIRST_VALUE(expr)函数返回第一个expr的值。
举例：按照价格排序，查询第1个商品的价格信息。

	id, category, NAME, price, stock,
	FIRST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price) AS
	first_price
FROM goods;

图片.png

2．LAST_VALUE(expr)函数
LAST_VALUE(expr)函数返回最后一个expr的值。
举例：按照价格排序，查询最后一个商品的价格信息。

 	id, category, NAME, price, stock,
 	LAST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price ) AS last_price
FROM goods;

图片.png

但为什么last_value的执行不是预期的结果？
在使用分析函数的时候，缺省的WINDOWING范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，当使用last_value分析函数的时候，在进行比较的时候从当前行向前进行比较，所以前面的语句执行的结果是正确，但不是预期的。
需要在over从句中加上{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [frame_exclusion ]

 	id, category, NAME, price, stock,
 	LAST_VALUE(price) OVER (PARTITION BY category_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_price
 FROM goods;

图片.png
注意这里加了RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
1.4.4 其他函数
1．NTH_VALUE(expr,n)函数
NTH_VALUE(expr,n)函数返回第n个expr的值。
举例：查询goods数据表中排名第2和第3的价格信息。

 	id, category, NAME, price,
 	NTH_VALUE(price,2) OVER (PARTITION BY category_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_price,
	NTH_VALUE(price,3) OVER (PARTITION BY category_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_price
FROM goods ;

图片.png

2．NTILE(n)函数
NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。
举例：将goods表中的商品按照价格分为3组。

 	NTILE(3) OVER (PARTITION BY category_id ORDER BY price) AS nt,id, category, NAME, price
FROM goods;

图片.png

1.5 小结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

作者