写出一个复杂的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这些表。甚至我们现在不考虑最终查询，我们就知道它将包含以下部分：
SELECT ... FROM country LEFT JOIN city ON city.country_id = country.id LEFT JOIN customer ON city.id = customer.city_id LEFT JOIN call ON call.customer_id = customer.id ...;
到这里我们必须做一件事，那就是测试这样的查询返回什么：
SELECT * FROM country LEFT JOIN city ON city.country_id = country.id LEFT JOIN customer ON city.id = customer.city_id LEFT 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_diff FROM country LEFT JOIN city ON city.country_id = country.id LEFT JOIN customer ON city.id = customer.city_id LEFT JOIN call ON call.customer_id = customer.id GROUP 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_diff FROM country -- 使用LEFT JOIN包括没有任何呼叫的国家 LEFT JOIN city ON city.country_id = country.id LEFT JOIN customer ON city.id = customer.city_id LEFT JOIN call ON call.customer_id = customer.id GROUP 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不写备注等于没写
