SQL-窗口函数

窗口函数时8.0版本的mysql中出现的新语法，又叫做开窗函数，可以指定数据的计算范围、

• 格式

  • over() 指定窗口的关键字，默认窗口范围是全表

  • over需要配合其他计算函数一起使用

  • 聚合函数 over()

    • sum(字段) over()

  • 生成序号函数

    • rank() over()

    • row_number over()

    • dense_rank() over()

  • 取值函数

    • lag() over()

    • lead() over()

select 字段,sum() over(), rank() over(), lag() over() from 表

• over中除了可以指定窗口，还能指定排序，可以在窗口范围内指定排序规则，一般配合生成序号函数一起使用

1-排序函数

create table employee (
    empid int comment '员工编号',
    ename varchar(20) comment '员工姓名',
    deptid int comment '部门id',
    salary decimal(10,2) comment '员工薪资'
);
​
insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(2,'张飞',10,3500.00);
insert into employee values(2,'关羽',10,4500.00);
​
insert into employee values(3,'曹操',20,1900.00);
insert into employee values(4,'许褚',20,4800.00);
insert into employee values(5,'张辽',20,6500.00);
insert into employee values(6,'徐晃',20,14500.00);
​
insert into employee values(7,'孙权',30,44500.00);
insert into employee values(8,'周瑜',30,6500.00);
insert into employee values(9,'陆逊',30,7500.00);

基本使用

对表中的字段按照指定顺序记性排序然后生成序号

需要借助窗口函数实现

rank() over(order by 排序字段)

dense_rank() over(order by 排序字段)

row_number() over(order by 排序字段)

-- 按照员工薪资进行降序排序，然后，根据薪资大小生成排名序号

select
       salary,
       rank() over(order by  salary desc ) as rk,
       dense_rank() over (order by  salary desc ) as drk,
       row_number() over (order by salary desc ) as rn
from employee ;

找员工中薪资第二高的员工信息

with  tb1 as(
    -- 对员工薪资进行降序排序生成序号
    select
        *,
       dense_rank() over (order by  salary desc ) as drk
    from employee
)
select * from tb1 where drk=2;

注意；如果在排序时，form的后面也写了orderby，最终展示的数据以外面的orderby为准

综合练习

