Hive中的窗口函數(shù)

在SQL中有一類(lèi)函數(shù)叫做聚合函數(shù)，例如sum()、avg()、max()等等，這類(lèi)函數(shù)可以將多行數(shù)據(jù)按照規(guī)則聚集為一行，一般來(lái)講聚集后的行數(shù)是要少于聚集前的行數(shù)的。但是有時(shí)我們想要既顯示聚集前的數(shù)據(jù)，又要顯示聚集后的數(shù)據(jù)，這時(shí)我們便引入了窗口函數(shù)。窗口函數(shù)又叫OLAP函數(shù)/分析函數(shù)，窗口函數(shù)兼具分組和排序功能。

本文分為兩部分：
第一部分是Hive窗口函數(shù)詳解，剖析各種窗口函數(shù)（幾乎涵蓋Hive所有的窗口函數(shù)）；
第二部分是窗口函數(shù)實(shí)際應(yīng)用，這部分總共有五個(gè)例子，都是工作常用、面試必問(wèn)的非常經(jīng)典的例子。

Hive 窗口函數(shù)

窗口函數(shù)最重要的關(guān)鍵字是 partition by 和 order by

具體語(yǔ)法如下：XXX over (partition by xxx order by xxx)

特別注意：over()里面的 partition by 和 order by 都不是必選的，over()里面可以只有partition by，也可以只有order by，也可以?xún)蓚€(gè)都沒(méi)有，大家需根據(jù)需求靈活運(yùn)用。

窗口函數(shù)我劃分了幾個(gè)大類(lèi)，我們一類(lèi)一類(lèi)的講解。

1. SUM、AVG、MIN、MAX

講解這幾個(gè)窗口函數(shù)前，先創(chuàng)建一個(gè)表，以實(shí)際例子講解大家更容易理解。

首先創(chuàng)建用戶(hù)訪(fǎng)問(wèn)頁(yè)面表：user_pv

create?table?user_pv(
cookieid?string,??--?用戶(hù)登錄的cookie，即用戶(hù)標(biāo)識(shí)
createtime?string,?--?日期
pv?int?--?頁(yè)面訪(fǎng)問(wèn)量
);?

給上面這個(gè)表加上如下數(shù)據(jù)：

cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4

• SUM()使用

執(zhí)行如下查詢(xún)語(yǔ)句：

select?cookieid,createtime,pv,
sum(pv)?over(partition?by?cookieid?order?by?createtime)?as?pv1?
from?user_pv;

結(jié)果如下：（因命令行原因，下圖字段名和值是錯(cuò)位的，請(qǐng)注意辨別！）

執(zhí)行如下查詢(xún)語(yǔ)句：

select?cookieid,createtime,pv,
sum(pv)?over(partition?by?cookieid?)?as?pv1?
from?user_pv;

結(jié)果如下：

第一條SQL的over()里面加 order by ，第二條SQL沒(méi)加order by ，結(jié)果差別很大

所以要注意了：

• over()里面加 order by 表示：分組內(nèi)從起點(diǎn)到當(dāng)前行的pv累積，如，11號(hào)的pv1=10號(hào)的pv+11號(hào)的pv, 12號(hào)=10號(hào)+11號(hào)+12號(hào)；

• over()里面不加 order by 表示：將分組內(nèi)所有值累加。

AVG，MIN，MAX，和SUM用法一樣，這里就不展開(kāi)講了，但是要注意 AVG，MIN，MAX 的over()里面加不加 order by 也和SUM一樣，如 AVG 求平均值，如果加上 order by，表示分組內(nèi)從起點(diǎn)到當(dāng)前行的平局值，不是全部的平局值。MIN，MAX 同理。

2. ROW_NUMBER、RANK、DENSE_RANK、NTILE

還是用上述的用戶(hù)登錄日志表：user_pv，里面的數(shù)據(jù)換成如下所示：

cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
cookie2,2021-05-10,2
cookie2,2021-05-11,3
cookie2,2021-05-12,5
cookie2,2021-05-13,6
cookie2,2021-05-14,3
cookie2,2021-05-15,9
cookie2,2021-05-16,7

