前言
隻有光頭才能變強。
文本已收錄至我的GitHub倉庫,歡迎Star:
https://github.com/ZhongFuCheng3y/3y
最近在公司做了幾張報表,還記得剛開始要做報表的時候都快把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
- 很多時候我們
了以後,還想要查詢結果中包含group by
之外的字段(一般情況下,我們都不可能将group by 涵蓋所有的字段),我們就可以上面那樣,将查詢後的結果作為子查詢,放在外部查詢的where 子句後,這樣外部查詢是可以select 出其他字段的。group by
(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
關鍵字後邊直接加就好了
更多用法詳情參考:
四、一些常用的函數
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://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')
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'
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多篇原創技術文章、海量視訊資源、精美腦圖,關注即可擷取!
覺得我的文章寫得不錯,點贊!