1.日期中的重疊問題
建表sessions:

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

?

?

插入記錄:

insert into sessions(app,usr,starttime,endtime) values('app1','user1','08:30','08:45'); insert into sessions(app,usr,starttime,endtime) values('app1','user2','09:00','09:30'); insert into sessions(app,usr,starttime,endtime) values('app1','user1','09:15','10:30'); insert into sessions(app,usr,starttime,endtime) values('app1','user2','09:15','09:30'); insert into sessions(app,usr,starttime,endtime) values('app1','user1','10:30','14:30'); insert into sessions(app,usr,starttime,endtime) values('app1','user2','10:45','11:30'); insert into sessions(app,usr,starttime,endtime) values('app1','user1','11:00','12:30'); insert into sessions(app,usr,starttime,endtime) values('app2','user1','08:30','08:45'); insert into sessions(app,usr,starttime,endtime) values('app2','user1','08:30','08:45'); insert into sessions(app,usr,starttime,endtime) values('app2','user2','09:00','09:30'); insert into sessions(app,usr,starttime,endtime) values('app2','user1','11:45','12:00'); insert into sessions(app,usr,starttime,endtime) values('app2','user2','12:30','14:00'); insert into sessions(app,usr,starttime,endtime) values('app2','user1','12:45','13:30'); insert into sessions(app,usr,starttime,endtime) values('app2','user2','13:00','14:00'); insert into sessions(app,usr,starttime,endtime) values('app2','user1','14:00','16:30'); 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); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index idx_app_s_e on sessions(app,starttime,endtime); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0

?

?

mysql> show index in sessions; +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | sessions | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | sessions | 0 | idx_app_usr_s_e_key | 1 | app | A | 16 | NULL | NULL | | BTREE | | | | sessions | 0 | idx_app_usr_s_e_key | 2 | usr | A | 16 | NULL | NULL | | BTREE | | | | sessions | 0 | idx_app_usr_s_e_key | 3 | starttime | A | 16 | NULL | NULL | | BTREE | | | | sessions | 0 | idx_app_usr_s_e_key | 4 | endtime | A | 16 | NULL | NULL | | BTREE | | | | sessions | 0 | idx_app_usr_s_e_key | 5 | id | A | 16 | NULL | NULL | | BTREE | | | | sessions | 1 | idx_app_s_e | 1 | app | A | 16 | NULL | NULL | | BTREE | | | | sessions | 1 | idx_app_s_e | 2 | starttime | A | 16 | NULL | NULL | | BTREE | | | | sessions | 1 | idx_app_s_e | 3 | endtime | A | 16 | NULL | NULL | | BTREE | | | +----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 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);

?

?

+------+-------+-----------+----------+-----------+----------+ | app | usr | starttime | endtime | starttime | endtime | +------+-------+-----------+----------+-----------+----------+ | app1 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 | | app1 | user1 | 09:15:00 | 10:30:00 | 09:15:00 | 10:30:00 | | app1 | user1 | 09:15:00 | 10:30:00 | 10:30:00 | 14:30:00 | | app1 | user1 | 10:30:00 | 14:30:00 | 09:15:00 | 10:30:00 | | app1 | user1 | 10:30:00 | 14:30:00 | 10:30:00 | 14:30:00 | | app1 | user1 | 10:30:00 | 14:30:00 | 11:00:00 | 12:30:00 | | app1 | user1 | 11:00:00 | 12:30:00 | 10:30:00 | 14:30:00 | | app1 | user1 | 11:00:00 | 12:30:00 | 11:00:00 | 12:30:00 | | app1 | user2 | 09:00:00 | 09:30:00 | 09:00:00 | 09:30:00 | | app1 | user2 | 09:00:00 | 09:30:00 | 09:15:00 | 09:30:00 | | app1 | user2 | 09:15:00 | 09:30:00 | 09:00:00 | 09:30:00 | | app1 | user2 | 09:15:00 | 09:30:00 | 09:15:00 | 09:30:00 | | app1 | user2 | 10:45:00 | 11:30:00 | 10:45:00 | 11:30:00 | | app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 | | app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 | | app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 | | app2 | user1 | 08:30:00 | 08:45:00 | 08:30:00 | 08:45:00 | | app2 | user1 | 11:45:00 | 12:00:00 | 11:45:00 | 12:00:00 | | app2 | user1 | 12:45:00 | 13:30:00 | 12:45:00 | 13:30:00 | | app2 | user1 | 14:00:00 | 16:30:00 | 14:00:00 | 16:30:00 | | app2 | user2 | 09:00:00 | 09:30:00 | 09:00:00 | 09:30:00 | | app2 | user2 | 12:30:00 | 14:00:00 | 12:30:00 | 14:00:00 | | app2 | user2 | 12:30:00 | 14:00:00 | 13:00:00 | 14:00:00 | | app2 | user2 | 13:00:00 | 14:00:00 | 12:30:00 | 14:00:00 | | app2 | user2 | 13:00:00 | 14:00:00 | 13:00:00 | 14:00:00 | | app2 | user2 | 15:30:00 | 17:00:00 | 15:30:00 | 17:00:00 | +------+-------+-----------+----------+-----------+----------+ 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); +------+-------+----------+ | app | usr | s | +------+-------+----------+ | app1 | user1 | 08:30:00 | | app1 | user1 | 09:15:00 | | app1 | user2 | 09:00:00 | | app1 | user2 | 10:45:00 | | app2 | user1 | 08:30:00 | | app2 | user1 | 11:45:00 | | app2 | user1 | 12:45:00 | | app2 | user1 | 14:00:00 | | app2 | user2 | 09:00:00 | | app2 | user2 | 12:30:00 | | app2 | user2 | 15:30:00 | +------+-------+----------+ 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); +------+-------+----------+ | app | usr | e | +------+-------+----------+ | app1 | user1 | 08:30:00 | | app1 | user1 | 10:30:00 | | app1 | user2 | 09:00:00 | | app1 | user2 | 09:15:00 | | app1 | user2 | 10:45:00 | | app2 | user1 | 08:30:00 | | app2 | user1 | 11:45:00 | | app2 | user1 | 12:45:00 | | app2 | user1 | 14:00:00 | | app2 | user2 | 09:00:00 | | app2 | user2 | 12:30:00 | | app2 | user2 | 13:00:00 | | app2 | user2 | 15:30:00 | +------+-------+----------+ 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);

未完待續......

?

總結

以上是生活随笔為你收集整理的mysql--SQL编程(关于mysql中的日期,关于重叠) 学习笔记2.2的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。