如何在MySQL中按日返回最大值？

Question

环境

表agent_poll记录呼叫中心代理所做的总呼叫，每1秒轮询电话系统以请求更新的总数。这就为每个代理每天提供了许多记录。

这一点如下所述。

DESCRIBE agent_poll
Field        |Type        |Null|Key|Default             |Extra         |
-------------|------------|----|---|--------------------|--------------|
agent_poll_id|int(11)     |NO  |PRI|                    |auto_increment|
poll_time    |timestamp(6)|NO  |   |CURRENT_TIMESTAMP(6)|              |
agent_id     |int(11)     |NO  |MUL|                    |              |
agent_status |int(11)     |NO  |MUL|                    |              |
total_calls  |int(11)     |NO  |   |                    |              |

下面是前十行的垃圾演示数据，它不像在生产中那样正常。

select * FROM agent_poll
agent_poll_id|poll_time                    |agent_id|agent_status|total_calls|
-------------|-----------------------------|--------|------------|-----------|
            1|          2019-11-12 12:02:01|       1|           1|         12|
            2|          2019-11-12 12:30:01|       4|           1|         12|
            3|          2019-11-12 12:34:18|       6|           4|         22|
            4|          2019-11-12 12:44:07|       1|           4|         22|
            5|          2019-11-14 12:15:44|       3|           3|          4|
            6|          2019-11-14 12:16:07|       1|           3|         23|
            7|          2019-11-14 12:21:42|       2|           3|          4|
            8|          2019-11-14 12:21:58|       5|           3|          4|
            9|          2019-11-14 12:22:47|       1|           1|         25|
           10|          2019-11-14 12:30:57|       2|           1|          4|

此外，下面是代理状态表。

select * from agent_status
agent_status_id|agent_status_description|
---------------|------------------------|
              1|Available               |
              2|Break                   |
              3|Admin                   |
              4|On a Call               |
              5|Sick                    |
              6|Holiday                 |
              7|Away                    |

问题

如何编写此查询？

• 如果agent_poll.agent_status为5、6或7，则返回agent_status，否则
• 返回每个代理在每周内接收的最大total_calls调用

限制

• 应该是动态的，以允许未来的表扩展-更多的代理，代理状态代码等。
• 应该总是查找最近的约会

输出应该如下所示。

Agent|Mon|Tue|Wed|Thu|Fri|
-----|---|---|---|---|---|
    1|   |   |   |   |   |
    2|   |   |   |   |   |
    3|   |   |   |   |   |
    4|   |   |   |   |   |
    5|   |   |   |   |   |
    6|   |   |   |   |   |
    7|   |   |   |   |   |
    8|   |   |   |   |   |
    9|   |   |   |   |   |
   10|   |   |   |   |   |
   11|   |   |   |   |   |
   12|   |   |   |   |   |
   13|   |   |   |   |   |

我已经尝试过的

我知道，给出正确的枢轴是可能的。由于我输出到Grafana，它只能具有只读权限，所以不能使用CREATE、UPDATE或DROP。

一种方法是使用虚拟表。这是一个TSQL向我建议的。

;WITH beepboop AS (
SELECT  B.agent_id,
        CASE WHEN WEEKDAY(B.poll_time) = 'Monday' THEN MAX(B.total_calls) ELSE NULL END AS 'Monday Calls', #one bin for the data to go into
        CASE WHEN WEEKDAY(B.poll_time) = 'Tuesday' THEN MAX(B.total_calls) ELSE NULL END AS 'Tuesday Calls',
        CASE WHEN WEEKDAY(B.poll_time) = 'Wednesday' THEN MAX(B.total_calls) ELSE NULL END AS 'Wednesday Calls',
        CASE WHEN WEEKDAY(B.poll_time) = 'Thursday' THEN MAX(B.total_calls) ELSE NULL END AS 'Thursday Calls',
        CASE WHEN WEEKDAY(B.poll_time) = 'Friday' THEN MAX(B.total_calls) ELSE NULL END AS 'Friday Calls'
FROM agent_poll AS B
WHERE B.poll_time > GETDATE() -7 --the last week
GROUP BY B.agent_id, DATENAME(WEEKDAY, B.poll_time) #Have to group by the datename as well as we're using it as part of the filter so it can't be aggregated
)
SELECT BB.agent_id, 
    MAX(BB.[Monday Calls]) AS 'Monday Calls', #now we are only grouping by the agent so we aggregate the bins 
    MAX(BB.[Tuesday Calls]) AS 'Tuesday Calls', 
    MAX(BB.[Wednesday Calls]) AS 'Wednesday Calls', 
    MAX(BB.[Thursday Calls]) AS 'Thursday Calls',
    MAX(BB.[Friday Calls]) AS 'Friday Calls'
FROM beepboop AS BB
GROUP BY BB.agent_id # only group by agent now

不幸的是，我还没有成功地在MySQL中实现这一目标。因此，我不得不假设，它需要从许多子查询中构建，但不能将它们放在正确的位置。这将返回agent_id以及当前周的日期。

SELECT agent_id as "Agent",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Monday'), '%X%V %W') as "Mon",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Tuesday'), '%X%V %W') as "Tue",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Wednesday'), '%X%V %W') as "Wed",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Thursday'), '%X%V %W') as "Thu",
STR_TO_DATE(CONCAT(yearweek(CURRENT_TIMESTAMP),' Friday'), '%X%V %W') as "Fri"
from agent
order by Agent

Agent|Mon       |Tue       |Wed       |Thu       |Fri       |
-----|----------|----------|----------|----------|----------|
    1|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    2|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    3|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    4|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    5|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    6|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    7|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    8|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
    9|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|
   10|2019-11-18|2019-11-19|2019-11-20|2019-11-21|2019-11-22|

我相信我只需要延长每一天的时间线，以他们提供的日期为条件。从这里我该去哪里？

Answer 1

您可以使用关联子查询对每个代理和工作日的最后记录进行筛选，然后执行条件聚合：

select 
    agent_id agent,
    max(case when weekday(poll_time) = 0 then total_calls end) mon,
    max(case when weekday(poll_time) = 1 then total_calls end) tue,
    max(case when weekday(poll_time) = 2 then total_calls end) wed,
    max(case when weekday(poll_time) = 3 then total_calls end) thu,
    max(case when weekday(poll_time) = 4 then total_calls end) fri
from agent a
where poll_time = (
    select max(polltime)
    from agent a1
    where 
        a.agent_poll_id = a1.agent_poll_id 
        and weekday(a.poll_time) = weekday(a1.poll_time)
)
group by agent_id