常用的4個(gè)SQL技巧

點(diǎn)擊上方“數(shù)據(jù)管道”，選擇“置頂星標(biāo)”公眾號(hào)

干貨福利，第一時(shí)間送達(dá)

SQL是大數(shù)據(jù)從業(yè)者的必備技能，大部分的大數(shù)據(jù)技術(shù)框架也都提供了SQL的解決方案。可以說(shuō)SQL是一種經(jīng)久不衰、歷久彌新的編程語(yǔ)言。尤其是在數(shù)倉(cāng)領(lǐng)域，使用SQL更是家常便飯。本文會(huì)分享四個(gè)在面試和工作中常用的幾個(gè)使用技巧，具體包括：

• 日期與期間的使用
• 臨時(shí)表與Common Table Expression (WITH)
• Aggregation 與CASE WHEN的結(jié)合使用
• Window Function的其他用途

數(shù)倉(cāng)？不就是寫(xiě)寫(xiě)SQL嗎…

第一：日期與期間的使用

日期與時(shí)間段的篩選在工作中是經(jīng)常被用到的，因?yàn)樵诶?bào)表、儀表板和各種分析時(shí)，周、月、季度、年度的表現(xiàn)往往是分析需要考量的重點(diǎn)。

時(shí)間區(qū)段的提取：Extract

• 語(yǔ)法

--?field可以是day、hour、minute,?month,?quarter等等
--?source可以是date、timestamp類(lèi)型
extract(field?FROM?source)

• 使用

