對(duì)于數(shù)據(jù)分析師而言埃篓，把數(shù)取對(duì)是一項(xiàng)最基本的能力，因?yàn)樗械臄?shù)據(jù)加工根资、分析工作都依賴于這一步架专。在互聯(lián)網(wǎng)公司筆試中SQL的考察也是不可少的一道坎。

對(duì)于產(chǎn)品經(jīng)理和運(yùn)營(yíng)而言玄帕，具備基本的取數(shù)能力則能夠節(jié)省很多繁瑣的溝通部脚，同時(shí)對(duì)解讀數(shù)據(jù)看板、溝通數(shù)據(jù)需求裤纹、合理設(shè)計(jì)項(xiàng)目的KPI也大有裨益委刘。工作中偶爾會(huì)有運(yùn)營(yíng)同學(xué)問有沒有SQL的學(xué)習(xí)鏈接，其實(shí)SQL學(xué)起來并不難鹰椒，這個(gè)系列將用3天時(shí)間讓你上手SQL實(shí)操锡移。

全文目錄：

--【1】全量表和增量表
--【2】SQL的語(yǔ)法句式
------【2.1】select...from...where...
------【2.2】group by 和 having
------【2.3】case..when..then..else..end
--【3】SQL的連接方法
------【3.1】...left join...
------【3.2】...inner join...
------【3.3】...full join...
--【4】SQL內(nèi)置函數(shù)
------【4.1】聚合函數(shù)
------------【場(chǎng)景1】對(duì)賣家當(dāng)日訂單進(jìn)行聚合統(tǒng)計(jì)
------【4.2】窗口函數(shù)
------------【場(chǎng)景2】顯示每個(gè)訂單中的賣家當(dāng)日銷售情況
------------【場(chǎng)景3】查詢每個(gè)品類下總實(shí)收入排名前10的賣家
------------【場(chǎng)景4】查詢連續(xù)2天下單的人
------------【場(chǎng)景5】每個(gè)品類訂單數(shù)量月環(huán)比
------------【場(chǎng)景6】對(duì)各品類下的賣家按GMV分桶、顯示排名和累計(jì)百分比
------【4.3】日期函數(shù)
------【4.4】數(shù)學(xué)函數(shù)
------【4.5】字符串函數(shù)
------【4.6】其他函數(shù)
------------【場(chǎng)景7】從不同流量表合并當(dāng)日登錄的用戶ID
------------【場(chǎng)景8】多列合并到1列
------------【場(chǎng)景9】1行拆分成多行

備注：不同的數(shù)據(jù)庫(kù)環(huán)境漆际，函數(shù)及其使用方法不太一致淆珊，總體上大同小異。

【1】全量表和增量表

在企業(yè)的數(shù)據(jù)倉(cāng)庫(kù)中奸汇，存在著許多從生產(chǎn)表生成的抽表施符，這些抽表在物理上存放在不同的分區(qū)，分區(qū)就好像抽屜一樣擂找，我們?cè)谑褂肧QL取數(shù)時(shí)操刀，必須告訴系統(tǒng)，我們要從哪些“抽屜”中取數(shù)據(jù)婴洼。
一般最常見的是按照天為分區(qū)骨坑，每日刷新數(shù)據(jù)，分析師在抽表的基礎(chǔ)上根據(jù)業(yè)務(wù)需求進(jìn)行加工提煉出基于某個(gè)主題的加工表應(yīng)用于分析柬采。數(shù)倉(cāng)的抽表可分為兩種類型：全量表和增量表欢唾。

全量表是截至某個(gè)日期的全量狀態(tài)拍照，每個(gè)分區(qū)數(shù)據(jù)量級(jí)只增不減粉捻；
增量表記錄某個(gè)日期新增加的業(yè)務(wù)記錄礁遣，每個(gè)分區(qū)數(shù)據(jù)量級(jí)有增有減。
舉例而言肩刃，用戶表是全量表祟霍，訂單表是增量表杏头。
如果以天為分區(qū)，用戶表中沸呐，最新的分區(qū)記錄著到目前為止全部的注冊(cè)用戶信息醇王，而訂單表最新的分區(qū)僅記錄當(dāng)日的訂單。

