[[272989]]

這篇文章來記錄一下我曾經(jīng)忘掉的group查詢、join查詢等一些比較實用/常用的SQL。

本文主打通俗易懂，不涵蓋任何優(yōu)化(適合新手觀看)

一、回顧group 查詢

group查詢就是分組查詢，為什么要分組查詢?因為我們想按某個維度進行統(tǒng)計。下面來看個圖： 

 

現(xiàn)在我的數(shù)據(jù)如下  

比如說，我想知道：每天Java3y這個公眾號的點擊量是多少。按我們?nèi)斯ざ?，思路很簡單：把相同的天?shù)以及公眾號名稱為Java3y的數(shù)據(jù)找出來，再將每個點擊量相加，就得出了結(jié)果了。

 

 

步驟

 

 

用上SQL我們可能會這樣寫：

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

1.1 group 查詢可能存在的誤解

記得有一天，有個群友在群上問了一個問題：

 

 

群里邊的一個問題

 

 

其實他的需求很簡單：檢索出數(shù)據(jù)分組后時間最高的記錄。但他是這樣干的：

1. 把先按照時間 order by
2. 對order by后的記錄進行分組

示例圖：

 

 

群里面的一個圖

 

 

1.2 造成這個誤解的可能原因

有的工具可以支持這種的寫法：

select * from xxx_table group by name 

這種寫法沒有被禁止，并可以得出結(jié)果，比如得到的結(jié)果是：

Java4y 20 7月15號
Java3y 30 7月15號 

這種寫法其實是不合理的，要知道的是：使用group by分組統(tǒng)計之后，我們的select 后面只能跟著group by 的字段，或者是聚合函數(shù)。

 

 

group by規(guī)則 

因為，我們對數(shù)據(jù)進行了分組查詢，數(shù)據(jù)的分布情況，我們是不關(guān)心的。

記?。合确纸M，后統(tǒng)計(先把數(shù)據(jù)歸類后，再對相同的數(shù)據(jù)進行統(tǒng)計)

1.3 group查詢常用的SQL

去重是我們經(jīng)常會遇到的問題，打個比方說，由于各種原因(不管是業(yè)務上還是說是臟數(shù)據(jù))，現(xiàn)在我有兩條重復的數(shù)據(jù)(除了ID，其余的字段都是相同的)：

 

 

重復的數(shù)據(jù) 

