我正在尝试完成一个查询，以便在手机上为我们的人提供不同的数字，并且我的标准总数计算得很好，但是我现在正在尝试这样做：
对于每个总列数（总呼叫数，总入站数，总出站数，总错过数），我现在需要为我们数据库中的已知数字建立一个新的列（因此我将知道总呼叫数，已知总入站数等。 ）。
我这样做的方法是检查session表中的两个字段： callingpartyno和finallycalledpartyno 。 因此，对于session每个调用，我需要检查以查看其中任何一个字段中的数字是否在phone_number字段中的knownNumbers表中。 如果是这样，我需要将它们计入已知列。
在我下面的查询中大约有8行，我的第一个实例似乎显示了准确的数字，但它只包含了callingpartyno 。 我首先需要知道如何在这种情况下组合条件何时to add finallycalledpartyno`。
另一个问题是，对于入站和出站，我需要将我正在使用的电话号码字段基于LEGTYPE字段。 所以，例如，如果我正在计算对已知数字的出站调用，我需要类似的东西
sum(if(LEGTYPE1 = 1,1,0)) AND finallycalledpartno = k.phone_number AS Total_Outbound_known.我希望这是有道理的，对于高级SQL程序员来说应该非常简单。 在声明工作的情况下，我似乎无法获得条件的组合。
查询如下：
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;I'm trying to finish up a query to total different numbers for our people on the phones and I have the standard totals calculated fine, it seems, but I'm now trying to do this:
For every total column (total calls, total inbound, total outbound, total missed) I now need to have a new column partnered with it for known numbers in our database (so I'll have total calls known, total inbound known, etc.).
The way I'm doing this is by checking two fields in my session table: callingpartyno and finallycalledpartyno. So, for every call in session I need to check to see if the number in either of those fields are in my knownNumbers table in the phone_number field. If so, I need to count them for the known column.
About 8 lines down in my query below, I have my first instance of this which seems to be showing accurate numbers, but it's only incorporating callingpartyno. I first need to know how to combine conditions in this 'case whento addfinallycalledpartyno` as well.
The other issue is that for inbound and outbound, I need to base the phone number field I'm using on the LEGTYPE field. So, for example, IF I'm calculating outbound calls to known numbers I need something like
sum(if(LEGTYPE1 = 1,1,0)) AND finallycalledpartno = k.phone_number AS Total_Outbound_known.I hope this makes sense, and it should be pretty straightforward for an advanced SQL programmer. I just can't seem to get the combination of conditions in a case when statement to work especially.
Query below:
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;最满意答案
除了我在上面的评论中写的，我会像这样重写你的查询（我认为至少应该更容易阅读它，避免重复一些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;Apart what I wrote in above comments, I would rewrite your query like this (I think at least it should be easier to read it, avoiding to repeat some SUMs)
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;更多推荐
发布评论