戳上方藍(lán)字關(guān)注我?

? ? 這兩年學(xué)會(huì)了跑sql，當(dāng)時(shí)有很多同學(xué)幫助我精進(jìn)了這個(gè)技能，現(xiàn)在也寫成一個(gè)小教程，反饋給大家。

? ? 適用對(duì)象：工作中能接觸到sql查詢平臺(tái)的業(yè)務(wù)同學(xué)(例如有數(shù)據(jù)查詢權(quán)限的產(chǎn)品與運(yùn)營同學(xué))

? ? 適用場(chǎng)景：查詢hive&mysql上的數(shù)據(jù)

????文檔優(yōu)勢(shì)：比起各類從零起步的教程教材，理解門檻低，有效信息密度大，可以覆蓋高頻業(yè)務(wù)場(chǎng)景。? ?

????文末有一些常見的小技巧，希望幫助同學(xué)們提升工作效率。

• SQL的基礎(chǔ)結(jié)構(gòu)：

• • 做一個(gè)類比，我們用的“表”，就像是一個(gè)賬本，每天就是一個(gè)“分區(qū)”，“字段”就是日記上記的不同的事情，例如支出、收入、物品和價(jià)格等；

• • 一般來講sql有如下結(jié)構(gòu)，意思是從表α.table里面，獲取某一天的abc三個(gè)字段，后面的講解都是在這個(gè)基礎(chǔ)上展開的：

select a,b,c --select后面輸入需要查詢的字段fromα.table --from后面輸入需要查詢的表名wheredate='yymmdd' --where后面輸入需要卡的條件，例如只看哪天的數(shù)據(jù)

• 多個(gè)條件同時(shí)生效-and和or

• • and的用法--表示多條件同時(shí)生效

select a,b,c --select后面輸入需要查詢的字段fromα.table --from后面輸入需要查詢的表名where date='yymmdd' --where后面輸入需要卡的條件，例如只看哪天的數(shù)據(jù)and --表示要看date='yymmdd' 且id='XXX'id=XXX

• • or的用法--表示有一個(gè)條件生效即可

select a,b,c --select后面輸入需要查詢的字段fromα.table --from后面輸入需要查詢的表名where date='yymmdd' --where后面輸入需要卡的條件，例如只看哪天的數(shù)據(jù)and (id=XXXor d_id=XXX)???--表示要看date='yymmdd'?，且id是xxx或d_id是xxx的數(shù)據(jù)。如果沒有這個(gè)括號(hào)，表示的是要看date是yymmdd且id是xxx，或者不分日期，d_id是xxx的數(shù)據(jù)

• 對(duì)指標(biāo)進(jìn)行加和--sum

• • 學(xué)習(xí)到本節(jié)時(shí)，我們需要明白維度和指標(biāo)的區(qū)別，維度是表示屬性的，指標(biāo)是表示量級(jí)的，例如全中國有56個(gè)民族，全中國就是一個(gè)維度，民族數(shù)量就是一個(gè)指標(biāo)

select --select后面輸入需要查詢的字段a,b,sum(c) --需要加和的c字段(指標(biāo))括起來加sum即可，有點(diǎn)像excel那種fromα.table --from后面輸入需要查詢的表名where date='yymmdd' --where后面輸入需要卡的條件，例如只看哪天的數(shù)據(jù)and (id=XXXor sp_id=XXX) --表示要看date='yymmdd' ，且id是xxx或sp_id是xxx的數(shù)據(jù)。如果沒有這個(gè)括號(hào)，表示的是要看date是yymmdd且id是xxx，或者不分日期，sp_id是xxx的數(shù)據(jù)group by a,b --沒有被處理的字段(維度)，需要在尾部group by 一下

• 為字段重新命名--as

select --select后面輸入需要查詢的字段a,b as b1,sum(c) as c1 --需要加和的c字段括起來加sum即可，有點(diǎn)像excel那種；這里用as把b重新命名成了b1，把c重新命名成了c1fromα.table --from后面輸入需要查詢的表名where date='yymmdd' --where后面輸入需要卡的條件，例如只看哪天的數(shù)據(jù)and (id=XXXor sp_id=XXX) --表示要看date='yymmdd' ，且id是xxx或sp_id是xxx的數(shù)據(jù)。如果沒有這個(gè)括號(hào)，表示的是要看date是yymmdd且id是xxx，或者不分日期，sp_id是xxx的數(shù)據(jù)group by a,b --沒有被處理的字段，需要在尾部group by 一下；被重新命名的維度字段，group by時(shí)仍用as前面的內(nèi)容

