sql计数/求和直到另一列中的特定日期的调用数 [英] sql count/sum the number of calls until a specific date in another column
问题描述
我有显示客户来电的数据。我有客户编号,电话号码(1个客户可以有多个),每个语音呼叫的日期记录和通话时间的列。表看起来在下面的示例中。
I have data that shows the customer calls. I have columns for customer number, phone number(1 customer can have many), date record for each voice call and duration of a call. Table looks lie below example.
CusID | PhoneNum | Date | Duration
20111 43576233 20.01.2016-14:00 00:10:12
20111 44498228 14.01.2016-15:30 00:05:12
20112 43898983 14.01.2016-15:30
我想要的是在回答之前对每个号码的呼叫尝试次数进行计数(持续时间为> 0)。这样我就可以估算出平均应该致电多少次才能联系到客户或电话号码。基本上,应该在持续时间> 0的min(Date)之前计算每个电话号码的任何列。
What I want is to count the number of call attempts for each number before It is answered(Duration is > 0). So that I can estimate how many time I should call on average to reach a customer or phone number. It should basically count any column per phone number before min(Date) where duration is >0.
SELECT Phone, Min(Date) FROM XX WHERE Duration IS NOT NULL GROUP BY Phone --
我认为这应该给我时间限制,直到我应该计算通话次数为止。我不知道如何完成剩余的工作
I think This should give me the time limit until when I should count the number of calls. I could not figure out how to finish the rest of the job
编辑-我将添加一个示例
EDIT- I will add an example
结果应该只计入第5行,因为这是第一次到达客户之前的呼叫。因此,结果表应类似于:
And the result should only count row number 5 since it is the call before the customer is reached for the first time. So resulted table should be like :
推荐答案
您的第一步是有效的:
SELECT
CusID
,PhoneNum
,MIN(Date) AS MinDate
FROM XX
WHERE Duration IS NOT NULL
GROUP BY CusID, PhoneNum
这使您每个 PhoneNum
都有一行,并带有第一个成功呼叫的日期。
This gives you one row per PhoneNum
with the date of the first successful call.
现在将其加入原始表,仅保留具有先前日期的行(每个 PhoneNum
)。再次将其按 PhoneNum
分组并计数。联接应该为 LEFT JOIN
,以便使第一次尝试回答的数字的计数为零。
Now join this to original table and leave only those rows that have a prior date (per PhoneNum
). Group it by PhoneNum
again and count. The join should be LEFT JOIN
to have a row with zero count for numbers that were answered on the first attempt.
WITH
CTE
AS
(
SELECT
CusID
,PhoneNum
,MIN(Date) AS MinDate
FROM XX
WHERE Duration IS NOT NULL
GROUP BY CusID, PhoneNum
)
SELECT
CusID
,PhoneNum
,COUNT(XX.PhoneNum) AS Count
FROM
CTE
LEFT JOIN XX
ON XX.PhoneNum = CTE.PhoneNum
AND XX.Date < CTE.MinDate
GROUP BY CusID, PhoneNum
;
如果一个数字从未应答,则它将根本不包括在结果集中。
If a number was never answered, it will not be included in the result set at all.
这篇关于sql计数/求和直到另一列中的特定日期的调用数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!