摘要:前言只有光頭才能變強(qiáng)。文本已收錄至我的倉(cāng)庫(kù),歡迎最近在公司做了幾張報(bào)表,還記得剛開始要做報(bào)表的時(shí)候都快把給忘光了當(dāng)時(shí)在廣州休假了個(gè)月多,在實(shí)習(xí)期間也沒(méi)咋寫過(guò),回到公司的第一個(gè)需求就是做報(bào)表。
前言
只有光頭才能變強(qiáng)。文本已收錄至我的GitHub倉(cāng)庫(kù),歡迎Star:https://github.com/ZhongFuCheng3y/3y
最近在公司做了幾張報(bào)表,還記得剛開始要做報(bào)表的時(shí)候都快把SQL給忘光了(當(dāng)時(shí)在廣州休假了1個(gè)月多,在實(shí)習(xí)期間也沒(méi)咋寫過(guò)SQL),回到公司的第一個(gè)需求就是做報(bào)表。
于是我很不要臉地跟帶我的學(xué)長(zhǎng)說(shuō):“SQL我好像忘光了,group 分組查詢好像都忘得差不多了,我得復(fù)習(xí)一下”。
這篇文章來(lái)記錄一下我曾經(jīng)忘掉的group查詢、join查詢等一些比較實(shí)用/常用的SQL
本文主打通俗易懂,不涵蓋任何優(yōu)化(適合新手觀看)
一、回顧group 查詢group 查詢就是分組查詢,為什么要分組查詢?因?yàn)槲覀兿氚茨硞€(gè)維度進(jìn)行統(tǒng)計(jì)。下面來(lái)看個(gè)圖:
比如說(shuō),我想知道:每天Java3y這個(gè)公眾號(hào)的點(diǎn)擊量是多少。按我們?nèi)斯ざ裕悸泛芎?jiǎn)單:把相同的天數(shù)以及公眾號(hào)名稱為Java3y的數(shù)據(jù)找出來(lái),再將每個(gè)點(diǎn)擊量相加,就得出了結(jié)果了。
用上SQL我們可能會(huì)這樣寫:
select name,time,sum(pv) as pv from xxx_table where name = "Java3y" group by name,time1.1 group 查詢可能存在的誤解
記得有一天,有個(gè)群友在群上問(wèn)了一個(gè)問(wèn)題:
其實(shí)他的需求很簡(jiǎn)單:檢索出數(shù)據(jù)分組后時(shí)間最高的記錄。但他是這樣干的:
把先按照時(shí)間 order by
對(duì)order by后的記錄進(jìn)行分組
示例圖:
1.2 造成這個(gè)誤解的可能原因有的工具可以支持這種的寫法:
select * from xxx_table group by name
這種寫法沒(méi)有被禁止,并可以得出結(jié)果,比如得到的結(jié)果是:
Java4y 20 7月15號(hào) Java3y 30 7月15號(hào)
這種寫法其實(shí)是不合理的,要知道的是:使用group by 分組統(tǒng)計(jì)之后,我們的select 后面只能跟著group by 的字段,或者是聚合函數(shù)。
因?yàn)椋覀儗?duì)數(shù)據(jù)進(jìn)行了分組查詢,數(shù)據(jù)的分布情況,我們是不關(guān)心的。
記住:先分組,后統(tǒng)計(jì)(先把數(shù)據(jù)歸類后,再對(duì)相同的數(shù)據(jù)進(jìn)行統(tǒng)計(jì))
1.3 group查詢最常用的SQL去重是我們經(jīng)常會(huì)遇到的問(wèn)題,打個(gè)比方說(shuō),由于各種原因(不管是業(yè)務(wù)上還是說(shuō)是臟數(shù)據(jù)),現(xiàn)在我有兩條重復(fù)的數(shù)據(jù)(除了ID,其余的字段都是相同的):
我這邊只希望留下某一條記錄作為查詢結(jié)果就好了,我們可以寫下以下的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 了以后,還想要查詢結(jié)果中包含group by之外的字段(一般情況下,我們都不可能將group by 涵蓋所有的字段),我們就可以上面那樣,將查詢后的結(jié)果作為子查詢,放在外部查詢的where 子句后,這樣外部查詢是可以select 出其他字段的。
(SQL寫得比較少的朋友可能沒(méi)什么感觸啊,但我希望上面那種寫法大家能夠記住,以后一定會(huì)遇到類似的情況的)
二、回顧join查詢join查詢不知道大家在剛學(xué)的時(shí)候是怎么理解的,反正我當(dāng)初好像就挺迷迷糊糊的。我覺(jué)得join查詢可以簡(jiǎn)單理解成這樣:我想要的查詢結(jié)果,一張表搞不掂,那我就join另一張表
比如說(shuō),現(xiàn)在我有兩張的表:
現(xiàn)在我想知道在7月25號(hào)時(shí):每個(gè)公眾號(hào)的點(diǎn)擊量、公眾號(hào)名稱、號(hào)主名稱、公眾號(hào)的創(chuàng)建日期
顯然,我們會(huì)發(fā)現(xiàn)一張表搞不掂啊,某些數(shù)據(jù)要依賴于另一張表才能把數(shù)據(jù)"完整"展示出來(lái)
那join其實(shí)就是把兩張表合起來(lái)的一個(gè)操作:
兩張表合并起來(lái)以后我們就會(huì)發(fā)現(xiàn),這張“大表”就含有這兩張表的所有字段啦,那我想要什么都有了!
值得注意的是:在join的時(shí)候,會(huì)產(chǎn)生笛卡爾積(至于什么是笛卡爾積我這里就不說(shuō)了,反正我們要記住的是join表時(shí)一定要寫關(guān)聯(lián)條件去除笛卡爾積)
另外,left join和right join也是我們經(jīng)常用到,如果我們單純寫join關(guān)鍵字,那會(huì)被當(dāng)成是inner join 。下面我簡(jiǎn)單解釋一下:
上面說(shuō)了,在join的時(shí)候一定要寫關(guān)聯(lián)條件,如果是inner join的話,只有符合關(guān)聯(lián)條件的數(shù)據(jù)才會(huì)存在最大表中
如果是left join的話,即便關(guān)聯(lián)條件不符合,左邊表的數(shù)據(jù)一定會(huì)存在大表中
如果是right join的話,即便關(guān)聯(lián)條件不符合,右邊表的數(shù)據(jù)一定會(huì)存在大表中
看下面的圖:
此時(shí)我們的兩張表關(guān)聯(lián)的條件是“公眾號(hào)” :如果是inner join,那么最后我們的表只有兩條記錄。如果是left join ,那么最后我們的表有三條數(shù)據(jù)。如果是right join,那么我們最后的表只有兩條數(shù)據(jù)
三、回顧case whenSQL中的case when then else end用法其實(shí)跟我們程序語(yǔ)言中的if-else很是類似,在寫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類型
參考資料:
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)查詢也是非常常見的:
昨天 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í)間格式,需要明確寫出關(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á)式 快速入門”了,請(qǐng)教了一下同事,同事很快就寫出來(lái)了:
select substring_index(left(title , INSTR(title , "】") -1 ) , "【",-1) FROM `xxx_table`
哇~,awesome
最后樂(lè)于輸出干貨的Java技術(shù)公眾號(hào):Java3y。公眾號(hào)內(nèi)有200多篇原創(chuàng)技術(shù)文章、海量視頻資源、精美腦圖,關(guān)注即可獲取!
覺(jué)得我的文章寫得不錯(cuò),點(diǎn)贊!
文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址:http://specialneedsforspecialkids.com/yun/75663.html
摘要:工作中常用到的快捷鍵命令模式下操作行光標(biāo)處縮進(jìn),普通模式下向右縮進(jìn)總結(jié)以上就是我認(rèn)為作為開發(fā)會(huì)經(jīng)常用到的操作,多聯(lián)系就可以熟練掌握啦 vim起源 showImg(https://segmentfault.com/img/remote/1460000016742520); 上圖可以清晰的看到早期的vim作者使用的機(jī)器,此時(shí)的終端機(jī)鍵盤是沒(méi)有獨(dú)立上下左右鍵位的,所以在vim的設(shè)計(jì)之初,HJ...
摘要:步優(yōu)化以及其它數(shù)據(jù)庫(kù)后端掘金原文鏈接在發(fā)表了一篇簡(jiǎn)潔有效有趣和令人信服的分鐘教程描述了如何進(jìn)行優(yōu)化。關(guān)于的七種后端掘金對(duì)于的,在學(xué)習(xí)起來(lái)可能是比較亂的。 5 步優(yōu)化 MongoDB 以及其它數(shù)據(jù)庫(kù) - 后端 - 掘金原文鏈接 Jared Rosoff 在 Scale Out Camp 發(fā)表了一篇簡(jiǎn)潔、有效、有趣和令人信服的《8 分鐘 MongoDB 教程》描述了如何進(jìn)行 MongoDB...
閱讀 1949·2023-04-26 01:59
閱讀 3264·2021-10-11 11:07
閱讀 3295·2021-09-22 15:43
閱讀 3374·2021-09-02 15:21
閱讀 2549·2021-09-01 10:49
閱讀 901·2019-08-29 15:15
閱讀 3089·2019-08-29 13:59
閱讀 2829·2019-08-26 13:36