在我們進(jìn)行查詢之前崭添，理清楚所用到的表是全量表還是增量表十分重要寓娩，全量表只需要取最新日期的分區(qū)即可。如果把全量表誤認(rèn)為是增量表呼渣，一次取過去30天的數(shù)據(jù)棘伴，數(shù)據(jù)會(huì)大量重復(fù)，加之全量表本身數(shù)據(jù)量十分龐大屁置，如果再跟其他表做關(guān)聯(lián)查詢焊夸，則極大可能把系統(tǒng)資源跑崩掉±督牵——這也是新人常見易犯的錯(cuò)誤淳地。
備注：本文中的案例使用MySQL，沒有涉及分區(qū)的概念

從上篇：SQL心法：MySQL安裝使用中帅容，我們已經(jīng)在MySQLWorkbench中導(dǎo)入數(shù)據(jù)生成了以下表，dt字段代表業(yè)務(wù)記錄生成的日期（格式為："yyyy-mm-ss"）：

一個(gè)用戶全量表usertb伍伤，字段：
dt,user_id
一個(gè)訂單增量表ordertb并徘，字段：
dt,user_id,order_id,seller_id,cat_id,payment
一個(gè)賣家全量表sellertb，字段:
dt,seller_id,cat_id
一個(gè)品類全量表categorytb扰魂，字段:
dt,category_id

【2】SQL的語(yǔ)法句式

SQL是不分大小寫的麦乞，select和SELECT都是一樣可用，看個(gè)人習(xí)慣劝评。SQL語(yǔ)法其實(shí)非常直白易懂姐直，這里不說建表和寫入表操作，只單純講解數(shù)據(jù)查詢技巧蒋畜。查詢的語(yǔ)法結(jié)構(gòu)如下：

select 字段1[声畏，字段2...字段N]
from tb
where 條件1
group by 字段a
order by 字段b
having 條件2

【2.1】select...from...where...

這是最簡(jiǎn)單的查詢語(yǔ)句了，表示從表中查找符合條件的記錄行姻成。
比如我們要查表usertb中2015年2月注冊(cè)的所有用戶：

select * 
from usertb
where dt like '2015-02%'

備注：%代表任意長(zhǎng)度的字符串插龄，點(diǎn)擊閃電?圖標(biāo)即可運(yùn)行代碼

【2.2】group by 和 having

我們常對(duì)周期內(nèi)的業(yè)務(wù)數(shù)據(jù)進(jìn)行匯總，這時(shí)需要用到分組科展，比如：統(tǒng)計(jì)每周訂單數(shù)均牢，找出其中訂單數(shù)在1000+的周：

select weekofyear(dt), count(distinct order_id) as order_cnt
from ordertb
group by weekofyear(dt)
having order_cnt>1000

【2.3】case..when..then..else..end

當(dāng)我們需要根據(jù)條件自定義一個(gè)字段時(shí)，可以用case when 語(yǔ)句才睹。
比如徘跪，取2012年高中低消費(fèi)等級(jí)的訂單數(shù)甘邀。
這里高中低消費(fèi)等級(jí)是我們新定義的：
100以上為高消費(fèi)，30-100為中消費(fèi)垮庐，30以下為低消費(fèi)松邪。
那么，按照新定義的消費(fèi)等級(jí)字段進(jìn)行分組突硝，即可得到結(jié)果：

select 
    case when payment>100 then '高消費(fèi)' 
        when payment>30 then '中消費(fèi)'
        else '低消費(fèi)'
    end as pay_level, 
    count(distinct order_id) as order_cnt
from ordertb
where dt between '2012-01-01' and '2012-12-31'
group by 
case when payment>100 then '高消費(fèi)' 
    when payment>30 then '中消費(fèi)'
    else '低消費(fèi)'
end

【3】SQL的連接方法

網(wǎng)絡(luò)上各種連接方法都能搜索到挟纱，但我個(gè)人覺得少即是多，熟練運(yùn)用以下三種就足夠了：

【3.1】...left join...

select * from A left join B on A.uid=B.uid
表A中所有記錄行都在結(jié)果中腐宋，而表B只有uid跟A表匹配的記錄行才出現(xiàn)紊服，不能與A.uid匹配的記錄行為空。
比如胸竞，查詢2015-02-05日注冊(cè)用戶當(dāng)日在品類id:28下單的記錄欺嗤，結(jié)果中，左邊表的數(shù)據(jù)會(huì)完整保留卫枝，而當(dāng)日沒有下單的用戶右邊表的取值為null：

