窗口函数
例子
1 北京 海淀 10
2 北京 朝阳 20
3 上海 黄埔 30
4 上海 长宁 10
CREATE TABLE sales(
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 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;
分类
数据表定义
CREATE TABLE goods(
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');
序号函数
ROW_NUMBER()函数
ROW_NUMBER() 函数能够对数据中的序号进行顺序显示。
Rank() 函数
使用 RANK() 函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为 1、1、3。
举例：使用 RANK() 函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
可以看到，使用RANK()函数得出的序号为1、2、2、4，相同价格的商品序号相同，后面的商品序号是不 连续的，跳过了重复的序号。
DENSE_RANK()函数
DENSE_RANK() 函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为 1、1、2。
举例：使用 DENSE_RANK() 函数获取 goods 数据表中各类别的价格从高到低排序的各商品信息。
使用 DENSE_RANK() 函数获取 goods 数据表中类别为 “女装 / 女士精品” 的价格最高的 4 款商品信息。
SELECT
*
FROM
(
SELECT
DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
id,
category_id,
category,
`name`,
price,
stock
FROM
goods
) t
WHERE
category_id = 1
AND row_num <= 3
分布函数
PERCENT_RANK() 函数
PERCENT_RANK() 函数是等级值百分比函数。按照如下方式进行计算。
(rank - 1) / (rows - 1)
其中，rank 的值为使用 RANK() 函数产生的序号，rows 的值为当前窗口的总记录数。
计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
SELECT RANK() OVER w as r,
PERCENT_RANK() OVER w as pr,
id, category_id, category, `name`, price, stock
FROM goods
WHERE category_id = 1 WINDOW w as (
PARTITION BY category_id ORDER BY price DESC
);
CUME_DIST() 函数
CUME_DIST() 函数主要用于查询小于或等于某个值的比例。
查询 goods 数据表中小于或等于当前价格的比例。
SELECT CUME_DIST() OVER(PARTITION BY category_id ORDER BY price ASC)
AS cd, id, category, `name`, price
FROM goods;
前后函数
LAG(expr,n)函数
AG(expr,n) 函数返回当前行的前 n 行的 expr 的值
SELECT id, category, NAME, price, pre_price, price - pre_price AS diff_price
FROM (
SELECT id, category, NAME, price,LAG(price,1) OVER w AS pre_price
FROM goods
WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;
LEAD(expr,n) 函数
LEAD(expr,n) 函数返回当前行的后 n 行的 expr 的值。
首尾函数
FIRST_VALUE(expr) 函数
FIRST_VALUE(expr) 函数返回第一个 expr 的值。
LAST_VALUE(expr) 函数
LAST_VALUE(expr) 函数返回最后一个 expr 的值。
其他函数
NTH_VALUE(expr,n)
NTH_VALUE(expr,n) 函数返回第 n 个 expr 的值。
查询 goods 数据表中排名第 2 和第 3 的价格信息。
SELECT id, category, NAME, price,NTH_VALUE(price,2) OVER w AS second_price,
NTH_VALUE(price,3) OVER w AS third_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);
NTILE(n) 函数
NTILE(n) 函数将分区中的有序数据分为 n 个桶，记录桶编号。
将 goods 表中的商品按照价格和类别分为 3 组。
SELECT NTILE(3) OVER w as nt, id, category, `name`, price
FROM goods WINDOW w as (PARTITION BY category_id ORDER BY price)
ORDER BY nt;
总结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。