mysql使用04

mysql> #现在我们来看看重叠问题
mysql>
mysql> create table sessions(
    -> id int primary key not null auto_increment,
    -> app varchar(10) not null,
    -> usr varchar(10) not null,
    -> starttime time not null,
    -> endtime time not null);
Query OK, 0 rows affected (0.10 sec)

mysql> #现在插入一些数据
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr1', '08:30', '10:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr2', '08:30', '08:45');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr1', '09:00', '09:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr2', '09:15', '10:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr1', '09:15', '09:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr2', '10:30', '14:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr1', '10:45', '11:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app1', 'usr2', '11:00', '12:30');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr1', '08:30', '08:45');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr2', '09:00', '09:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr1', '11:45', '12:00');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr2', '12:30', '14:00');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr1', '12:45', '13:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr2', '13:00', '14:00');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr1', '14:00', '16:30');
Query OK, 1 row affected (0.03 sec)

mysql>
mysql> insert into sessions(app, usr, starttime, endtime)
    -> values('app2', 'usr2', '15:30', '17:00');
Query OK, 1 row affected (0.03 sec)

mysql> select * from sessions;
+----+------+------+-----------+----------+
| id | app  | usr  | starttime | endtime  |
+----+------+------+-----------+----------+
|  1 | app1 | usr1 | 08:30:00  | 10:30:00 |
|  2 | app1 | usr2 | 08:30:00  | 08:45:00 |
|  3 | app1 | usr1 | 09:00:00  | 09:30:00 |
|  4 | app1 | usr2 | 09:15:00  | 10:30:00 |
|  5 | app1 | usr1 | 09:15:00  | 09:30:00 |
|  6 | app1 | usr2 | 10:30:00  | 14:30:00 |
|  7 | app1 | usr1 | 10:45:00  | 11:30:00 |
|  8 | app1 | usr2 | 11:00:00  | 12:30:00 |
|  9 | app2 | usr1 | 08:30:00  | 08:45:00 |
| 10 | app2 | usr2 | 09:00:00  | 09:30:00 |
| 11 | app2 | usr1 | 11:45:00  | 12:00:00 |
| 12 | app2 | usr2 | 12:30:00  | 14:00:00 |
| 13 | app2 | usr1 | 12:45:00  | 13:30:00 |
| 14 | app2 | usr2 | 13:00:00  | 14:00:00 |
| 15 | app2 | usr1 | 14:00:00  | 16:30:00 |
| 16 | app2 | usr2 | 15:30:00  | 17:00:00 |
+----+------+------+-----------+----------+
16 rows in set (0.00 sec)

mysql> #现在创建表的索引
mysql> create unique index idx_app_usr_s_e_key on sessions(app, usr, starttime, endtime);
Query OK, 0 rows affected (0.16 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.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> #现在 我们看一下用户重叠的会话情况
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 | usr1 | 08:30:00  | 10:30:00 | 08:30:00  | 10:30:00 |
| app1 | usr1 | 08:30:00  | 10:30:00 | 09:00:00  | 09:30:00 |
| app1 | usr1 | 08:30:00  | 10:30:00 | 09:15:00  | 09:30:00 |
| app1 | usr1 | 09:00:00  | 09:30:00 | 08:30:00  | 10:30:00 |
| app1 | usr1 | 09:00:00  | 09:30:00 | 09:00:00  | 09:30:00 |
| app1 | usr1 | 09:00:00  | 09:30:00 | 09:15:00  | 09:30:00 |
| app1 | usr1 | 09:15:00  | 09:30:00 | 08:30:00  | 10:30:00 |
| app1 | usr1 | 09:15:00  | 09:30:00 | 09:00:00  | 09:30:00 |
| app1 | usr1 | 09:15:00  | 09:30:00 | 09:15:00  | 09:30:00 |
| app1 | usr1 | 10:45:00  | 11:30:00 | 10:45:00  | 11:30:00 |
| app1 | usr2 | 08:30:00  | 08:45:00 | 08:30:00  | 08:45:00 |
| app1 | usr2 | 09:15:00  | 10:30:00 | 09:15:00  | 10:30:00 |
| app1 | usr2 | 09:15:00  | 10:30:00 | 10:30:00  | 14:30:00 |
| app1 | usr2 | 10:30:00  | 14:30:00 | 09:15:00  | 10:30:00 |
| app1 | usr2 | 10:30:00  | 14:30:00 | 10:30:00  | 14:30:00 |
| app1 | usr2 | 10:30:00  | 14:30:00 | 11:00:00  | 12:30:00 |
| app1 | usr2 | 11:00:00  | 12:30:00 | 10:30:00  | 14:30:00 |
| app1 | usr2 | 11:00:00  | 12:30:00 | 11:00:00  | 12:30:00 |
| app2 | usr1 | 08:30:00  | 08:45:00 | 08:30:00  | 08:45:00 |
| app2 | usr1 | 11:45:00  | 12:00:00 | 11:45:00  | 12:00:00 |
| app2 | usr1 | 12:45:00  | 13:30:00 | 12:45:00  | 13:30:00 |
| app2 | usr1 | 14:00:00  | 16:30:00 | 14:00:00  | 16:30:00 |
| app2 | usr2 | 09:00:00  | 09:30:00 | 09:00:00  | 09:30:00 |
| app2 | usr2 | 12:30:00  | 14:00:00 | 12:30:00  | 14:00:00 |
| app2 | usr2 | 12:30:00  | 14:00:00 | 13:00:00  | 14:00:00 |
| app2 | usr2 | 13:00:00  | 14:00:00 | 12:30:00  | 14:00:00 |
| app2 | usr2 | 13:00:00  | 14:00:00 | 13:00:00  | 14:00:00 |
| app2 | usr2 | 15:30:00  | 17:00:00 | 15:30:00  | 17:00:00 |
+------+------+-----------+----------+-----------+----------+
28 rows in set (0.00 sec)

mysql> #ok  现在我们可以看出会话的重叠了
mysql>
mysql> #现在 来看看分组会话  (场景:将在前一段时刻的会话包含后一段时刻 则归为钱一时刻的会话)
mysql>
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 | usr1 | 08:30:00 |
| app1 | usr1 | 10:45:00 |
| app1 | usr2 | 08:30:00 |
| app1 | usr2 | 09:15:00 |
| app2 | usr1 | 08:30:00 |
| app2 | usr1 | 11:45:00 |
| app2 | usr1 | 12:45:00 |
| app2 | usr1 | 14:00:00 |
| app2 | usr2 | 09:00:00 |
| app2 | usr2 | 12:30:00 |
| app2 | usr2 | 15:30:00 |
+------+------+----------+
11 rows in set (0.00 sec)

mysql> #从现象中 我们可以看到此时 会话合并了 条件很明确(a.starttime>b.satrttime) 保证了滞后性  a.starttime<=b.endtime  保证了数据的完整性
mysql>
mysql> #现在我们也可以得到会话的结束时间
mysql>
mysql> select distinct app, usr, endtime 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 | usr1 | 10:30:00 |
| app1 | usr1 | 11:30:00 |
| app1 | usr2 | 08:45:00 |
| app1 | usr2 | 14:30:00 |
| app2 | usr1 | 08:45:00 |
| app2 | usr1 | 12:00:00 |
| app2 | usr1 | 13:30:00 |
| app2 | usr1 | 16:30:00 |
| app2 | usr2 | 09:30:00 |
| app2 | usr2 | 14:00:00 |
| app2 | usr2 | 17:00:00 |
+------+------+----------+
11 rows in set (0.00 sec)

mysql> #现在我们标出所有时间的不重叠范围
mysql>
mysql> select distinct s.app, s.usr, s.s,
    -> (
    -> select min(e)
    -> from
    -> (
    -> select distinct app, usr, endtime 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
    -> )
    -> )
    -> as s2
    -> where s2.e>s.s and s.app=s2.app and s.usr=s2.usr
    -> )
    -> as e
    -> from
    -> (
    -> 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
    -> )
    -> )
    -> as s,
    -> (
    -> select distinct app, usr, endtime 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
    -> )
    -> )
    -> as e
    -> where s.app=e.app and s.usr=e.usr;
