mysql累积和查询未返回预期结果

rfbsl7qr 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(215)

我正在尝试创建一个mysql查询（mysql v5），该查询按天/入站号码对呼叫记录进行总计，并使用运行的累计总计。我已经引用了其他关于堆栈溢出的页面，但是我得到的结果是不正确的。
引用：mysql cumulative sum by datemysql cumulative sum order by date
查询如下所示：

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中的相同值。

sql mysql aggregate-functions

来源：https://stackoverflow.com/questions/62046719/mysql-cumulative-sum-query-not-returning-expected-results

1条答案

按热度按时间

hfyxw5xn1#

在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 .
所有列均应合格。什么table start 从哪里来？
这个 GROUP BY 以及 SELECT 通过使用 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;

赞(0）回复(0）举报 2021-08-13

我来回答

mysql累积和查询未返回预期结果

1条答案

相关问题

热门标签

最新问答