mysql - MySQL累积和查询未返回预期结果
问题描述
我正在尝试创建一个 MySQL 查询(MySQL v5),该查询按天/入站号码汇总呼叫记录,并具有运行累积总数。我在 Stack Overflow 上引用了其他页面,但我得到的结果并没有加起来。
参考资料: MYSQL 按日期 累积总和 MySQL按日期累积总和
查询如下所示:
SET @RUNNING_TOTAL :=0;
SELECT
DATE_FORMAT(start,'%d/%m/%Y') As CallDate,
ch.did AS InboundNo,
COUNT(*) AS DayTotal,
(@RUNNING_TOTAL := @RUNNING_TOTAL + COUNT(*)) AS CumulativeCalls
FROM
`call_history` ch
LEFT JOIN (SELECT callid, event FROM ast_queue_log WHERE event = 'ENTERQUEUE') aql ON aql.callid = ch.callid
WHERE
ch.did = '01234567891' AND
start BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
GROUP BY
ch.did, DATE(start)
ORDER BY
ch.did;
我希望得到以下输出:
+-------------------------------+-------------+----------+-----------------+
| CallDate | InboundNo | DayTotal | CumulativeCalls |
+-------------------------------+-------------+----------+-----------------+
| 01/05/2020 | 01234567891 | 232 | 232 |
| 02/05/2020 | 01234567891 | 50 | 282 |
| 03/05/2020 | 01234567891 | 14 | 296 |
| 04/05/2020 | 01234567891 | 246 | 542 |
| 05/05/2020 | 01234567891 | 187 | 729 |
| 06/05/2020 | 01234567891 | 182 | 911 |
| 07/05/2020 | 01234567891 | 105 | 1016 |
| 08/05/2020 | 01234567891 | 46 | 1062 |
| 09/05/2020 | 01234567891 | 26 | 1088 |
| 10/05/2020 | 01234567891 | 7 | 1095 |
| 11/05/2020 | 01234567891 | 255 | 1350 |
+-------------------------------+-------------+----------+-----------------+
我得到的是每天在 DayTotal 和 CumulativeCalls 中的相同值。
解决方案
在 MySQL 8+ 中,您应该使用窗口函数:
SELECT DATE_FORMAT(DATE(start), '%d/%m/%Y') As CallDate,
ch.did AS InboundNo,
COUNT(*) AS DayTotal,
SUM(COUNT(*)) OVER (PARTITION BY ch.did ORDER BY DATE(start)) as CumulativeCalls
FROM call_history ch LEFT JOIN
ast_queue_log aql
ON aql.event = 'ENTERQUEUE' AND aql.callid = ch.callid
WHERE ch.did = '01234567891' AND
start BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW()
GROUP BY ch.did, DATE(start)
ORDER BY ch.did;
笔记:
- 不需要子查询
LEFT JOIN
。 - 所有列都应该是合格的。来自什么表
start
? - 通过在语句中使用,
GROUP BY
andSELECT
是一致的。DATE(start)
SELECT
在旧版本的 MySQL 中,您需要变量和子查询:
SELECT dc.*,
(@s := @s + DayTotal) as CumulativeCalls
FROM (SELECT DATE_FORMAT(DATE(start), '%d/%m/%Y') As CallDate,
ch.did AS InboundNo,
COUNT(*) AS DayTotal
FROM call_history ch LEFT JOIN
ast_queue_log aql
ON aql.event = 'ENTERQUEUE' AND aql.callid = ch.callid
WHERE ch.did = '01234567891' AND
start BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND NOW()
GROUP BY ch.did, DATE(start)
ORDER BY ch.did, DATE(start)
) dc CROSS JOIN
(SELECT @s := 0) params;
推荐阅读
- django - 从 DRF ListAPIView 获取数据后,如何将其转换为对其执行 map() 方法所需的表单?
- python - 变量在 python 2 中没有显示正确的值
- python - 通过张量损失时需要磁带
- python - 使用python中的递归函数从JSON获取嵌套字典
- html - 元素在 Chrome 中表现得很奇怪,但在 Firefox 中却没有?
- sql - 删除从当前日期开始超过两个月创建的数据库中的表
- python - 如何使用预训练的权重在 tensorflow 中训练卷积神经网络?
- javascript - 如果选择“全部”,则重置过滤器Vuejs
- postgresql - 使用 PostgreSQL 配置 Keycloak
- django - Django 静态文件和 Vue.js 静态目录的 NGINX 配置