转载

在工作中常用到的SQL

只有光头才能变强。

文本已收录至我的GitHub仓库，欢迎Star： github.com/ZhongFuChen…

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

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

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

本文主打 通俗易懂 ，不涵盖任何优化(适合新手观看)

一、回顾group 查询

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

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

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

select name,time,sum(pv) as pv  
from xxx_table 
where name = 'Java3y' group by name,time
复制代码

1.1 group 查询可能存在的误解

记得有一天，有个群友在群上问了一个问题：

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

order by
order by

示例图：

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

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

select * from xxx_table group by name
复制代码

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

Java4y    20  7月15号
Java3y    30  7月15号
复制代码

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

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

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

1.3 group查询最常用的SQL

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

我这边只希望留下某一条记录作为查询结果就好了，我们可以写下以下的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;
)
复制代码

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

上面已经说了，使用 group by 分组统计之后，我们的select 后面只能跟着group by 的字段，或者是聚合函数。
很多时候我们 group by 了以后，还想要查询结果中包含 group by 之外的字段(一般情况下，我们都不可能将group by 涵盖所有的字段)，我们就可以上面那样，将查询后的结果作为子查询，放在外部查询的where 子句后，这样外部查询是可以select 出其他字段的。

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

二、回顾join查询

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

比如说，现在我有两张的表：

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

显然，我们会发现 一张表搞不掂 啊，某些数据要依赖于另一张表才能把数据"完整"展示出来

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

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

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

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

上面说了，在join的时候一定要写 关联条件 ，如果是 inner join 的话，只有符合关联条件的数据才会存在最大表中
如果是 left join 的话，即便关联条件不符合，左边表的数据一定会存在大表中
如果是 right 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   
复制代码

在when后面可以跟多个表达式，比如说：

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

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

更多用法详情参考：

www.cnblogs.com/prefect/p/5…

四、一些常用的函数

4.1 hive和presto解析json

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

在MySQL存json数据，这样方便扩展啊。如果那些字段不需要用到索引，改动比较频繁，你又不想改动表的结构，那可以存join。
ps:在MySQL 5.7版本以后支持json类型

参考资料：

cloud.tencent.com/developer/a…
www.zhihu.com/question/32…

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

hive解析json函数：

get_json_object(param1,'$.param2')

-- 如果是数组
get_json_object(xjson，'$.[0].param2')
复制代码

presto 对json的处理函数:

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

参考资料：

www.cnblogs.com/drjava/p/10…

4.2 时间函数

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

昨天

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - 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_FORMAT( CURDATE( ) , '%Y%m' )

上一月

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


复制代码

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

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

复制代码

参考资料：

blog.csdn.net/cool_easy/a…

4.3 其他常用的函数

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

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

复制代码

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

【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`

复制代码

哇~，awesome

原文 https://juejin.im/post/5d3f9cc1f265da03a31d1192

正文到此结束

所属分类： Java 编程技术

本文标签： java sql 时间分组查询 Presto mysql cat 需求 src id 正则表达式 tar 代码 Developer 希望 GitHub REST IO js SDN json ORM https 大数据 Select 文章 git 数据解析 http 2019 tab UI 初学者统计索引
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

在工作中常用到的SQL

一、回顾group 查询

1.1 group 查询可能存在的误解

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

1.3 group查询最常用的SQL

二、回顾join查询

三、回顾case when

四、一些常用的函数

4.1 hive和presto解析json

4.2 时间函数

4.3 其他常用的函数

哇~，awesome

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流