mysql 分析函数_MySQL实现常用分析函数

最新推荐文章于 2024-04-18 20:37:04 发布
最新推荐文章于 2024-04-18 20:37:04 发布
阅读量990 收藏 1
点赞数
文章标签： mysql 分析函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_31901801/article/details/113121344
版权

分别在 MySQL5.7.25-log 和 8.0.16 环境中实现类似Oracle的分析函数(8.0版本中已支持，直接使用即可)。

一、创建测试数据

二、row_number() over()

三、rank() over()

四、dense_rank() over()

五、lag() over()

六、lead() over()

七、待补充

一、创建测试数据:

CREATE TABLE `devicecounter` (

`roomid` int(11) DEFAULT NULL,

`deviceid` int(11) DEFAULT NULL,

`counter` decimal(12,4) DEFAULT NULL,

`readtime` datetime DEFAULT NULL

) ENGINE=InnoDB;

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (536, 147, 26.0000, '2020-01-17 10:21:16');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (536, 502, 872.2700, '2020-01-17 10:21:19');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3750, 4922, 1304.8000, '2020-01-17 10:21:30');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3750, 5164, 20.9450, '2020-01-17 10:21:36');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3928, 6282, 2514.7000, '2020-01-17 10:21:40');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3930, 6284, 2087.8300, '2020-01-17 10:42:11');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3930, 6284, 2087.7300, '2020-01-17 10:41:08');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3928, 6434, 70.0000, '2020-01-17 10:21:42');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3930, 6436, 18.5000, '2020-01-17 10:45:11');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3930, 6436, 18.0000, '2020-01-17 10:41:10');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3981, 7260, 5.6000, '2020-01-17 10:21:38');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (4038, 7314, 6.6000, '2020-01-17 10:25:31');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (4038, 7314, 5.8000, '2020-01-17 10:19:30');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3981, 7364, 838.7000, '2020-01-17 10:21:33');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (4038, 7418, 253.5000, '2020-01-17 10:30:31');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (4038, 7418, 252.7000, '2020-01-17 10:19:29');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (3981, 7468, 12.0000, '2020-01-17 10:21:29');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (4038, 7522, 12.8000, '2020-01-17 10:29:31');

INSERT INTO `devicecounter`(`roomid`, `deviceid`, `counter`, `readtime`) VALUES (4038, 7522, 12.0000, '2020-01-17 10:19:30');

二、查询结果添加序列号，类Oracle 的row_number() OVER()

例1：不分组，全部数据添加序列号，类Oracle 的rownum伪列

#### 按照房间和设备排序，依次标注序列号

######### MySQL5.7

mysql> SELECT

( @rownum := @rownum + 1 ) AS rownum,

a.*

FROM

devicecounter a,

( SELECT @rownum := 0 ) AS t

ORDER BY roomid,deviceid,counter;

+--------+--------+----------+-----------+---------------------+

| rownum | roomid | deviceid | counter | readtime |

+--------+--------+----------+-----------+---------------------+

| 1 | 536 | 147 | 26.0000 | 2020-01-17 10:21:16 |

| 2 | 536 | 502 | 872.2700 | 2020-01-17 10:21:19 |

| 3 | 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 |

| 4 | 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 |

| 5 | 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 |

| 6 | 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 |

| 7 | 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 |

| 8 | 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 |

| 9 | 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 |

| 10 | 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 |

| 11 | 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 |

| 12 | 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 |

| 13 | 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 |

| 14 | 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 |

| 15 | 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 |

| 16 | 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 |

| 17 | 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 |

| 18 | 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 |

| 19 | 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 |

+--------+--------+----------+-----------+---------------------+

######### MySQL8.0

mysql>SELECT

ROW_NUMBER () OVER w AS 'row_number',

a.*

FROM devicecounter a

WINDOW w AS ( ORDER BY roomid,deviceid,counter );

#或者下面写法：

SELECT

ROW_NUMBER() OVER (ORDER BY roomid,deviceid,counter) AS 'row_number',

a.*

