在工作中常用到的SQL

叁歲伎倆 2021-09-30 09:22 328阅读 0赞

## 前言 ##

> 只有光头才能变强。

> 文本已收录至我的GitHub仓库，欢迎Star：[https://github.com/ZhongFuCheng3y/3y][https_github.com_ZhongFuCheng3y_3y]

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

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

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

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

## 一、回顾group 查询 ##

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

![现在我的数据如下][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxMzRkMWU5OGM5NGI]

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

![步骤][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxMzk4MTU0YTI5Yzc]

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

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

### 1.1 group 查询可能存在的误解 ###

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

![群里边的一个问题][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxNzYyYTAxMDUxMjE]

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

1.  把先按照时间 `order by`
2.  对`order by`后的记录进行分组

示例图：

![群里面的一个图][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxYTMyMDk1ZmIwYTg]

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

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

select * from xxx_table group by name

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

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

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

![group by规则][group by]

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

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

### 1.3 group查询最常用的SQL ###

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

![重复的数据][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMzYTJiNGY3NzViNWU]

我这边只希望留下某一条记录作为查询结果就好了，我们可以写下以下的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另一张表**

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

![第一张表][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMzYmFhYWU2NjMxNjI]

![第二张表][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMzYmIyODI2MTk4YTA]

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

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

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

![join其实就是一个合并的操作][join]

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

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

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

*  上面说了，在join的时候一定要写**关联条件**，如果是`inner join`的话，只有符合关联条件的数据才会存在最大表中
 *  如果是`left join`的话，即便关联条件不符合，左边表的数据一定会存在大表中
 *  如果是`right join`的话，即便关联条件不符合，右边表的数据一定会存在大表中

看下面的图：

![join][join 1]

此时我们的两张表关联的条件是“公众号” ：如果是`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` 关键字后边直接加就好了

更多用法详情参考：

*  [https://www.cnblogs.com/prefect/p/5746624.html][https_www.cnblogs.com_prefect_p_5746624.html]

### 四、一些常用的函数 ###

### 4.1 hive和presto解析json ###

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

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

参考资料：

*  [https://cloud.tencent.com/developer/article/1004449][https_cloud.tencent.com_developer_article_1004449]
 *  [https://www.zhihu.com/question/324674084/answer/685522547][https_www.zhihu.com_question_324674084_answer_685522547]

我这边做报表一般来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')

参考资料：

*  [https://www.cnblogs.com/drjava/p/10536922.html][https_www.cnblogs.com_drjava_p_10536922.html]

### 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'

参考资料：

*  [https://blog.csdn.net/cool\_easy/article/details/50880949][https_blog.csdn.net_cool_easy_article_details_50880949]

### 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**

## 最后 ##

> 乐于输出**干货**的Java技术公众号：**Java3y**。公众号内**有200多篇原创**技术文章、海量视频资源、精美脑图，**关注即可获取！**

![转发到朋友圈是对我最大的支持！][aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI0LzE2YzI0MTc2OWVkNzdmMDY]

觉得我的文章写得不错，点**赞**！

[https_github.com_ZhongFuCheng3y_3y]: https://github.com/ZhongFuCheng3y/3y
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxMzRkMWU5OGM5NGI]: /images/20210827/e6ff9ba7a6bd434fb5076d0ee1ec08e6.png
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxMzk4MTU0YTI5Yzc]: /images/20210827/37f983527dc24cd6bbf2e9e3086d6ded.png
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxNzYyYTAxMDUxMjE]: /images/20210827/be4a168ba5b04498b25e4879d125ac33.png
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMxYTMyMDk1ZmIwYTg]: /images/20210827/5cc2232deedd4dc4b190428494588270.png
[group by]: /images/20210827/57e21bd7ec4e4f1eb6e045654b4d8174.png
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMzYTJiNGY3NzViNWU]: /images/20210827/927cf704179f4010ac9ec1a0340b8f85.png
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMzYmFhYWU2NjMxNjI]: /images/20210827/53b2fdd573024cf1b54064e51f0a8578.png
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI3LzE2YzMzYmIyODI2MTk4YTA]: /images/20210827/a7ed22deda0b4d5e80c42ec55653b3c3.png
[join]: /images/20210827/525b0f5e402249fbb8dfbb66b456b26c.png
[join 1]: /images/20210827/34abaf7c7c704cf28e6c8f07119f1d53.png
[https_www.cnblogs.com_prefect_p_5746624.html]: https://www.cnblogs.com/prefect/p/5746624.html
[https_cloud.tencent.com_developer_article_1004449]: https://cloud.tencent.com/developer/article/1004449
[https_www.zhihu.com_question_324674084_answer_685522547]: https://www.zhihu.com/question/324674084/answer/685522547
[https_www.cnblogs.com_drjava_p_10536922.html]: https://www.cnblogs.com/drjava/p/10536922.html
[https_blog.csdn.net_cool_easy_article_details_50880949]: https://blog.csdn.net/cool_easy/article/details/50880949
[aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS83LzI0LzE2YzI0MTc2OWVkNzdmMDY]: /images/20210827/e862372226a94f3cbf0b146ea9d70358.png