题目描述

(通过次数1,855 | 提交次数4,257,通过率43.58%)

表: Calls
+--------------+----------+
| Column Name  | Type     |
+--------------+----------+
| caller_id    | int      |
| recipient_id | int      |
| call_time    | datetime |
+--------------+----------+
(caller_id, recipient_id, call_time) 是这个表的主键。
每一行所含的时间信息都是关于caller_id 和recipient_id的。

编写一个 SQL 查询来找出那些ID们在任意一天的第一个电话和最后一个电话都是和同一个人的。这些电话不论是拨打者还是接收者都会被记录。
结果请放在一个任意次序约束的表中。

查询结果格式如下所示:
输入:
Calls table:
+-----------+--------------+---------------------+
| caller_id | recipient_id | call_time           |
+-----------+--------------+---------------------+
| 8         | 4            | 2021-08-24 17:46:07 |
| 4         | 8            | 2021-08-24 19:57:13 |
| 5         | 1            | 2021-08-11 05:28:44 |
| 8         | 3            | 2021-08-17 04:04:15 |
| 11        | 3            | 2021-08-17 13:07:00 |
| 8         | 11           | 2021-08-17 22:22:22 |
+-----------+--------------+---------------------+
输出:
+---------+
| user_id |
+---------+
| 1       |
| 4       |
| 5       |
| 8       |
+---------+
解释:
在 2021-08-24,这天的第一个电话和最后一个电话都是在user 8和user 4之间。user8应该被包含在答案中。
同样的,user 4在2 021-08-24 的第一个电话和最后一个电话都是和user 8的。user 4也应该被包含在答案中。
在 2021-08-11,user 1和5有一个电话。这个电话是他们彼此当天的唯一一个电话。因此这个电话是他们当天的第一个电话也是最后一个电话,他们都应该被包含在答案中。

来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/first-and-last-call-on-the-same-day
//测试数据
Create table If Not Exists Calls (caller_id int, recipient_id int, call_time datetime);

insert into Calls (caller_id, recipient_id, call_time) values ('8', '4', '2021-08-24 17:46:07');
insert into Calls (caller_id, recipient_id, call_time) values ('4', '8', '2021-08-24 19:57:13');
insert into Calls (caller_id, recipient_id, call_time) values ('5', '1', '2021-08-11 05:28:44');
insert into Calls (caller_id, recipient_id, call_time) values ('8', '3', '2021-08-17 04:04:15');
insert into Calls (caller_id, recipient_id, call_time) values ('11', '3', '2021-08-17 13:07:00');
insert into Calls (caller_id, recipient_id, call_time) values ('8', '11', '2021-08-17 22:22:22');

解题思路

这又是一道困难题,通过率在50%以下。

其实我觉得,这道题从题目本身描述来看,说的不是太清楚,导致一时半会无法很好的理解题意。

这也是我常说的,理解了问题,离解决问题就已经完成了50%。

源表Calls表记录了每一通电话的时间、拨出者、接听者。而题目本身并不关注谁是拨出者,谁是接听者。只关心通话的双方,在某一天是不是都是第一个和最后一个通话。

所以,本题的题目修改为“同一天同一对通话者的第一个电话和最后一个电话”会更加准确。

因为Calls表仅仅以拨出者为视角记录了通话信息,但题目关注通话双方,那么可以把数据分别以拨出者和接听者各发散一份,形成每一个人的通话记录。

select caller_id as user1,recipient_id as user2,call_time,cast(call_time as date) as call_date from calls
union all
select recipient_id as user1,caller_id as user2,call_time,cast(call_time as date) as call_date from calls

然后,再使用窗口函数,得出每个人每天的第一个通话和最后一个通话。

最后,再返回这两通通话的另一方是同一个人的记录即可。

参考SQL

未特别说明的情况下,参考SQL为基于MySQL8.0实现。
--方法一
with
tmp1 as(
    select caller_id as user1,recipient_id as user2,call_time,cast(call_time as date) as call_date from calls
    union all
    select recipient_id as user1,caller_id as user2,call_time,cast(call_time as date) as call_date from calls
),
tmp2 as(
    select
        user1,
        user2,
        call_date,
        rank() over(partition by user1,call_date order by call_time asc) rn1,
        rank() over(partition by user1,call_date order by call_time desc) rn2
    from tmp1
)
select
    distinct user1 user_id
from tmp2
where rn1 = 1 or rn2 = 1
group by user1,call_date
having count(distinct user2)=1;

--方法二
with
tmp1 as(
    select caller_id as user1,recipient_id as user2,call_time,cast(call_time as date) as call_date from calls
    union all
    select recipient_id as user1,caller_id as user2,call_time,cast(call_time as date) as call_date from calls
),
tmp2 as(
    select
        user1,
        user2,
        call_date,
        rank() over(partition by user1,call_date order by call_time asc) rn1,
        rank() over(partition by user1,call_date order by call_time desc) rn2
    from tmp1
)
select
    distinct user1 user_id
from tmp2
where rn1 = 1 or rn2 = 1
group by user1,user2,call_date
having min(rn1) = 1 and min(rn2)=1;
picture loss