• ROW_NUMBER()使用：

ROW_NUMBER()從1開(kāi)始，按照順序，生成分組內(nèi)記錄的序列。

SELECT?
cookieid,
createtime,
pv,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?desc)?AS?rn?
FROM?user_pv;

結(jié)果如下：

• RANK 和 DENSE_RANK 使用：

RANK() 生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中留下空位。

DENSE_RANK()生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中不會(huì)留下空位。

SELECT?
cookieid,
createtime,
pv,
RANK()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?desc)?AS?rn1,
DENSE_RANK()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?desc)?AS?rn2,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?pv?DESC)?AS?rn3?
FROM?user_pv?
WHERE?cookieid?=?'cookie1';

結(jié)果如下：

• NTILE的使用：

有時(shí)會(huì)有這樣的需求:如果數(shù)據(jù)排序后分為三部分，業(yè)務(wù)人員只關(guān)心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來(lái)呢?NTILE函數(shù)即可以滿(mǎn)足。

ntile可以看成是：把有序的數(shù)據(jù)集合平均分配到指定的數(shù)量（num）個(gè)桶中, 將桶號(hào)分配給每一行。如果不能平均分配，則優(yōu)先分配較小編號(hào)的桶，并且各個(gè)桶中能放的行數(shù)最多相差1。

然后可以根據(jù)桶號(hào)，選取前或后 n分之幾的數(shù)據(jù)。數(shù)據(jù)會(huì)完整展示出來(lái)，只是給相應(yīng)的數(shù)據(jù)打標(biāo)簽；具體要取幾分之幾的數(shù)據(jù)，需要再嵌套一層根據(jù)標(biāo)簽取出。

SELECT?
cookieid,
createtime,
pv,
NTILE(2)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn1,
NTILE(3)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn2,
NTILE(4)?OVER(ORDER?BY?createtime)?AS?rn3
FROM?user_pv?
ORDER?BY?cookieid,createtime;

結(jié)果如下：

3. LAG、LEAD、FIRST_VALUE、LAST_VALUE

講解這幾個(gè)窗口函數(shù)時(shí)還是以實(shí)例講解，首先創(chuàng)建用戶(hù)訪(fǎng)問(wèn)頁(yè)面表：user_url

CREATE?TABLE?user_url?(
cookieid?string,
createtime?string,??--頁(yè)面訪(fǎng)問(wèn)時(shí)間
url?string???????--被訪(fǎng)問(wèn)頁(yè)面
);

表中加入如下數(shù)據(jù)：

cookie1,2021-06-10?10:00:02,url2
cookie1,2021-06-10?10:00:00,url1
cookie1,2021-06-10?10:03:04,1url3
cookie1,2021-06-10?10:50:05,url6
cookie1,2021-06-10?11:00:00,url7
cookie1,2021-06-10?10:10:00,url4
cookie1,2021-06-10?10:50:01,url5
cookie2,2021-06-10?10:00:02,url22
cookie2,2021-06-10?10:00:00,url11
cookie2,2021-06-10?10:03:04,1url33
cookie2,2021-06-10?10:50:05,url66
cookie2,2021-06-10?11:00:00,url77
cookie2,2021-06-10?10:10:00,url44
cookie2,2021-06-10?10:50:01,url55

• LAG的使用：

LAG(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往上第n行值。

第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往上第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往上第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL）

SELECT?cookieid,
createtime,
url,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn,
LAG(createtime,1,'1970-01-01?00:00:00')?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last_1_time,
LAG(createtime,2)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last_2_time?
FROM?user_url;

結(jié)果如下：

解釋?zhuān)?/p>

last_1_time:?指定了往上第1行的值，default為'1970-01-01?00:00:00'??
?????????????????cookie1第一行，往上1行為NULL,因此取默認(rèn)值?1970-01-01?00:00:00
?????????????????cookie1第三行，往上1行值為第二行值，2021-06-10?10:00:02
?????????????????cookie1第六行，往上1行值為第五行值，2021-06-10?10:50:01
last_2_time:?指定了往上第2行的值，為指定默認(rèn)值
???????cookie1第一行，往上2行為NULL
???????cookie1第二行，往上2行為NULL
???????cookie1第四行，往上2行為第二行值，2021-06-10?10:00:02
???????cookie1第七行，往上2行為第五行值，2021-06-10?10:50:01