FROM devicecounter a;

查询结果同上，不予列出。

例2：先按roomid分组，再按照deviceid,counter排序，类Oracle 的row_number() OVER(PARTITION BY ORDER BY )

##### 先按照房间分组，再添加序列号

######### MySQL5.7

mysql> SELECT

a.*,

IF( @room_id = roomid,@rownum := @rownum + 1, @rownum := 1 ) AS rownum,

@room_id := roomid AS roomid1

FROM

devicecounter a,(SELECT @rownum := 0, @room_id := 0) AS t

ORDER BY roomid,deviceid,counter;

+--------+----------+-----------+---------------------+--------+---------+

| roomid | deviceid | counter | readtime | rownum | roomid1 |

+--------+----------+-----------+---------------------+--------+---------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 1 | 536 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 2 | 536 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 1 | 3750 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 2 | 3750 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 1 | 3928 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 2 | 3928 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 1 | 3930 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 2 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 3 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 4 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 1 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 2 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 3 | 3981 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 1 | 4038 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 2 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 3 | 4038 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 4 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 5 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 6 | 4038 |

+--------+----------+-----------+---------------------+--------+---------+

######### MySQL8.0

mysql>SELECT

a.*,

ROW_NUMBER() OVER w AS 'row_number'

FROM devicecounter a

WINDOW w AS ( PARTITION BY roomid order by deviceid,counter);

#或者下面写法：

SELECT

a.*,

ROW_NUMBER() OVER (PARTITION BY roomid order by deviceid,counter) AS 'row_number'

FROM devicecounter a;

三、rank，类似Oracle的 rank() over()

例1：不分组，全部数据按 roomid 排序，再添加序号，类Oracle 的rank() OVER(ORDER BY)

######### MySQL5.7

mysql> SELECT

a.*,

@rownum := @rownum + 1 AS rownum,

IF(@room_id = roomid, @rank := @rank,@rank := @rownum) AS rank,

@room_id := roomid AS roomid1

FROM

devicecounter a,(SELECT @rownum := 0, @rank := 0,@room_id:= 0) AS t

ORDER BY roomid;

+--------+----------+-----------+---------------------+--------+------+---------+

| roomid | deviceid | counter | readtime | rownum | rank | roomid1 |

+--------+----------+-----------+---------------------+--------+------+---------+

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 1 | 1 | 536 |

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 2 | 1 | 536 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 3 | 3 | 3750 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 4 | 3 | 3750 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 5 | 5 | 3928 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 6 | 5 | 3928 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 7 | 7 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 8 | 7 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 9 | 7 | 3930 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 10 | 7 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 11 | 11 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 12 | 11 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 13 | 11 | 3981 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 14 | 14 | 4038 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 15 | 14 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 16 | 14 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 17 | 14 | 4038 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 18 | 14 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 19 | 14 | 4038 |

+--------+----------+-----------+---------------------+--------+------+---------+

######### MySQL8.0

mysql> SELECT

a.*,

rank() OVER w AS 'rank'

FROM devicecounter a

WINDOW w AS (order by roomid) ;

#或者下面写法：

SELECT

a.*,

rank() OVER (order by roomid) AS 'rank'

FROM devicecounter a ;

例2：先按roomid分组，再按deviceid排序，类Oracle 的rank() OVER(PARTITION BY ORDER BY)

######### MySQL5.7

mysql> SELECT

a.*,

IF(@room_id = roomid, @rownum := @rownum + 1, @rownum := 1 ) AS rownum,

IF(@device_id = deviceid, @rank := @rank,@rank := @rownum) AS rank ,

@device_id := deviceid AS deviceid1,

@room_id := roomid AS roomid1

FROM

devicecounter a,(SELECT @rownum := 0, @room_id := -1, @rank := 0,@device_id:= -1) AS t

ORDER BY roomid,deviceid;

+--------+----------+-----------+---------------------+--------+------+-----------+---------+

| roomid | deviceid | counter | readtime | rownum | rank | deviceid1 | roomid1 |

