在工作中常用到的SQL

前言


 

只有光头才能变强。

文本已收录至我的GitHub仓库,欢迎Star:https://github.com/ZhongFuCheng3y/3y

最近在公司做了几张报表,还记得刚开始要做报表的时候都快把SQL给忘光了(当时在广州休假了1个月多,在实习期间也没咋写过SQL),回到公司的第一个需求就是做报表。

于是我很不要脸地跟带我的学长说:“SQL我好像忘光了,group 分组查询好像都忘得差不多了,我得复习一下”。

这篇文章来记录一下我曾经忘掉的group查询、join查询等一些比较实用/常用的SQL

一、回顾group 查询

group查询就是分组查询,为什么要分组查询?因为我们想按某个维度进行统计。下面来看个图:

640?wx_fmt=png
现在我的数据如下

比如说,我想知道:每天Java3y这个公众号的点击量是多少。按我们人工而言,思路很简单:把相同的天数以及公众号名称为Java3y的数据找出来,再将每个点击量相加,就得出了结果了。

640?wx_fmt=png
步骤

用上SQL我们可能会这样写:

select name,time,sum(pv) as pv  from xxx_table where name = 'Java3y' group by name,timename,time,sum(pv) as pv  
from xxx_table 
where name = 'Java3y' group by name,time

1.1 group 查询可能存在的误解

记得有一天,有个群友在群上问了一个问题:

640?wx_fmt=png
群里边的一个问题

其实他的需求很简单:检索出数据分组后时间最高的记录。但他是这样干的:

  1. 把先按照时间 order by

  2. order by后的记录进行分组

示例图:

640?wx_fmt=png
群里面的一个图

1.2 造成这个误解的可能原因

有的工具可以支持这种的写法:

select * from xxx_table group by namefrom xxx_table group by name

这种写法没有被禁止,并可以得出结果,比如得到的结果是:

Java4y    20  7月15号Java3y    30  7月15号

这种写法其实是不合理的,要知道的是:使用group by分组统计之后,我们的select 后面只能跟着group by 的字段,或者是聚合函数。

640?wx_fmt=png
group by规则

因为,我们对数据进行了分组查询,数据的分布情况,我们是不关心的

记住:先分组,后统计(先把数据归类后,再对相同的数据进行统计)

1.3 group查询最常用的SQL

去重是我们经常会遇到的问题,打个比方说,由于各种原因(不管是业务上还是说是脏数据),现在我有两条重复的数据(除了ID,其余的字段都是相同的):

640?wx_fmt=png
重复的数据

我这边只希望留下某一条记录作为查询结果就好了,我们可以写下以下的SQL:

select * from user where id in(   select min(id) from user where name = 'Java3y' and pv = 20 and time='7-25' group by name,pv,time;)from user where id in(
   select min(idfrom user where name = 'Java3y' and pv = 20 and time='7-25' group by name,pv,time;
)

上面这条SQL是非常非常实用的,除了我说的去重以外,其实我们可以再”思考“一下:

(SQL写得比较少的朋友可能没什么感触啊,但我希望上面那种写法大家能够记住,以后一定会遇到类似的情况的)

二、回顾join查询

join查询不知道大家在刚学的时候是怎么理解的,反正我当初好像就挺迷迷糊糊的。我觉得join查询可以简单理解成这样:我想要的查询结果,一张表搞不掂,那我就join另一张表

比如说,现在我有两张的表:

640?wx_fmt=png
第一张表
640?wx_fmt=png
第二张表

现在我想知道在7月25号时:每个公众号的点击量、公众号名称、号主名称、公众号的创建日期

那join其实就是把两张表合起来的一个操作:

640?wx_fmt=png
join其实就是一个合并的操作

两张表合并起来以后我们就会发现,这张“大表”就含有这两张表的所有字段啦,那我想要什么都有了!

值得注意的是:在join的时候,会产生笛卡尔积(至于什么是笛卡尔积我这里就不说了,反正我们要记住的是join表时一定要写关联条件去除笛卡尔积

另外,left joinright join也是我们经常用到,如果我们单纯写join关键字,那会被当成是inner join 。下面我简单解释一下:

看下面的图:

640?wx_fmt=png
join

此时我们的两张表关联的条件是“公众号” :如果是inner join,那么最后我们的表只有两条记录。如果是left join ,那么最后我们的表有三条数据。如果是right join,那么我们最后的表只有两条数据

三、回顾case when

SQL中的case when then else end用法其实跟我们程序语言中的if-else很是类似,在写SQL的时候也常常会用到。

我用得比较多的语法如下:

CASE WHEN sex = '1' THEN '男'         WHEN sex = '2' THEN '女'ELSE '其他' END   
ELSE '其他' END   

在when后面可以跟多个表达式,比如说:

CASE WHEN sex = '1' and name ='Java3y' THEN '男'         WHEN sex = '2' and name ='Java4y' THEN '女'ELSE '其他' END   
ELSE '其他' END   

如果要为case when表达式取别名,在end 关键字后边直接加就好了

更多用法详情参考:

四、一些常用的函数

4.1 hive和presto解析json

我这边会有这种情况:将json数据存到MySQL上。我去网上搜了一下以及问了同事,为什么要将json存到MySQL的字段上时,他们的答复都差不多:

参考资料:

我这边做报表一般来hive或presto上搞的,所以解析json的也是在那上面。

hive解析json函数:

get_json_object(param1,'$.param2')-- 如果是数组get_json_object(xjson,'$.[0].param2')
-- 如果是数组
get_json_object(xjson,'$.[0].param2')

presto 对json的处理函数:

 -- 数组  (去除第index个json) json_array_get(xjson,index)  -- 单个jsoin对象 json_extract(xjson,'$.param2')
 json_array_get(xjson,index) 

 -- 单个jsoin对象
 json_extract(xjson,'$.param2')

参考资料:

4.2 时间函数

昨天/近7天/本月按照这种指标来查询也是非常常见的:

昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 17天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)近30天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)本月SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )上一月SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

