mysql - mysql 中自定义公式的语法

Question

我有一个可以正常工作的现有 mysql 查询，但我需要向其中添加三个平均值/百分比公式。此查询用于调用中心指标。

我真正的问题在于我的第三个指标，missed_call_score。我需要做这样的事情:

(未接来电/总通话时间)*(每个 CSR 的平均通话次数/总通话次数)* 100。现在，我在下面的查询中有未接来电、总通话时间和总通话时间，但为了获得每个 CSR 的平均通话次数，我需要取出最高和最低的总通话次数(15 个 CSR 中)，所以我'总共还剩 13 个。我需要将这些总数相加，并假设除以 13，这将得出去除最高/最低后每个 CSR 的平均值。

我的问题是如何将其合并到我的查询中？

这是我现有查询的必要部分:

SELECT 
 , extension
 , Total_Outbound+Total_Missed+Total_Received AS Total_Calls
 , Total_Missed
 , Total_Talk_Time_minutes

 /*Here I'll add average_TT_day, average_TT_call, missed_call_score*/

FROM (
  SELECT 
  , c.extension  
  , sum(if(Answered = 1,0,1)) AS Total_Missed     
  , round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes

  /*Here I'll have my new averages and score formula */

    FROM ambition.session a
    INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
  WHERE b.ts between curdate() - interval 5 day and now()

  GROUP BY c.extension  
 ) ;

因此，我需要将三个指标添加到我留下评论的选择语句中。最重要的是，对于未接来电分数，它需要如下所示:

(Total_Missed/Total_Talk_Time_Minutes) * ((SUM(all total calls - highest and lowest) / 13) / sum of total_calls) * 100

显然这是伪代码，并不完全正确，而是一个想法。

最后，这是我应该得到的示例:

extension | Total calls | missed calls | total talk time | missed call score
----------------------------------------------------------------------------
1               10              5               20              6.5     
2               8               2               15              3.4             
3               5               3               10              7.8
4               2               2               5               10.4

Formulas:

Total call sum = 25
Total call sum without high and low = 13
average calls per CSR = (13/2) = 6.5

extension 1 = (5/20) * (6.5/25) * 100
extension 2 = (2/15) * (6.5/25) * 100
extension 3 = (3/10) * (6.5/25) * 100
extension 4 = (2/5) * (6.5/25) * 100

Answer 1

无法访问您的数据，这很难。但类似这样的事情应该为您指明正确的方向:

SELECT TOP 95 PERCENT
 , extension
 , Total_Outbound+Total_Missed+Total_Received AS Total_Calls
 , Total_Missed
 , Total_Talk_Time_minutes
 , banana.Total_Talk_Time_minutes / SUM(banana.[row]) AS average_TT_call
 , (Total_Missed / Total_Talk_Time_minutes) * ([CallsPerExt].county / SUM([banana.row])) AS [Missed Call Score]

 /*Here I'll add average_TT_day, average_TT_call, missed_call_score*/

FROM (
  SELECT TOP 95 PERCENT
  , c.extension  
  , sum(if(Answered = 1,0,1)) AS Total_Missed     
  , round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes
  , 1 AS [row]

  /*Here I'll have my new averages and score formula */

    FROM ambition.session a
    INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
  WHERE b.ts between curdate() - interval 5 day and now()

  GROUP BY c.extension  
  ORDER BY round(sum(Duration) / 60,2) --Total_Talk_Time_minutes
 ) AS banana 
 OUTER APPLY (select count(*) as county, b.extension from banana b group by b.extension) AS [CallsPerExt]