mysql - CASE、mysql查询的组合条件

Question

我正在尝试完成一个查询，以计算我们手机上的人员的不同数字总数，看起来我的标准总数计算得很好，但我现在正在尝试这样做:

对于每个总计列(总调用数、总入站数、总出站数、总错过数)，我现在需要一个新列与其合作，以获取数据库中的已知号码(这样我就可以知道总调用数、总入站数) ， ETC。)。

我执行此操作的方法是检查 session 表中的两个字段:callingpartyno 和 finallycalledpartyno。因此，对于 session 中的每个调用，我都需要检查这些字段中的任何一个中的号码是否在 phone_number 的 knownNumbers 表中 field 。如果是这样，我需要对已知列进行计数。

在下面的查询中大约 8 行，我有第一个实例，它似乎显示了准确的数字，但它只包含 callingpartyno。我首先需要知道如何在“case whento addfinally Calledpartyno”中组合条件。

另一个问题是，对于入站和出站，我需要将我使用的电话号码字段基于 LEGTYPE 字段。因此，例如，如果我正在计算对已知号码的出站调用，我需要类似

sum(if(LEGTYPE1 = 1,1,0)) AND finallycalledpartno = k.phone_number AS Total_Outbound_known.

我希望这是有道理的，对于高级 SQL 程序员来说应该非常简单。我似乎无法让 case when 语句中的条件组合特别有效。

查询如下:

SELECT u.firstn
      ,u.lastn
      ,c.extension
      ,SUM(IF(LEGTYPE1 = 1, 1, 0)) +                   -- outbound calls
       SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) +  -- inbound calls
       SUM(IF(Answered = 1, 0, 1))  AS Total_Calls
      ,sum(case when CALLINGPARTYNO = k.phone_number  then 1 else 0 end ) AS total_known
      ,sum(if(Answered = 1,0,1)) AS Total_Missed
      ,sum(if(LEGTYPE1 = 2,1,0) and ANSWERED = 1) AS Total_Recieved
      ,sum(if(LEGTYPE1 = 1,1,0)) AS Total_Outbound
      ,round(sum(Duration) / 60,2) AS Total_Talk_Time_minutes
      ,sum(if(Answered = 1,0,1)) / (SUM(IF(LEGTYPE1 = 1, 1, 0)) +                   -- outbound calls
       SUM(IF(LEGTYPE1 = 2, 1, 0) AND ANSWERED = 1) +  -- inbound calls
       SUM(IF(Answered = 1, 0, 1)))  * 100 AS Percentage_Missed
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;

Answer 1

除了我在上面的评论中写的内容之外，我会像这样重写您的查询(我认为至少应该更容易阅读，避免重复一些 SUM)

SELECT firstn
    , lastn
    , extension
    , Total_Outbound+Total_Missed+Total_Received AS Total_Calls
    , Total_Known
    , Total_Missed
    , Total_Received
    , Total_Outbound
    , Total_Talk_Time_minutes
    , Total_Missed  / (Total_Outbound+Total_Missed+Total_Received)  * 100 AS Percentage_Missed
FROM (
      SELECT u.firstn
    , u.lastn
    , c.extension       
    , sum(case when CALLINGPARTYNO = k.phone_number  then 1 else 0 end ) AS Total_Known
    , 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 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;