• 查數(shù)據(jù)條數(shù)，或查詢維度的數(shù)量--count和distinct

select --select后面輸入需要查詢的字段a,b,sum(c), --需要加和的c字段括起來加sum即可，有點(diǎn)像excel那種count(distinct d)--去重查詢?cè)赼,b枚舉下,d有幾個(gè),例如查ka,la(a)的客戶id(b)下,總共有幾個(gè)廣告主(d);不加distinct查詢的是所有的廣告主(d)總共出現(xiàn)了幾次fromα.table --from后面輸入需要查詢的表名where date='yymmdd' --where后面輸入需要卡的條件，例如只看哪天的數(shù)據(jù)and (id=XXXor sp_id=XXX) --表示要看date='yymmdd' ，且id是xxx或sp_id是xxx的數(shù)據(jù)。如果沒有這個(gè)括號(hào)，表示的是要看date是yymmdd且id是xxx，或者不分日期，sp_id是xxx的數(shù)據(jù)group by a,b --沒有被處理的字段，需要在尾部group by 一下

• 對(duì)一份數(shù)據(jù)做多次處理--嵌套結(jié)構(gòu)

• • 如下sql查詢了每個(gè)人在當(dāng)天的頁面訪問頻次。3到7行先查詢出每個(gè)用戶id的頁面訪問頻次，然后使用3到7行的結(jié)果，查詢每個(gè)訪問頻次下，有幾個(gè)id：

SELECT?cnt,count(id)FROM(select?id,count(*)?as?cnt??---'*'可以用來查詢行數(shù)from α.tablewhere date ='20190928' and label='show'group?by?id) a ---這里寫一個(gè)'a',用來給3到7行的結(jié)果命名,這樣外層的sql才能識(shí)別括號(hào)里面的內(nèi)容group by cnt

• 對(duì)多份數(shù)據(jù)做關(guān)聯(lián)處理--join

• • 最常用的場(chǎng)景：假設(shè)表A上有門店id是a，收入是b，表B上有門店id是a，門店名稱c，如果需要獲取門店id，收入，門店名稱的關(guān)系，可以這么寫：

SELECT a,b,cFROM(select a,sum (b) from A group by a)?cost????????????????---為3到6行的sql命名為costleft join ( ---這里使用左連接select a,cfrom B group?by?a,c?)text????---為8到10行的sql命名為texton cost.a=text.a ---這里需要寫清連接兩段sql的字段

• • 放下這張圖,形象的表達(dá)了各種join方法，獲取的數(shù)據(jù)范圍。想獲取對(duì)應(yīng)數(shù)據(jù)時(shí)，替換上邊第7行就可以用：?

(圖片來源于網(wǎng)絡(luò)，侵刪)

• 條件判斷，對(duì)同一個(gè)字段做區(qū)分計(jì)算--if 和case when

• • 判斷一次是否--if

select a,sum (b),if (label = 'show_over', duration, 0) --這一句的意思是，當(dāng)這一行數(shù)據(jù)的label是show_over的時(shí)候，取duration這個(gè)字段里的值，label不是show_over的時(shí)候，取0from A group by a

• • 判斷一次或多次是否--case when

• • • 多加判斷的方式見4和5兩行：

select a,sum (b),case when label?=?'pv'?then?durationwhenlabel='play'?then?mockdurationelse 0end,?--2到4行的意思是，當(dāng)這一行數(shù)據(jù)的label是pv的時(shí)候，取duration這個(gè)字段里的值，如果label是play的時(shí)候，取duration這個(gè)字段里的值，如果還沒有，就取0from A group by a

• 除法取整--floor

select?a,floor?(X/100)??---把X按100分檔，0檔表示X在[0,100)之間，1檔表示X在[100,200)之間，以此類推from A group by a

• 篩選字段為空/不為空的方法--null

