首页 > 数据库 >mysql 窗口函数

mysql 窗口函数

时间:2022-11-03 09:12:51浏览次数:43  
标签:category 00 goods 窗口 函数 price mysql id select

语法结构

窗口函数的语法结构:

  1. 函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
  2. 函数 OVER 窗口名 … WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])

模拟数据

创建表

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');

函数介绍

1. 序号函数

1.1 row_number()

单纯的对每一组数据编号

-- 对查询结果的序号进行显示
SELECT
*,
ROW_NUMBER() OVER () AS row_num
FROM goods;

-- 每个商品分类下的价格,降序展示
select *,ROW_NUMBER() OVER(PARTITION BY category ORDER BY price desc) as row_num from goods

-- 查询每个商品分类下,价格最高的三种商品信息
select * from
(
select *,ROW_NUMBER() OVER(PARTITION BY category ORDER BY price DESC) as top3Price from goods
) as t
where top3Price<= 3
1.2 rank()

排序每一组的某一字段, 同等级同序号前后不连续

-- 使用RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息
select *,RANK() OVER(PARTITION BY category ORDER BY price DESC) as rank_num from goods

-- 使用RANK()函数获取goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息
select * from goods where category='女装/女士精品' order by price desc limit 4;
select *,RANK() OVER(partition by category order by price desc) as top4Price from goods where category='女装/女士精品' limit 4
1.3 dense_rank()

排序每一组的某一字段, 同等级同序号前后也连续

SELECT *,DENSE_RANK() OVER (partition by category ORDER BY price desc) as price_rank from goods;

2. 分布函数

2.1 percent_rank()

计算分区或结果集中行的百分位数排名,等级值百分比, (rank - 1)/ (rows - 1)

select RANK() over(PARTITION by category_id ORDER BY price desc) as r,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;
2.2 cume_dist()

累计分布值, <=当前rank值的行数 / 分组内总行数

select CUME_DIST() over(partition by category_id ORDER BY price asc) as cd,id,category,name,price from goods;

3. 前后函数

3.1 LAG(expr, n)

返回当前行的前n行(本组内)的expr值

-- 前一个商品的价格
select *,LAG(price,1) over (partition by category_id order by price desc) as prePrice from goods;
3.2 LEAD(expr,n)

返回当前行的后n行(本组)的expr值

select id,category,name,price,lead(price,1) over w as behindPrice from goods WINDOW w as (partition by category_id order by price)

4. 首位函数

4.1 first_value(expr)

取分组内排序后,截止到当前行,第一个值

select id,name,category,price,FIRST_VALUE(price) over (partition by category_id ORDER BY price desc) as firstVal from goods;
4.2 last_value(expr)

取分组内排序后,截止到当前行,最后一个值

默认帧规范

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
这意味着框架从第一行开始,到结果集的当前行结束。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
这表示框架从第一行开始,到结果集的最后一行结束。

select id,name,category,price,LAST_VALUE(price) over (order by price RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as lastValue from goods;

select id,name,category,price,LAST_VALUE(price) over (partition by category_id order by price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lastValue from goods;

5. 其他函数

5.1 nth_value(expr, n)

返回第n个expr的值

-- 查询goods中排名第二和排名第三的价格信息
select id,category,name,price,NTH_VALUE(price,2) over w as secondPrice,NTH_VALUE(price,3) over w as thirdPrice from goods window w as (partition by category_id ORDER BY price)
5.2 ntile(n)

将分区中的有序数据分为n个桶,记录桶编号。

-- 将goods表中的商品按照价格分为3组。
select ntile(3) over (partition by category_id ORDER BY price),id,category,name,price from goods;

标签:category,00,goods,窗口,函数,price,mysql,id,select
From: https://www.cnblogs.com/Bin-x/p/16853266.html

相关文章

  • navicate12 连接MySQL8 报错
      mysql>selectuser,plugin,hostfromuserwhereuser='root';+------+-----------------------+------+|user|plugin|host|+------+------......
  • MySQL进阶篇
    1.存储引擎1.1MySQL体系结构 连接层  最上层是一些客户端和链接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成......
  • Mysql的INTERVAL()函数和INTERVAL关键字
    Mysql的INTERVAL()函数和INTERVAL关键字  一,INTERVAL()函数INTERVAL()函数可以返回分段后的结果,语法如下:INTERVAL(N,N1,N2,N3,..........)其中,N是要判断的数值,N1,N2......
  • mysql 查询30分钟内的验证码
    mysql查询30分钟内的验证码 selectt.`value`as`value`fromt_message_infot wheret.user_name=13072281151ANDt.`key`=1ANDt.is_use=0ANDt.crea......
  • Java函数式编程(1):Lambda表达式(1)
    您好,我是湘王,这是我的博客园,欢迎您来,欢迎您再来~ Java在其技术发展周期中经历过几次比较重要的变化,如果不是这几次比较重要的变化,恐怕不会有现在这样的江湖地位。个人看......
  • 实验三 函数应用编程
    实验任务一task1.c#include<stdio.h>#include<stdlib.h>#include<time.h>#include<Windows.h>#defineN80voidprint_text(intline,intcol,chartext[]);voidpri......
  • C语言 模拟实现字符串函数 看着一篇够了
    C语言模拟实现字符串操作的库函数求字符串长度strlen思路1.如果碰到\0就代表字符串已经到了末尾size_tmy_strlen(constchar*str){ assert(str!=NULL); //......
  • 字符操作函数
    核心都是将字符串在传参时,由于形参int的限制转换为了每个字符对应的ASCII码然后进行判断和转换字符的判断分类函数返回值是该分类返回非0的正数,否则返回0 //字......
  • piapiapia(代码审计、反序列化逃逸、函数绕过)
    wp进入题目,一个登录框,F12和源代码没有看到提示,robots.txt也没有东西。于是试一试访问www.zip,没想到有源码,省了扫描目录的时间。里面有6个php文件,使用Seay审计代码,简单看......
  • Java函数式编程:二、高阶函数,闭包,函数组合以及柯里化
    承接上文:Java函数式编程:一、函数式接口,lambda表达式和方法引用这次来聊聊函数式编程中其他的几个比较重要的概念和技术,从而使得我们能更深刻的掌握Java中的函数式编程。......