我需要根据分组工作时间选择呼叫、应答、交易、费率、通话时间。
这是我的选择:
SELECT
users.username as username,
DATE_FORMAT(users_worktime.start,'%Y-%m-%d') as start,
SUM(users_worktime.length) as working_time
FROM
users_worktime
LEFT JOIN users ON users.id = users_worktime.user_id
WHERE 1
AND users_worktime.user_id = '8'
AND users_worktime.start >= '2015-12-30 00:00:00'
AND users_worktime.start <= '2015-12-31 23:59:59'
GROUP BY
DAY(users_worktime.start)
一切都很好,我在2015-12-30和2015-12-31之前得到了两排:
| username | start | working_time |
-----------------------------------------
| Haroldas | 2015-12-30 | 85.00 |
| Haroldas | 2015-12-31 | 170.00 |
我的问题是:如何从表调用中选择COUNT调用,答案-SUM所有状态为"结束"的调用,从表订单中选择COUNT交易,rate-deals/SUM调用,以及talking_time-calls.call_length。所有这些都是根据分组的工作时间进行选择的。
我需要这样的结果:
| username | start | calls | answers | deals | rate | talking_time| working_time |
----------------------------------------------------------------------------------------
| Haroldas | 2015-12-30 | 1 | 1 | 1 | 100% | 35 | 85.00 |
| Haroldas | 2015-12-31 | 3 | 2 | 1 | 50% | 160 | 170.00 |
这是我的数据表:
users_worktime:
| id | user_id | length | start |
-----------------------------------------------
| 1 | 8 | 30 | 2015-12-30 07:53:38 |
| 2 | 8 | 55 | 2015-12-30 12:53:38 |
| 3 | 8 | 170 | 2015-12-31 22:53:38 |
用户:
| id | username |
-----------------
| 8 | Haroldas |
调用:
| id | user_id | order_id | status | call_length | created_at |
-------------------------------------------------------------------------
| 1 | 8 | 3 | ended | 35 | 2015-12-30 07:53:38 |
| 2 | 8 | 4 | ended | 100 | 2015-12-31 12:53:38 |
| 3 | 8 | NULL | started | 15 | 2015-12-31 14:53:38 |
| 4 | 8 | NULL | ended | 45 | 2015-12-31 20:53:38 |
订单:
| id | user_id | call_id | start |
-----------------------------------------------
| 3 | 8 |1 | 2015-12-30 07:53:38 |
| 4 | 8 |2 | 2015-12-31 12:53:38 |
用户Haroldas这些天工作时有多少电话/应答/交易等。
感谢
您可以使用子查询来实现您的目标。我还更改了您的基本查询,因为若用户在指定的时间段内并没有工作,您将看不到记录。
SELECT T2.*,
(deals / answers_cnt) * 100 AS rate
FROM
(SELECT T.username,
T.day_start
SUM(T.working_time) AS working_time,
(SELECT COUNT(*) FROM calls AS C
WHERE DATE(created_at) = T.day_start
AND C.user_id = T.user_id) AS calls_cnt,
(SELECT COUNT(*) FROM calls AS C
WHERE DATE(created_at) = T.day_start
AND C.user_id = T.user_id
AND C.status = 'ended') AS answers_cnt,
(SELECT SUM(call_length) FROM calls AS C
WHERE DATE(created_at) = T.day_start
AND C.user_id = T.user_id) AS talking_time,
(SELECT COUNT(*) FROM orders AS O
WHERE DATE(O.start) = T.day_start
AND O.user_id = T.user_id) AS deals_cnt
FROM
(SELECT U.username,
U.id AS user_id,
DATE(UW.start) as day_start,
UW.length AS working_time
FROM users AS U
LEFT JOIN users_worktime AS UW ON UW.user_id = U.id
WHERE U.id = 8
AND UW.start >= '2015-12-30 00:00:00'
AND UW.start <= '2015-12-31 23:59:59') AS T
GROUP BY T.username, T.user_id, T.day_start
) AS T2
您可以通过user_id加上DAY时间来LEFT JOIN。例如
FROM
users_worktime
LEFT JOIN users ON users.id = users_worktime.user_id
LEFT JOIN users ON users.id = calls.user_id
AND DAY(users_worktime.start)=DAY(calls.created_at)
然后将所有必要的聚合函数应用于calls
数据