mysql–SQL编程(关于mysql中的日期,关于重叠) 学习笔记2.2

Hi_Amos
2022-10-21
原文

1.日期中的重叠问题
建表sessions:

CREATE TABLE `sessions` (<br/>
`id` int(11) NOT NULL AUTO_INCREMENT,<br/>
`app` varchar(10) NOT NULL,<br/>
`usr` varchar(10) NOT NULL,<br/>
`starttime` time NOT NULL,<br/>
`endtime` time NOT NULL,<br/>
PRIMARY KEY (`id`)<br/>
) ENGINE=InnoDB

插入记录:

insert into sessions(app,usr,starttime,endtime) values('app1','user1','08:30','08:45');<br/>
insert into sessions(app,usr,starttime,endtime) values('app1','user2','09:00','09:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app1','user1','09:15','10:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app1','user2','09:15','09:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app1','user1','10:30','14:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app1','user2','10:45','11:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app1','user1','11:00','12:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user1','08:30','08:45');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user1','08:30','08:45');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user2','09:00','09:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user1','11:45','12:00');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user2','12:30','14:00');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user1','12:45','13:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user2','13:00','14:00');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user1','14:00','16:30');<br/>
insert into sessions(app,usr,starttime,endtime) values('app2','user2','15:30','17:00');

创建索引,加快查询速度:

mysql> create unique index idx_app_usr_s_e_key on sessions(app,usr,starttime,endtime,id);<br/>
Query OK, 0 rows affected (0.23 sec)<br/>
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index idx_app_s_e on sessions(app,starttime,endtime);<br/>
Query OK, 0 rows affected (0.22 sec)<br/>
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index in sessions;<br/>
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+<br/>
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |<br/>
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+<br/>
| sessions | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |<br/>
| sessions | 0 | idx_app_usr_s_e_key | 1 | app | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 0 | idx_app_usr_s_e_key | 2 | usr | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 0 | idx_app_usr_s_e_key | 3 | starttime | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 0 | idx_app_usr_s_e_key | 4 | endtime | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 0 | idx_app_usr_s_e_key | 5 | id | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 1 | idx_app_s_e | 1 | app | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 1 | idx_app_s_e | 2 | starttime | A | 16 | NULL | NULL | | BTREE | | |<br/>
| sessions | 1 | idx_app_s_e | 3 | endtime | A | 16 | NULL | NULL | | BTREE | | |<br/>
+----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+<br/>
9 rows in set (0.00 sec)

重叠的分类:标示重叠,分组重叠,最大重叠

标示重叠:为每个会话标示出相同应用程序用户重叠及最大重叠会话数

mysql> select a.app,a.usr,a.starttime,a.endtime,b.starttime,b.endtime from sessions a,sessions b where a.app=b.app and a.usr=b.usr and (b.endtime>=a.starttime and b.starttime<=a.endtime);