+--------+----------+-----------+---------------------+--------+------+-----------+---------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 1 | 1 | 147 | 536 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 2 | 2 | 502 | 536 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 1 | 1 | 4922 | 3750 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 2 | 2 | 5164 | 3750 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 1 | 1 | 6282 | 3928 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 2 | 2 | 6434 | 3928 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 1 | 1 | 6284 | 3930 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 2 | 1 | 6284 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 3 | 3 | 6436 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 4 | 3 | 6436 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 1 | 1 | 7260 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 2 | 2 | 7364 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 3 | 3 | 7468 | 3981 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 1 | 1 | 7314 | 4038 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 2 | 1 | 7314 | 4038 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 3 | 3 | 7418 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 4 | 3 | 7418 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 5 | 5 | 7522 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 6 | 5 | 7522 | 4038 |

+--------+----------+-----------+---------------------+--------+------+-----------+---------+

######### MySQL8.0

mysql>SELECT

a.*,

rank() OVER w AS 'rank'

FROM devicecounter a

WINDOW w AS (PARTITION BY roomid order by deviceid);

#或者下面写法：

mysql>SELECT

a.*,

rank() OVER (PARTITION BY roomid order by deviceidq) AS 'rank'

FROM devicecounter a ;

四、dense_rank，类Oracle 的 dense_rank() over()

例1：不分组，全部数据按roomid排序，再添加序号，类Oracle 的dense_rank() OVER(ORDER BY)

######### MySQL5.7

mysql> SELECT

a.*,

@rank := @rank + if(@room_id = roomid,0,1) AS rank ,

@room_id := roomid AS roomid1

FROM

devicecounter a,(SELECT @room_id := -1, @rank := 0,@device_id:= -1) AS t

ORDER BY roomid,deviceid;

+--------+----------+-----------+---------------------+------+---------+

| roomid | deviceid | counter | readtime | rank | roomid1 |

+--------+----------+-----------+---------------------+------+---------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 1 | 536 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 1 | 536 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 2 | 3750 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 2 | 3750 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 3 | 3928 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 3 | 3928 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 4 | 3930 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 4 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 4 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 4 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 5 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 5 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 5 | 3981 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 6 | 4038 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 6 | 4038 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 6 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 6 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 6 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 6 | 4038 |

+--------+----------+-----------+---------------------+------+---------+

######### MySQL8.0

mysql>mysql>SELECT

a.*,

dense_rank() OVER w AS 'dense_rank'

FROM devicecounter a

WINDOW w AS (order by roomid);

#或者下面写法：

mysql>SELECT

a.*,

dense_rank() OVER (order by roomid) AS 'dense_rank'

FROM devicecounter a ;

例2：先按roomid分组，再按deviceid排序，类Oracle 的dense_rank() OVER(PARTITION BY ORDER BY)

######### MySQL5.7

mysql> SELECT

a.*,

IF(@room_id = roomid, @rank := @rank + if(@device_id = deviceid,0,1),@rank := 1) AS dense_rank ,

@device_id := deviceid AS deviceid1,

@room_id := roomid AS roomid1

FROM

devicecounter a,(SELECT @room_id := -1, @rank := 0,@device_id:= -1) AS t

ORDER BY roomid,deviceid;

+--------+----------+-----------+---------------------+------------+-----------+---------+

| roomid | deviceid | counter | readtime | dense_rank | deviceid1 | roomid1 |

+--------+----------+-----------+---------------------+------------+-----------+---------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 1 | 147 | 536 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 2 | 502 | 536 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 1 | 4922 | 3750 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 2 | 5164 | 3750 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 1 | 6282 | 3928 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 2 | 6434 | 3928 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 1 | 6284 | 3930 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 1 | 6284 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 2 | 6436 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 2 | 6436 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 1 | 7260 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 2 | 7364 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 3 | 7468 | 3981 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 1 | 7314 | 4038 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 1 | 7314 | 4038 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 2 | 7418 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 2 | 7418 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 3 | 7522 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 3 | 7522 | 4038 |

