我正在尝试让下面的select语句工作。从本质上讲,我试图为每个代理获得一行&日期,包含代理状态="已登录"时的最小值(时间)、代理状态="注销"时的最大值(时间"、timediff(max(时间),min(时间))和代理状态='准备就绪'时的总和(持续时间)。
我目前的SQL语句还不完整,因为我一开始只是得到正确的最小/最大时间值,结果遇到了麻烦。现在,会返回Logged in min time,但不会返回logout max time。
AGENT, DATE, TIME, DURATION, AGENT_STATE
Alex, 2013-01-01, 07:25:37, 00:00:00, Logged-in
Alex, 2013-01-01, 07:26:01, 00:24:48, Ready
Alex, 2013-01-01, 07:54:20, 00:21:47, Ready
Alex, 2013-01-01, 08:28:11, 00:03:00, Ready
Alex, 2013-01-01, 08:34:11, 00:06:29, Ready
Alex, 2013-01-01, 08:44:30, 00:05:14, Ready
Alex, 2013-01-01, 08:53:29, 00:06:52, Ready
Alex, 2013-01-01, 09:03:48, 00:12:13, Ready
Alex, 2013-01-01, 09:31:56, 00:36:44, Ready
Alex, 2013-01-01, 09:32:27, 14:58:51, Logout
Alex, 2013-01-01, 10:11:37, 00:00:00, Logged-in
Alex, 2013-01-01, 10:12:26, 00:54:44, Ready
Alex, 2013-01-01, 11:09:28, 00:47:48, Ready
Alex, 2013-01-01, 11:57:33, 00:16:54, Ready
Alex, 2013-01-01, 12:15:15, 00:17:32, Ready
Alex, 2013-01-01, 12:34:44, 00:13:32, Ready
Alex, 2013-01-01, 12:51:04, 00:16:44, Ready
Alex, 2013-01-01, 13:12:36, 00:31:38, Ready
Alex, 2013-01-01, 13:49:46, 00:39:14, Ready
Alex, 2013-01-01, 14:31:42, 00:28:45, Ready
Alex, 2013-01-01, 15:00:27, 14:58:51, Logout
SQL语句:
select
`agent`,
`date`,
if(`agent_state` = 'Logged-in', min(`time`), null) as `min_login`,
if(`agent_state` = 'Logout', max(`time`),null) as `max_logout`
from
t_cisco_agent_state_details
group by
`agent`, `date`
order by
`agent`, `date`, `time` asc;
感谢您的帮助或建议。
Jim
你不能这样做。"other"列为null的原因是,当您按代理和日期分组时,agent_state不存在。如果通过将agent_state添加到组中,则每个代理/日期将获得3行。这不是你想要的。
您需要一个子查询或两个自联接。试试这样的东西:
SELECT agent,
date,
MIN(dur) AS total_duration,
TIMEDIFF(MIN(maxt), MIN(mint)) AS time_difference_between_login_and_out,
MIN(maxt) AS logout_time,
MIN(mint) AS login_time
FROM (SELECT agent,
date,
IF(agent_state = 'Ready', SUM(time), NULL) AS dur,
IF(agent_state = 'Logged-in', MIN(time), NULL) AS mint,
IF(agent_state = 'Logout', MAX(time), NULL) AS maxt,
agent_state
FROM t_cisco_agent_state_details
GROUP BY agent,
date,
agent_state) AS subq
GROUP BY agent,
date
子查询为每个代理状态、代理和日期创建一行。然后,您可以使用外部查询从中提取时间,该查询仅按代理和日期分组。