• LEAD的使用：

與LAG相反

LEAD(col,n,DEFAULT) 用于統(tǒng)計(jì)窗口內(nèi)往下第n行值。

第一個(gè)參數(shù)為列名，第二個(gè)參數(shù)為往下第n行（可選，默認(rèn)為1），第三個(gè)參數(shù)為默認(rèn)值（當(dāng)往下第n行為NULL時(shí)候，取默認(rèn)值，如不指定，則為NULL）

SELECT?cookieid,
createtime,
url,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn,
LEAD(createtime,1,'1970-01-01?00:00:00')?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?next_1_time,
LEAD(createtime,2)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?next_2_time?
FROM?user_url;

結(jié)果如下：

• FIRST_VALUE的使用：

取分組內(nèi)排序后，截止到當(dāng)前行，第一個(gè)值。

SELECT?cookieid,
createtime,
url,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn,
FIRST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?first1?
FROM?user_url;

結(jié)果如下：

• LAST_VALUE的使用：

取分組內(nèi)排序后，截止到當(dāng)前行，最后一個(gè)值。

SELECT?cookieid,
createtime,
url,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn,
LAST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last1?
FROM?user_url;

結(jié)果如下：

如果想要取分組內(nèi)排序后最后一個(gè)值，則需要變通一下：

SELECT?cookieid,
createtime,
url,
ROW_NUMBER()?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?rn,
LAST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime)?AS?last1,
FIRST_VALUE(url)?OVER(PARTITION?BY?cookieid?ORDER?BY?createtime?DESC)?AS?last2?
FROM?user_url?
ORDER?BY?cookieid,createtime;

注意上述SQL，使用的是 FIRST_VALUE 的倒序取出分組內(nèi)排序最后一個(gè)值！

結(jié)果如下：

此處要特別注意order ?by

如果不指定ORDER BY，則進(jìn)行排序混亂，會(huì)出現(xiàn)錯(cuò)誤的結(jié)果

SELECT?cookieid,
createtime,
url,
FIRST_VALUE(url)?OVER(PARTITION?BY?cookieid)?AS?first2??
FROM?user_url;

結(jié)果如下：

上述 url2 和 url55 的createtime即不屬于最靠前的時(shí)間也不屬于最靠后的時(shí)間，所以結(jié)果是混亂的。

4. CUME_DIST

先創(chuàng)建一張員工薪水表：staff_salary

CREATE?EXTERNAL?TABLE?staff_salary?(
dept?string,
userid?string,
sal?int
);

表中加入如下數(shù)據(jù)：

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

• CUME_DIST的使用：

此函數(shù)的結(jié)果和order by的排序順序有關(guān)系。

CUME_DIST：小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)。 ?order默認(rèn)順序 ：正序

比如，統(tǒng)計(jì)小于等于當(dāng)前薪水的人數(shù)，所占總?cè)藬?shù)的比例。

SELECT?
dept,
userid,
sal,
CUME_DIST()?OVER(ORDER?BY?sal)?AS?rn1,
CUME_DIST()?OVER(PARTITION?BY?dept?ORDER?BY?sal)?AS?rn2?
FROM?staff_salary;

結(jié)果如下：

解釋?zhuān)?/p>

rn1:?沒(méi)有partition,所有數(shù)據(jù)均為1組，總行數(shù)為5，
?????第一行：小于等于1000的行數(shù)為1，因此，1/5=0.2
?????第三行：小于等于3000的行數(shù)為3，因此，3/5=0.6
rn2:?按照部門(mén)分組，dpet=d1的行數(shù)為3,
?????第二行：小于等于2000的行數(shù)為2，因此，2/3=0.6666666666666666

5. GROUPING SETS、GROUPING__ID、CUBE、ROLLUP

