SQL语句整理——函数篇

函数是大牛封装好的程序，一些函数经过时间的沉淀和不断地被开发运用，变得十分的精巧和实用。
函数让人不再纠结于底层的逻辑，就像‘遥控器’或者说类似于数学中的抽象概念一样，在数学中运用定理，很多时候不需要一步步推导出定理（当然能推导的都是大牛。），我们在学习过程中，很多时候也不用纠结‘如果不懂底层的逻辑成为不了高手’这样到处可见的言论，只管做，体会就行了，当有需要的时候，自然会了解底层的东西，顺其自然就行（自勉哈哈）。

聚合函数

聚合函数就是常见的汇总函数：SUM 求和，AVG平均（相当于average）,MAX最大，MIN最小，COUNT平均等。

成绩表

打开EXCEL数据→现有连接

image.png
浏览更多
image.png

点击recent

image.png
根据需要这里有三个选项。
image.png
单击连接属性，选择定义。
image.png
快捷键是ALT+D+D+D

• select sum(语文) as 语文总分, avg(数学) as 数学平均分,max(英语) as 英语最高分,count(政治) as 政治考试人数 from [成绩表$]

  
  image.png
  

  将之前学到的东西结合运用一下：

  
  比赛成绩表
  要求：不重复的人数个数

• select count(姓名) as 不重复人数 from (select distinct 姓名 from [比赛成绩表$])

  
  结果

分组与聚合函数结合应用

demo3

• select 学校名称,班级,count(姓名) as 人数 from [demo3$] group by 学校名称,班级

image.png

• 求出demo4表中各类别的菜的总数量和总金额

  
  image.png

• select 类别,品名,sum(数量) as 总数量, sum(金额) as 总金额 from [demo4$] group by 类别,品名

  
  结果

SWITCH函数类似于工作表函数IF

• 表达式: switch(表达式1,结果1......表达式14,结果14)最多14个条件。

  
  image.png

• select *, switch(成绩>=90,"优秀",成绩>=80,"良好",成绩>=60,"中等",成绩<60,"较差",) as 等级 from [成绩表$]

  
  image.png

• 与透视表结合使用完成一些复杂的统计要求

  
  image.png
  

  要求：统计表中名字数和各性别人数
  身份证的倒数第二位为奇数则是男，为偶数则是女

• select *,switch(姓名 like "",2,姓名 like "",3,姓名 like "____",4,) as 名字数,switch(身份证号 like "%[13579]","男",身份证号 like "%[02468]","女",) as 性别 from [data1$]（又是不可识别__）

image.png

IIF函数

相当于if函数，最多能嵌套14层。
IIF（条件，true,false）

image.png

给语文成绩大于90的人发小红花

• select 姓名,语文,iif(语文>90,1,0) as 小红花 from [成绩表$]

  
  image.png
  

  语文数学同时大于80的发小红花

• select 姓名,语文,数学,iif(语文>80,(iif(数学>80,1,0)),0) as 小红花 from [成绩表$]

image.png

• 通话时间计算

  
  image.png
• select *,iif(结束时间<开始时间,1+结束时间,结束时间)-开始时间 as 通话时间 from [dome1$]

• 多条件判断

  
  image.png
  

  要求计算各职务奖金的总额。
  条件1，年龄大于等于50或者工龄大于等于5年
  条件2，职务为副经理级别以上，副经理，经理，总管
  满足以上任1条件可以获得奖金1000，否则得500。

• select *, iif((职务 like "%经理" or 职务="主管") or(年龄>=50 and 工龄>=5),1000,500) as 奖金 from [data$]

image.png

INT函数

它是一个取整函数。

image.png

• select ,int(数量/[个/箱]) as 箱数, (数量/[个/箱]-int(数量/[个/箱]))[个/箱] as 个数 from [data1$]

• 求日期的季度

  
  image.png

• select *, "第"&int((month(日期)+2)/3)&"季度" as 季度 from [data2$]
  这个例子看出sql语句也是十分灵活的，支持连接符等操作。

  
  image.png

MOD求余函数

data2

• select *,int(数量/[个/箱]) as 箱数,数量 mod [个/箱] as 余下个数 from [data2$]
  可以看出语句简化很多也更容易理解，当然mod在sql语句中的用法和在excel工作表函数中的用法还是略有区别的。

CHOOSE函数

根据第一参数的结果，在后面参数对应位置给出结果。
如果表达式结果有小数则取整。
在实际运用中choose函数还是比较小众的。

image.png

• 根据分数判断优良中差
• select *,choose(分数/10,"差","差","差","差","差","中","中","良","优","优") as 等级 from[成绩表$]

下面的例子有些意思，统计生肖的人数。需要做到数据透视表中。

image.png

• select *,choose((出生年 mod 12)+1,"猴","鸡","狗","猪","鼠","牛","虎","兔","龙","蛇","马","羊") as 生肖 from [demo$]

  
  image.png

LEFT 和 RIGHT 函数

image.png

left 从左到右提取字符
right 从右向左提取字符

• select 员工编号,left(姓名,1) as 姓氏 from [员工表$]

  
  image.png
  

  姓氏加称谓

• select 员工编号,left(姓名,1)&iif(性别="男","先生","大姐") as 称呼 from [员工表$]

  
  image.png
  

  修整编号

  
  image.png