+------+-------+-----------+----------+-----------+----------+<br/>
| app | usr | starttime | endtime | starttime | endtime |<br/>
+------+-------+-----------+----------+-----------+----------+<br/>
| app1 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |<br/>
| app1 | user1 | 09:15:00 | 10:30:00 | 09:15:00 | 10:30:00 |<br/>
| app1 | user1 | 09:15:00 | 10:30:00 | 10:30:00 | 14:30:00 |<br/>
| app1 | user1 | 10:30:00 | 14:30:00 | 09:15:00 | 10:30:00 |<br/>
| app1 | user1 | 10:30:00 | 14:30:00 | 10:30:00 | 14:30:00 |<br/>
| app1 | user1 | 10:30:00 | 14:30:00 | 11:00:00 | 12:30:00 |<br/>
| app1 | user1 | 11:00:00 | 12:30:00 | 10:30:00 | 14:30:00 |<br/>
| app1 | user1 | 11:00:00 | 12:30:00 | 11:00:00 | 12:30:00 |<br/>
| app1 | user2 | 09:00:00 | 09:30:00 | 09:00:00 | 09:30:00 |<br/>
| app1 | user2 | 09:00:00 | 09:30:00 | 09:15:00 | 09:30:00 |<br/>
| app1 | user2 | 09:15:00 | 09:30:00 | 09:00:00 | 09:30:00 |<br/>
| app1 | user2 | 09:15:00 | 09:30:00 | 09:15:00 | 09:30:00 |<br/>
| app1 | user2 | 10:45:00 | 11:30:00 | 10:45:00 | 11:30:00 |<br/>
| app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |<br/>
| app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |<br/>
| app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |<br/>
| app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 |<br/>
| app2 | user1 | 11:45:00 | 12:00:00 | 11:45:00 | 12:00:00 |<br/>
| app2 | user1 | 12:45:00 | 13:30:00 | 12:45:00 | 13:30:00 |<br/>
| app2 | user1 | 14:00:00 | 16:30:00 | 14:00:00 | 16:30:00 |<br/>
| app2 | user2 | 09:00:00 | 09:30:00 | 09:00:00 | 09:30:00 |<br/>
| app2 | user2 | 12:30:00 | 14:00:00 | 12:30:00 | 14:00:00 |<br/>
| app2 | user2 | 12:30:00 | 14:00:00 | 13:00:00 | 14:00:00 |<br/>
| app2 | user2 | 13:00:00 | 14:00:00 | 12:30:00 | 14:00:00 |<br/>
| app2 | user2 | 13:00:00 | 14:00:00 | 13:00:00 | 14:00:00 |<br/>
| app2 | user2 | 15:30:00 | 17:00:00 | 15:30:00 | 17:00:00 |<br/>
+------+-------+-----------+----------+-----------+----------+<br/>
26 rows in set (0.00 sec)

分组重叠:服务商可能允许多个session的连接,并把其计费统计为1次,这就是所谓的分组重叠,对于例子中应该把app1,user1在08:30–10:30合并算为一次会话.
如下:

mysql> select distinct app,usr,starttime as s from sessions as a where not exists(select * from sessions as b where a.app=b.app and a.usr=b.usr and a.starttime>b.starttime and a.starttime<=b.endtime);

+------+-------+----------+<br/>
| app | usr | s |<br/>
+------+-------+----------+<br/>
| app1 | user1 | 08:30:00 |<br/>
| app1 | user1 | 09:15:00 |<br/>
| app1 | user2 | 09:00:00 |<br/>
| app1 | user2 | 10:45:00 |<br/>
| app2 | user1 | 08:30:00 |<br/>
| app2 | user1 | 11:45:00 |<br/>
| app2 | user1 | 12:45:00 |<br/>
| app2 | user1 | 14:00:00 |<br/>
| app2 | user2 | 09:00:00 |<br/>
| app2 | user2 | 12:30:00 |<br/>
| app2 | user2 | 15:30:00 |<br/>
+------+-------+----------+<br/>
11 rows in set (0.01 sec)

mysql> select distinct app,usr,starttime as e from sessions as a where not exists(select * from sessions as b where a.app=b.app and a.usr=b.usr and a.endtime>=b.starttime and a.endtime<b.endtime);

+------+-------+----------+<br/>
| app | usr | e |<br/>
+------+-------+----------+<br/>
| app1 | user1 | 08:30:00 |<br/>
| app1 | user1 | 10:30:00 |<br/>
| app1 | user2 | 09:00:00 |<br/>
| app1 | user2 | 09:15:00 |<br/>
| app1 | user2 | 10:45:00 |<br/>
| app2 | user1 | 08:30:00 |<br/>
| app2 | user1 | 11:45:00 |<br/>
| app2 | user1 | 12:45:00 |<br/>
| app2 | user1 | 14:00:00 |<br/>
| app2 | user2 | 09:00:00 |<br/>
| app2 | user2 | 12:30:00 |<br/>
| app2 | user2 | 13:00:00 |<br/>
| app2 | user2 | 15:30:00 |<br/>
+------+-------+----------+<br/>
13 rows in set (0.00 sec)

创建视图:v_s和v_e

mysql> create view v_s as select distinct app,usr,starttime as s from sessions as a where not exists(select * from sessions as b where a.app=b.app and a.usr=b.usr and a.starttime>b.starttime and a.starttime<=b.endtime);

mysql> create view v_e as select distinct app,usr,starttime as e from sessions as a where not exists(select * from sessions as b where a.app=b.app and a.usr=b.usr and a.endtime>=b.starttime and a.endtime<b.endtime);