這幾個(gè)分析函數(shù)通常用于OLAP中，不能累加，而且需要根據(jù)不同維度上鉆和下鉆的指標(biāo)統(tǒng)計(jì)，比如，分小時(shí)、天、月的UV數(shù)。

還是先創(chuàng)建一個(gè)用戶(hù)訪(fǎng)問(wèn)表：user_date

CREATE?TABLE?user_date?(
month?STRING,
day?STRING,?
cookieid?STRING?
);

表中加入如下數(shù)據(jù)：

2021-03,2021-03-10,cookie1
2021-03,2021-03-10,cookie5
2021-03,2021-03-12,cookie7
2021-04,2021-04-12,cookie3
2021-04,2021-04-13,cookie2
2021-04,2021-04-13,cookie4
2021-04,2021-04-16,cookie4
2021-03,2021-03-10,cookie2
2021-03,2021-03-10,cookie3
2021-04,2021-04-12,cookie5
2021-04,2021-04-13,cookie6
2021-04,2021-04-15,cookie3
2021-04,2021-04-15,cookie2
2021-04,2021-04-16,cookie1

• GROUPING SETS的使用：

grouping sets是一種將多個(gè)group by 邏輯寫(xiě)在一個(gè)sql語(yǔ)句中的便利寫(xiě)法。

等價(jià)于將不同維度的GROUP BY結(jié)果集進(jìn)行UNION ALL。

SELECT?
month,
day,
COUNT(DISTINCT?cookieid)?AS?uv,
GROUPING__ID?
FROM?user_date?
GROUP?BY?month,day?
GROUPING?SETS?(month,day)?
ORDER?BY?GROUPING__ID;

注：上述SQL中的GROUPING__ID，是個(gè)關(guān)鍵字，表示結(jié)果屬于哪一個(gè)分組集合，根據(jù)grouping sets中的分組條件month，day，1是代表month，2是代表day。

結(jié)果如下：

上述SQL等價(jià)于：

SELECT?month,
NULL?as?day,
COUNT(DISTINCT?cookieid)?AS?uv,
1?AS?GROUPING__ID?
FROM?user_date?
GROUP?BY?month?

UNION?ALL?

SELECT?NULL?as?month,
day,
COUNT(DISTINCT?cookieid)?AS?uv,
2?AS?GROUPING__ID?
FROM?user_date?
GROUP?BY?day;

• CUBE的使用：

根據(jù)GROUP BY的維度的所有組合進(jìn)行聚合。

SELECT?
month,
day,
COUNT(DISTINCT?cookieid)?AS?uv,
GROUPING__ID?
FROM?user_date?
GROUP?BY?month,day?
WITH?CUBE?
ORDER?BY?GROUPING__ID;

結(jié)果如下：

上述SQL等價(jià)于：

SELECT?NULL,NULL,COUNT(DISTINCT?cookieid)?AS?uv,0?AS?GROUPING__ID?FROM?user_date

UNION?ALL?

SELECT?month,NULL,COUNT(DISTINCT?cookieid)?AS?uv,1?AS?GROUPING__ID?FROM?user_date?GROUP?BY?month?

UNION?ALL?

SELECT?NULL,day,COUNT(DISTINCT?cookieid)?AS?uv,2?AS?GROUPING__ID?FROM?user_date?GROUP?BY?day

UNION?ALL?

SELECT?month,day,COUNT(DISTINCT?cookieid)?AS?uv,3?AS?GROUPING__ID?FROM?user_date?GROUP?BY?month,day;

• ROLLUP的使用：

是CUBE的子集，以最左側(cè)的維度為主，從該維度進(jìn)行層級(jí)聚合。

比如，以month維度進(jìn)行層級(jí)聚合：

SELECT?
month,
day,
COUNT(DISTINCT?cookieid)?AS?uv,
GROUPING__ID??
FROM?user_date?
GROUP?BY?month,day
WITH?ROLLUP?
ORDER?BY?GROUPING__ID;

結(jié)果如下：

把month和day調(diào)換順序，則以day維度進(jìn)行層級(jí)聚合：

SELECT?
day,
month,
COUNT(DISTINCT?cookieid)?AS?uv,
GROUPING__ID??
FROM?user_date?
GROUP?BY?day,month?
WITH?ROLLUP?
ORDER?BY?GROUPING__ID;

