同一天的第一个电话和最后一个电话sql

Create table If Not Exists Calls (caller_id int, recipient_id int, call_time datetime)

Truncate table Calls

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

 

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

结果请放在一个任意次序约束的表中。

 

SELECT DISTINCT 
  u1 user_id
FROM (
  SELECT 
      u1, u2, dt
  FROM (
  SELECT # 2. 用户一天内通话行为的时间先后
      u1, u2, DATE(call_time) dt,
      ROW_NUMBER() OVER(PARTITION BY u1, DATE(call_time) ORDER BY call_time asc) rk_asc,
      ROW_NUMBER() OVER(PARTITION BY u1, DATE(call_time) ORDER BY call_time desc) rk_desc
    FROM (
      # 1. 列出用户所有的通话行为(呼出，或接听)
      SELECT caller_id u1, recipient_id u2, call_time FROM Calls
      UNION ALL 
      SELECT recipient_id u1, caller_id u2, call_time FROM Calls) t1) t2 
  WHERE rk_asc = 1 or rk_desc = 1 # 3. 筛选出用户一天内的第一通和最后一通通话
) t3
GROUP BY u1, dt # 4. 按用户日期聚合
HAVING COUNT(DISTINCT u2) = 1 # 5. 筛选出一天内，第一通和最后一通电话都为同一人的记录。