SELECT?extract(year?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?2020
SELECT?extract(quarter?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?3
SELECT?extract(month?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?8
SELECT?extract(week?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?31,一年中的第幾周
SELECT?extract(day?FROM?'2020-08-05?09:30:08');??--?結(jié)果為?5
SELECT?extract(hour?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?9
SELECT?extract(minute?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?30
SELECT?extract(second?FROM?'2020-08-05?09:30:08');???--?結(jié)果為?8

上面可供提取的字段，不同的數(shù)據(jù)庫(kù)存在些許的差異。以Hive為例，支持day, dayofweek, hour, minute, month, quarter, second, week 和 year。其中周、月、年使用最為廣泛，因?yàn)闊o(wú)論是公司內(nèi)部產(chǎn)品，還是商用的產(chǎn)品所提供的數(shù)據(jù)后臺(tái)統(tǒng)計(jì)，周報(bào)和月報(bào)(比如近7天、近30天)最注重表現(xiàn)的周期。

注意：

impala支持：YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH

Hive支持：day, dayofweek, hour, minute, month, quarter, second, week 和 year

Hive是從Hive2.2.0版本開(kāi)始引入該函數(shù)

周的提取

• 語(yǔ)法

在按照周的區(qū)間進(jìn)行統(tǒng)計(jì)時(shí)，需要識(shí)別出周一的日期與周日的日期，這個(gè)時(shí)候經(jīng)常會(huì)用到下面的函數(shù)：

next_day(STRING?start_date,?STRING?day_of_week)
--?返回當(dāng)前日期對(duì)應(yīng)的下一個(gè)周幾對(duì)應(yīng)的日期
--?2020-08-05為周三
SELECT?next_day('2020-08-05','MO')?--?下一個(gè)周一對(duì)應(yīng)的日期：2020-08-10
SELECT?next_day('2020-08-05','TU')?--?下一個(gè)周二對(duì)應(yīng)的日期：2020-08-11
SELECT?next_day('2020-08-05','WE')?--?下一個(gè)周三對(duì)應(yīng)的日期：2020-08-12
SELECT?next_day('2020-08-05','TH')?--?下一個(gè)周四對(duì)應(yīng)的日期：2020-08-06，即為本周四
SELECT?next_day('2020-08-05','FR')?--?下一個(gè)周五對(duì)應(yīng)的日期：2020-08-07，即為本周五
SELECT?next_day('2020-08-05','SA')?--?下一個(gè)周六對(duì)應(yīng)的日期：2020-08-08，即為本周六
SELECT?next_day('2020-08-05','SU')?--?下一個(gè)周日對(duì)應(yīng)的日期：2020-08-09，即為本周日
--?星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

• 使用

那么該如何獲取當(dāng)前日期所在周的周一對(duì)應(yīng)的日期呢？只需要先獲取當(dāng)前日期的下周一對(duì)應(yīng)的日期，然后減去7天，即可獲得：

SELECT?date_add(next_day('2020-08-05','MO'),-7);

同理，獲取當(dāng)前日期所在周的周日對(duì)應(yīng)的日期，只需要先獲取當(dāng)前日期的下周一對(duì)應(yīng)的日期，然后減去1天，即可獲得：

select?date_add(next_day('2020-08-05','MO'),-1)?
--?2020-08-09

月的提取

• 語(yǔ)法

至于怎么將月份從單一日期提取出來(lái)呢，LAST_DAY這個(gè)函數(shù)可以將每個(gè)月中的日期變成該月的最后一天(28號(hào)，29號(hào)，30號(hào)或31號(hào))，如下：

last_day(STRING?date)

• 使用

SELECT?last_day('2020-08-05');?--?2020-08-31

除了上面的方式，也可以使用date_format函數(shù)，比如：

SELECT?date_format('2020-08-05','yyyy-MM');
--?2020-08

日期的范圍

月的Window：使用add_months加上trunc()的應(yīng)用

--?返回加減月份之后對(duì)應(yīng)的日期
--?2020-07-05
select?add_months('2020-08-05',?-1)

--?返回當(dāng)前日期的月初日期
--?2020-08-01
select?trunc("2020-08-05",'MM')

由上面范例可見(jiàn)，單純使用add_months，減N個(gè)月的用法，可以剛好取到整數(shù)月的數(shù)據(jù)，但如果加上trunc()函數(shù)，則會(huì)從前N個(gè)月的一號(hào)開(kāi)始取值。

--?選取2020-07-05到2020-08-05所有數(shù)據(jù)
BETWEEN?add_months('2020-08-05',?-1)?AND?'2020-08-05'?
--?選取2020-07-01到2020-08-05之間所有數(shù)據(jù)
BETWEEN?add_months(trunc("2020-08-05",'MM'),-1)?AND?'2020-08-05'?

第二：臨時(shí)表與Common Table Expression (WITH)

這兩種方法是日常工作中經(jīng)常被使用到，對(duì)于一些比較復(fù)雜的計(jì)算任務(wù)，為了避免過(guò)多的JOIN，通常會(huì)先把一些需要提取的部分?jǐn)?shù)據(jù)使用臨時(shí)表或是CTE的形式在主要查詢(xún)區(qū)塊前進(jìn)行提取。

臨時(shí)表的作法：

CREATE?TEMPORARY?TABLE?table_1?AS??
????SELECT?
????????columns
????FROM?table?A;
CREATE?TEMPORARY?table_2?AS?
????SELECT
????????columns
????FROM?table?B;

SELECT
????table_1.columns,
????table_2.columns,?
????c.columns?
FROM?table?C?JOIN?table_1
?????JOIN?table_2;

CTE的作法：

--?注意Hive、Impala支持這種語(yǔ)法，低版本的MySQL不支持(高版本支持)
WITH?employee_by_title_count?AS?(
????SELECT
????????t.name?as?job_title
????????,?COUNT(e.id)?as?amount_of_employees
????FROM?employees?e
????????JOIN?job_titles?t?on?e.job_title_id?=?t.id
????GROUP?BY?1
),
salaries_by_title?AS?(
?????SELECT
?????????name?as?job_title
?????????,?salary
?????FROM?job_titles
)
SELECT?*
FROM?employee_by_title_count?e
????JOIN?salaries_by_title?s?ON?s.job_title?=?e.job_title

可以看到TEMP TABLE和CTE WITH的用法其實(shí)非常類(lèi)似，目的都是為了讓你的Query更加一目了然且優(yōu)雅簡(jiǎn)潔。很多人習(xí)慣將所有的Query寫(xiě)在單一的區(qū)塊里面，用過(guò)多的JOIN或SUBQUERY，導(dǎo)致最后邏輯丟失且自己也搞不清楚寫(xiě)到哪里，適時(shí)的使用TEMP TABLE和CTE作為輔助，絕對(duì)是很加分的。

第三：Aggregation 與CASE WHEN的結(jié)合使用

將Aggregation function (SUM/COUNT/COUNT DISTINCT/MIN/MAX) 結(jié)合CASE WHEN是最強(qiáng)大且最有趣的使用方式。這樣的使用創(chuàng)造出一種類(lèi)似EXCEL中SUMIF/COUNTIF的效果，可以用這個(gè)方式做出很多高效的分析。

• Table Name: order
• Column: register_date, order_date, user_id, country, order_sales, order_id

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?order(
????register_date?string,
????order_date?string,
????user_id?string,
????country?string,
????order_sales?decimal(10,2),
????order_id?string);

INSERT?INTO?TABLE?order?VALUES("2020-06-07","2020-06-09","001",'c0',210,"o1");
INSERT?INTO?TABLE?order?VALUES("2020-06-08","2020-06-09","002",'c1',220,"o2");
INSERT?INTO?TABLE?order?VALUES("2020-06-07","2020-06-10","003",'c2',230,"o3");
INSERT?INTO?TABLE?order?VALUES("2020-06-09","2020-06-10","004",'c3',200,"o4");
INSERT?INTO?TABLE?order?VALUES("2020-06-07","2020-06-20","005",'c4',300,"o5");
INSERT?INTO?TABLE?order?VALUES("2020-06-10","2020-06-23","006",'c5',400,"o6");
INSERT?INTO?TABLE?order?VALUES("2020-06-07","2020-06-19","007",'c6',600,"o7");
INSERT?INTO?TABLE?order?VALUES("2020-06-12","2020-06-18","008",'c7',700,"o8");
INSERT?INTO?TABLE?order?VALUES("2020-06-07","2020-06-09","009",'c8',100,"o9");
INSERT?INTO?TABLE?order?VALUES("2020-06-15","2020-06-18","0010",'c9',200,"o10");
INSERT?INTO?TABLE?order?VALUES("2020-06-15","2020-06-19","0011",'c10',250,"o11");
INSERT?INTO?TABLE?order?VALUES("2020-06-12","2020-06-29","0012",'c11',270,"o12");
INSERT?INTO?TABLE?order?VALUES("2020-06-16","2020-06-19","0013",'c12',230,"o13");
INSERT?INTO?TABLE?order?VALUES("2020-06-17","2020-06-20","0014",'c13',290,"o14");
INSERT?INTO?TABLE?order?VALUES("2020-06-20","2020-06-29","0015",'c14',203,"o15");

CASE WHEN 時(shí)間，進(jìn)行留存率/使用率的分析

--?允許多列去重
set?hive.groupby.skewindata?=?false
--?允許使用位置編號(hào)分組或排序
set?hive.groupby.orderby.position.alias?=?true

SELECT
????date_add(Next_day(register_date,?'MO'),-1)?AS?week_end,
????COUNT(DISTINCT?CASE?WHEN?order_date?BETWEEN?register_date?AND?date_add(register_date,6)?THEN?user_id?END)?AS?first_week_order,
????COUNT(DISTINCT?CASE?WHEN?order_date?BETWEEN?date_add(register_date?,7)?AND?date_add(register_date,13)?THEN?user_id?END)?AS?sencod_week_order,
????COUNT(DISTINCT?CASE?WHEN?order_date?BETWEEN?date_add(register_date?,14)?AND?date_add(register_date,20)?THEN?user_id?END)?as?third_week_order
FROM?order
GROUP?BY?1

上面的示例可以得知到用戶(hù)在注冊(cè)之后，有沒(méi)有創(chuàng)建訂單的行為。比如注冊(cè)后的第一周，第二周，第三周分別有多少下單用戶(hù)，這樣可以分析出用戶(hù)的使用情況和留存情況。

注意：上面的使用方式，需要配置兩個(gè)參數(shù)：

hive.groupby.skewindata = false：允許多列去重，否則報(bào)錯(cuò)：

SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in data

hive.groupby.orderby.position.alias = true：允許使用位置編號(hào)分組或排序,否則報(bào)錯(cuò)：

SemanticException [Error 10025]: line 79:13 Expression not in GROUP BY key ''MO''

CASE WHEN 時(shí)間，進(jìn)行每個(gè)用戶(hù)消費(fèi)金額的分析

SELECT
????user_id,
????SUM?(CASE?WHEN?order_date?BETWEEN?register_date?AND?date_add(register_date,6)?THEN?order_sales?END)?AS?first_week_amount,
????SUM?(CASE?WHEN?order_date?BETWEEN?date_add(register_date?,7)?AND?date_add(register_date,13)?THEN?order_sales?END)?AS?second_week_amount
????FROM?order
GROUP?BY?1

通過(guò)篩選出注冊(cè)與消費(fèi)的日期，并且進(jìn)行消費(fèi)金額統(tǒng)計(jì)，每個(gè)用戶(hù)在每段時(shí)間段(注冊(cè)后第一周、第二周…以此類(lèi)推)的消費(fèi)金額，可以觀察用戶(hù)是否有持續(xù)維持消費(fèi)習(xí)慣或是消費(fèi)金額變低等分析。

CASE WHEN數(shù)量，消費(fèi)金額超過(guò)某一定額的數(shù)量分析

SELECT
????user_id,
????COUNT(DISTINCT?CASE?WHEN?order_sales?>=?100?THEN?order_id?END)?AS?count_of_order_greateer_than_100
FROM?order
GROUP?BY?1

上面的示例就是類(lèi)似countif的用法，針對(duì)每個(gè)用戶(hù)，統(tǒng)計(jì)其訂單金額大于某個(gè)值的訂單數(shù)量，分析去篩選出高價(jià)值的顧客。

CASE WHEN數(shù)量，加上時(shí)間的用法

SELECT
????user_id,
????MIN(CASE?WHEN?order_sales?>?100?THEN?order_date?END)?AS?first_order_date_over1000,
????MAX(CASE?WHEN?order_sales?>?100?THEN?order_date?END)?AS?recent_order_date_over100
FROM?order
GROUP?BY?1

CASE WHEN加上MIN/MAX時(shí)間，可以得出該用戶(hù)在其整個(gè)使用過(guò)程中，首次購(gòu)買(mǎi)超過(guò)一定金額的訂單日期，以及最近一次購(gòu)買(mǎi)超過(guò)一定金額的訂單日期。

第四：Window Function的其他用途

Window Function既是工作中經(jīng)常使用的函數(shù)，也是面試時(shí)經(jīng)常被問(wèn)到的問(wèn)題。常見(jiàn)的使用場(chǎng)景是分組取topN。本文介紹的另外一個(gè)用法，使用開(kāi)窗函數(shù)進(jìn)行用戶(hù)訪問(wèn)session分析。

session是指在指定的時(shí)間段內(nèi)用戶(hù)在網(wǎng)站上發(fā)生的一系列互動(dòng)。例如，一次session可以包含多個(gè)網(wǎng)頁(yè)瀏覽、事件、社交互動(dòng)和電子商務(wù)交易。session就相當(dāng)于一個(gè)容器，其中包含了用戶(hù)在網(wǎng)站上執(zhí)行的操作。

session具有一個(gè)過(guò)期時(shí)間，比如30分鐘，即不活動(dòng)狀態(tài)超過(guò) 30 分鐘，該session就會(huì)過(guò)時(shí)。

假設(shè)張三訪問(wèn)了網(wǎng)站，從他到達(dá)網(wǎng)站的那一刻開(kāi)始，就開(kāi)始計(jì)時(shí)。如果過(guò)了 30 分鐘，而張三仍然沒(méi)有進(jìn)行任何形式的互動(dòng)，則視為本次session結(jié)束。但是，只要張三與某個(gè)元素進(jìn)行了互動(dòng)（例如發(fā)生了某個(gè)事件、社交互動(dòng)或打開(kāi)了新網(wǎng)頁(yè)），就會(huì)在該次互動(dòng)的時(shí)間基礎(chǔ)上再增加 30 分鐘，從而重置過(guò)期時(shí)間。

數(shù)據(jù)準(zhǔn)備

• Table Name: user_visit_action
• Columns: user_id, session_id , page_url, action_time

CREATE?TABLE?user_visit_action(?
????user_id?string,
????session_id?string,
????page_url?string,
????action_time?string);
????
INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss001","http://a.com","2020-08-06?13:34:11.478");
INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss001","http://b.com","2020-08-06?13:35:11.478");
INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss001","http://c.com","2020-08-06?13:36:11.478");

INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss002","http://a.com","2020-08-06?14:30:11.478");
INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss002","http://b.com","2020-08-06?14:31:11.478");
INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss002","http://e.com","2020-08-06?14:33:11.478");
INSERT?INTO?TABLE?user_visit_action?VALUES("001","ss002","http://f.com","2020-08-06?14:35:11.478");

INSERT?INTO?TABLE?user_visit_action?VALUES("002","ss003","http://u.com","2020-08-06?18:34:11.478");
INSERT?INTO?TABLE?user_visit_action?VALUES("002","ss003","http://k.com","2020-08-06?18:38:11.478");

用戶(hù)訪問(wèn)session分析

范例的資料表如上，有使用者、訪次和頁(yè)面的連結(jié)和時(shí)間。以下則使用partition by來(lái)表達(dá)每個(gè)使用者在不同訪次之間的瀏覽行為。

SELECT
????user_id,
????session_id,
????page_url,
????DENSE_RANK()?OVER?(PARTITION?BY?user_id,?session_id?ORDER?BY?action_time?ASC)?AS?page_order,
????MIN(action_time)?OVER?(PARTITION?BY?user_id,?session_id)?AS?session_start_time,
????MAX(action_time)?OVER?(PARTITION?BY?user_id,?session_id)?AS?session_finisht_time
FROM?user_visit_action

上面的查詢(xún)會(huì)返回針對(duì)每個(gè)用戶(hù)、每次的到訪，瀏覽頁(yè)面行為的先后次序，以及該session開(kāi)始與結(jié)束的時(shí)間，以此為基礎(chǔ)就可以將這個(gè)結(jié)果存入TEMP TABLE或是CTE ，進(jìn)行更進(jìn)一步的分析。

本文主要分享了四個(gè)在工作和面試中經(jīng)常遇到的SQL使用技巧。當(dāng)然，這些都與具體的分析業(yè)務(wù)息息相關(guān)。最后，不管你是SQL boy ?or ?SQL girl，只要是掌握一些技巧，相信都能夠Happy SQL querying ?。