你在工作中常用到的那些SQL
這篇文章來(lái)記錄一下我曾經(jīng)忘掉的group查詢(xún)、join查詢(xún)等一些比較實(shí)用/常用的SQL。
本文主打通俗易懂，不涵蓋任何優(yōu)化(適合新手觀(guān)看)
一、回顧group 查詢(xún)
group查詢(xún)就是分組查詢(xún)，為什么要分組查詢(xún)?因?yàn)槲覀兿氚茨硞€(gè)維度進(jìn)行統(tǒng)計(jì)。下面來(lái)看個(gè)圖：
現(xiàn)在我的數(shù)據(jù)如下
比如說(shuō)，我想知道：每天Java3y這個(gè)公眾號(hào)的點(diǎn)擊量是多少。按我們?nèi)斯ざ裕悸泛芎?jiǎn)單：把相同的天數(shù)以及公眾號(hào)名稱(chēng)為Java3y的數(shù)據(jù)找出來(lái)，再將每個(gè)點(diǎn)擊量相加，就得出了結(jié)果了。
步驟
用上SQL我們可能會(huì)這樣寫(xiě)：
- select name,time,sum(pv) as pv from xxx_table where name = 'Java3y' group by name,time
1.1 group 查詢(xún)可能存在的誤解
記得有一天，有個(gè)群友在群上問(wèn)了一個(gè)問(wèn)題：
群里邊的一個(gè)問(wèn)題
其實(shí)他的需求很簡(jiǎn)單：檢索出數(shù)據(jù)分組后時(shí)間最高的記錄。但他是這樣干的：
- 把先按照時(shí)間 order by
- 對(duì)order by后的記錄進(jìn)行分組
示例圖：
群里面的一個(gè)圖
1.2 造成這個(gè)誤解的可能原因
有的工具可以支持這種的寫(xiě)法：
- select * from xxx_table group by name
這種寫(xiě)法沒(méi)有被禁止，并可以得出結(jié)果，比如得到的結(jié)果是：
- Java4y 20 7月15號(hào)
- Java3y 30 7月15號(hào)
這種寫(xiě)法其實(shí)是不合理的，要知道的是：使用group by分組統(tǒng)計(jì)之后，我們的select 后面只能跟著group by 的字段，或者是聚合函數(shù)。
group by規(guī)則
因?yàn)?，我們?duì)數(shù)據(jù)進(jìn)行了分組查詢(xún)，數(shù)據(jù)的分布情況，我們是不關(guān)心的。
記?。合确纸M，后統(tǒng)計(jì)(先把數(shù)據(jù)歸類(lèi)后，再對(duì)相同的數(shù)據(jù)進(jìn)行統(tǒng)計(jì))
1.3 group查詢(xún)常用的SQL
去重是我們經(jīng)常會(huì)遇到的問(wèn)題，打個(gè)比方說(shuō)，由于各種原因(不管是業(yè)務(wù)上還是說(shuō)是臟數(shù)據(jù))，現(xiàn)在我有兩條重復(fù)的數(shù)據(jù)(除了ID，其余的字段都是相同的)：
重復(fù)的數(shù)據(jù)
我這邊只希望留下某一條記錄作為查詢(xún)結(jié)果就好了，我們可以寫(xiě)下以下的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是非常非常實(shí)用的，除了我說(shuō)的去重以外，其實(shí)我們可以再”思考“一下：
上面已經(jīng)說(shuō)了，使用group by分組統(tǒng)計(jì)之后，我們的select 后面只能跟著group by 的字段，或者是聚合函數(shù)。
很多時(shí)候我們group by了以后，還想要查詢(xún)結(jié)果中包含group by之外的字段(一般情況下，我們都不可能將group by 涵蓋所有的字段)，我們就可以上面那樣，將查詢(xún)后的結(jié)果作為子查詢(xún)，放在外部查詢(xún)的where 子句后，這樣外部查詢(xún)是可以select 出其他字段的。
(SQL寫(xiě)得比較少的朋友可能沒(méi)什么感觸啊，但我希望上面那種寫(xiě)法大家能夠記住，以后一定會(huì)遇到類(lèi)似的情況的)
二、回顧join查詢(xún)
join查詢(xún)不知道大家在剛學(xué)的時(shí)候是怎么理解的，反正我當(dāng)初好像就挺迷迷糊糊的。我覺(jué)得join查詢(xún)可以簡(jiǎn)單理解成這樣：我想要的查詢(xún)結(jié)果，一張表搞不掂，那我就join另一張表
比如說(shuō)，現(xiàn)在我有兩張的表：
第一張表
第二張表
現(xiàn)在我想知道在7月25號(hào)時(shí)：每個(gè)公眾號(hào)的點(diǎn)擊量、公眾號(hào)名稱(chēng)、號(hào)主名稱(chēng)、公眾號(hào)的創(chuàng)建日期
- 顯然，我們會(huì)發(fā)現(xiàn)一張表搞不掂啊，某些數(shù)據(jù)要依賴(lài)于另一張表才能把數(shù)據(jù)"完整"展示出來(lái)
那join其實(shí)就是把兩張表合起來(lái)的一個(gè)操作：
join其實(shí)就是一個(gè)合并的操作
兩張表合并起來(lái)以后我們就會(huì)發(fā)現(xiàn)，這張“大表”就含有這兩張表的所有字段啦，那我想要什么都有了!
值得注意的是：在join的時(shí)候，會(huì)產(chǎn)生笛卡爾積(至于什么是笛卡爾積我這里就不說(shuō)了，反正我們要記住的是join表時(shí)一定要寫(xiě)關(guān)聯(lián)條件去除笛卡爾積)
另外，left join和right join也是我們經(jīng)常用到，如果我們單純寫(xiě)join關(guān)鍵字，那會(huì)被當(dāng)成是inner join 。下面我簡(jiǎn)單解釋一下：
- 上面說(shuō)了，在join的時(shí)候一定要寫(xiě)關(guān)聯(lián)條件，如果是inner join的話(huà)，只有符合關(guān)聯(lián)條件的數(shù)據(jù)才會(huì)存在最大表中
- 如果是left join的話(huà)，即便關(guān)聯(lián)條件不符合，左邊表的數(shù)據(jù)一定會(huì)存在大表中
- 如果是right join的話(huà)，即便關(guān)聯(lián)條件不符合，右邊表的數(shù)據(jù)一定會(huì)存在大表中
看下面的圖：
join
此時(shí)我們的兩張表關(guān)聯(lián)的條件是“公眾號(hào)” ：如果是inner join，那么最后我們的表只有兩條記錄。如果是left join ，那么最后我們的表有三條數(shù)據(jù)。如果是right join，那么我們最后的表只有兩條數(shù)據(jù)
三、回顧case when
SQL中的case when then else end用法其實(shí)跟我們程序語(yǔ)言中的if-else很是類(lèi)似，在寫(xiě)SQL的時(shí)候也常常會(huì)用到。
我用得比較多的語(yǔ)法如下：
- CASE
- WHEN sex = '1' THEN '男'
- WHEN sex = '2' THEN '女'
- ELSE '其他' END
在when后面可以跟多個(gè)表達(dá)式，比如說(shuō)：
- CASE
- WHEN sex = '1' and name ='Java3y' THEN '男'
- WHEN sex = '2' and name ='Java4y' THEN '女'
- ELSE '其他' END
如果要為case when表達(dá)式取別名，在end 關(guān)鍵字后邊直接加就好了
更多用法詳情參考：
- https://www.cnblogs.com/prefect/p/5746624.html
四、一些常用的函數(shù)
4.1 hive和presto解析json
我這邊會(huì)有這種情況：將json數(shù)據(jù)存到MySQL上。我去網(wǎng)上搜了一下以及問(wèn)了同事，為什么要將json存到MySQL的字段上時(shí)，他們的答復(fù)都差不多：
- 在MySQL存json數(shù)據(jù)，這樣方便擴(kuò)展啊。如果那些字段不需要用到索引，改動(dòng)比較頻繁，你又不想改動(dòng)表的結(jié)構(gòu)，那可以存json。
- ps:在MySQL 5.7版本以后支持json類(lèi)型
參考資料：
- https://cloud.tencent.com/developer/article/1004449
- https://www.zhihu.com/question/324674084/answer/685522547
我這邊做報(bào)表一般來(lái)hive或presto上搞的，所以解析json的也是在那上面。
hive解析json函數(shù)：
- get_json_object(param1,'$.param2')
- -- 如果是數(shù)組
- get_json_object(xjson，'$.[0].param2')
presto 對(duì)json的處理函數(shù):
- -- 數(shù)組 (去除第index個(gè)json)
- json_array_get(xjson,index)
- -- 單個(gè)jsoin對(duì)象
- json_extract(xjson,'$.param2')
參考資料：
- https://www.cnblogs.com/drjava/p/10536922.html
4.2 時(shí)間函數(shù)
昨天/近7天/本月按照這種指標(biāo)來(lái)查詢(xún)也是非常常見(jiàn)的：
昨天
- SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 時(shí)間字段名) <= 1
7天
- SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(時(shí)間字段名)
近30天
- SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(時(shí)間字段名)
本月
- SELECT * FROM 表名 WHERE DATE_FORMAT( 時(shí)間字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
- SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 時(shí)間字段名, '%Y%m' ) ) =1
在presto中使用時(shí)間格式，需要明確寫(xiě)出關(guān)鍵字timestamp，比如：
- select supplier,count(id) from xxx_table where sendtime >= timestamp '2019-06-01'
參考資料：
- https://blog.csdn.net/cool_easy/article/details/50880949
4.3 其他常用的函數(shù)
這里我簡(jiǎn)單整理一下我最近用過(guò)函數(shù)：
- length --計(jì)算字符串長(zhǎng)度
- concat --連接兩個(gè)字符串
- substring -- 截取字符串
- count -- 統(tǒng)計(jì)數(shù)量
- max -- 最大
- min -- 最小
- sum -- 合計(jì)
- floor/ceil --...數(shù)學(xué)函數(shù)
再來(lái)分享一下最近遇到的一個(gè)需求，現(xiàn)在有的數(shù)據(jù)如下：
- 【Java3y簡(jiǎn)單】快樂(lè)學(xué)習(xí)
- 【Java3y簡(jiǎn)單】快樂(lè)學(xué)習(xí)渣渣
- 【Java3y通俗易懂】簡(jiǎn)單學(xué)
- 【Java3y通俗易懂】簡(jiǎn)單學(xué)芭芭拉
- 【Java3y平易近人】無(wú)聊學(xué)
- 【Java3y初學(xué)者】枯燥學(xué)
- 【Java3y初學(xué)者】枯燥學(xué)呱呱
- 【Java3y大數(shù)據(jù)】欣慰學(xué)
- 【Java3y學(xué)習(xí)】巴拉巴拉學(xué)
- 【Java3y學(xué)習(xí)】巴拉巴拉學(xué)哈哈
- 【Java3y好】雨女無(wú)瓜學(xué)
現(xiàn)在我統(tǒng)計(jì)出【】括號(hào)里邊出現(xiàn)的頻次，比如說(shuō)：Java3y通俗易懂出現(xiàn)的頻次是多少。當(dāng)時(shí)一直都沒(méi)想到好的思路，都快要搜“SQL 正則表達(dá)式 快速入門(mén)”了，請(qǐng)教了一下同事，同事很快就寫(xiě)出來(lái)了：
- select substring_index(left(title , INSTR(title , '】') -1 ) , '【',-1) FROM `xxx_table`
哇~，awesome