+--------+----------+-----------+---------------------+------------+-----------+---------+

######### MySQL8.0

mysql>SELECT

a.*,

dense_rank() OVER w AS 'dense_rank'

FROM devicecounter a

WINDOW w AS (PARTITION BY roomid order by deviceid);

#或者下面写法：

mysql> SELECT

a.*,

dense_rank() OVER (PARTITION BY roomid order by deviceid) AS 'dense_rank'

FROM devicecounter a ;

五、lag，类Oracle 的 lag() over()

例1：不分组，全部数据按roomid,deviceid升序排序，类Oracle 的lag() OVER(ORDER BY)

######### MySQL5.7

mysql> SELECT

a.*,

@lag as lag_field,

@lag:=deviceid

FROM

devicecounter a,(SELECT @lag := '') AS t

ORDER BY roomid,deviceid;

+--------+----------+-----------+---------------------+-----------+----------------+

| roomid | deviceid | counter | readtime | lag_field | @lag:=deviceid |

+--------+----------+-----------+---------------------+-----------+----------------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | | 147 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 147 | 502 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 502 | 4922 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 4922 | 5164 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 5164 | 6282 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 6282 | 6434 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 6434 | 6284 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 6284 | 6284 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 6284 | 6436 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 6436 | 6436 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 6436 | 7260 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 7260 | 7364 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 7364 | 7468 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 7468 | 7314 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 7314 | 7314 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 7314 | 7418 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 7418 | 7418 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 7418 | 7522 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 7522 | 7522 |

+--------+----------+-----------+---------------------+-----------+----------------+

######### MySQL8.0

mysql> SELECT

a.*,

lag(deviceid) over w as lag_field

FROM

devicecounter a

WINDOW w AS (ORDER BY roomid,deviceid);

#或者下面写法：

mysql> SELECT

a.*,

lag(deviceid) over(ORDER BY roomid,deviceid) as lag_field

FROM

devicecounter a ;

例2：先按roomid分组，再按roomid,deviceid排序，类Oracle 的lag() OVER(PARTITION BY ORDER BY)

######### MySQL5.7

mysql> SELECT

a.*,

if(@room_id=roomid,@lag,'') as lag_field,

@lag:=deviceid,

@room_id:=roomid

FROM

devicecounter a,(SELECT @lag := '',@room_id:=-1) AS t

ORDER BY roomid,deviceid;

+--------+----------+-----------+---------------------+-----------+----------------+------------------+

| roomid | deviceid | counter | readtime | lag_field | @lag:=deviceid | @room_id:=roomid |

+--------+----------+-----------+---------------------+-----------+----------------+------------------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | | 147 | 536 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 147 | 502 | 536 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | | 4922 | 3750 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 4922 | 5164 | 3750 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | | 6282 | 3928 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 6282 | 6434 | 3928 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | | 6284 | 3930 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 6284 | 6284 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 6284 | 6436 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 6436 | 6436 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | | 7260 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 7260 | 7364 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 7364 | 7468 | 3981 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | | 7314 | 4038 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 7314 | 7314 | 4038 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 7314 | 7418 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 7418 | 7418 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | 7418 | 7522 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 7522 | 7522 | 4038 |

+--------+----------+-----------+---------------------+-----------+----------------+------------------+

######### MySQL8.0

mysql> SELECT

a.*,

lag(deviceid) over w as lag_field

FROM

devicecounter a

WINDOW w AS (PARTITION by roomid ORDER BY roomid,deviceid);

#或者下面写法：

mysql> SELECT

a.*,

lag(deviceid) over(PARTITION by roomid ORDER BY roomid,deviceid) as lag_field

FROM

devicecounter a ;

六、lead，类Oracle 的 lead() over()

例1：不分组，全部数据按roomid,deviceid,counter升序排序，类Oracle 的lead() OVER(ORDER BY)

######### MySQL5.7 先全部降序，获取前一个值，然后再升序即可