CREATE TABLE `game`  (
  `id` int NOT NULL,
  `name` varchar(100)  DEFAULT NULL comment '游戏名称',
  `platform` varchar(20)  DEFAULT NULL comment '发行平台',
  `genre` varchar(20)  DEFAULT NULL comment '游戏类型',
  `editor_rating` smallint(6) NULL DEFAULT NULL comment '游戏评分',
  `size` int NULL DEFAULT NULL comment '安装包大小',
  `released` date NULL DEFAULT NULL comment '发行时间',
  `updated` date NULL DEFAULT NULL comment '更新时间'
)comment '游戏信息表' ;
​
INSERT INTO `game` VALUES (1, 'Go Bunny', 'iOS', 'action', 5, 101, '2015-05-01', '2015-07-13');
INSERT INTO `game` VALUES (2, 'Fire Rescue', 'iOS', 'action', 9, 36, '2015-07-30', '2016-09-27');
INSERT INTO `game` VALUES (3, 'Eternal Stone', 'iOS', 'adventure', 10, 125, '2015-03-20', '2015-10-25');
INSERT INTO `game` VALUES (4, 'Froggy Adventure', 'iOS', 'adventure', 7, 127, '2015-05-01', '2015-07-02');
INSERT INTO `game` VALUES (5, 'Speed Race', 'iOS', 'racing', 7, 127, '2015-03-20', '2015-07-25');
INSERT INTO `game` VALUES (6, 'Monsters in Dungeon', 'Android', 'adventure', 9, 10, '2015-12-01', '2015-12-15');
INSERT INTO `game` VALUES (7, 'Shoot in Time', 'Android', 'shooting', 9, 123, '2015-12-01', '2016-03-20');
INSERT INTO `game` VALUES (8, 'Hit Brick', 'Android', 'action', 4, 54, '2015-05-01', '2016-01-05');
INSERT INTO `game` VALUES (9, 'The Square', 'Android', 'action', 4, 86, '2015-12-01', '2016-03-16');
INSERT INTO `game` VALUES (10, 'Duck Dash', 'Android', 'shooting', 4, 36, '2015-07-30', '2016-05-23');
INSERT INTO `game` VALUES (11, 'Perfect Time', 'Windows Phone', 'action', 6, 55, '2015-12-01', '2016-01-07');
INSERT INTO `game` VALUES (12, 'First Finish', 'Windows Phone', 'racing', 7, 44, '2015-10-01', '2016-02-20');
​
​
​
CREATE TABLE `game_purchase`  (
  `id` int NOT NULL,
  `game_id` int NULL DEFAULT NULL comment '游戏id',
  `price` decimal(10, 2) NULL DEFAULT NULL comment '售卖价格',
  `date` date NULL DEFAULT NULL comment '销售时间'
) comment '游戏销售表';
​
INSERT INTO `game_purchase` VALUES (1, 7, 15.99, '2016-03-07');
INSERT INTO `game_purchase` VALUES (2, 12, 13.99, '2016-08-13');
INSERT INTO `game_purchase` VALUES (3, 6, 11.99, '2016-01-21');
INSERT INTO `game_purchase` VALUES (4, 11, 7.99, '2016-10-21');
INSERT INTO `game_purchase` VALUES (5, 4, 12.99, '2016-05-03');
INSERT INTO `game_purchase` VALUES (6, 2, 1.99, '2016-07-08');
INSERT INTO `game_purchase` VALUES (7, 2, 5.99, '2016-03-29');
INSERT INTO `game_purchase` VALUES (8, 10, 18.99, '2016-01-05');
INSERT INTO `game_purchase` VALUES (9, 8, 3.99, '2016-07-18');
INSERT INTO `game_purchase` VALUES (10, 4, 7.99, '2016-06-04');
INSERT INTO `game_purchase` VALUES (11, 12, 14.99, '2016-10-16');
INSERT INTO `game_purchase` VALUES (12, 10, 15.99, '2016-08-23');
INSERT INTO `game_purchase` VALUES (13, 5, 13.99, '2016-09-20');
INSERT INTO `game_purchase` VALUES (14, 9, 14.99, '2016-07-17');
INSERT INTO `game_purchase` VALUES (15, 10, 10.99, '2016-06-07');
INSERT INTO `game_purchase` VALUES (16, 2, 1.99, '2016-06-09');
INSERT INTO `game_purchase` VALUES (17, 8, 8.99, '2016-04-13');
INSERT INTO `game_purchase` VALUES (18, 1, 6.99, '2016-01-12');
INSERT INTO `game_purchase` VALUES (19, 2, 3.99, '2016-06-18');
INSERT INTO `game_purchase` VALUES (20, 6, 19.99, '2016-08-07');
INSERT INTO `game_purchase` VALUES (21, 6, 7.99, '2016-04-06');
INSERT INTO `game_purchase` VALUES (22, 12, 5.99, '2016-07-24');
INSERT INTO `game_purchase` VALUES (23, 2, 10.99, '2016-08-05');
INSERT INTO `game_purchase` VALUES (24, 12, 16.99, '2016-07-21');
INSERT INTO `game_purchase` VALUES (25, 9, 4.99, '2016-10-05');
INSERT INTO `game_purchase` VALUES (26, 8, 11.99, '2016-02-27');
INSERT INTO `game_purchase` VALUES (27, 12, 15.99, '2016-05-26');
INSERT INTO `game_purchase` VALUES (28, 3, 17.99, '2016-01-30');
INSERT INTO `game_purchase` VALUES (29, 9, 7.99, '2016-01-21');
INSERT INTO `game_purchase` VALUES (30, 8, 19.99, '2016-06-28');
INSERT INTO `game_purchase` VALUES (31, 2, 7.99, '2016-07-09');
INSERT INTO `game_purchase` VALUES (32, 9, 1.99, '2016-10-14');
INSERT INTO `game_purchase` VALUES (33, 5, 15.99, '2016-07-04');
INSERT INTO `game_purchase` VALUES (34, 4, 19.99, '2016-01-27');
INSERT INTO `game_purchase` VALUES (35, 5, 14.99, '2016-04-06');
INSERT INTO `game_purchase` VALUES (36, 4, 13.99, '2016-03-27');
INSERT INTO `game_purchase` VALUES (37, 5, 12.99, '2016-07-19');
INSERT INTO `game_purchase` VALUES (38, 12, 7.99, '2016-06-20');
INSERT INTO `game_purchase` VALUES (39, 11, 4.99, '2016-01-02');
INSERT INTO `game_purchase` VALUES (40, 1, 8.99, '2016-08-17');
INSERT INTO `game_purchase` VALUES (41, 4, 18.99, '2016-06-02');
INSERT INTO `game_purchase` VALUES (42, 8, 7.99, '2016-04-20');
INSERT INTO `game_purchase` VALUES (43, 5, 14.99, '2016-06-05');
INSERT INTO `game_purchase` VALUES (44, 2, 7.99, '2016-04-08');
INSERT INTO `game_purchase` VALUES (45, 8, 17.99, '2016-06-03');
INSERT INTO `game_purchase` VALUES (46, 6, 16.99, '2016-10-10');
INSERT INTO `game_purchase` VALUES (47, 3, 13.99, '2016-10-04');
INSERT INTO `game_purchase` VALUES (48, 1, 9.99, '2016-09-29');
INSERT INTO `game_purchase` VALUES (49, 3, 9.99, '2016-03-25');
INSERT INTO `game_purchase` VALUES (50, 7, 13.99, '2016-02-01');
INSERT INTO `game_purchase` VALUES (51, 4, 15.99, '2016-08-29');
INSERT INTO `game_purchase` VALUES (52, 3, 9.99, '2016-03-13');
INSERT INTO `game_purchase` VALUES (53, 6, 14.99, '2016-04-29');
INSERT INTO `game_purchase` VALUES (54, 12, 6.99, '2016-05-09');
INSERT INTO `game_purchase` VALUES (55, 9, 14.99, '2016-06-07');
INSERT INTO `game_purchase` VALUES (56, 8, 18.99, '2016-09-24');
INSERT INTO `game_purchase` VALUES (57, 6, 8.99, '2016-10-30');
INSERT INTO `game_purchase` VALUES (58, 11, 4.99, '2016-05-11');
INSERT INTO `game_purchase` VALUES (59, 2, 8.99, '2016-08-22');
INSERT INTO `game_purchase` VALUES (60, 9, 2.99, '2016-03-24');