select a,sum (b)from A where type is not null --找出type 不是null的情況，不加"not",就是找出type 是null的情況group by a

• 各種常見類型字段、指定值的查詢方法：

• • string：加單引號(hào)即可，例如一個(gè)字段type是string，就可以寫：

select a,sum (b)from A where?type='1'???---string加單引號(hào)group by a

• • bigint：后面加一個(gè)L，例如一個(gè)字段type是bigint，就可以寫：

select a,sum (b)from A where?type=1L --- bigint后加Lgroup by a

• • array：XXX代表數(shù)組內(nèi)的字段類型，需要根據(jù)此類型的方式取數(shù)，假設(shè)model字段的類型是array：

select a,sum (b)from A where array_contains(model,123L)group by a

• • json：json經(jīng)常會(huì)出現(xiàn)字段包字段的情況，例如常用的data是個(gè)json字段，里面會(huì)有a字段，a字段里面還會(huì)有b字段，如果想取出b，我們應(yīng)該這么寫：

select?get_json_object(data,'$.a.b')?from A

• 擴(kuò)展閱讀

• • 一些提升效率的方法

• • • 時(shí)間分區(qū)有多種存儲(chǔ)方式，查詢where條件的時(shí)候需要注意：有的表是‘yymmdd’，有的表是‘yy-mm-dd’；字段名也不固定，有的表是p_date，有的表是date，但是對(duì)于單個(gè)表，分區(qū)字段一般是固定的，例如你經(jīng)常查a.bcde這個(gè)表，上次他的時(shí)間分區(qū)格式是date=‘yymmdd’，下次查的時(shí)候它還會(huì)是date=‘yymmdd’；

• • • 有時(shí)表中的時(shí)間戳不是常見的yymmdd，而是一串?dāng)?shù)字，如果where條件里需要卡時(shí)間戳，卻不知道日期對(duì)應(yīng)的一串?dāng)?shù)字是什么，可使用時(shí)間戳轉(zhuǎn)換器轉(zhuǎn)換：https://tool.lu/timestamp/

• • • sql沒數(shù)、跑錯(cuò)怎么辦：有時(shí)解析功能沒有發(fā)現(xiàn)問題，但是數(shù)據(jù)直觀感覺不對(duì)，可以用如下方式自查：

檢查相關(guān)表的分區(qū)，和你取的分區(qū)一致不一致，例如日期有多種格式，例如yymmdd，yy-mm-dd，yy-mm-dd 00:00:00等等；

如果sql包含多個(gè)部分，比如有join，可以把其他部分的sql注釋掉，分別看每個(gè)部分的sql哪里有問題；(注釋方式：代碼前加“--” ?例如 ?--select ...)

• • • 需要研究單個(gè)json字段的邏輯：可以用這個(gè)網(wǎng)址整理json字段，方便閱讀：https://www.json.cn/

• • • 同時(shí)編輯多行，可以按住shift+alt/option，鼠標(biāo)點(diǎn)擊起始行和結(jié)束行，就能同時(shí)編輯多行了，例如批量

• • 常用概念的解釋

• • • 全量表和增量表

• • • • 增量表：每天存下來的數(shù)據(jù)，是當(dāng)天產(chǎn)生的所有數(shù)據(jù)，例如日記，每日走路的步數(shù)，銀行每天的收支信息等；

• • • • 全量表：每天存下來的數(shù)據(jù)，是從有表開始所有的數(shù)據(jù)，相當(dāng)于每天抄一份歷史上所有的日記，再寫今天的日記，例如銀行賬戶的余額；

• • • 不同數(shù)據(jù)庫的區(qū)別

• • • • mysql等實(shí)時(shí)查詢的數(shù)據(jù)庫：一般沒有分區(qū)概念，存儲(chǔ)的數(shù)據(jù)比較少，但是響應(yīng)快；

• • • • hive等離線查詢數(shù)據(jù)庫：有分區(qū)概念，可以較低成本的存儲(chǔ)海量數(shù)據(jù)，支持各種復(fù)雜處理，查詢速度一般比mysql慢。

總結(jié)

以上是生活随笔為你收集整理的sql获取某列出现频次最多的值_业务硬核SQL集锦的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。