結(jié)果如下：

這里，根據(jù)日和月進(jìn)行聚合，和根據(jù)日聚合結(jié)果一樣，因?yàn)橛懈缸雨P(guān)系，如果是其他維度組合的話(huà)，就會(huì)不一樣。

窗口函數(shù)實(shí)際應(yīng)用

1. 第二高的薪水

難度簡(jiǎn)單。

編寫(xiě)一個(gè) SQL 查詢(xún)，獲取 Employee 表中第二高的薪水（Salary）。

+----+--------+
|?Id?|?Salary?|
+----+--------+
|?1??|?100????|
|?2??|?200????|
|?3??|?300????|
+----+--------+

例如上述 Employee 表，SQL查詢(xún)應(yīng)該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢(xún)應(yīng)返回 null。

+---------------------+
|?SecondHighestSalary?|
+---------------------+
|?200?????????????????|
+---------------------+

這道題可以用 row_number 函數(shù)解決。

參考代碼：

SELECT
??*
??FROM(
????SELECT?Salary,?row_number()?over(order?by?Salary?desc)?rk?
????FROM?Employee
??)?t?WHERE?t.rk?=?2;

更簡(jiǎn)單的代碼：

SELECT?DISTINCT?Salary
FROM?Employee
ORDER?BY?Salary?DESC
LIMIT?1?OFFSET?1

OFFSET：偏移量，表示從第幾條數(shù)據(jù)開(kāi)始取，0代表第1條數(shù)據(jù)。

2. 分?jǐn)?shù)排名

難度簡(jiǎn)單。

編寫(xiě)一個(gè) SQL 查詢(xún)來(lái)實(shí)現(xiàn)分?jǐn)?shù)排名。

如果兩個(gè)分?jǐn)?shù)相同，則兩個(gè)分?jǐn)?shù)排名（Rank）相同。請(qǐng)注意，平分后的下一個(gè)名次應(yīng)該是下一個(gè)連續(xù)的整數(shù)值。換句話(huà)說(shuō)，名次之間不應(yīng)該有“間隔”。

+----+-------+
|?Id?|?Score?|
+----+-------+
|?1??|?3.50??|
|?2??|?3.65??|
|?3??|?4.00??|
|?4??|?3.85??|
|?5??|?4.00??|
|?6??|?3.65??|
+----+-------+

例如，根據(jù)上述給定的 Scores 表，你的查詢(xún)應(yīng)該返回（按分?jǐn)?shù)從高到低排列）：

+-------+------+
|?Score?|?Rank?|
+-------+------+
|?4.00??|?1????|
|?4.00??|?1????|
|?3.85??|?2????|
|?3.65??|?3????|
|?3.65??|?3????|
|?3.50??|?4????|
+-------+------+

參考代碼：

SELECT?Score,
dense_rank()?over(order?by?Score?desc)?as?`Rank`
FROM?Scores;

3. 連續(xù)出現(xiàn)的數(shù)字

難度中等。

編寫(xiě)一個(gè) SQL 查詢(xún)，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字。

+----+-----+
|?Id?|?Num?|
+----+-----+
|?1??|??1??|
|?2??|??1??|
|?3??|??1??|
|?4??|??2??|
|?5??|??1??|
|?6??|??2??|
|?7??|??2??|
+----+-----+

例如，給定上面的 Logs 表， 1 是唯一連續(xù)出現(xiàn)至少三次的數(shù)字。

+-----------------+
|?ConsecutiveNums?|
+-----------------+
|?1???????????????|
+-----------------+

參考代碼：

SELECT?DISTINCT?`Num`?as?ConsecutiveNums
FROM
??(
????SELECT?Num,
????lead(Num,?1,?null)?over(order?by?id)?n2,
????lead(Num,?2,?null)?over(order?by?id)?n3?
????FROM?Logs
??)?t1
WHERE?Num?=?n2?and?Num?=?n3

4. 連續(xù)N天登錄

難度困難。

