程序员必知必会的SQL用法

SQL语句
507
0
0
2022-07-11
标签   SQL优化

程序员必知必会的SQL用法

导语

本文讲解的知识点主要有:公用表表达式(CTE)、SQL NULL 函数及SQL 条件判断、窗口函数;

针对人群:对SQL一知半解,停留在增删改查初级层面,大神可以飘过。

一、公用表表达式(Common Table Expression,CTE)

CTE 可以作为临时的结果集,可以在DML(Data Manipulation Language)语句中被多次引用,从而使语句更加简练。

公用表达式定义 :
WITH express_name [(colum_name[,....n])]
AS
(CTE_query_definition)

(1)参数含义

express_name:公用表表达式名字;

colum_name :字段名;

CTE_query_definition:一个Select语句。

(2)CTE特点

1)在一定程度上提高SQL语句的可读性;

2)提高SQL语句执行性能;

在多表进行 full join on (MySQL 目前不支持),每个部分都需要使用 一个select 查询结果集时,相应每个部分都需要去执行一遍,而使用WITH AS 语句 ,则需执行一次 ;另外,SQL优化器也会对WITH AS 短语做优化,连续被调用两次以上,优化器会自动将WITH AS 短语的结果集存放在一个临时表中。

3)可以在一个语句中多次引用公用表表达式;

4)可以定义递归共用表表达式;

实例讲解:获取某APP每一天不同平台dau数据

这个好办,弄一个表变量不就可以了吗?

一运行报错,查找一下PostgreSQL中并不支持declare 方式创建表变量,PostgreSQL只有通过函数返回record or ref Cursor。(MySQL支持)

程序员必知必会的SQL用法

declare 定义表变量

程序员必知必会的SQL用法

CTE方式

二、窗口函数

在将窗口函数前,先来对比一下聚合函数(min() /max() /avg()/ sum()等)窗口函数

(1)聚合函数返回的各个分组的结果,窗口函数则为每一行返回结果。

窗口函数计算一些行集合的数据,与常规的聚集函数不同的是,窗口函数不会将参与计算的行合并成一行输出,而是保留它们原来的样子。

(2)聚合函数都支持窗口函数运算。

(3)聚合函数对一组值执行计算,并返回单个值。除了 COUNT 以外,聚合函数都会忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY子句一起使用。

(4)窗口函数是对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行和聚合列。

实例讲解

假设现在要得到某APP所有版本crash率的平均值,按照通常的做法,一定是通过聚合函数AVG来求crash率的平均值,输出结果中包含该APP对应的版本、crash率及平均crash率。

(1)方法 1 (初学者经常犯的错误

程序员必知必会的SQL用法

出错原因 :Select 指定字段要么包含在聚合函数中,那么包含在Group By语句后面(Group by对Select指定字段做了限制)。

(2)方法2

使用方法1 碰壁后,立马改写成如下方式,一看查询结果立马尴尬了,都按 version 和crash_rate Group by了,还求啥avg?

程序员必知必会的SQL用法

上述两种方法均未达到我们的目的,上一节介绍了公用表达式(CTE),那我们就先用这种方式来实现。

(3)CTE方式实现

程序员必知必会的SQL用法

那有没有更加简单的方式了 ?

咱也不卖关子了 ,既然是讲窗口函数,那肯定可用窗口函数来实现了。

(4)窗口函数实现

程序员必知必会的SQL用法

窗口函数的作用是不是很强大,使用也相当简单 。

窗口函数使用OVER函数实现,OVER函数分带参不带参两种。

OVER 函数定义:
OVER([PARTITION BY value_expression,..[n] ],<ORDER BY BY_Clause> )

其中可选参数PARTITION BY用于将数据按照特定字段分组 ; ORDER BY 用于排序。


三、SQL NULL 函数

不同的DB对应的SQL NULL函数均有所不同,目前SQL NULL函数主要有以下几种:

(1)ISNULL() —— SQL Server/MS Access

(2)NVL() —— Oracle

(3)IFNULL() —— MySQL

(4)COALESCE() —— MySQL / PostgreSQL

用法很简单,以COALESCE()为例,COALESCE(data,defaultValue) ,如果data为NULL,那么COALESCE(data,defaultValue)将返回 defaultValue,反之,返回data。

为什么数据库中很在意对NULL的处理,这牵扯到SQL的三值逻辑,True(真)、False(假)、UNKNOWN(不知道,不确定),在数据库中的NULL相当于UNKNOWN,若对NULL值处理不当会造成意想不到的后果。

实例:新版app在原有上报基础上,新增了某些上报字段(没有设置默认值,深坑),后期需要对这个数据进行统计分析,如某版本得分=sum( 根据各项指标得分*权重)。

结果发现 低版本的得分为NULL,原因很简单 某些字段只在高版本中才上报,而NULL+数字=NULL 。

程序员必知必会的SQL用法

正确做法:

(1)Create Table 时,设置字段默认值 ;

(2)没有默认值,则可利用上述的SQL NULL 函数来实现。

四、SQL条件判断(简要列举)

(1) case when及if

程序员必知必会的SQL用法

(2)insert into .... where not exists ()及insert overwrite

某些数据库不支持replace,如PostgreSQL。