64，SQL训练之，力扣，1699. 两人之间的通话次数

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

思路一，常规方法

思路二 ：巧用函数 least和greatest 函数

五，SQL解答

解法一，常规拆表 +开窗+  然后全外连接 

解法二，巧用返回大小值函数

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（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)。

三，建表语句

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

insert into Calls (from_id, to_id, duration) values ('4', '3', '100');
insert into Calls (from_id, to_id, duration) values ('4', '3', '100');
insert into Calls (from_id, to_id, duration) values ('4', '3', '100');
insert into Calls (from_id, to_id, duration) values ('4', '3', '100');
insert into Calls (from_id, to_id, duration) values ('4', '3', '100');
insert into Calls (from_id, to_id, duration) values ('4', '1', '100');

select * from calls;

四，分析

题解：

表：通话时长表

字段：打电话id，接电话的id，时长

要求  输出：

第一个字段是打电话的人,第二个字段是接电话的人 且第一个字段的id小于第二个字段的id，

第三个字段是两人通话的次数，第四个字段是两人通话的时长

思路一，常规方法

第一步，拆表，

如果打电话的id小于接电话的id  拆为一张表

如果打电话人的id大于接电话的人的id 拆为一张表

第二步，对各表分别的分组 求次数 求时长 

第三步，左表 左连接 右表  条件：左表的打电话的id=右表接电话的id 并且左表接电话的id=右表打电话的id。

如果次数和时长是null 设置为0

-- 其实如果有右表的from id是null 也可以改为 是左表接电话的人；

右表接电话的人id 是null  设置为左表打电话的人

第四步，左表 右连接 左表  条件 ：左表的打电话的id=右表接电话的id 并且左表接电话的id=右表打电话的id。

同上：设置null值为 条件满足的值

第五步，把上下两个表 完成全连接   并去重 union distinct

 第六步，同组之间相互累加 

第七步，得到最终结果

思路二 ：巧用函数 least和greatest 函数

 第一步 巧用 

least(from_id, to_id), greatest(from_id, to_id); 分组

第一个函数  返回2个参数的最小值。第二个函数返回两个参数的最大值

第一行的：1和2进去 返回1和2

第二行的：2和1进去 返回1和2    这不是巧妙的满足了打电话的人和接电话的人 是同一组了么

没有聚合前如下：  当然这个表逻辑表  毕竟分组groupby之后要聚合 不然除非开窗 不然显示

那就分组 聚合呗   count 次数  sum 时长

一步到位 直接拿到结果

五，SQL解答

解法一，常规拆表 +开窗+  然后全外连接 

with t1 as (
    select distinct from_id, to_id,
                    count(*)over(partition by from_id,to_id) cnt1,
                    sum(duration) over(partition by from_id,to_id) so1
        from calls where from_id<Calls.to_id
),t2 as (
    select distinct from_id, to_id,
                    count(*)over(partition by from_id,to_id) cnt2,
                    sum(duration) over(partition by from_id,to_id) so2
    from calls where from_id>Calls.to_id
),t3 as (
    select t1.from_id,t1.to_id,
       cnt1+if(cnt2 is null,0,cnt2) as cnn,
       so1+if(so2 is null,0,so2) as summ
from t1 left join t2 on t1.from_id=t2.to_id and t1.to_id=t2.from_id
),t4 as (
    select if(t1.from_id is null,t2.to_id,t1.from_id) as tt1,
           if(t1.to_id is null,t2.from_id,t1.to_id) as tt2,
       if(cnt1 is null,0,cnt1)+cnt2 as cnn1,
       if(so1 is null,0,so1)+so2 as  summ
from t1 right join t2 on t1.from_id=t2.to_id and t1.to_id=t2.from_id
),t5 as (
select from_id, to_id, cnn, summ from t3
union distinct
select tt1, tt2, cnn1, summ from t4
),t6 as (
   select from_id as person1, to_id as person2 ,cnn as call_count,summ as total_duration from t5
)
select * from t6;

解法二，巧用返回大小值函数

-- 绝了！
select
    least(from_id, to_id) person1,
    greatest(from_id, to_id) person2,
    count(*) as call_count,
    sum(duration) as total_duration
from
    Calls
group by
    least(from_id, to_id), greatest(from_id, to_id);

六，验证

七，知识点总结

• 开窗聚合的练习
• mysql中的全外连接=  左外连接 union 右外连接
• if null 函数的练习
• 返回两个参数的最小值函数 least 练习
• 返回两个参数的最大值函数 greatest 练习 

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用