數據分析之SQL：常用模型
以下介紹常用的SQL寫法：
case when的用法---不管偏不偏，你可能真沒見過這種寫法
內連接VS左連接---80%的業務代碼都與之相關
distinct的用法--你可能真的錯怪distinct了
order by的注意事項---order by一般放在主查詢後，子查詢無效！
group by---新手小白，總是group by時報錯！
having--having有時真的很牛逼
topN問題---分組取最大，分組取前幾
標準SQL與基於hive的SQL---最常見的區別
不得不知的聚合函數---資料庫中的聚合函數真的比excel快很多！
SQL優化--路漫漫其修遠兮……
做需求寫SQL需要注意的問題---一家之言
case when
1、不同的崗位調不同等級的薪水；
select last_name,job_id,salary,case job_id
when IT_PROG then 1.1*salary
when ST_CLERK then 1.15*salary
when SA_REP then 1.2*salary
else salary
end
「zhangngongzi」
from employees;
2、行列轉換之case when
select deptno,sum(clerk) as clerk ,sum(salesman) as salesman ,sum(manager) as manager,sum(analyst) as analyst ,sum(president) as president
from
(select deptno,case job when CLERK THEN SAL end as clerk, case job WHEN SALESMAN THEN SAL end as salesman, case job WHEN MANAGER THEN SAL end as manager,
case job WHEN ANALYST THEN SAL end as analyst,
case job WHEN PRESIDENT THEN SAL END as president FROM EMP ) group by deptno;
順便提一下SQL語句的執行順序：from--where---group by--having--select --distinct ---order by --limit
加上多表連接後，執行順序：先執行子查詢，再執行主查詢；先對兩個表執行笛卡爾乘積--join--where---group by--having--select --distinct ---order by --limit
帶Left Join的SQL語句的執行順序
最終實現的效果：
3、當你想得到多個指標的數據，又不想寫多條語句，那麼case when 可以幫到你：
create table qiuzhiliao as
select week_begin_date_id,u_user,
(case
when channel = oppokeke then oppokeke
when channel = huawei then huawei
when channel = xiaomi then xiaomi
when channel = yingyongbao then yingyongbao
when channel = yingyongbaozx then yingyongbaozx
when channel = AppStore then AppStore
when channel = baidu then baidu
--when channel = 360zhushou then channel =360zhushou
when channel = wandoujia then wandoujia
when channel like bdsem% then bdsem
when channel like sgsem% then sgsem
when channel like smsem% then smsem
else 360zhushou
end
) AS channel
from tmp.qzl_1
where (channel in (oppokeke,huawei,xiaomi,yingyongbao,yingyongbaozx,AppStore,baidu,360zhushou,wandoujia)
or channel like bdsem%
or channel like sgsem%
or channel like smsem%)
select week_begin_date_id,channel ,count (distinct u_user)
from qiuzhiliao
group by week_begin_date_id,channel
order by week_begin_date_id,channel
這樣可以將具有相同特徵信息的聚成一類，然後統計這類的數據；
case 欄位 when 條件 then =case when 欄位 =條件 then ；
4、case when完成多條件統計
select zc.stab,
sum(case when toppop>10000 then 1 else 0 end ) as num_10000,
--sum(case when toppop>10000 then 1 end ) as num_10000
sum(case when toppop >1000 then 1 else 0 end) as num_10000
from ZipCensus zc
group by zc.stab
toppop>10000的計數統計為num_10000,toppop>1000的計數統計為num_1000;---case when 完成多條件統計；
上面注釋行：當不滿足條件時返回null，第一個case when不滿足條件時，返回0；通常在計數時，推薦返回值返回數字而不是null！並且在聚合函數中使用case when，適用的函數sum（）或max（），avg（），很少情況使用count(distinct ).
內連接VS左連接
內連接：連接鍵如果匹配上，就連接，沒有匹配上，就丟掉！
左連接：以左表為準（左表中的所有數據都會出現），右表匹配主表，匹配上就寫入，沒匹配上就寫null。---因為null的存在，就有了業務上最終的漏斗模型！
以下表格完整解釋了左連接與內連接的區別！
舉個例子：
create table tmp.qiuzhiliao as
select t1.u_user,t1.date_sk
from (
select u_user,date_sk
from dw.order
where date_sk between 20190211 and 20190318
and status in (支付成功)
)t1
left join (
select u_user
from dw.order
where date_sk between 20190211 and 20190318
and status = 退款成功
)t2
on t1.u_user=t2.u_user
where t2.u_user is null
業務中最常用的模型：主表t1是支付成功的用戶（包括先支付成功，然後又退款的用戶），t2表是退款成功的用戶；通過左連接，並where限制，就可以求出實際付款成功的用戶（支付成功-退款成功）
我們什麼時候使用內連接了：因為表設計的原因，我們一張表不可能容納所有的數據項，從io性能角度出發，表維度越大，查詢時間越長；所以你想跨表引用欄位時，就可以使用內連接!
舉個例子：A表有用戶，視頻，支付類型；B表有視頻，視頻時長；
你現在想知道：用戶看了多久的視頻；你就得獲取A表的用戶，視頻名，B表的視頻時長。
你就得使用內連接。
表設計中：不是所有的表都有主鍵，但表之間要發生連接，就一定需要主鍵；那麼沒有外鍵約束的表，稱為父表；有外鍵約束的表稱為子表；並且子表的外鍵就連接在主表的主鍵上。
主鍵：非空，唯一約束；主鍵在一張表中，只有一個！但一個主鍵可以包括多個欄位（聯合主鍵）。
回到本題：A表中的視頻就是主鍵，B表中的視頻就是外鍵!
distinct的用法
1、distinct有去除重複值的效果，但查詢欄位>=2時，就是對這兩個欄位聯合去重（兩個欄位同時相同，才會被當作重複值）
2、distinct只能放在首列，否則報錯！
3、去除重複值最好用group by，distinct更多時候出現在count(distinct 欄位）中用於統計去除重複值的條數。
MySql中distinct的用法 - 苔苔以苔苔以苔 - 博客園
order by 的注意事項
1、order by放在語句的最後，同時執行順序也是最後。
2、order by放在子查詢中會失效，一般放在主查詢中最後執行！
又經過其它側面證明，order by放在主查詢中得到的u_user纔是我們想要的前30名用戶！
group by
1、group by+聚合鍵；
2、select 後只能出現聚合鍵，或是聚合函數；不能出現其它的欄位，否則報錯！
SQL中GROUP BY用法示例
having 有時真的很牛逼
1、having只能跟在group by後，不能單獨使用；
2、having是對group by分組後的數據進行篩選判斷。
SQL中GROUP BY用法示例
topN問題
1、分組取最大，最小，平均值；group by +聚合函數；但無法得到聚合鍵之外的數據，這時可以使用關聯子查詢。
2、求得每組前兩名數據；limit+union all
select 課程號,max(成績) as 最大成績
from score
group by 課程號;
分組取最大，但得不到聚合鍵之外的數據；
select *
from score as a
where 成績 = (
select max(成績)
from score as b
where b.課程號 = a.課程號);
使用關聯子查詢實現；
每組最大的N條記錄
1、先求出最大記錄所在的組
2、union連接
select 課程號,max(成績) as 最大成績
from score
group by 課程號;
(select * from score where 課程號 = 0001 order by 成績 desc limit 2)
union all
(select * from score where 課程號 = 0002 order by 成績 desc limit 2)
union all
(select * from score where 課程號 = 0003 order by 成績 desc limit 2);
標準SQL與基於hive的SQL
在標準SQL中，我們有以下語句：查詢所有課程成績小於60分學生的學號、姓名
select 學號,姓名
from student
where 學號 in (
select 學號
from student
where 成績 < 60
);
在Hive中，in後只能跟字元串；比如in (huawei,oppo)，不允許出現上述的語法結構！
現在通過內連接，對上述語言進行改寫：
select t1.學號,t1.姓名
from (
select 學號,姓名
from student
)t1
inner join (
select 學號
from student
where 成績 < 60
)t2
on t1.學號=t2.學號
不得不知的聚合函數
1,sum與case when的結合
2,count與case when的結合
使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱；
只為講清邏輯，想了解表格結構，具體思路，請點擊文末提供的參考文獻。
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100
then 1 else 0 end) as [100-85],
sum(case when 成績 >=70 and 成績<85
then 1 else 0 end) as [85-70],
sum(case when 成績>=60 and 成績<70
then 1 else 0 end) as [70-60],
sum(case when 成績<60 then 1 else 0 end) as [<60]
from score as a right join course as b
on a.課程號=b.課程號
group by a.課程號,b.課程名稱;
接下來給一個企業級別的應用（已經去除敏感信息）：基於Hive
--產品路徑統計可以這麼寫
--春節活動入口統計
SELECT day,
count(CASE when x2_1.event_key = initApp then x2_1.u_user ELSE NULL end) as ad_pv,--開屏曝光的pv
count(DISTINCT CASE when x2_1.event_key = initApp then x2_1.u_user ELSE NULL end) as ad_uv,--開屏曝光的uv
FROM
(
SELECT from_unixtime(unix_timestamp(cast(day as string),yyyyMMdd),yyyy-MM-dd) as day,
event_key,u_user,status,button,device
from table
WHERE from_unixtime(unix_timestamp(cast(day as string),yyyyMMdd),yyyy-MM-dd) BETWEEN 2019-03-28 and date_sub(from_unixtime(unix_timestamp(),yyyy-MM-dd),1) --int
AND
--開屏曝光
event_key = initApp
group by day
order by day
這種寫法在企業中很常見；統計驗證埋點數據是否正常，我們一般將case when 當作if else 使用。
SQL優化
1、Hive sql中多表join時，需要將小表寫在左邊，hive會將小表數據存放內存中，以實現MR中map join的效果；sparksql中，join時大小表的順序對執行效率幾乎沒有影響。sparksql-cache小表實現mapjoin優化性能-雲棲社區-阿里雲
2、count （*）會返回包含null的行數；count （欄位）返回不包括null的行數；
3、spark 支持非等值連接，並且查詢速度高於hive；一般先在spark上創建表格，然後再hive中查詢並導出查詢結果。
……
需要注意的事項
0、SQL語言很笨，有時你以為你要的A，實際上跑數是B；SQL語言又很清晰，只要你的邏輯清楚，就可以得出你想要的數據！
1、內連接無所謂表先後順序；漏斗模型，誰是最左邊的表至關重要！選誰漏斗誰要符合業務！
2、篩選哪個表欄位，就限制該表渠道，至於連接的其他表，欄位不必限制（防止過緊，實際上限制與不限制可能效果一樣）
3、左連接漏斗模型，記住右表有NULL，所以，不能group by 右表欄位，計數左表欄位！（內連接可以）
3、理清了業務邏輯，再去寫SQL開發，在用戶行為中想SQL邏輯，想清楚用漏斗還是內連接（二者有時可以替換），連接條件是連接用戶，還是設備，連接渠道，還是時間（按日），一個表中哪個欄位是定的，哪個欄位是變的，是否存在一對多關係（一個假mac對應多個用戶）
4、建表時，多用欄位，少where限制（where用在查詢時限制），這樣可以保證少建表。
推薦閱讀：
