江苏龙网

  1. 主页 > 生活百科 > >

写出一个复杂的SQL步骤

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.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 callDATEDIFF函数计算开始时间和结束时间之间给定时间段（单位：秒）的单位差 。
计算相关呼叫的数量：
SUM(CASE WHEN call.id IS NOT NULL THEN 1 ELSE 0 END) AS callsSUM函数对呼叫统计数量 。
计算平均持续通话时间：
【写出一个复杂的SQL步骤】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


推荐阅读


上一篇：正则表达式进阶--玩转分组、前瞻和后顾

下一篇：2023 年远程访问检测揭露隐形欺诈