select 
t1.*,
t2.*
from usertb t1
left join ordertb t2
    on t1.user_id=t2.user_id
    and t2.dt ='2015-02-05'
    and t2.cat_id=28
where t1.dt ='2015-02-05'

【3.2】...inner join...

上面同一個(gè)例子，如果把left join換成inner join校赤，則結(jié)果僅會(huì)保留兩個(gè)表中都共同存在的用戶的記錄：

select 
t1.*,
t2.*
from usertb t1
inner join ordertb t2
    on t1.user_id=t2.user_id
    and t2.dt ='2015-02-05'
    and t2.cat_id=28
where t1.dt ='2015-02-05'

【3.3】...full join...

select * from A full join B on A.uid=B.uid
表A和表B在uid匹配的記錄行合并數(shù)據(jù)吆玖，不能匹配的記錄行左右各自保留數(shù)據(jù)，另一方的記錄為空马篮，所以結(jié)果里面包含了兩個(gè)表所有的數(shù)據(jù)沾乘。如果加一條where A.uid is null or B.uid is null，那么就把兩個(gè)表uid匹配的行刪掉浑测，剩下的是僅其中一方有的翅阵。

【4】SQL內(nèi)置函數(shù)

SQL提供了很多方便易用的內(nèi)置函數(shù)，來處理數(shù)據(jù)格式和進(jìn)行數(shù)據(jù)加工計(jì)算迁央，接下來我們將結(jié)合使用場(chǎng)景來研究SQL的基本和進(jìn)階用法怎顾。五角星??的個(gè)數(shù)代表難度級(jí)別指數(shù)。

聚合函數(shù)

聚合函數(shù)比較簡(jiǎn)單漱贱，因此不用詳細(xì)介紹槐雾，

avg ??
max ??
min ??
count ??
sum ??

【場(chǎng)景1】對(duì)賣家當(dāng)日訂單進(jìn)行聚合統(tǒng)計(jì)
查詢2012-09-10日訂單的均實(shí)付客單價(jià)、最大/最小實(shí)付客單價(jià)幅狮、總下單人數(shù)募强、總實(shí)收入：

select seller_id
    avg(payment),
    max(payment),
    min(payment),
    count(distinct user_id),
    sum(payment)
from ordertb
where dt='2012-04-10'
group by seller_id

窗口函數(shù)

avg ????
count ????
max ????
min ????
sum ????

【場(chǎng)景2】顯示每個(gè)訂單中的賣家當(dāng)日銷售情況
2012-09-10日每個(gè)訂單的實(shí)付客單株灸，以及對(duì)應(yīng)的賣家的均實(shí)付客單價(jià)，最大/最小實(shí)付客單價(jià)擎值、總下單人次慌烧、總實(shí)收入（比較一下這種寫法跟用group by哪里不同？）：

select
    order_id,
    payment,
    seller_id,
    avg(payment) over(partition by seller_id),
    max(payment) over(partition by seller_id),
    min(payment) over(partition by seller_id),
    count(distinct user_id) over(partition by seller_id),
    sum(payment) over(partition by seller_id)
from ordertb
where dt='2012-09-10'

row_number ??????
用途：分組在組內(nèi)對(duì)記錄逐行進(jìn)行排序
備注：窗口函數(shù)不能出現(xiàn)在同一個(gè)Select查詢的where子句下鸠儿，這時(shí)候使用qualify條件語(yǔ)句篩選屹蚊，而不是having。某些數(shù)據(jù)庫(kù)不支持qualify用法进每，則需要增加一層嵌套進(jìn)行篩選汹粤，我們這里使用嵌套方法。

【場(chǎng)景3】查詢每個(gè)品類下總實(shí)收入排名前10的賣家

use mydata;
select
*
from(
    select 
    cat_id,
    seller_id,
    row_number() over(partition by cat_id order by gmv desc) as rk
    from
        (
        select
        cat_id,
        seller_id,
        sum(payment) as gmv
        from ordertb 
        where dt between '2013-04-01' and '2013-04-10'
        group by cat_id,
        seller_id
        ) a 
    ) b
where rk<=10