SELECT * FROM 表名 WHERE TO_DAYSNOW( ) ) - TO_DAYS( 时间字段名) <= 1

7

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

30

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMATCURDATE( ) , '%Y%m' )

上一月

SELECT * FROM 表名 WHERE PERIOD_DIFFdate_formatnow( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

在presto中使用时间格式,需要明确写出关键字timestamp,比如:

select supplier,count(id) from xxx_table where sendtime >= timestamp '2019-06-01' count(id
from xxx_table 
where sendtime >= timestamp '2019-06-01' 

参考资料:

4.3 其他常用的函数

这里我简单整理一下我最近用过函数:

length  --计算字符串长度concat  --连接两个字符串substring -- 截取字符串count   -- 统计数量max   -- 最大min   -- 最小sum   -- 合计floor/ceil  --...数学函数
concat  --连接两个字符串
substring -- 截取字符串
count   -- 统计数量
max   -- 最大
min   -- 最小
sum   -- 合计
floor/ceil  --...数学函数

再来分享一下最近遇到的一个需求,现在有的数据如下:

【Java3y简单】快乐学习【Java3y简单】快乐学习渣渣【Java3y通俗易懂】简单学【Java3y通俗易懂】简单学芭芭拉【Java3y平易近人】无聊学【Java3y初学者】枯燥学【Java3y初学者】枯燥学呱呱【Java3y大数据】欣慰学【Java3y学习】巴拉巴拉学【Java3y学习】巴拉巴拉学哈哈【Java3y好】雨女无瓜学
【Java3y通俗易懂】简单学
【Java3y通俗易懂】简单学芭芭拉
【Java3y平易近人】无聊学
【Java3y初学者】枯燥学
【Java3y初学者】枯燥学呱呱
【Java3y大数据】欣慰学
【Java3y学习】巴拉巴拉学
【Java3y学习】巴拉巴拉学哈哈
【Java3y好】雨女无瓜学

现在我统计出【】括号里边出现的频次,比如说:Java3y通俗易懂出现的频次是多少。当时一直都没想到好的思路,都快要搜“SQL 正则表达式 快速入门”了,请教了一下同事,同事很快就写出来了:

select substring_index(left(title , INSTR(title , '】') -1 ) , '【',-1) FROM `xxx_table`
FROM `xxx_table`

哇~,awesome

推荐阅读:

640?wx_fmt=jpeg


200多篇 原创 技术文章 海量视频资源 精美脑图 面试题

长按扫码可关注获取 

欢迎关注640?wx_fmt=png点个再看640?wx_fmt=png

已标记关键词 清除标记
相关推荐
简介 笔者当初为了学习JAVA,收集了很多经典源码,源码难易程度分为初级、中级、高级等,详情看源码列表,需要的可以直接下载! 这些源码反映了那时那景笔者对未来的盲目,对代码的热情、执着,对IT的憧憬、向往!此时此景,笔者只专注Android、Iphone等移动平台开发,看着这些源码心中有万分感慨,写此文章纪念那时那景! Java 源码包 Applet钢琴模拟程序java源码 2个目标文件,提供基本的音乐编辑功能。编辑音乐软件的朋友,这款实例会对你有所帮助。 Calendar万年历 1个目标文件 EJB 模拟银行ATM流程及操作源代码 6个目标文件,EJB来模拟银行ATM机的流程及操作:获取系统属性,初始化JNDI,取得Home对象的引用,创建EJB对象,并将当前的计数器初始化,调用每一个EJB对象的count()方法,保证Bean正常被激活和钝化,EJB对象是用完毕,从内存中清除,从账户中取出amt,如果amt>账户余额抛出异常,一个实体Bean可以表示不同的数据实例,我们应该通过主键来判断删除哪个数据实例…… ejbCreate函数用于初始化一个EJB实例 5个目标文件,演示Address EJB的实现 ,创建一个EJB测试客户端,得到名字上下文,查询jndi名,通过强制转型得到Home接口,getInitialContext()函数返回一个经过初始化的上下文,用client的getHome()函数调用Home接口函数得到远程接口的引用,用远程接口的引用访问EJB。 EJB中JNDI的使用源码例子 1个目标文件,JNDI的使用例子,有源代码,可以下载参考,JNDI的使用,初始化Context,它是连接JNDI树的起始点,查找你要的对象,打印找到的对象,关闭Context…… ftp文件传输 2个目标文件,FTP的目标是:(1)提高文件的共享性(计算机程序和/或数据),(2)鼓励间接地(通过程序)使用远程计算机,(3)保护用户因主机之间的文件存储系统导致的变化,(4)为了可靠和高效地传输,虽然用户可以在终端上直接地使用它,但是它的主要作用是供程序使用的。本规范尝试满足大型主机、微型主机、个人工作站、和TACs 的不同需求。例如,容易实现协议的设计。 Java EJB中有、无状态SessionBean的两个例子 两个例子,无状态SessionBean可会话Bean必须实现SessionBean,获取系统属性,初始化JNDI,取得Home对象的引用,创建EJB对象,计算利息等;在有状态SessionBean中,用累加器,以对话状态存储起来,创建EJB对象,并将当前的计数器初始化,调用每一个EJB对象的count()方法,保证Bean正常被激活和钝化,EJB对象是用完毕,从内存中清除…… Java Socket 聊天通信演示代码 2个目标文件,一个服务器,一个客户端。 Java Telnet客户端实例源码 一个目标文件,演示Socket的使用。 Java 组播组中发送和接受数据实例 3个目标文件。 Java读写文本文件的示例代码 1个目标文件。 java俄罗斯方块 一个目标文件。 Java非对称加密源码实例 1个目标文件 摘要:Java源码,算法相关,非对称加密   Java非对称加密源程序代码实例,本例中使用RSA加密技术,定义加密算法可用 DES,DESede,Blowfish等。   设定字符串为“张三,你好,我是李四”   产生张三的密钥对(keyPairZhang)   张三生成公钥(publicKeyZhang)并发送给李四,这里发送的是公钥的数组字节   通过网络或磁盘等方式,把公钥编码传送给李四,李四接收到张三编码后的公钥,将其解码,李四用张三的公钥加密信息,并发送给李四,张三用自己的私钥解密从李四处收到的信息…… Java利用DES私钥对称加密代码实例 同上 java聊天室 2个目标文件,简单。 java模拟掷骰子2个 1个目标文件,输出演示。 java凭图游戏 一个目标文件,简单。 java求一个整数的因子 如题。 Java生成密钥的实例 1个目标文件 摘要:Java源码,算法相关,密钥   Java生成密钥、保存密钥的实例源码,通过本源码可以了解到Java如何产生单钥加密的密钥(myKey)、产生双钥的密钥对(keyPair)、如何保存公钥的字节数组、保存私钥到文件privateKey.dat、如何用Java对象序列化保存私钥,通常应对私钥加密后再保存、如何从文件中得到公钥编码的字节数组、如何从字节数组解码公钥。 Java数据压缩与传输实例 1个目标文件 摘要:Java源码,文件操作,数据压缩,文件传输   Java数据压缩与传输实例,可以学习一下实例化套按字、得到文件输入流、压缩输入流、文件输出流、实例化缓冲
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页