找出用户单次最长在线时长，及相应登录、退出时间。

考点：窗口函数与TIMESTAMPDIFF()函数

题目：找出用户单次最长在线时长，及相应登录、退出时间。

1.建表：

CREATE TABLE IF NOT EXISTS `login_1`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `user_id` VARCHAR(10) NOT NULL,
   `opt_id` INT NOT NULL,
   `opt_type` VARCHAR(10) NOT NULL,
   `opt_time` TIMESTAMP NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入数据：

insert into login_1 (user_id, opt_id, opt_type, opt_time) values('A','0','登录','2019-03-13 12:35:43');
insert into login_1 (user_id, opt_id, opt_type, opt_time) values('A','1','退出','2019-03-13 13:34:42');
insert into login_1 (user_id, opt_id, opt_type, opt_time) values('A','0','登录','2019-03-14 14:38:25');
insert into login_1 (user_id, opt_id, opt_type, opt_time) values('B','0','登录','2019-03-15 12:16:22');
insert into login_1 (user_id, opt_id, opt_type, opt_time) values('B','1','退出','2019-03-15 17:43:53');
insert into login_1 (user_id, opt_id, opt_type, opt_time) values('A','1','退出','2019-03-14 16:18:34');

3.查询数据：

SELECT t2.user_id,
	   t2.online_time,
	   t2.login_time,
	   t2.logout_time
FROM(
	SELECT t1.user_id as user_id,
		   TIMESTAMPDIFF(SECOND, t1.opt_time, t1.next_opt_time) as online_time,
		   t1.opt_time as login_time, 
		   t1.next_opt_time as logout_time, 
		   ROW_NUMBER() OVER(PARTITION BY t1.user_id ORDER BY TIMESTAMPDIFF(SECOND, t1.opt_time, t1.next_opt_time) DESC) as row_time
	FROM(
		SELECT user_id,
			   opt_id,
			   opt_type,
			   opt_time,
			   LEAD(opt_time,1) OVER(PARTITION BY user_id ORDER BY opt_time) next_opt_time
		FROM login_1)t1
	WHERE t1.opt_id = 0)t2
WHERE row_time = 1