在另一列中，统计截止到特定日期的通话次数或总通话时长。需使用SQL中的COUNT或SUM函数。

Question

我有一些关于客户电话的数据。我的表格包含客户编号、电话号码（一个客户可能会有多个电话号码）、每次语音通话的日期记录以及通话时长。表格如下所示：

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)）。

SELECT Phone, Min(Date) FROM XX WHERE Duration IS NOT NULL GROUP BY Phone -- 

我认为这应该给我一个时间限制，告诉我何时应该计算呼叫次数。但我无法弄清如何完成其余的工作。

编辑- 我会添加一个例子。

结果应该只计算第五行，因为这是客户第一次接通电话之前的呼叫。因此，结果表格应该如下：

- Zeir

---

你想如何处理每天多次（成功）调用？ - ESP32

我对成功的通话不感兴趣。我正在尝试找出在每个电话的第一个成功通话之前有多少次不成功的通话。 - Zeir

Answer 1

你的第一步是正确的：

    SELECT
        CusID
        ,PhoneNum
        ,MIN(Date) AS MinDate
    FROM XX
    WHERE Duration IS NOT NULL
    GROUP BY CusID, PhoneNum

这将为每个PhoneNum提供一行，并显示第一个成功通话的日期。

现在将其与原始表联接，并仅保留具有先前日期（按PhoneNum）的那些行。再次按PhoneNum进行分组并计数。联接应该是LEFT JOIN，对于第一次尝试就被回答的号码，应该有一行零计数。

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
;

如果一个号码从未被接听，那么它将完全不会出现在结果集中。

- Vladimir Baranov

Answer 2

请尝试这个查询：

SELECT phonecalls.CusID, COUNT(0) AS failedcalls, phonenumber, success.firstsuccess FROM phonecalls, 
(SELECT min(Date) AS firstsuccess, CusID, phonenumber FROM phonecalls WHERE Duration IS NOT NULL GROUP BY CusID, phonenumber) success
WHERE phonecalls.CusID = success.CusID AND phonecalls.phonenumber = success.phonenumber AND phonecalls.Date < success.firstsuccess
GROUP BY phonecalls.CusID, phonecalls.phonenumber, success.firstsuccess;

我还没有测试过它...

注意：未建立成功通话的用户不会被列出。这样可以吗，还是您需要将他们列出？如果需要，您需要进行“左连接”：

    SELECT phonecalls.CusID, COUNT(0) AS failedcalls, phonenumber, success.firstsuccess FROM phonecalls LEFT JOIN 
(SELECT min(Date) AS firstsuccess, CusID, phonenumber FROM phonecalls WHERE Duration IS NOT NULL GROUP BY CusID, phonenumber) success ON 
phonecalls.CusID = success.CusID AND phonecalls.phonenumber = success.phonenumber AND phonecalls.Date < success.firstsuccess
GROUP BY phonecalls.CusID, phonecalls.phonenumber, success.firstsuccess;

- ESP32

Answer 3

在SQL Server 2012+中，您可以使用以下逻辑：

• 将“未回答”的通话数量分配给数据中的每一行。这使用了带有窗口函数的条件聚合。
• 然后，对于每个用户，取已回答通话的计数的最大值。
• 计算已回答的通话数量。
• 比率是平均值。
• 这忽略了未跟随已回答通话的未回答通话字符串。

生成的查询：

select phone, max(cume_unanswered), count(*) as num_answered,
       max(cume_unanswered) * 1.0 / count(*) as ratio
from (select t.*,
             sum(case when duration is null then 1 else 0 end) over (partition by phone order by date) as cume_unanswered
      from t
     ) t
where duration is not null
group by phone;

- Gordon Linoff

---

如果我理解正确的话，我认为这段代码将为我提供未接来电与已接来电的比率。我想要找到第一个已接来电之前的未接来电。我对第一个已接来电之后的未接或已接来电不感兴趣。我在我的第一个问题中添加了一些Excel表格，以便更清楚地说明。 - Zeir

@Zeir . . . 这个程序会计算每个电话号码在被接听前未接听的通话次数。这似乎是你想要的。如果一个电话只有一个已接听的通话，那么这两个指标就是相同的。 - Gordon Linoff

我正在尝试找到第一个已接通电话之前未接通的电话数量，而不是每个电话。您可以在我的问题中检查电话号码118的结果。 - Zeir