时间戳

首页 > 解决方案 > 你如何在 MySQL 中按天返回最大值？

mysql - 你如何在 MySQL 中按天返回最大值？

问题描述

环境

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

我相信我只需要延长每一天的路线，以使用它们提供的日期作为条件。我应该从这里去哪里？

标签： mysqlgrafana

解决方案

您可以使用相关子查询来过滤每个代理和工作日的最后一条记录，然后进行条件聚合：

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

推荐阅读