上面代碼田晚，用row_number的話嘱兼，如果第10個(gè)跟第11個(gè)相等的gmv，那么排在第11位的seller_id則不會(huì)出現(xiàn)于結(jié)果中贤徒。

dense_rank ??????
用途：分組在組內(nèi)對(duì)記錄進(jìn)行排序芹壕，用dense_rank的話，如果第10個(gè)跟第11個(gè)相等的gmv接奈，那么第10踢涌、11個(gè)都是同樣的排名，都出現(xiàn)于結(jié)果中

rank ??????
用途：分組在組內(nèi)對(duì)記錄進(jìn)行排序序宦，用rank的話睁壁，如果第10個(gè)跟第11個(gè)相等的gmv，那么第10挨厚、11個(gè)都是同樣的排名，都出現(xiàn)于結(jié)果中糠惫，并且第11名置空疫剃，后續(xù)記錄從第12名開始

row_number、dense_rank硼讽、rank這三個(gè)窗口函數(shù)在使用時(shí)要結(jié)合業(yè)務(wù)使用場(chǎng)景決定需要用那個(gè)巢价，避免搞混淆。

【場(chǎng)景4】查詢連續(xù)2天下單的人
備注：關(guān)鍵在于怎么識(shí)別連續(xù)——只要把訂單日減按用戶ID分組的訂單排序拙已，如果一個(gè)用戶連續(xù)下單的話吼过，相減得到的日期相等

select * 
from(
    select  user_id,diff,
        count(1) as days
    from (
        select 
            user_id, dt,
            row_number() over(partition by user_id order by dt asc) as rk,
            date_add(dt, interval -(row_number() over(partition by user_id order by dt asc)) day) as diff
        from (select
                user_id, dt
                from ordertb
                where dt between '2012-04-01' and '2013-04-10'
                group by user_id, dt
            )basedata
    )orderdt_minus_rownum
    group by user_id,
    diff
) conti_days
where days=2

lag & lead ????????
用途：分組在組內(nèi)找往過去/未來數(shù)第N個(gè)數(shù)據(jù)

【場(chǎng)景5】每個(gè)品類訂單數(shù)量月環(huán)比

select
    mon,
    cat_id,
    ordernum,
    lag(ordernum,1) over(partition by cat_id order by mon) as ordernum_t7,
    ordernum/lag(ordernum,1) over(partition by cat_id order by mon)-1 as rate_t7
from
(select 
    month(dt) as mon,
    cat_id,
    count(order_id) as ordernum
from ordertb
where dt between '2012-04-01' and '2013-04-10'
group by
    month(dt),
    cat_id) t

ntile ????????
用途：分組在組內(nèi)對(duì)某列進(jìn)行分桶
percent_rank ????????
用途：分組在組內(nèi)對(duì)某列找某條記錄所處的相對(duì)排名
cume_dist ????????
用途：分組在組內(nèi)對(duì)每列找某條記錄所處的累計(jì)百分比

【場(chǎng)景6】對(duì)各品類下的賣家按GMV分桶砸紊、顯示排名和累計(jì)百分比

select 
    cat_id,
    seller_id,
    gmv,
    ntile(4) over(partition by cat_id order by gmv desc) as buck,
    percent_rank() over(partition by cat_id order by gmv desc) as prank,
    cume_dist() over(partition by cat_id order by gmv desc) as cdist
from
    (select
    cat_id,
    seller_id,
    sum(payment) as gmv
    from ordertb
    where dt between '2012-04-01' and '2013-04-10'
    group by cat_id,
    seller_id
) t

日期函數(shù)

date_add ????
用途：日期+時(shí)間間隔

date_add('2020-01-01',interval 2 day) # date往后推2天
date_add('2020-01-01',interval -2 day) # date往前推2天

datediff????
用途：第一個(gè)日期減去第二個(gè)日期得到的天數(shù)，可正可負(fù)

datediff('2020-01-01','2008-11-29')

date_format????
用途：將時(shí)間轉(zhuǎn)化成指定格式

datepart('2018-06-08 01:10:00', 'yyyy') 
輸出：2018-06-08

weekday ????
用途：返回日期所在當(dāng)前周的第幾天碉克。周一作為一周的第一天，返回值為0,周日返回6并齐。輸入必須是日期類型漏麦。

