我需要计算在某些范围0到10秒,0至20秒内回答的呼叫。。%将是当前计数/最终计数总数。
这是您可以与数据一起测试的SQLFIDDLE:http://sqlfiddle.com/#!9/803d2/2
calldetails的示例表:
+-----+----------------+----------+----------+---------------+
| id | callid | callerno | duration | status |
+-----+----------------+----------+----------+---------------+
| 634 | 1479097551.228 | 1000 | 2 | complete |
| 635 | 1479102518.248 | 1000 | 12 | complete |
+-----+----------------+----------+----------+---------------+
预期结果:
+------------------------+----------+----------+----------+
| Ranges | Count | Delta | % |
+------------------------+----------+----------+----------+
| Between 0 to 10 secs | 44 | +44 | 84.62 % |
| Between 0 to 20 secs | 48 | +4 | 92.31 % |
| Between 0 to 30 secs | 50 | +2 | 96.15 % |
| Between 0 to 40 secs | 51 | +1 | 98.08 % |
| Between 0 to 50 secs | 51 | +0 | 98.08 % |
| Between 0 to 60 secs | 51 | +0 | 98.08 % |
| Between 0 to 70 secs | 51 | +0 | 98.08 % |
| Between 0 to 80 secs | 52 | +1 | 100.00 % |
| Between 0 to 90 secs | 52 | +0 | 100.00 % |
| Between 0 to 100+ secs | 52 | +0 | 100.00 % |
+------------------------+----------+----------+----------+
Total 52
如果您可以帮助提供更好的解决方案,请提供建议,我现在能够创建的问题。我现在面临的问题是(优先)我无法获得计数和(次要的)%总数(52),现在我手动将最终计数总数(52)投入。请帮忙。
SELECT Ranges,Delta,ROUND(Delta/52*100,2) AS '%'
FROM
(
SELECT
(
IF(duration<=10,'10',IF(duration<=20,'20',IF(duration<=30,'30',
IF(duration<=40,'40',IF(duration<=50,'50',
IF(duration<=60,'60',IF(duration<=70,'70',IF(duration<=80,
'80',IF(duration<=90,'90','100+'))))))))))
AS Ranges,COUNT(duration) AS Delta
FROM callsdetails
GROUP BY Ranges
) a
GROUP BY Ranges;
当前结果:
+--------+-------+-------+
| Ranges | Delta | % |
+--------+-------+-------+
| 10 | 44 | 84.62 |
| 20 | 4 | 7.69 |
| 30 | 2 | 3.85 |
| 40 | 1 | 1.92 |
| 80 | 1 | 1.92 |
+--------+-------+-------+
====================================================================================
感谢ETSA帮助我解决了上述问题,这是所有呼叫的总数,我现在可能需要再次提供帮助。它是与上述公式相同的公式,但现在必须分组或每个队列名称组。
这是您可以与数据一起用于测试的SQLFIDDLE:http://sqlfiddle.com/#!9/efe11b/2
预期结果:
+---------+------+-----------+-------+--------+
| QUEUE | RAN | TOT_COUNT | DELTA | % |
+---------+------+-----------+-------+--------+
| sales | 10 | 6 | 6 | 100.00 |
| sales | 20 | 6 | 0 | 100.00 |
| sales | 30 | 6 | 0 | 100.00 |
| sales | 40 | 6 | 0 | 100.00 |
| sales | 50 | 6 | 0 | 100.00 |
| sales | 60 | 6 | 0 | 100.00 |
| sales | 70 | 6 | 0 | 100.00 |
| sales | 80 | 6 | 0 | 100.00 |
| sales | 90 | 6 | 0 | 100.00 |
| sales | 100+ | 6 | 0 | 100.00 |
| enquiry | 10 | 32 | 32 | 80.00 |
| enquiry | 20 | 36 | 4 | 90.00 |
| enquiry | 30 | 38 | 2 | 95.00 |
| enquiry | 40 | 39 | 1 | 97.50 |
| enquiry | 50 | 39 | 0 | 97.50 |
| enquiry | 60 | 39 | 0 | 97.50 |
| enquiry | 70 | 39 | 0 | 97.50 |
| enquiry | 80 | 40 | 1 | 100.00 |
| enquiry | 90 | 40 | 0 | 100.00 |
| enquiry | 100+ | 40 | 0 | 100.00 |
| others | 10 | 6 | 6 | 100.00 |
| others | 20 | 6 | 0 | 100.00 |
| others | 30 | 6 | 0 | 100.00 |
| others | 40 | 6 | 0 | 100.00 |
| others | 50 | 6 | 0 | 100.00 |
| others | 60 | 6 | 0 | 100.00 |
| others | 70 | 6 | 0 | 100.00 |
| others | 80 | 6 | 0 | 100.00 |
| other | 90 | 6 | 0 | 100.00 |
| others | 100+ | 6 | 0 | 100.00 |
+---------+------+-----------+-------+--------+
一种方法可以是这样(使用变量获得累积总和,并在所有间隔中使用一个JOIN。最后,我终于添加了一个带有select Count(*)的JOIN以获得52):
SELECT RAN, TOT_COUNT, DELTA, ROUND(TOT_COUNT/E.TOT*100,2) AS '%'
FROM (SELECT B.RAN, a.Ranges, COALESCE(a.Delta,0) AS DELTA
, @r:=@r+COALESCE(a.Delta,0) AS TOT_COUNT
FROM (SELECT (
IF(duration<=10,'10',IF(duration<=20,'20',IF(duration<=30,'30',
IF(duration<=40,'40',IF(duration<=50,'50',
IF(duration<=60,'60',IF(duration<=70,'70',IF(duration<=80,
'80',IF(duration<=90,'90','100+'))))))))))
AS Ranges,COUNT(duration) AS Delta
FROM callsdetails C
GROUP BY Ranges) A
RIGHT JOIN (SELECT '10' AS RAN, 1 AS ORD UNION ALL SELECT '20', 2 AS ORD UNION ALL SELECT '30', 3
UNION ALL SELECT '40', 4 UNION ALL SELECT '50', 5 UNION ALL SELECT '60',6 UNION ALL SELECT '70',7
UNION ALL SELECT '80',8 UNION ALL SELECT '90',9 UNION ALL SELECT '100+',10) B ON A.Ranges=B.RAN
CROSS JOIN (SELECT @r:=0) T
ORDER BY ORD
) D
CROSS JOIN (SELECT COUNT(*) AS TOT FROM callsdetails) E
;
输出:
RAN TOT_COUNT DELTA %
10 44 44 84.62
20 48 4 92.31
30 50 2 96.15
40 51 1 98.08
50 51 0 98.08
60 51 0 98.08
70 51 0 98.08
80 52 1 100
90 52 0 100
100+ 52 0 100
更新(有问题的新请求)。
SELECT queueALL, RAN, TOT_COUNT, DELTA, ROUND(TOT_COUNT/D.TOT*100,2) AS '%'
FROM (
SELECT B.queueALL, B.RAN, a.Ranges, COALESCE(a.Delta,0) AS DELTA, B.TOT
, CASE WHEN queueALL=@q THEN @r:=@r+COALESCE(a.Delta,0) ELSE @r:=COALESCE(a.Delta,0) END AS TOT_COUNT
, @q:=queueALL
, ORD
FROM (SELECT queuename
, CASE WHEN duration DIV 10.0001+1<=9 THEN CAST((duration DIV 10.0001+1)*10 AS CHAR)
ELSE '100+' END AS Ranges
, COUNT(duration) AS Delta
FROM callsdetails C
GROUP BY queuename, Ranges) A
RIGHT JOIN (SELECT B2.queuename AS queueALL, B2.TOT, B1.RAN, B1.ORD
FROM
(SELECT queuename, COUNT(*) AS TOT FROM callsdetails GROUP BY queuename) B2
CROSS JOIN (SELECT '10' AS RAN,1 AS ORD UNION ALL SELECT '20',2 AS ORD UNION ALL SELECT '30',3
UNION ALL SELECT '40',4 UNION ALL SELECT '50',5 UNION ALL SELECT '60',6 UNION ALL SELECT '70',7
UNION ALL SELECT '80',8 UNION ALL SELECT '90',9 UNION ALL SELECT '100+',10)B1 ) B ON A.Ranges=B.RAN AND A.queuename = B.queueALL
CROSS JOIN (SELECT @r:=0, @q:='') T
ORDER BY queueALL, ORD) D
ORDER BY queueALL, ORD
;
输出:
+----------+------+-----------+-------+------+
| queueALL | RAN | TOT_COUNT | DELTA | % |
+----------+------+-----------+-------+------+
| enquiry | 10 | 32 | 32 | 80 |
| enquiry | 20 | 36 | 4 | 90 |
| enquiry | 30 | 38 | 2 | 95 |
| enquiry | 40 | 39 | 1 | 97.5 |
| enquiry | 50 | 39 | 0 | 97.5 |
| enquiry | 60 | 39 | 0 | 97.5 |
| enquiry | 70 | 39 | 0 | 97.5 |
| enquiry | 80 | 40 | 1 | 100 |
| enquiry | 90 | 40 | 0 | 100 |
| enquiry | 100+ | 40 | 0 | 100 |
| others | 10 | 6 | 6 | 100 |
| others | 20 | 6 | 0 | 100 |
| others | 30 | 6 | 0 | 100 |
| others | 40 | 6 | 0 | 100 |
| others | 50 | 6 | 0 | 100 |
| others | 60 | 6 | 0 | 100 |
| others | 70 | 6 | 0 | 100 |
| others | 80 | 6 | 0 | 100 |
| others | 90 | 6 | 0 | 100 |
| others | 100+ | 6 | 0 | 100 |
| sales | 10 | 6 | 6 | 100 |
| sales | 20 | 6 | 0 | 100 |
| sales | 30 | 6 | 0 | 100 |
| sales | 40 | 6 | 0 | 100 |
| sales | 50 | 6 | 0 | 100 |
| sales | 60 | 6 | 0 | 100 |
| sales | 70 | 6 | 0 | 100 |
| sales | 80 | 6 | 0 | 100 |
| sales | 90 | 6 | 0 | 100 |
| sales | 100+ | 6 | 0 | 100 |
+----------+------+-----------+-------+------+