php - 如何按工作时间选择来电、接听、优惠、费率等？

Question

我需要按分组工作时间选择通话、接听、交易、费率、通话时间。

这是我的选择:

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 日有 2 行:

| username | start       | working_time |
-----------------------------------------
| Haroldas | 2015-12-30  | 85.00        | 
| Haroldas | 2015-12-31  | 170.00       |

我的问题是:如何从表调用中选择 COUNT 个调用，答案 - 对状态 =“已结束”的所有调用进行求和，从表订单中选择 COUNT 个交易，费率 - 交易/SUM 调用，以及通话时间 - 通话长度。所有这些都是按分组工作时间来选择的。

我需要这样的结果:

| 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       |

这是我的数据表:

用户工作时间:

| 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 这些天工作时有多少个电话/接听/交易/等等。

谢谢

Answer 1

您可以使用子查询来实现您的目标。我还更改了您的基本查询，因为如果用户在指定时间段内没有工作，您将看不到记录。

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