mysql - 在 SELECT 查询中表达公式

Question

我有这个现有的查询:

SELECT 
  extension
, Total_Outbound+Total_Missed+Total_Received AS Total_Calls
, Total_Missed
, Total_Talk_Time_minutes    
FROM (
  SELECT 
, extension       
, sum(if(Answered = 1,0,1)) AS Total_Missed
, sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Total_Received
, sum(if(LEGTYPE1 = 1,1,0)) AS Total_Outbound
, round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes

  FROM session a
  GROUP BY extension 
 ) x;

效果很好，但我需要在 Total_Talk_Time_Minutes 下添加一个名为 missed_call_score 的指标/公式。

未接来电得分的公式是这样的:(missed calls/total talk time) * (average calls per CSR/total calls) * 100 但需要注意的是 average calls per csr 需要忽略MAX 和 MIN，因此接听电话的最低和最高数量。

我不确定如何在单个选择变量中构建这个分数，或者我将为此使用的语法，因为它必须抛出最大值和最小值。

这是我需要的输出示例及其应使用的公式:

extension | Total calls | missed calls | total talk time | missed call score
----------------------------------------------------------------------------
1234            8               4               15.5            5.7
4321            4               0               9.42            0.0
5678            5               2               6.78            6.5
9876            13              6               18.3            7.2


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

extension 1 = (4/15.5) * (6.5/30) * 100 = 5.7
extension 2 = (0/9.42) * (6.5/30) * 100 = 0.0
extension 3 = (2/6.78) * (6.5/30) * 100 = 6.5
extension 4 = (6/18.3) * (6.5/30) * 100 = 7.2

上面关于分机、总通话、未接来电和通话时间的数据取 self 的 sql fiddle，链接如下。我只是添加了分数列来举例说明我的预期输出。

下面链接的 fiddle 显示了我的创建和插入，希望它能提供帮助我完成此操作所需的一切。

**sql fiddle **

http://sqlfiddle.com/#!9/aa1f9/1

更新

带连接的完整生产查询

 SELECT    firstn , 
             lastn , 
             extension , 
             Total_Outbound+Total_Missed+Total_Received AS Total_Calls , 
             Total_Missed , 
             Total_Talk_Time_minutes , 
             Total_All_Calls , 
             Max_Calls , 
             Min_Calls , 
             CSR_Count , 
             ((Total_Missed/Total_Talk_Time_minutes) *  
             (((Total_All_Calls-Max_Calls-Min_Calls)/CSR_Count)/Total_All_Calls)) * 100 
             FROM (   SELECT     u.firstn     , 
             u.lastn     , 
             c.extension        , 
             sum(if(Answered = 1,0,1)) AS Total_Missed , 
             sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Total_Received , 
             sum(CASE WHEN LEGTYPE1 = 1 THEN 1 ELSE 0 END) AS Total_Outbound ,
             round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes , 

             (SELECT COUNT(1) FROM ambition.session a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID    
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension  
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)) Total_All_Calls , 

             (SELECT MAX(CNT) FROM (SELECT COUNT(1) CNT, c.extension 
             FROM ambition.SESSION a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID      
             INNER join jackson_id.users u ON c.extension = u.extension 
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312) GROUP BY responsibleuserextensionid) y) Max_Calls , 

             (SELECT MIN(CNT) FROM (SELECT COUNT(1) CNT, c.extension
             FROM ambition.SESSION a 
             INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension   
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)GROUP BY responsibleuserextensionid) y) Min_Calls , 

             (SELECT COUNT(DISTINCT c.extension)-2 
             FROM ambition.SESSION a INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension  
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)) CSR_Count       

             FROM ambition.session a       
             INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID       
             INNER join ambition.mxuser c ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID       
             INNER join jackson_id.users u ON c.extension = u.extension       
             LEFT JOIN ambition.knownnumbers k ON a.callingpartyno = k.phone_number       
             WHERE b.ts between curdate() - interval 5 day and now()       
             AND c.extension IN (7276,7314,7295,7306,7357,7200,7218,7247,7331,7255,7330,7000,7215,7240,7358,7312)       
             GROUP BY c.extension, u.firstn, u.lastn    ) x

Answer 1

这应该适合你:

SELECT 
  extension
, Total_Outbound+Total_Missed+Total_Received AS Total_Calls
, Total_Missed
, Total_Talk_Time_minutes
, Total_All_Calls
, Max_Calls
, Min_Calls
, CSR_Count
, ((Total_Missed/Total_Talk_Time_minutes) * 
      (((Total_All_Calls-Max_Calls-Min_Calls)/CSR_Count)/Total_All_Calls)) * 100
FROM (
  SELECT 
 extension       
, sum(if(Answered = 1,0,1)) AS Total_Missed
, sum(CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 1 ELSE 0 END) AS Total_Received
, sum(CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 THEN 1 ELSE 0 END) AS Total_Outbound
, round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes
, (SELECT COUNT(1) FROM session) Total_All_Calls
, (SELECT MAX(CNT) FROM (SELECT COUNT(1) CNT, EXTENSION FROM SESSION GROUP BY EXTENSION) y) Max_Calls
, (SELECT MIN(CNT) FROM (SELECT COUNT(1) CNT, EXTENSION FROM SESSION GROUP BY EXTENSION) y) Min_Calls
, (SELECT COUNT(DISTINCT EXTENSION)-2 FROM SESSION) CSR_Count

  FROM session a
  GROUP BY extension 
 ) x;

这是 fiddle .

基本上，我在您的派生表 x 中使用子计数来获取 missed_call_score 所需的每个变量。一件值得注意的主要事情是 Total_Outbound 的逻辑已关闭，因此我将其调整为 CASE 语句而不是 IF()。我在外部查询中选择了计数列，这样您就可以看到发生了什么，您可以删除它们。