SQL 选择基于初始选择的后续值

Question

我有以下数据:

  Client_id     Call_started               Call_ended                 Outcome_id      
 ----------- -------------------------- -------------------------- ---------------- 
  111         Aug. 21, 2018, 4:10 p.m.   Aug. 21, 2018, 4:13 p.m.   Rescheduled     
  111         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  112         Aug. 21, 2018, 3:10 p.m.   Aug. 21, 2018, 3:11 p.m.   Rescheduled     
  111         Aug. 22, 2018, 5:00 p.m.   Aug. 22, 2018, 5:08 p.m.   Interested      
  113         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  114         Aug. 21, 2018, 2:10 p.m.   Aug. 21, 2018, 2:11 p.m.   NotReachable   
  112         Aug. 22, 2018, 9:10 a.m.   Aug. 22, 2018, 9:20 a.m.  NotInterested  
  113         Aug. 22, 2018, 5:20 p.m.   Aug. 22, 2018, 5:25 p.m.   Interested 

以下是重新安排的客户端调用的 SQL 查询

Select a.client_id, a.call_start,a.call_end, a.outcome_id
From client_analysis a
where a.outcome_id like %Rescheduled%'

现在我还想选择后续重新安排的调用发生了什么(他们是否按时调用等)。如何选择对给定 client_ids 进行的其他调用(或下一个 a.call_start)，其中 a.outcome_id 被重新安排(next a.call_start < rescheduled a.call_end)？

下面是预期的输出:

 Client_id     Call_started               Call_ended                 Outcome_id      
 ----------- -------------------------- -------------------------- ---------------- 
  111         Aug. 21, 2018, 4:10 p.m.   Aug. 21, 2018, 4:13 p.m.   Rescheduled     
  111         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  111         Aug. 22, 2018, 5:00 p.m.   Aug. 22, 2018, 5:08 p.m.   Interested      
  112         Aug. 21, 2018, 3:10 p.m.   Aug. 21, 2018, 3:11 p.m.   Rescheduled     
  112         Aug. 22, 2018, 9:10 a.m.   Aug. 22, 2018, 9:20 a.m.  NotInterested  
  113         Aug. 22, 2018, 1:00 p.m.   Aug. 22, 2018, 1:10 p.m.   Rescheduled     
  113         Aug. 22, 2018, 5:20 p.m.   Aug. 22, 2018, 5:25 p.m.   Interested

对于给定的 client_id，Call_start 位于相同的列中，并且根据开始时间，我们可以计算出调用的顺序。

Answer 1

这可以通过查找满足条件的客户的下一个调用来完成(重新安排调用时，查找下一个调用)

这使用了两个概念，LEAD——查找下一个值；和 CASE WHEN - 何时应用 LEAD 的条件

试试这个 SQL:

CASE WHEN Outcome_ID = 'Rescheduled' 
    THEN LEAD(Call_Started) OVER (PARTITION BY Client_ID ORDER BY Call_Started ASC) 
        END AS next_call_start_time

逐行解释:

只有当 Outcome_ID 列等于 Resceduled 的行时，CASE 才执行语句，

如果重新安排，LEAD 查找 Call_Started 值，用于 Client_ID 的(认为 PARTITION BY 类似于​​ GROUP BY)下一次调用(ORDER BY Call Started ASC)

如果您想在此新列中放置与另一列不同的值，例如下一次通话的结束日期，请将 LEAD(Call_Started) 替换为 LEAD(Call_Ended) 等