select weekday('2020-01-01')
輸出：2

weekofyear ????
用途：返回日期所在周為當(dāng)年的第幾周

weekofyear(to_date('20190421','yyyymmdd'))
weekofyear('2020-01-01')
輸出：1

數(shù)學(xué)運(yùn)算函數(shù)

round ??
用途：四舍五入
floor ??
用途：向下取整

round(118.815,2),
floor(118.815)
輸出：
118.82  
118 

字符串處理函數(shù)

concat & concat_ws????
用途：合并字符串客税，前者不接受空值，后者接受

select CONCAT('11','22','33')
>112233
select CONCAT('11','22',NULL)
>NULL
select CONCAT_WS(',','11','22','33')
>11,22,33
select CONCAT_WS(',','11','22',NULL)
>11,22

json_extract????
用途：從json格式中摘取變量值
備注：hive中用get_json_object()

場(chǎng)景：從{"orderId":"4","PayInfo":"[{"pay_id":1,"money":1650}]"}中獲取orderId和money的值撕贞。

select 
json_extract('{"orderId":4,"PayInfo":{"pay_id":1,"money":1650}}','$.orderId'),
json_extract('{"orderId":4,"PayInfo":{"pay_id":1,"money":1650}}','$.PayInfo.money')
輸出：
4
1650

md5 ??
用途：計(jì)算輸入字符串value的md5值更耻，在需要對(duì)手機(jī)號(hào)碼加密時(shí)用到

select md5('13X13697810')
輸出：faf3cae4ba50be8b85ae80203f6dba96

substr ??
用途：按照指定開始位置和長(zhǎng)度截取字符串，字符的位置從1開始數(shù)

select substr('2019-04-10 10:10:10 ',1,10)
輸出：2019-04-10

其他函數(shù)

coalesce????
用途：在幾個(gè)變量中取第一個(gè)非空的值

select coalesce(null,123,'abc')
輸出：123

場(chǎng)景：有2個(gè)流量表捏膨，分別是一個(gè)產(chǎn)品在APP秧均、第三方H5頁(yè)面的用戶訪問情況，要求合并流量表得到當(dāng)天登錄的用戶ID（我們可以使用union方法号涯，除此之外目胡，還可以使用outer join）

select
coalesce(t1.dt,t2.dt) as dt,
coalesce(t1.user_id,t2.user_id) as user_id
from apppv_tb t1
full join h5pv_tb t2
on t1.user_id=t2.user_id
and t1.dt=t2.dt

greatest????
least ????
用途：若干變量中的最大最小值

select 
greatest(1,4,3,9), 
least(1,4,3,9)
輸出：
9
1

trans_cols??????
用途：根據(jù)關(guān)鍵字將一行的拆成多行

場(chǎng)景：某個(gè)表中mobile1和 mobile2字段分別記錄用戶的主用手機(jī)號(hào)碼和備用手機(jī)號(hào)碼，要求將mobile1和 mobile2放入一個(gè)字段诚隙，且分行顯示
輸入：

輸入：
user_id      mobile1         mobile2
小明          139XXXX0101     138XXXX0111
小王          135XXXX0101     135XXXX0111

select trans_cols(1, user_id, mobile1, mobile2) as (user_id, mobile)

輸出：
user_id   mobile1
小明      139XXXX0101
小明      138XXXX0111
小王      135XXXX0101
小王      135XXXX0111

trans_array??????
用途：根據(jù)關(guān)鍵字將一行的拆成多行讶隐，跟trans_cols不一樣的地方是，需要拆成多行的字段只有一個(gè)久又，值之間用分隔符分開巫延。

場(chǎng)景：某個(gè)表中mobile字段記錄用戶的使用過的手機(jī)號(hào)碼，要求每個(gè)手機(jī)號(hào)碼分行顯示

輸入：
user_id   mobile                   recent_calltime
小明      139XXXX0101;138XXXX0111   20190120;20190220
小王      135XXXX0101;135XXXX0111   20180120;20190323

select trans_array(1, ';',user_id, mobile, recent_calltime) as (user_id, mobile, recent_calltime)

輸出：
user_id   mobile         recent_calltime
小明      139XXXX0101     20190120
小明      138XXXX0111     20190220
小王      135XXXX0101     20180120
小王      135XXXX0111     20190323