mysql8窗口函数

mysql window函数

mysql 开窗函数demo&practice

分组求和2、和MySQL其它函数搭配 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)

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

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

-- 创建临时表create TEMPORARY TABLE all_salesselect 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;

窗口函数+子查询解决

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 = 1window 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 awhere 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), 查询的时候就可以引用了。