mysql> select * from (

SELECT

a.*,

@lead as lead_field,

@lead:=deviceid

FROM

devicecounter a,(SELECT @lead := '') AS t

ORDER BY roomid desc ,deviceid desc,counter desc)tt

ORDER BY roomid ,deviceid,counter;

+--------+----------+-----------+---------------------+------------+-----------------+

| roomid | deviceid | counter | readtime | lead_field | @lead:=deviceid |

+--------+----------+-----------+---------------------+------------+-----------------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 502 | 147 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | 4922 | 502 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 5164 | 4922 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | 6282 | 5164 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 6434 | 6282 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | 6284 | 6434 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 6284 | 6284 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 6436 | 6284 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 6436 | 6436 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | 7260 | 6436 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 7364 | 7260 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 7468 | 7364 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | 7314 | 7468 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 7314 | 7314 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 7418 | 7314 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 7418 | 7418 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 7522 | 7418 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 7522 | 7522 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | | 7522 |

+--------+----------+-----------+---------------------+------------+-----------------+

######### MySQL8.0

mysql> SELECT

a.*,

lead(deviceid) over w as lead_field

FROM

devicecounter a

WINDOW w AS (ORDER BY roomid,deviceid,counter);

#或者下面写法：

mysql> SELECT

a.*,

lead(deviceid) over( ORDER BY roomid,deviceid,counter) as lead_field

FROM

devicecounter a ;

例2：先按roomid分组，再按deviceid,counter排序，类Oracle 的lead() OVER(PARTITION BY ORDER BY)

######### MySQL5.7

mysql> select * from ( SELECT

a.*,

if(@room_id=roomid,@lag,'') as lead_field,

@lead:=deviceid,

@room_id:=roomid

FROM

devicecounter a,(SELECT @lead := '',@room_id:=-1) AS t

ORDER BY roomid desc ,deviceid desc,counter desc )tt

ORDER BY roomid ,deviceid,counter;

+--------+----------+-----------+---------------------+------------+----------------+------------------+

| roomid | deviceid | counter | readtime | lead_field | @lag:=deviceid | @room_id:=roomid |

+--------+----------+-----------+---------------------+------------+----------------+------------------+

| 536 | 147 | 26.0000 | 2020-01-17 10:21:16 | 502 | 147 | 536 |

| 536 | 502 | 872.2700 | 2020-01-17 10:21:19 | | 502 | 536 |

| 3750 | 4922 | 1304.8000 | 2020-01-17 10:21:30 | 5164 | 4922 | 3750 |

| 3750 | 5164 | 20.9450 | 2020-01-17 10:21:36 | | 5164 | 3750 |

| 3928 | 6282 | 2514.7000 | 2020-01-17 10:21:40 | 6434 | 6282 | 3928 |

| 3928 | 6434 | 70.0000 | 2020-01-17 10:21:42 | | 6434 | 3928 |

| 3930 | 6284 | 2087.7300 | 2020-01-17 10:41:08 | 6284 | 6284 | 3930 |

| 3930 | 6284 | 2087.8300 | 2020-01-17 10:42:11 | 6436 | 6284 | 3930 |

| 3930 | 6436 | 18.0000 | 2020-01-17 10:41:10 | 6436 | 6436 | 3930 |

| 3930 | 6436 | 18.5000 | 2020-01-17 10:45:11 | | 6436 | 3930 |

| 3981 | 7260 | 5.6000 | 2020-01-17 10:21:38 | 7364 | 7260 | 3981 |

| 3981 | 7364 | 838.7000 | 2020-01-17 10:21:33 | 7468 | 7364 | 3981 |

| 3981 | 7468 | 12.0000 | 2020-01-17 10:21:29 | | 7468 | 3981 |

| 4038 | 7314 | 5.8000 | 2020-01-17 10:19:30 | 7314 | 7314 | 4038 |

| 4038 | 7314 | 6.6000 | 2020-01-17 10:25:31 | 7418 | 7314 | 4038 |

| 4038 | 7418 | 252.7000 | 2020-01-17 10:19:29 | 7418 | 7418 | 4038 |

