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屋！