1699. 两人之间的通话次数

本文链接：https://blog.csdn.net/CYJ1844/article/details/143242824

力扣题目跳转(1699. 两人之间的通话次数 - 力扣（LeetCode）)

表： Calls

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| from_id     | int     |
| to_id       | int     |
| duration    | int     |
+-------------+---------+
该表没有主键(具有唯一值的列)，它可能包含重复项。
该表包含 from_id 与 to_id 间的一次电话的时长。
from_id != to_id

题目要求：

编写解决方案，统计每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。

以 任意顺序 返回结果表。

返回结果格式如下示例所示。

示例 1：

输入：
Calls 表：
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1       | 2     | 59       |
| 2       | 1     | 11       |
| 1       | 3     | 20       |
| 3       | 4     | 100      |
| 3       | 4     | 200      |
| 3       | 4     | 200      |
| 4       | 3     | 499      |
+---------+-------+----------+
输出：
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1       | 2       | 2          | 70             |
| 1       | 3       | 1          | 20             |
| 3       | 4       | 4          | 999            |
+---------+---------+------------+----------------+
解释：
用户 1 和 2 打过 2 次电话，总时长为 70 (59 + 11)。
用户 1 和 3 打过 1 次电话，总时长为 20。
用户 3 和 4 打过 4 次电话，总时长为 999 (100 + 200 + 200 + 499)。

case 1 的建表语句。

Create table If Not Exists Calls (from_id int, to_id int, duration int)
Truncate table Calls
insert into Calls (from_id, to_id, duration) values ('1', '2', '59')
insert into Calls (from_id, to_id, duration) values ('2', '1', '11')
insert into Calls (from_id, to_id, duration) values ('1', '3', '20')
insert into Calls (from_id, to_id, duration) values ('3', '4', '100')
insert into Calls (from_id, to_id, duration) values ('3', '4', '200')
insert into Calls (from_id, to_id, duration) values ('3', '4', '200')
insert into Calls (from_id, to_id, duration) values ('4', '3', '499')

一我们使用 union 将两列的数据放在同一列上。

with tmp as
(select from_id as person1,
       to_id as person2,
       duration
from calls
union all
select to_id as person1,
       from_id as person2,
       duration
from calls)
select * from tmp

输出如下

二然后我们将数据按照 person1 和 person2 进行分组。统计打电话的次数和总的通话时长，并满足要求 person1 < person2

with tmp as
(select from_id as person1,
       to_id as person2,
       duration
from calls
union all
select to_id as person1,
       from_id as person2,
       duration
from calls)
select person1,
       person2,
       count(*) as call_count,
       sum(duration) as total_duration
from tmp
where person1 < person2
group by person1, person2