• 在列表中查找比较新,且安装包体积较小的游戏（ released ，size )

with tb1 as(select *,rank() over (order by released desc,size) as rn from game)
select * from tb1 where rn=1;

• 查询游戏名称，类别，以安装包的大小生成排名序号，结果按发行日期降序排列

# 查询游戏名称，类别，以安装包的大小生成排名序号，结果按发行日期降序排列
select name,genre,dense_rank() over (order by size) as rn from game order by released desc ;

• 在游戏销售表中添加日期排序列（按日期从近到远排序），最终结果按打分（ editor_rating ）排序

select *,dense_rank() over (order by date desc ) as rn
from  game_purchase
    join game on game_purchase.game_id = game.id
order by editor_rating;

• 查询安装包大小最小的游戏，返回游戏名称，类别，安装包大小

with tb1 as(select *,rank() over (order by size) as rn from game)
select * from tb1 where rn=1;

• 查询最近更新的游戏中，时间第二近的游戏，返回游戏名称，运行平台，更新时间

with tb1 as(select *,dense_rank() over (order by updated desc ) as rn from game)
select name,platform,updated from tb1 where rn = 2;

2-取值函数

基本用法

• lead(字段) over()

  • 根据指定的字段向下一行取值

• lag(字段) over()

  • 根据指定的字段向上一行取值

• first_value(字段) over()

  • 根据指定的字段取第一行值

• last_value(字段) over()

  • 根据指定的字段取最后一行值

select *,
       lead(ename) over () as lead_data,
       lag(ename) over() as lag_data,
       first_value(ename) over () as first,
       last_value(ename) over () as last
from employee;

指定取值范围，默认取值范围是全表

over(rows between ... and ... )

rows 指定行范围的关键字
between ...起始行 and  ...  结束行  是以当前行为基准进行判断
unbounded preceding 向上无限制，以当前行为基准，向上找数据没有行数限制
2 preceding  向上两行 
current row  代表当前行  以当前行为基准，向下查找数据到当前行结束
2 following  向下两行  以当前行为基准，向下查找两行数据
unbounded following 向下无限制