我這邊只希望留下某一條記錄作為查詢結(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是非常非常實用的，除了我說的去重以外，其實我們可以再”思考“一下：

上面已經(jīng)說了，使用group by分組統(tǒng)計之后，我們的select 后面只能跟著group by 的字段，或者是聚合函數(shù)。

很多時候我們group by了以后，還想要查詢結(jié)果中包含group by之外的字段(一般情況下，我們都不可能將group by 涵蓋所有的字段)，我們就可以上面那樣，將查詢后的結(jié)果作為子查詢，放在外部查詢的where 子句后，這樣外部查詢是可以select 出其他字段的。

(SQL寫得比較少的朋友可能沒什么感觸啊，但我希望上面那種寫法大家能夠記住，以后一定會遇到類似的情況的)

二、回顧join查詢

join查詢不知道大家在剛學的時候是怎么理解的，反正我當初好像就挺迷迷糊糊的。我覺得join查詢可以簡單理解成這樣：我想要的查詢結(jié)果，一張表搞不掂，那我就join另一張表

比如說，現(xiàn)在我有兩張的表：

 

第一張表 

 

第二張表 

 

現(xiàn)在我想知道在7月25號時：每個公眾號的點擊量、公眾號名稱、號主名稱、公眾號的創(chuàng)建日期

• 顯然，我們會發(fā)現(xiàn)一張表搞不掂啊，某些數(shù)據(jù)要依賴于另一張表才能把數(shù)據(jù)"完整"展示出來

那join其實就是把兩張表合起來的一個操作： 

 

join其實就是一個合并的操作  

兩張表合并起來以后我們就會發(fā)現(xiàn)，這張“大表”就含有這兩張表的所有字段啦，那我想要什么都有了!

值得注意的是：在join的時候，會產(chǎn)生笛卡爾積(至于什么是笛卡爾積我這里就不說了，反正我們要記住的是join表時一定要寫關(guān)聯(lián)條件去除笛卡爾積)

另外，left join和right join也是我們經(jīng)常用到，如果我們單純寫join關(guān)鍵字，那會被當成是inner join 。下面我簡單解釋一下：

• 上面說了，在join的時候一定要寫關(guān)聯(lián)條件，如果是inner join的話，只有符合關(guān)聯(lián)條件的數(shù)據(jù)才會存在最大表中
• 如果是left join的話，即便關(guān)聯(lián)條件不符合，左邊表的數(shù)據(jù)一定會存在大表中
• 如果是right join的話，即便關(guān)聯(lián)條件不符合，右邊表的數(shù)據(jù)一定會存在大表中

看下面的圖： 

 

join 

此時我們的兩張表關(guān)聯(lián)的條件是“公眾號” ：如果是inner join，那么最后我們的表只有兩條記錄。如果是left join ，那么最后我們的表有三條數(shù)據(jù)。如果是right join，那么我們最后的表只有兩條數(shù)據(jù)

三、回顧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 關(guān)鍵字后邊直接加就好了

更多用法詳情參考：

• https://www.cnblogs.com/prefect/p/5746624.html

四、一些常用的函數(shù)

4.1 hive和presto解析json

我這邊會有這種情況：將json數(shù)據(jù)存到MySQL上。我去網(wǎng)上搜了一下以及問了同事，為什么要將json存到MySQL的字段上時，他們的答復都差不多：

• 在MySQL存json數(shù)據(jù)，這樣方便擴展啊。如果那些字段不需要用到索引，改動比較頻繁，你又不想改動表的結(jié)構(gòu)，那可以存json。
• ps:在MySQL 5.7版本以后支持json類型

參考資料：

• https://cloud.tencent.com/developer/article/1004449
• https://www.zhihu.com/question/324674084/answer/685522547

我這邊做報表一般來hive或presto上搞的，所以解析json的也是在那上面。

hive解析json函數(shù)： 

get_json_object(param1,'$.param2')  
-- 如果是數(shù)組  
get_json_object(xjson，'$.[0].param2')  

presto 對json的處理函數(shù): 

-- 數(shù)組 (去除第index個json)  
json_array_get(xjson,index)  
-- 單個jsoin對象  
json_extract(xjson,'$.param2')  

參考資料：

• https://www.cnblogs.com/drjava/p/10536922.html

4.2 時間函數(shù)

昨天/近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中使用時間格式，需要明確寫出關(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ù)

這里我簡單整理一下我最近用過函數(shù)：

length  --計算字符串長度 
concat  --連接兩個字符串 
substring -- 截取字符串 
count   -- 統(tǒng)計數(shù)量 
max   -- 最大 
min   -- 最小 
sum   -- 合計 
floor/ceil  --...數(shù)學函數(shù) 

再來分享一下最近遇到的一個需求，現(xiàn)在有的數(shù)據(jù)如下：

【Java3y簡單】快樂學習 
【Java3y簡單】快樂學習渣渣 
【Java3y通俗易懂】簡單學 
【Java3y通俗易懂】簡單學芭芭拉 
【Java3y平易近人】無聊學 
【Java3y初學者】枯燥學 
【Java3y初學者】枯燥學呱呱 
【Java3y大數(shù)據(jù)】欣慰學 
【Java3y學習】巴拉巴拉學 
【Java3y學習】巴拉巴拉學哈哈 
【Java3y好】雨女無瓜學 

現(xiàn)在我統(tǒng)計出【】括號里邊出現(xiàn)的頻次，比如說：Java3y通俗易懂出現(xiàn)的頻次是多少。當時一直都沒想到好的思路，都快要搜“SQL 正則表達式 快速入門”了，請教了一下同事，同事很快就寫出來了：

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

哇~，awesome