+------+------+----------+----------+
| app  | usr  | s        | e        |
+------+------+----------+----------+
| app1 | usr1 | 08:30:00 | 10:30:00 |
| app1 | usr1 | 10:45:00 | 11:30:00 |
| app1 | usr2 | 08:30:00 | 08:45:00 |
| app1 | usr2 | 09:15:00 | 14:30:00 |
| app2 | usr1 | 08:30:00 | 08:45:00 |
| app2 | usr1 | 11:45:00 | 12:00:00 |
| app2 | usr1 | 12:45:00 | 13:30:00 |
| app2 | usr1 | 14:00:00 | 16:30:00 |
| app2 | usr2 | 09:00:00 | 09:30:00 |
| app2 | usr2 | 12:30:00 | 14:00:00 |
| app2 | usr2 | 15:30:00 | 17:00:00 |
+------+------+----------+----------+
11 rows in set (0.00 sec)

mysql>  
mysql> #查询重叠时期内的并发数目
mysql> select app, s(
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> select app, s, (select count(1) from sessions as b where
    -> s>=starttime and s<endtime) as count
    -> from (select distinct app, starttime as a from sessions) as a;
ERROR 1054 (42S22): Unknown column 's' in 'field list'
mysql> select app, s, (select count(1) from sessions as b where  s>=starttime and s<endtime) as count from (select distinct app, starttime as s from sessions) as a;
+------+----------+-------+
| app  | s        | count |
+------+----------+-------+
| app1 | 08:30:00 |     3 |
| app1 | 09:00:00 |     3 |
| app1 | 09:15:00 |     5 |
| app1 | 10:30:00 |     1 |
| app1 | 10:45:00 |     2 |
| app1 | 11:00:00 |     3 |
| app2 | 08:30:00 |     3 |
| app2 | 09:00:00 |     3 |
| app2 | 11:45:00 |     3 |
| app2 | 12:30:00 |     2 |
| app2 | 12:45:00 |     3 |
| app2 | 13:00:00 |     4 |
| app2 | 14:00:00 |     2 |
| app2 | 15:30:00 |     2 |
+------+----------+-------+
14 rows in set (0.00 sec)

mysql> #可以看出我们计算出了 一个最大重叠时期内的重叠的个数
mysql>
mysql>
mysql> #现在 我们又要算出app1, app2的最大会话数
mysql>
mysql> select app, max(count) as max
    -> from (select app, s,
    -> (select count(1) from sessions as b where a.app=b.app and s>=starttime and s<endtime) as count
    -> from (selct distinct app, starttime as s from sessions ) as a) as bc group by app;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct app, starttime as s from sessions ) as a) as bc group by app' at line 4
mysql>
mysql>
mysql> select app, max(count) as max from (select app, s, (select count(1) from sessions as b where a.app=b.app and s>=starttime and s<endtime) as count  from (select distinct app, starttime as s from sessions ) as a) as bc group by app;
+------+------+
| app  | max  |
+------+------+
| app1 |    4 |
| app2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> #可以看出结果了
mysql> exit