|
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
|