1972. 同一天的第一个电话和最后一个电话

SQL架构

表: 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有一个电话。这个电话是他们彼此当天的唯一一个电话。因此这个电话是他们当天的第一个电话也是最后一个电话，他们都应该被包含在答案中。

with t1 as (select
caller_id,recipient_id,call_time #选出 所有id 在某个时间 跟另一个通话的id
from
Calls
 
union all
 
select
recipient_id,caller_id,call_time
from
Calls
),
t2 as (
select#按 caller_id 和 每天 分组  对 call_time标号 便于 找到 每天 第一个 和最后的一通电话
caller_id,recipient_id,call_time,row_number() over(partition by caller_id,date_format(call_time,'%Y-%m-%d') order by call_time) rn,row_number() over(partition by caller_id,date_format(call_time,'%Y-%m-%d') order by call_time desc) rn2
from
t1
),
t3 as (
select
caller_id,recipient_id,date_format(call_time,'%Y-%m-%d') day #某天第一次通话
from
t2
where rn = 1 
union all
select
caller_id,recipient_id,date_format(call_time,'%Y-%m-%d') day#某天最后一次通话
from
t2
where rn2 = 1 
)
select
distinct caller_id user_id
from
 
t3
group by 
caller_id,day #对 某人某天第一次 和最后一次通话的id 去重计数  值为1就是题中需求
having
count(distinct recipient_id)=1

笔记:

select * from
(
    select *,row_number() over w as row_num,
    from order_tab
    WINDOW w AS (partition by product_id order by amount desc)
)t ;

window关键字的作用就是为当前窗口起别名。在本语句中，将当前窗口命名为w。
该语句的含义为按照product_id进行分组，并在组内根据amount值进行降序排列。

此处row_number()为窗口函数，参见另一篇文章：MySQL8窗口函数的语法与示例详解

等价于以下语句
select * from
(
    select *,row_number() over (partition by product_id order by amount desc) as row_num,
    from order_tab
    )t ;
————————————————
版权声明：本文为CSDN博主「程序员先森」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44848356/article/details/118808946