MySQL通过虚拟列相邻行数据处理

MySQL通过虚拟列相邻行数据处理

1、问题描述

需要比较同一个表中相邻两行的数据差异，做数据处理。

2、数据环境

表结构如下：

mysql> desc bigdata;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| str_info  | varchar(16) | YES  | MUL | NULL    |       |
| sys_date  | datetime    | YES  | MUL | NULL    |       |
| sys_year  | int(11)     | YES  |     | NULL    |       |
| sys_month | int(11)     | YES  |     | NULL    |       |
| sys_week  | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

数据如下所示：

mysql> select * from bigdata limit 3;
+------------------+---------------------+----------+-----------+----------+
| str_info         | sys_date            | sys_year | sys_month | sys_week |
+------------------+---------------------+----------+-----------+----------+
| 11A1EA00347BE1B6 | 2017-12-27 08:47:10 |     2017 |        12 |       52 |
| 7CEFF8E8ED098F65 | 2018-05-21 07:11:14 |     2018 |         5 |       20 |
| 96ABCEC7FDCE638C | 2018-12-17 09:29:18 |     2018 |        12 |       50 |
+------------------+---------------------+----------+-----------+----------+
3 rows in set (0.00 sec)

需求：
按时间排序后，把str_info相邻两行合并。

3、虚拟列

通过select @arownum:=0 做虚拟表，和数据表关联后，增加一个虚拟列rownum :

mysql> select @arownum:=@arownum+1 rownum,t.* from bigdata t,(select @arownum:=0) a order by t.sys_date limit 3;
+--------+------------------+---------------------+----------+-----------+----------+
| rownum | str_info         | sys_date            | sys_year | sys_month | sys_week |
+--------+------------------+---------------------+----------+-----------+----------+
|      1 | 13034E50378C24F9 | 2017-01-01 00:00:10 |     2017 |         1 |        1 |
|      2 | B7E9CA875F6D362E | 2017-01-01 00:00:16 |     2017 |         1 |        1 |
|      3 | 01EEAD2F6A7BB2A5 | 2017-01-01 00:00:16 |     2017 |         1 |        1 |
+--------+------------------+---------------------+----------+-----------+----------+
3 rows in set, 2 warnings (0.00 sec)

4、相邻行处理

在上面的语句上，做成两个临时表，利用行号差进行对比。
第一个临时表，select @arownum:=0，序号从1开始；
第二个临时表，select @brownum:=1，序号从2开始；
所以，按序号关联第一个临时表的当前，第二个临时表为之前。把信息字符串合并。

select t1.str_info cur_info, t1.sys_date cur_date,t2.sys_date
pre_date, t2.str_info pre_info ,concat(t1.str_info,t2.str_info) as
concatinof from (select @arownum:=@arownum+1 rownum,t.* from bigdata
t,(select @arownum:=0) a order by t.sys_date limit 5) t1 , (select
@brownum:=@brownum+1 rownum,t.* from bigdata t,(select @brownum:=1) b
order by t.sys_date limit 5) t2 where t1.rownum=t2.rownum;

mysql> select t1.str_info cur_info, t1.sys_date cur_date,t2.sys_date pre_date, t2.str_info pre_info ,concat(t1.str_info,t2.str_info) as concatinof from 
    -> (select @arownum:=@arownum+1 rownum,t.* from bigdata t,(select @arownum:=0) a order by t.sys_date limit 5) t1 ,
    -> (select @brownum:=@brownum+1 rownum,t.* from bigdata t,(select @brownum:=1) b order by t.sys_date limit 5) t2
    -> where t1.rownum=t2.rownum;
+------------------+---------------------+---------------------+------------------+----------------------------------+
| cur_info         | cur_date            | pre_date            | pre_info         | concatinof                       |
+------------------+---------------------+---------------------+------------------+----------------------------------+
| B7E9CA875F6D362E | 2017-01-01 00:00:16 | 2017-01-01 00:00:10 | 13034E50378C24F9 | B7E9CA875F6D362E13034E50378C24F9 |
| 01EEAD2F6A7BB2A5 | 2017-01-01 00:00:16 | 2017-01-01 00:00:16 | B7E9CA875F6D362E | 01EEAD2F6A7BB2A5B7E9CA875F6D362E |
| DF5674B2AD590C2B | 2017-01-01 00:00:18 | 2017-01-01 00:00:16 | 01EEAD2F6A7BB2A5 | DF5674B2AD590C2B01EEAD2F6A7BB2A5 |
| 171A3E61A84EE8EC | 2017-01-01 00:00:18 | 2017-01-01 00:00:18 | DF5674B2AD590C2B | 171A3E61A84EE8ECDF5674B2AD590C2B |
+------------------+---------------------+---------------------+------------------+----------------------------------+
4 rows in set, 4 warnings (0.00 sec)

mysql> 

MySQL通过虚拟列相邻行数据处理，重点还是在于rownum行号的处理上。

5、相隔N行

如果要同一个表中，间隔5行取一条记录。
为了从第一条记录开始： @arownum:=-1
每隔5行取一条数据：mod(h.rownum,5) = 0

select * from (
	select @arownum:=@arownum+1 rownum,t.* from bigdata t,(select @arownum:=-1) a order by t.sys_date limit 1000 ) h 
		where mod(h.rownum,5) = 0 limit 5;

效果如下：

mysql> select * from (
    -> select @arownum:=@arownum+1 rownum,t.* from bigdata t,(select @arownum:=-1) a order by t.sys_date limit 1000 ) h 
    -> where mod(h.rownum,5) = 0 limit 5;
+--------+------------------+---------------------+----------+-----------+----------+
| rownum | str_info         | sys_date            | sys_year | sys_month | sys_week |
+--------+------------------+---------------------+----------+-----------+----------+
|      0 | 13034E50378C24F9 | 2017-01-01 00:00:10 |     2017 |         1 |        1 |
|      5 | 34F69381FB244AB8 | 2017-01-01 00:00:23 |     2017 |         1 |        1 |
|     10 | E5E5348D52DD0EC3 | 2017-01-01 00:00:39 |     2017 |         1 |        1 |
|     15 | 92BC97452788960C | 2017-01-01 00:01:02 |     2017 |         1 |        1 |
|     20 | B1E2DD7EA84F6460 | 2017-01-01 00:01:23 |     2017 |         1 |        1 |
+--------+------------------+---------------------+----------+-----------+----------+
5 rows in set, 2 warnings (0.01 sec)