时间戳

首页 > 解决方案 > 呼叫者的第一个和最后一个呼叫是同一个人

sql - 呼叫者的第一个和最后一个呼叫是同一个人

问题描述

我有一个电话记录表，其中包含有关呼叫者通话记录的信息。我想找出在某一天第一次和最后一次打电话给同一个人的来电者。

Callerid  Recipientid        DateCalled
1          2            2019-01-01 09:00:00.000
1          3            2019-01-01 17:00:00.000
1          4            2019-01-01 23:00:00.000
2          5            2019-07-05 09:00:00.000
2          5            2019-07-05 17:00:00.000
2          3            2019-07-05 23:00:00.000
2          5            2019-07-06 17:00:00.000
2          3            2019-08-01 09:00:00.000
2          3            2019-08-01 17:00:00.000
2          4            2019-08-02 09:00:00.000
2          5            2019-08-02 10:00:00.000
2          4            2019-08-02 11:00:00.000

Expected Output
Callerid   Recipientid     Datecalled
2             5            2019-07-05
2             3            2019-08-01
2             4            2019-08-02

我写了下面的查询，但无法让它返回收件人 ID。对此的任何帮助将不胜感激！

select pl.callerid,cast(pl.datecalled as date) as datecalled
from phonelog pl inner join (select callerid, cast(datecalled as date) as datecalled, 
                             min(datecalled) as firstcall, max(datecalled) as lastcall
                             from phonelog
                             group by callerid, cast(datecalled as date)) as x
on pl.callerid = x.callerid and cast(pl.datecalled as date) = x.datecalled
and (pl.datecalled = x.firstcall or pl.datecalled = x.lastcall)
group by pl.callerid, cast(pl.datecalled as date) 
having count(distinct recipientid) = 1

标签： sqlsql-serverdatabase

解决方案

另一个 dbFiddle 选项

首先，我的预查询（PQ 别名），我每天为给定的客户获取最短和最长的通话时间，但还必须确保该人在一天内至少有 2 个电话。从那以后，我在给定日期的第一个（MIN）呼叫中重新加入电话记录表。然后，我在同一天为同一个人的 LAST (MAX) 电话再加入一次，并确保第一个电话的接收者与最后一个相同。

我不必加入用于分组的精简“JustDate”列，因为 MIN/MAX 限定了完整的日期/时间。

select
       PQ.JustDate,
       PQ.CallerID,
       pl1.RecipientID
    from
        ( select
                callerID,
                convert( date, dateCalled ) JustDate,
                min( DateCalled ) minDateCall,
                max( DateCalled ) maxDateCall
            from
                PhoneLog pl
            group by
                callerID,
                convert( date, dateCalled ) 
            having
                count(*) > 1) PQ
            JOIN PhoneLog pl1
                on PQ.CallerID = pl1.CallerID
                AND PQ.minDateCall = pl1.dateCalled
                JOIN PhoneLog pl2
                    on PQ.CallerID = pl2.CallerID
                    AND PQ.maxDateCall = pl2.dateCalled
                    AND pl1.RecipientID = pl2.RecipientID

推荐阅读