| 4038 | 7418 | 253.5000 | 2020-01-17 10:30:31 | 7522 | 7418 | 4038 |

| 4038 | 7522 | 12.0000 | 2020-01-17 10:19:30 | 7522 | 7522 | 4038 |

| 4038 | 7522 | 12.8000 | 2020-01-17 10:29:31 | | 7522 | 4038 |

+--------+----------+-----------+---------------------+------------+----------------+------------------+

######### MySQL8.0

mysql> SELECT

a.*,

lead(deviceid) over w as lead_field

FROM

devicecounter a

WINDOW w AS (partition by roomid ORDER BY deviceid,counter);

#或者下面写法：

mysql>SELECT

a.*,

lead(deviceid) over(partition by roomid ORDER BY deviceid,counter) as lead_field

FROM

devicecounter a ;

刘昌睿
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql的分析函数_MySQL分析函数实现
weixin_39626211的博客
01-18 657
| MySQL分析函数实现还好MySQL8.0已经实现了与Oracle相同的分析函数。1. 实现rownumSET@rn:=0;SELECT@rn:=@rn+1ASrownum,e.*FROMempe;或者写成：SELECT@rn:=@rn+1ASrownum,e.*FROMempe,(SELECT@rn:=0)c2. 各种分析函数写法 (MySQL实现分析...
mysql 分析函数_mysql实现开窗函数、Mysql实现分析函数
weixin_32770541的博客
01-18 345
关键字：mysql实现开窗函数、Mysql实现分析函数、利用变量实现窗口函数【Mysql5.7及以下版本】适用范围：mysql5.7及以下版本，mysql8.0+ 可以直接使用窗口函数注意，变量是从左到右顺序执行的【测试数据】-- 测试数据CREATE TABLE `tem` (`id` int(11) NOT NULL AUTO_INCREMENT,`str` char(1) DEFAULT N...
mysql分析函数
michael_linux的博客，一个小小小学生。滴水穿石，步步为营，只为那刹那芳华。
04-18 5072
排序类：row_number、rank、dense_rank等，percent_rank、cume_dist 等分布排序类 相对引用类：lag、lead、first_value、last_value、nth_value 等
mysql的分析函数_分析函数
weixin_42524530的博客
01-28 439
分析函数 row_number() over(partition by a order by b) order_id dense_rank() over(partition by a order by b) order_id rank() over()(partition by a order by b) order_id row_number dense_rank rank 2001 1 1 1...
mysql分析函数的实现
为无为，事无事，味无味。
06-24 1538
http://hidba.org/?p=295 mysql分析函数的实现 May 10th, 2011hidba 今天收到同事的一需求，要求实现以下功能: drop table test; create table test(name varchar(10),name1 varchar(10),count bigint); delete
mysql数据分析函数_MySQL常见函数
weixin_36293915的博客
01-21 905
MySQL中的函数有数值函数、字符串函数、日期函数、转换分组合并函数和聚合函数，今天对一些常见的函数进行简单的介绍汇总：数值函数abs、floor、ceiling、round、mod、rand字符串函数concat、length、upper、lower、replace、repeat、reverse、substring、instr、left、substring、ltrim日期函数curdate、cu...
mysql常用函数
热门推荐
sugang_ximi的专栏
08-06 9万+
一、数学函数 ABS(x)   返回x的绝对值 BIN(x)   返回x的二进制（OCT返回八进制，HEX返回十六进制） CEILING(x)   返回大于x的最小整数值 EXP(x)   返回值e（自然对数的底）的x次方 FLOOR(x)   返回小于x的最大整数值
数据库-分析函数
Strive_Surpassing的博客
07-19 1661
分析函数基本语法函数名称([参数]) VOER (PARTITION BY 子句 字段,....[ORDER BY 子句 字段,..[ASC][DESC][NULLS FIRST][NULLS LAST]][WINDOWING 子句]); 使用PARTITION BY子句 SELECT deptno,ename,sal,job, SUM(sal) OVER(PARTITION BY deptn...
MYSQL中分析函数---(分析函数方法名字) OVER(PARTITION BY ...... ORDER BY ......)
qq_44795873的博客
08-23 2750
分析函数解析---over()
数据分析之MySql篇{常用函数}
樊鴻燁的博客
07-04 573
常用函数字符串函数数学函数时期时间函数分组合并函数逻辑函数 字符串函数  CONCAT(str1,str2,…)   把多个文本字符串合并成一个长字符串()，除NULL外 mysql> select concat('CDA','数据', '分析'); +------------------------------+ | concat('CDA','数据', '分析') | +------------------------------+ | CDA数据分析 | +----------
Mysql中模拟分析函数
我思，故我在！--My data life
02-14 1067
[sql] view plain copySET @add_sal=0;    SELECT deptno, empno, ename, sal, @add_sal := @add_sal + sal AS add_sal    FROM emp   ORDER BY empno;    +--------+-------+--------+------+---------+  | deptno 
有关mysql实现oracle分析函数功能的方法
weixin_30363981的博客
03-06 206
目前公司erp开发有一个脚本需求：对于收款合同审批单和收款合同（n：1），需要获取收款审批单中最新的一条审批记录来更新其对应的收款合同的相关信息。 难点主要在对相同类别的属性进行分组然后组内排序（分组排序加序号），这类问题大致的思路，对于同类别的进行分组，组内排序标号来获取最新一条的数据，有点类似Oracle的分析函数功能。 由于mysql没有实现分析函数功能，在查询网上许多帖子也都是互相复制...
mysql利用自定义变量实现分析函数
sql_2016
10-24 437
公司存在一个需求，对考勤进行统计。考勤机的信息记录如下： 员工编号|员工名称| 打卡日期 |打卡时间 select * from table_name; 001    |li          |2016-10-20  |18:15 002    |yang    |2016-10-20 |09:28 003    |li1  |2016-10-20 |09:26 003  
Mysql5.7_SpatialAnalysisFunctions空间分析函数
weixin_45728126的博客
12-31 800
Mysql5.7_SpatialAnalysisFunctions空间分析函数 12.17 Spatial Analysis Functions 空间分析函数 12.17.1 Spatial Function Reference空间函数参考: https://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference.html 12.17.2 Argument Handling by Spatial Functions 空间函数参数处理：http
mysql中的分析函数_mysql中，分析函数实现的方法
weixin_42356659的博客
02-02 214
SELECT t2.team,MIN(t2.y) start_hm,MAX(t2.y) end_hmFROM (SELECT team,y,y - rank y1 from (SELECT tmp.team,tmp.y,IF(@groupid = tmp.team,@rank := @rank + 1,@rank := 1) AS rank,@groupid := tmp.teamFROM (SE...
详细分析Mysql常用函数（附Demo）
最新发布
码农研究僧的博客
04-18 1135
1. 聚合函数，2. 字符串函数，3. 日期函数，4. 条件函数，5. 数值函数，6. 类型转换函数
MySQL分析与整理 — 常用函数
初八
07-10 431
文章中所有操作均是在 MySQL 5.7 版本下进行的 MySQL 数据库中提供了很丰富的内置函数。MySQL 函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。这里我们就介绍一下常用内置函数，不太常用的将不做太多介绍。 1 数学常用函数 -- abs(m)，返回m的绝对值 select abs(-16); --16 -- mod(m,n)，返回m被n除的余数，n可以为0返回null，且有警告 select mod(11..
mysql 5.7函数说明
yudaonihaha的专栏
05-07 598
名称 描述 & 按位与 > 大于运算符 >> 右移 >= 大于或等于运算符 < 少于运算符 <>， != 不等于运算符 << 左移 <= 小于或等于运算符 <=> NULL安全等于运算符 %， MOD 模运算符 * 乘法运算符 + 加法运算符 - 减号 - 更改参数的符号 -> 评估路径后从JSON列返回值；等效于JSON_EXTRACT（）。 ->> （介绍5.7.13） 在评估路径并取消引用结果后，从J.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值