select *,
       # rows 指定行范围的关键字
       # between ...起始行 and  ...  结束行  是以当前行为基准进行判断
       # unbounded preceding 向上无限制，以当前行为基准，向上找数据没有行数限制
       # 2 preceding  向上两行 
       # current row  代表当前行  以当前行为基准，向下查找数据到当前行结束
       # 2 following  向下两行  以当前行为基准，向下查找两行数据
       # unbounded following 向下无限制
       lead(ename) over (rows between unbounded preceding and current row ) as lead_data,
       last_value(ename) over (rows between unbounded preceding and current row) as last,
       last_value(ename) over (rows between unbounded preceding and 2 following) as last1,
       last_value(ename) over (rows between unbounded preceding and unbounded following) as last2
from employee;

综合练习

CREATE TABLE `website`  (
  `id` int NOT NULL,
  `name` varchar(50)  DEFAULT NULL comment '网站名字',
  `budget` int NULL DEFAULT NULL comment '每月预算',
  `opened` date NULL DEFAULT NULL comment '开始运营时间'
) comment '网站信息表';
​
INSERT INTO `website` VALUES (1, 'Gaming Heaven', 3000, '2016-02-01');
INSERT INTO `website` VALUES (2, 'All About Health', 700, '2016-03-15');
INSERT INTO `website` VALUES (3, 'Around The World', 500, '2016-05-01');
​
​
CREATE TABLE `statistics`  (
  `website_id` int NULL DEFAULT NULL,
  `day` date NULL DEFAULT NULL comment '访问日期',
  `users` int NULL DEFAULT NULL comment '显示当天该网站的UV（unique visit，独立IP，一个UV代表
一个用户',
  `impressions` int NULL DEFAULT NULL comment '广告展示的次数',
  `clicks` int NULL DEFAULT NULL comment '广告的点击次数',
  `revenue` decimal(10, 2) NULL DEFAULT NULL comment '每日点击产生的收入'
) comment '网站访问信息表';
​
INSERT INTO `statistics` VALUES (1, '2016-05-01', 36169, 108507, 237, 66.34);
INSERT INTO `statistics` VALUES (1, '2016-05-02', 29580, 295800, 793, 214.12);
INSERT INTO `statistics` VALUES (1, '2016-05-03', 30907, 463605, 1545, 401.79);
INSERT INTO `statistics` VALUES (1, '2016-05-04', 19154, 57462, 160, 38.31);
INSERT INTO `statistics` VALUES (1, '2016-05-05', 10897, 163455, 343, 99.58);
INSERT INTO `statistics` VALUES (1, '2016-05-06', 24602, 369030, 804, 184.92);
INSERT INTO `statistics` VALUES (1, '2016-05-07', 19882, 139174, 348, 76.55);
INSERT INTO `statistics` VALUES (1, '2016-05-08', 26932, 296252, 782, 117.25);
INSERT INTO `statistics` VALUES (1, '2016-05-09', 39275, 117825, 342, 68.30);
INSERT INTO `statistics` VALUES (1, '2016-05-10', 28900, 317900, 1029, 236.62);
INSERT INTO `statistics` VALUES (1, '2016-05-11', 23714, 142284, 423, 84.69);
INSERT INTO `statistics` VALUES (1, '2016-05-12', 19006, 171054, 378, 101.95);
INSERT INTO `statistics` VALUES (1, '2016-05-13', 24791, 198328, 526, 89.43);
INSERT INTO `statistics` VALUES (1, '2016-05-14', 27617, 165702, 407, 85.50);
INSERT INTO `statistics` VALUES (1, '2016-05-15', 8563, 59941, 135, 33.75);
INSERT INTO `statistics` VALUES (1, '2016-05-16', 33679, 303111, 609, 121.73);
INSERT INTO `statistics` VALUES (1, '2016-05-17', 25123, 175861, 383, 57.47);
INSERT INTO `statistics` VALUES (1, '2016-05-18', 32233, 225631, 594, 118.75);
INSERT INTO `statistics` VALUES (1, '2016-05-19', 33504, 335040, 857, 197.08);
INSERT INTO `statistics` VALUES (1, '2016-05-20', 10830, 86640, 229, 52.58);
INSERT INTO `statistics` VALUES (1, '2016-05-21', 13904, 152944, 380, 75.90);
INSERT INTO `statistics` VALUES (1, '2016-05-22', 35180, 386980, 992, 168.68);
INSERT INTO `statistics` VALUES (1, '2016-05-23', 18911, 283665, 773, 154.59);
INSERT INTO `statistics` VALUES (1, '2016-05-24', 19938, 259194, 553, 121.58);
INSERT INTO `statistics` VALUES (1, '2016-05-25', 14796, 192348, 416, 66.61);
INSERT INTO `statistics` VALUES (1, '2016-05-26', 20953, 146671, 298, 59.50);
INSERT INTO `statistics` VALUES (1, '2016-05-27', 14756, 191828, 564, 84.63);
INSERT INTO `statistics` VALUES (1, '2016-05-28', 20397, 203970, 645, 135.55);
INSERT INTO `statistics` VALUES (1, '2016-05-29', 30382, 182292, 446, 71.31);
INSERT INTO `statistics` VALUES (1, '2016-05-30', 39977, 519701, 1382, 262.61);
INSERT INTO `statistics` VALUES (1, '2016-05-31', 34817, 382987, 796, 230.91);
INSERT INTO `statistics` VALUES (2, '2016-05-01', 7058, 28232, 106, 30.78);
INSERT INTO `statistics` VALUES (2, '2016-05-02', 7716, 46296, 132, 21.16);
INSERT INTO `statistics` VALUES (2, '2016-05-03', 6877, 55016, 144, 34.66);
INSERT INTO `statistics` VALUES (2, '2016-05-04', 9498, 47490, 145, 33.40);
INSERT INTO `statistics` VALUES (2, '2016-05-05', 8350, 41750, 128, 38.54);
INSERT INTO `statistics` VALUES (2, '2016-05-06', 3508, 28064, 83, 14.07);
INSERT INTO `statistics` VALUES (2, '2016-05-07', 5097, 45873, 202, 60.63);
INSERT INTO `statistics` VALUES (2, '2016-05-08', 5491, 10982, 54, 8.11);
INSERT INTO `statistics` VALUES (2, '2016-05-09', 3350, 30150, 78, 17.23);
INSERT INTO `statistics` VALUES (2, '2016-05-10', 9669, 48345, 204, 44.88);
INSERT INTO `statistics` VALUES (2, '2016-05-11', 8929, 35716, 149, 29.76);
INSERT INTO `statistics` VALUES (2, '2016-05-12', 5758, 17274, 51, 9.20);
INSERT INTO `statistics` VALUES (2, '2016-05-13', 6342, 63420, 202, 48.47);
INSERT INTO `statistics` VALUES (2, '2016-05-14', 6219, 49752, 143, 38.71);
INSERT INTO `statistics` VALUES (2, '2016-05-15', 5881, 35286, 126, 36.42);
INSERT INTO `statistics` VALUES (2, '2016-05-16', 4959, 19836, 64, 18.50);
INSERT INTO `statistics` VALUES (2, '2016-05-17', 9966, 109626, 359, 100.64);
INSERT INTO `statistics` VALUES (2, '2016-05-18', 4182, 41820, 116, 20.79);
INSERT INTO `statistics` VALUES (2, '2016-05-19', 3538, 38918, 193, 40.46);
INSERT INTO `statistics` VALUES (2, '2016-05-20', 3584, 17920, 47, 9.77);
INSERT INTO `statistics` VALUES (2, '2016-05-21', 5473, 32838, 124, 28.50);
INSERT INTO `statistics` VALUES (2, '2016-05-22', 9484, 66388, 227, 54.38);
INSERT INTO `statistics` VALUES (2, '2016-05-23', 5971, 29855, 119, 30.93);
INSERT INTO `statistics` VALUES (2, '2016-05-24', 8085, 32340, 139, 20.82);
INSERT INTO `statistics` VALUES (2, '2016-05-25', 3970, 19850, 78, 20.32);
INSERT INTO `statistics` VALUES (2, '2016-05-26', 8805, 79245, 325, 48.72);
INSERT INTO `statistics` VALUES (2, '2016-05-27', 9563, 19126, 70, 10.51);
INSERT INTO `statistics` VALUES (2, '2016-05-28', 6682, 80184, 297, 47.52);
INSERT INTO `statistics` VALUES (2, '2016-05-29', 6701, 80412, 228, 36.45);
INSERT INTO `statistics` VALUES (2, '2016-05-30', 9558, 105138, 300, 60.08);
INSERT INTO `statistics` VALUES (2, '2016-05-31', 5548, 44384, 178, 53.26);
INSERT INTO `statistics` VALUES (3, '2016-05-01', 37, 148, 1, 0.10);
INSERT INTO `statistics` VALUES (3, '2016-05-02', 73, 292, 1, 0.21);
INSERT INTO `statistics` VALUES (3, '2016-05-03', 95, 285, 1, 0.30);
INSERT INTO `statistics` VALUES (3, '2016-05-04', 32, 224, 1, 0.15);
INSERT INTO `statistics` VALUES (3, '2016-05-05', 56, 392, 2, 0.37);
INSERT INTO `statistics` VALUES (3, '2016-05-06', 100, 1000, 3, 0.70);
INSERT INTO `statistics` VALUES (3, '2016-05-07', 167, 668, 2, 0.39);
INSERT INTO `statistics` VALUES (3, '2016-05-08', 246, 1722, 8, 1.59);
INSERT INTO `statistics` VALUES (3, '2016-05-09', 108, 648, 2, 0.31);
INSERT INTO `statistics` VALUES (3, '2016-05-10', 158, 1264, 6, 1.81);
INSERT INTO `statistics` VALUES (3, '2016-05-11', 216, 2160, 8, 2.18);
INSERT INTO `statistics` VALUES (3, '2016-05-12', 187, 1309, 4, 0.94);
INSERT INTO `statistics` VALUES (3, '2016-05-13', 254, 1270, 4, 0.90);
INSERT INTO `statistics` VALUES (3, '2016-05-14', 107, 535, 3, 0.60);
INSERT INTO `statistics` VALUES (3, '2016-05-15', 270, 3240, 9, 1.65);
INSERT INTO `statistics` VALUES (3, '2016-05-16', 323, 2584, 11, 2.41);
INSERT INTO `statistics` VALUES (3, '2016-05-17', 316, 1264, 4, 0.83);
INSERT INTO `statistics` VALUES (3, '2016-05-18', 307, 2763, 8, 1.81);
INSERT INTO `statistics` VALUES (3, '2016-05-19', 361, 2527, 11, 3.07);
INSERT INTO `statistics` VALUES (3, '2016-05-20', 357, 2856, 9, 2.54);
INSERT INTO `statistics` VALUES (3, '2016-05-21', 484, 1452, 7, 1.09);
INSERT INTO `statistics` VALUES (3, '2016-05-22', 324, 3888, 12, 2.58);
INSERT INTO `statistics` VALUES (3, '2016-05-23', 570, 6840, 19, 4.24);
INSERT INTO `statistics` VALUES (3, '2016-05-24', 1664, 8320, 36, 5.36);
INSERT INTO `statistics` VALUES (3, '2016-05-25', 2315, 11575, 30, 5.10);
INSERT INTO `statistics` VALUES (3, '2016-05-26', 3586, 28688, 72, 16.54);
INSERT INTO `statistics` VALUES (3, '2016-05-27', 1226, 6130, 20, 3.00);
INSERT INTO `statistics` VALUES (3, '2016-05-28', 5998, 29990, 117, 21.09);
INSERT INTO `statistics` VALUES (3, '2016-05-29', 7287, 58296, 166, 39.86);
INSERT INTO `statistics` VALUES (3, '2016-05-30', 7785, 23355, 91, 19.16);
INSERT INTO `statistics` VALUES (3, '2016-05-31', 1545, 16995, 55, 9.96);

• 统计id 为1的网站，每天访问的人数以及下一天访问的人数

  • 展示字段： day 日期， users 访问人数， lead 下一天访问人数

select day,users,lead(users) over (order by day) as lead_day from statistics where website_id = 1;

• 统计id为3的网站每天的点击数量，前一天的点击数量

select day,users,lag(users) over (order by day) as lead_day from statistics where website_id = 3;

• 统计id = 3 的网站收入情况，

  • 展示日期，收入，和第一天的收入

select day,revenue,first_value(revenue) over (order by day) as lead_day from statistics where website_id = 3;