mysql 开窗函数demo&practice

注意: mysql8 才有开窗函数, mysql5.7以下不要阅读。

分组求和

create table sales (id int PRIMARY KEY auto_increment,city varchar(15),county varchar(15),sales_value DECIMAL
);insert into sales values 
(null, '北京市', '朝阳区', 30),
(null, '北京市', '海定区', 40),
(null, '上海市', '浦东新区', 30),
(null, '上海市', '徐汇区', 40)

需求:现在计算这个网站在每个城市的销售总额、在全国的销售总额、
每个区的销售额占所在城市销售额中的比率,以及占总销售额中的比率。

1. 使用临时表 + 连接查询解决

-- 创建临时表
create TEMPORARY TABLE all_sales
select sum(sales_value) as sales_value from sales;select * from all_sales;-- 城市销售总额临时表
create TEMPORARY TABLE city_sales 
select city, sum(sales_value) as sales_value from sales 
group by city;select * from city_sales;SELECTa.*,b.sales_value AS 城市销售总额,c.sales_value AS 全国销售总额 
FROMsales aJOIN city_sales b ON a.city = b.cityJOIN all_sales c;

2. 窗口函数+子查询解决

select 
(b.sales_value / b.city_val) as city_per,
(b.sales_value / b.all_val) as all_per,
b.* from 
(select sum(a.sales_value) over(partition by a.city) as city_val, sum(a.sales_value) over() as all_val, a.* from sales a
) as b;

partition by xxx, 按字段分区，可以理解分组，但是分组太鸡肋了。这个分组原来有多少行，现在就有多少行的。

使用语法小结：配合over使用

MySQL函数 over(开窗函数, 如分区，排序)# 如果没有函数
MySQL函数 over() as xxx

2. 和MySQL其它函数搭配

create table goods (id int primary key auto_increment,category_id int,category varchar(15),name varchar(50),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.1 ROW NUMBER() 函数
#ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
#举例:查询goods数据表中每个商品分类下价格降序排列的各个商品信息。
select ROW_NUMBER() over(PARTITION by a.category_id order by a.price desc) as row_num, a.* from goods a;#1.2 RANK() 函数
#使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3。
#举例:使用RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息。
select RANK() over(partition by a.category_id order by a.price desc) as row_num, a.* from goods a;# DENSE_RANK()函数对序号进行并列排序,并且不会跳过重复的序号，比如序号为1、1、2。
# 举例:使用DENSE _RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息。
select DENSE_RANK() over(PARTITION by a.category_id order by price desc) as row_num, a.* from goods a;#2.1 PERCENT RANK() 函数
#举例:计算goods数据表中名称为“女装/女士精品"的类别下的商品的PERCENT_RANK值。
# PERCENT_RANK() 是等级值百分比函数 
# 计算公式: (rank - 1) / (rows - 1), rank值为rank()函数产生的序号, rows为当前窗口的总记录数
select RANK() over(w) as v1, PERCENT_RANK() over(w) as v2, a.* from goods a where a.category_id = 1
window w as (partition by a.category_id order by a.price desc);-- ps: 使用 window w as (sql exp...) 类似的表示封装窗口函数, 然后用mysql特定的函数 + over(窗口函数) 调用。-- 2. CUME_DIST()函数
-- CUME_DIST()函数主要用于查询小于或等于某个值的比例。
-- 举例:查询goods数据表中小于或等于当前价格的比例。
select CUME_DIST() over(w) as v1, a.* from goods a
where 1=1 window w as (order by price);-- 3. 前后函数
-- 1. LAG(expr,n)函数
-- LAG(expr,n)函数返回当前行的前n行的expr的值。
-- 举例:查询Goods数据表中前一个商品价格与当前商品价格的差值.-- 查询前一个商品的价格... 
select LAG(a.price, 1) over(w) as last_price, a.* from goods a 
window w as (order by a.id);select (b.last_price - b.price) as diff_price, b.* from 
(select LAG(a.price, 1) over(w) as last_price, a.* from goods a window w as (order by a.id)
) as b;-- 2. LEAD(expr,n)函数
-- LEAD(expr,n)函数返回当前行的后n行的expr的值。
-- I举例:查询goods数据表中后一个商品价格与当前商品价格的差值。
select LEAD(a.price, 1) over(w) as behind_price, a.* from goods a 
window w as (order by a.price);-- 4.首尾函数
-- 1. FIRST_VALUE(expr)函数
-- FIRST_VALUE(expr)函数返回第一个expr的值。
-- 按照价格排序，查询第1个商品的价格信息。
select FIRST_VALUE(a.price) over(w) as v, a.* from goods a 
window w as (order by a.price);-- 5.其他函数
-- 1. NTH_VALUE(expr,n)函数
-- NTH_VALUE(expr,n)函数返回第n个expr的值。
-- 举例:查询goods数据表中排名第2和第3的价格信息。
select NTH_VALUE(a.price, 2) over(w) as second_val, NTH_VALUE(a.price,3) over(w) as third_val, a.* from goods a 
window w as (partition by a.category_id);-- 2. NTILE(n)函数
-- NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。
-- 举例:将goods表中的商品按照价格分为3组。
select NTILE(3) over(w) as group_num, a.* from goods a 
window w as (partition by a.category_id order by a.price);

总结：能够分组，不会影响查询行数，可以在分组的基础上做数据操作。
上面的window写法，类似声明了变量 window 别名 as (xxxx), 查询的时候就可以引用了。