无子查询的MySQL查询优化

Question

我正在计算每个用户使用此查询的总登录时间：

模式：

CREATE TABLE `EventLog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventType` varchar(45) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `timeZone` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGIN', '2014-04-27 09:00:04', 1,'GMT-7');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGIN', '2014-04-27 10:00:04', 1,'GMT-7');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGIN', '2014-04-27 10:00:04', 2,'GMT-6');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGOUT', '2014-04-27 09:49:04', 1,'GMT-7');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGOUT', '2014-04-27 10:30:04', 1,'GMT-7');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGOUT', '2014-04-27 10:30:04', 2,'GMT-6');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGIN', '2014-04-27 11:49:04', 3,'GMT-5');
INSERT INTO `EventLog` (`eventType`, `time`, `userId`,`timeZone`) VALUES ('LOGIN', '2014-04-27 08:30:04', 4,'GMT-2');

查询：

select userId,
sum(loginTimeInHour) as loginTimeInHour
from(
    select e1.userId, (TIME_TO_SEC(TIMEDIFF(IFNULL(e2.time,NOW()),e1.time)))/(60*60) as loginTimeInHour from 
    EventLog e1
    left join EventLog e2
    on e1.userId = e2.userId
    and e2.eventType = 'LOGOUT'
    and e1.time < e2.time
    where e1.eventType = 'LOGIN'
    group by e1.id having min(IFNULL(e2.time,NOW()) - e1.time)
) temp
group by userId;

(小提琴)。如果不使用SubQuery，是否可能得到相同的结果。实际上，我想要创建mysql、视图、和视图不假设SubQuery。有什么选择吗？谢谢你的忠告

Answer 1

我得到了答案

SELECT e1.userid, SUM(UNIX_TIMESTAMP(COALESCE(e2.time, NOW())) - 
                      UNIX_TIMESTAMP(e1.time))/3600 total 
FROM eventlog e1 
LEFT JOIN eventlog e2
  ON e1.userid = e2.userid AND e2.eventType='LOGOUT' AND e1.time < e2.time
LEFT JOIN eventlog e3
  ON e1.userid = e3.userid AND e1.time < e3.time AND e3.time < e2.time
WHERE e1.eventType='LOGIN' AND e3.time IS NULL
GROUP BY e1.userid

Answer 2

您可以避免子查询以获得相同的结果：

select e1.userId,
sum((TIME_TO_SEC(TIMEDIFF(IFNULL(e2.time,NOW()),e1.time)))/(60*60)) 
as loginTimeInHour from
EventLog e1
left join EventLog e2
on e1.userId = e2.userId
and e2.eventType = 'LOGOUT'
and e1.time < e2.time
 where e1.eventType = 'LOGIN'
group by e1.id having min(IFNULL(e2.time,NOW()) - e1.time)

SQL Fiddle

Answer 3

您可以通过这样的方式加入表来优化查询：

select e1.userId, 
    sum((TIME_TO_SEC(TIMEDIFF(IFNULL(e2.time,NOW()), e1.time)))/(60*60)) as loginTimeInHour
from EventLog e1 left outer join EventLog e2 on
    (e1.userId = e2.userId and e1.time < coalesce(e2.time, NOW()))
    where e1.eventType = 'LOGIN' and coalesce(e2.eventType, 'LOGOUT') = 'LOGOUT'
group by e1.userId; 

更新：

由于上面的查询总结了登录时间/注销时间的所有变化，所以它不适合您。

为了满足您的需求，查询需要双重聚合(例如sum(min()，mysql不允许这样做。这样的查询必须被分割成子查询，就像您在问题中的子查询一样。

不幸的是，您无法创建一个在其FROM子句中包含子查询的视图，因此，我建议您考虑让视图记录每个登录会话的持续时间，并在查询数据时对其进行检索总结：

select distinct e1.userId, 
    TIME_TO_SEC(TIMEDIFF(coalesce(e2.time,NOW()), e1.time))/(60*60) as LoginTimeHour
from EventLog e1 left outer join EventLog e2 on
    (e1.userId = e2.userId and e1.time < coalesce(e2.time, NOW()))
    where e1.eventType = 'LOGIN' and coalesce(e2.eventType, 'LOGOUT') = 'LOGOUT'
group by  e1.userId, e1.time;

您还可以考虑在记录一个EventLog事件时，使用一个loggedInTime字段扩展您的LOGOUT表，在触发器中填充该字段(计算实际用户登录的时间)。

这样，你已经有时间来总结你的观点。