写出一个复杂的 SQL 步骤
今天就浅显写一篇文章专门介绍数据处理中常见的 sql 查询场景中的复杂查询。为什么单独说复杂查询呢？因为在业务开发中我们常用 orm 就可以解决很多查询问题，但是都是比较简单的那种 sql，所以 orm 就能满足。但是一旦涉及到复杂的查询，orm 就束手无策了。这个时候我们一般都是写 sql 查询，但是很多 sql 还是不太复杂。真正复杂的 sql 一般都是业务上线之后，领导找你统计各个维度的数据，那么这个时候 sql 能力就非常重要了，因为它往往涉及很多表很多字段很多子查询。那为了一天就能掌握写复杂 sql 的能力，为此爆肝这篇文章，一起学习成长。
一. 明确需求
返回所有国家以及相关呼叫的数量及其平均持续通话时间（以秒为单位）。在结果中，仅显示平均呼叫持续时间大于所有呼叫的平均呼叫持续时间的国家。
1. 拆解需求
需要展示所有国家 ID 或者国家名称。
相关呼叫的数量：展示国家的呼叫统计数量。
平均持续通话时间：展示国家的呼叫平均持续时间，单位：秒。
2. 条件
仅显示平均呼叫持续时间大于所有呼叫的平均呼叫持续时间的国家：
那就是先计算所有呼叫的 AVG(endTime-startTime)，然后每个国家的平均呼叫时间与它比较即可。
3. 涉及到的表：
国家：country
呼叫：call
城市：city
用户：customer
为什么需要 city 和 customer 表呢？因为 call 中有外键 customer，而 customer 中有外键 city，city 中有外键 country。
因为要展示所有国家，因此要查询 country，而想统计呼叫，就得链接 call，怎么能找到 call 呢，就得从 country->city->customer->call。
黑色部分就是需要用到的表
二.开始设计查询
1. 先写出整体框架 SQL
通过明确需求得知，我们需要关联四张表：country，city，customer 和 call。
拆解需求中提到“展示所有国家的名称”，那么这是大前提，因此如果我们想正确使用这些表，我们需要使用外键 LEFT JOIN 这些表。甚至我们现在不考虑最终查询，我们就知道它将包含以下部分：
SSQELECT ...FROM country LEFT JOIN city ON city.country_id = country.idLEFT JOIN customer ON city.id = customer.city_idLEFT JOIN call ON call.customer_id = customer.id...;
复制代码
到这里我们必须做一件事，那就是测试这样的查询返回什么：
SELECT *FROM country LEFT JOIN city ON city.country_id = country.idLEFT JOIN customer ON city.id = customer.city_idLEFT JOIN call ON call.customer_id = customer.id;
复制代码
这一步骤非常必要，因为我们可以看到数据是什么样子的，为后面的查询做好基础。
2. 在添砖加瓦
计算条件中的查询：
SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call
复制代码
DATEDIFF 函数计算开始时间和结束时间之间给定时间段（单位：秒）的单位差。
计算相关呼叫的数量：
SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls
复制代码
SUM 函数对呼叫统计数量。
计算平均持续通话时间：
AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diff
复制代码
这里大家可能发现我有对 NULL 做了判断，不管是使用ISNULL函数还是IS NOT NULL判断。为什么呢？因为LEFT JOIN的时候，右边可能出现 NULL 的情况，我们怎么知道的呢？这就是我们上面提到的：必须将整体 SQL 框架先打印看结果，知道数据长啥样，自然就知道哪里需要用 NULL 逻辑特殊处理了。
好了，现在可以把这些 sql 添加到 SQL 框架中了：
SELECT country.country_name, SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls, AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diffFROM country LEFT JOIN city ON city.country_id = country.idLEFT JOIN customer ON city.id = customer.city_idLEFT JOIN call ON call.customer_id = customer.idGROUP BY country.id, country.country_name
复制代码
最后一步：按照条件：“仅显示平均呼叫持续时间大于所有呼叫的平均呼叫持续时间的国家”，我们可以很容易得出，查询最后的结果需要用HAVING对聚合结果做下过滤，因为“所有呼叫的平均呼叫持续时间”和“均呼叫持续时间”我们已经统计出来了，因此大于长这样：
HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
复制代码
所以最后的 SQL 长这样：
--返回所有国家以及相关呼叫的数量及其平均持续通话时间（以秒为单位）。在结果中，仅显示平均呼叫持续时间大于所有呼叫的平均呼叫持续时间的国家。SELECT country.country_name, SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS calls, AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) AS avg_diffFROM country -- 使用LEFT JOIN包括没有任何呼叫的国家LEFT JOIN city ON city.country_id = country.idLEFT JOIN customer ON city.id = customer.city_idLEFT JOIN call ON call.customer_id = customer.idGROUP BY country.id, country.country_name-- 过滤掉不符合条件的结果HAVING AVG(ISNULL(DATEDIFF(SECOND, call.start_time, call.end_time),0)) > (SELECT AVG(DATEDIFF(SECOND, call.start_time, call.end_time)) FROM call)
复制代码
三.总结步骤
先明确需求
拆解展示字段和条件
确定所要用到的表
先写出整体框架 SQL 并打印结果看数据很重要这一步
创建子查询，并且测试结果，最后添加到整体框架 SQL 中
测试验证所有数据
添加备注，复杂 SQL 不写备注等于没写
评论