likes
comments
collection
share

写出一个复杂的SQL步骤

作者站长头像
站长
· 阅读数 20

写出一个复杂的SQL步骤

公号原文

今天就浅显写一篇文章专门介绍数据处理中常见的sql查询场景中的复杂查询。为什么单独说复杂查询呢？因为在业务开发中我们常用orm就可以解决很多查询问题，但是都是比较简单的那种sql，所以orm就能满足。但是一旦涉及到复杂的查询，orm就束手无策了。这个时候我们一般都是写sql查询，但是很多sql还是不太复杂。真正复杂的sql一般都是业务上线之后，领导找你统计各个维度的数据，那么这个时候sql能力就非常重要了，因为它往往涉及很多表很多字段很多子查询。那为了一天就能掌握写复杂sql的能力，为此爆肝这篇文章，一起学习成长。

一. 明确需求

返回所有国家以及相关呼叫的数量及其平均持续通话时间（以秒为单位）。在结果中，仅显示平均呼叫持续时间大于所有呼叫的平均呼叫持续时间的国家。

1. 拆解需求

  1. 需要展示所有国家ID或者国家名称。
  2. 相关呼叫的数量： 展示国家的呼叫统计数量。
  3. 平均持续通话时间：展示国家的呼叫平均持续时间，单位：秒。

2. 条件

仅显示平均呼叫持续时间大于所有呼叫的平均呼叫持续时间的国家：

那就是先计算所有呼叫的AVG(endTime-startTime)，然后每个国家的平均呼叫时间与它比较即可。

3. 涉及到的表：

  1. 国家：country
  2. 呼叫：call
  3. 城市：city
  4. 用户：customer

为什么需要city和customer表呢？因为call中有外键customer，而customer中有外键city，city中有外键country。

因为要展示所有国家，因此要查询country，而想统计呼叫，就得链接call，怎么能找到call呢，就得从country->city->customer->call。

写出一个复杂的SQL步骤

黑色部分就是需要用到的表

二.开始设计查询

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)

三.总结步骤

  1. 先明确需求
  2. 拆解展示字段和条件
  3. 确定所要用到的表
  4. 先写出整体框架SQL并打印结果看数据很重要这一步
  5. 创建自查询，并且测试结果，最后添加到框架SQL中
  6. 测试验证所有数据
  7. 添加备注，复杂SQL不写备注等于没写SQL。