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)