MySQL實(shí)現(xiàn)常用分析函數(shù)

分別在 MySQL5.7.25-log 和 8.0.16 環(huán)境中實(shí)現(xiàn)類似Oracle的分析函數(shù)(8.0版本中已支持劫樟，直接使用即可)。
一、創(chuàng)建測試數(shù)據(jù)
二硝枉、row_number() over()
三、rank() over()
四缠黍、dense_rank() over()
五弄兜、lag() over()
六、lead() over()
七瓷式、待補(bǔ)充

一替饿、創(chuàng)建測試數(shù)據(jù):

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

二、查詢結(jié)果添加序列號贸典，類Oracle 的row_number() OVER()

例1：不分組视卢，全部數(shù)據(jù)添加序列號，類Oracle 的rownum偽列

#### 按照房間和設(shè)備排序廊驼，依次標(biāo)注序列號
#########   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;

查詢結(jié)果同上据过，不予列出。

例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：不分組，全部數(shù)據(jù)按 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：不分組宾尚，全部數(shù)據(jù)按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：不分組怠肋，全部數(shù)據(jù)按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：不分組杈抢，全部數(shù)據(jù)按roomid,deviceid,counter升序排序数尿，類Oracle 的lead() OVER(ORDER BY)

#########   MySQL5.7   先全部降序，獲取前一個(gè)值惶楼，然后再升序即可
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 ;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末歼捐，一起剝皮案震驚了整個(gè)濱河市何陆，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌豹储，老刑警劉巖贷盲，帶你破解...
沈念sama閱讀 217,277評論 6贊 503
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異剥扣，居然都是意外死亡巩剖，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,689評論 3贊 393
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門朦乏，熙熙樓的掌柜王于貴愁眉苦臉地迎上來球及，“玉大人，你說我怎么就攤上這事呻疹〕砸” “怎么了？”我有些...
開封第一講書人閱讀 163,624評論 0贊 353
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵刽锤，是天一觀的道長镊尺。經(jīng)常有香客問我，道長并思，這世上最難降的妖魔是什么庐氮？我笑而不...
開封第一講書人閱讀 58,356評論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮宋彼，結(jié)果婚禮上弄砍，老公的妹妹穿的比我還像新娘仙畦。我一直安慰自己，他們只是感情好音婶，可當(dāng)我...
茶點(diǎn)故事閱讀 67,402評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布慨畸。她就那樣靜靜地躺著，像睡著了一般衣式。火紅的嫁衣襯著肌膚如雪寸士。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,292評論 1贊 301
城市分裂傳說
那天碴卧，我揣著相機(jī)與錄音弱卡，去河邊找鬼。笑死住册，一個(gè)胖子當(dāng)著我的面吹牛婶博，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播荧飞，決...
沈念sama閱讀 40,135評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼凡蜻，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了垢箕？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 38,992評論 0贊 275
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤兑巾，失蹤者是張志新（化名）和其女友劉穎条获，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體蒋歌，經(jīng)...
沈念sama閱讀 45,429評論 1贊 314
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡帅掘，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,636評論 3贊 334
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了堂油。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片修档。...
茶點(diǎn)故事閱讀 39,785評論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖府框，靈堂內(nèi)的尸體忽然破棺而出吱窝，到底是詐尸還是另有隱情，我是刑警寧澤迫靖，帶...
沈念sama閱讀 35,492評論 5贊 345
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布院峡，位于F島的核電站，受9級特大地震影響系宜，放射性物質(zhì)發(fā)生泄漏照激。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,092評論 3贊 328
男人毒藥：我在死后第九天來索命
文/蒙蒙一盹牧、第九天我趴在偏房一處隱蔽的房頂上張望俩垃。院中可真熱鬧励幼，春花似錦、人聲如沸口柳。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,723評論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽啄清。三九已至六水，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間辣卒，已是汗流浹背掷贾。一陣腳步聲響...
開封第一講書人閱讀 32,858評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留荣茫，地道東北人想帅。一個(gè)月前我還...
沈念sama閱讀 47,891評論 2贊 370
代替公主和親
正文我出身青樓，卻偏偏與公主長得像啡莉，于是被迫代替她去往敵國和親港准。傳聞我的和親對象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,713評論 2贊 354