• select 编号, right("000"&编号,4) as 修整编号 from [demo$]

  
  请忽略错别字
  

  统计男女性别人数

  
  image.png
  两种方法：一种是使用like运算符，另外一种是用right,left,和mod函数。
  mod 2 将会产生1,0,1,0....这样的序列，在函数中1可以理解成TRUE，0可以理解成FALSE
• select 身份证号, iif(身份证号 like "%[02468]_","女","男") as 性别1, iif(right(left(身份证号,17),1) mod 2,"男","女") as 性别2 from [demo$]

image.png

MID函数

语法结构 mid(字符串，提取的起始位置，提取的长度)

image.png

• select *,mid("差差差差差中中良优优",int(分数/10),1) as 等级 from [demo2$]
  结果：

  
  image.png

根据身份证号判断性别

image.png

身份证号有的是15位有的是18位，是15位的最后一位代表性别，18位的第十七位代表性别。那么取出第15位到第17位的数字求余，就能满足条件。

• select 身份证号,iif(mid(身份证号,15,3) mod 2,"男","女") as 性别 from [demo3$]

  
  image.png

LEN函数

字符串长度提取。

demo2

• select *,19&mid(身份证号,int(len(身份证号)/2),2) as 出生年 from [demo2$]

  
  image.png

• 根据星级计算各个部门的奖金总额

  
  image.png
  image.png
• select ,mid("03489",iif(len(星级) is null,0,len(星级))+1,1)100 as 奖金 from [demo1$]
  也可以用choose函数
• select ,choose(iif(len(星级) is null,0,len(星级))+1,0,3,4,8,9)100 as 奖金 from [demo1$]
  用数据透视表简单汇总即可
  image.png

INSTR

INSTR函数与工作表函数FIND类似，只是参数位置略有区别：
INSTR([查找的起始位置]（可省略），查找区域，查找关键字)
FIND(查找的关键字，查找的区域，查找的起始位置（可省略）)

image.png

我们想要把市名提取出来

• select 地址,left(地址,instr(地址,"市")) as 市名 from [demo1$]

  
  image.png
  

  要求提取尺寸代号：

  
  image.png
• select 编码,mid(编码,instr(instr(编码,"-")+1,编码,"-")+1,4) as 尾号 from [demo2]
  这个用法与上一个例子类似。缺点是复杂，在实际运用中可以用内置的分列方法处理，又或者用简单的正则表达式也可以处理。
  image.png

REPLACE查找替换函数

REPLACE（查找的区域，查找的内容，替换内容）
相当于在工作表中按CTRL+F替换的功能。

1. 工作小时数计算

   
   image.png
   

   在实际工作中，经常会碰到一些不太适合数据分析汇总的数据。
   那么要会一些数据处理的方法和技巧。

• select sum(replace(工作时间,"小时","")) as 总小时数 from [demo1$]

  
  image.png

2. 计算人数

   
   image.png

• select 姓名,len(姓名)-len(replace(姓名,"、",""))+1 as 人数 from [demo2$]

  
  image.png
  

  STRING函数
  string(number,string)返回string首字符重复的次数的字符串。

• select string(6,"大上海")

  
  image.png
  

  替换部分内容

  
  image.png

• select *,string(instr(管理层待遇,"-")-1,"x")&mid(管理层待遇,instr(管理层待遇,"-"),50) as 去姓名 from [demo$]

  
  image.png

时间和日期函数

image.png
image.png

拆分出生年月日

• select *,year(出生日期) as 年,month(出生日期) as 月,day(出生日期) as 日 from [demo$]

  
  image.png
  

  汇总上下半年以及每个季度的销量

  
  image.png
• image.png
  

  求出本月余下天数
  dateadd(日期单位，间隔数，日期)

• select dateadd("m",1,now)- now -day(now) as 本月剩余天数

  
  image.png
  

  datediff(日期单位,起始日期,结束日期)

  
  image.png
  计算工龄

• select 员工姓名,入职日期,datediff("yyyy",入职日期,date()) as 工龄 from [demo0$]

  
  结果

条件分组（HAVING）

我们可以用group by 和聚合函数进行各种汇总，但是想对汇总结果进行进一步的条件筛选，这时就用到having语句，having语句与where条件语句基本类似，不过它是针对group by汇总后的进行的条件筛选。

image.png

需要筛选汇总出农作物总产量大于10000数据。

• select 农作物,sum(产量) as 总产量 from [demo1] group by 学号,姓名 having avg(成绩)>80
  这里用到了format函数，是一个十分强大的函数，由于知识点较琐碎，而且刚接触python的format,为了不混淆这里没有进行进一步的深入。
  image.png
  下面做一些综合运用
  image.png
  image.png
  根据这两张表，筛选出考试成绩两次以上不合格人员的学号，姓名，家长，电话。
• select t1.* from [demo4] where 成绩<60) group by 学号 having count(成绩)>1) t2 where t1.学号= t2.学号
  这里用了两层嵌套，首先用where语句筛选出不及格的记录，然后用having 筛选出不及格次数大于1的记录，根据这些条件调出结果。

TRANSFORM转置函数

可以实现像数据透视表一样的现实结果。
TRANSFORM 聚合函数(汇总方式) select 行标签 from 表名 group by 行字段 pivot 列标签

demo1

pivot中心旋转中心轴的意思

• transform sum(销售额) select 销售地区 from [demo1$] group by 销售地区 pivot 产品名称

  
  image.png
  

  行总计

• transform sum(销售额) select 销售地区 from (select * from [demo1] group by 销售地区) group by 销售地区 pivot 产品名称
  几层嵌套之后，成功地迷失了方向