寫(xiě)一個(gè) SQL 查詢(xún), ?找到活躍用戶(hù)的 id 和 name，活躍用戶(hù)是指那些至少連續(xù) 5 天登錄賬戶(hù)的用戶(hù)，返回的結(jié)果表按照 id 排序。

表 Accounts：

+----+-----------+
|?id?|?name??????|
+----+-----------+
|?1??|?Winston???|
|?7??|?Jonathan??|
+----+-----------+

表 Logins：

+----+-------------+
|?id?|?login_date??|
+----+-------------+
|?7??|?2020-05-30??|
|?1??|?2020-05-30??|
|?7??|?2020-05-31??|
|?7??|?2020-06-01??|
|?7??|?2020-06-02??|
|?7??|?2020-06-02??|
|?7??|?2020-06-03??|
|?1??|?2020-06-07??|
|?7??|?2020-06-10??|
+----+-------------+

例如，給定上面的Accounts和Logins表，至少連續(xù) 5 天登錄賬戶(hù)的是id=7的用戶(hù)

+----+-----------+
|?id?|?name??????|
+----+-----------+
|?7??|?Jonathan??|
+----+-----------+

思路：

1. 去重：由于每個(gè)人可能一天可能不止登陸一次，需要去重
2. 排序：對(duì)每個(gè)ID的登錄日期排序
3. 差值：計(jì)算登錄日期與排序之間的差值，找到連續(xù)登陸的記錄
4. 連續(xù)登錄天數(shù)計(jì)算：select id, count(*) group by id, 差值（偽代碼）
5. 取出登錄5天以上的記錄
6. 通過(guò)表合并，取出id對(duì)應(yīng)用戶(hù)名

參考代碼：

SELECT?DISTINCT?b.id,?name
FROM
??(SELECT?id,?login_date,
????DATE_SUB(login_date,?ROW_NUMBER()?OVER(PARTITION?BY?id?ORDER?BY?login_date))?AS?diff?
???FROM(SELECT?DISTINCT?id,?login_date?FROM?Logins)?a)?b
INNER?JOIN?Accounts?ac
ON?b.id?=?ac.id
GROUP?BY?b.id,?diff
HAVING?COUNT(b.id)?>=?5

注意點(diǎn)：

1. DATE_SUB的應(yīng)用：DATE_SUB (DATE, X)，注意，X為正數(shù)表示當(dāng)前日期的前X天；
2. 如何找連續(xù)日期：通過(guò)排序與登錄日期之間的差值，因?yàn)榕判蜻B續(xù)，因此若登錄日期連續(xù)，則差值一致；
3. GROUP BY和HAVING的應(yīng)用：通過(guò)id和差值的GROUP BY，用COUNT找到連續(xù)天數(shù)大于5天的id，注意COUNT不是一定要出現(xiàn)在SELECT后，可以直接用在HAVING中

5. 給定數(shù)字的頻率查詢(xún)中位數(shù)

難度困難。

Numbers 表保存數(shù)字的值及其頻率。

+----------+-------------+
|??Number??|??Frequency??|
+----------+-------------|
|??0???????|??7??????????|
|??1???????|??1??????????|
|??2???????|??3??????????|
|??3???????|??1??????????|
+----------+-------------+

在此表中，數(shù)字為 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位數(shù)是 (0 + 0) / 2 = 0。

+--------+
|?median?|
+--------|
|?0.0000?|
+--------+

請(qǐng)編寫(xiě)一個(gè)查詢(xún)來(lái)查找所有數(shù)字的中位數(shù)并將結(jié)果命名為 median 。

參考代碼：

select
avg(cast(number?as?float))?as?median
from
??(
????select?Number,
????Frequency,
????sum(Frequency)?over(order?by?Number)?-?Frequency?as?prev_sum,
????sum(Frequency)?over(order?by?Number)?as?curr_sum?
????from?Numbers
??)?t1,?(
????select?sum(Frequency)?as?total_sum?
????from?Numbers
??)?t2
where
t1.prev_sum?<=?(cast(t2.total_sum?as?float)?/?2)?
and
t1.curr_sum?>=?(cast(t2.total_sum?as?float)?/?2)