還原試題

首先新建一張表來還原一下試題：

CREATE TABLE last_3_day_test_table
(
     user_id 	varchar(300),
     login_date 	date
);

INSERT INTO last_3_day_test_table ( user_id , login_date )
VALUES
    ('A', '2019/9/2'),
    ('A', '2019/9/3'),
    ('A', '2019/9/4'),
    ('B', '2018/11/25'),
    ('B', '2018/12/31'),
    ('C', '2019/1/1'),
    ('C', '2019/4/4'),
    ('C', '2019/9/3'),
    ('C', '2019/9/4'),
    ('C', '2019/9/5');

表中數(shù)據(jù)如下所示：

+──────────+─────────────+
| user_id  | login_date  |
+──────────+─────────────+
| A        | 2019-09-02  |
| A        | 2019-09-03  |
| A        | 2019-09-04  |
| B        | 2018-11-25  |
| B        | 2018-12-31  |
| C        | 2019-01-01  |
| C        | 2019-04-04  |
| C        | 2019-09-03  |
| C        | 2019-09-04  |
| C        | 2019-09-05  |
+──────────+─────────────+

現(xiàn)在需要找出這張表中所有的連續(xù)3天登錄用戶

這個(gè)問題雖然說難不難，但說易也不簡(jiǎn)單，而且，偏受大小廠喜歡。其實(shí)，不管是數(shù)倉(cāng)/ETL/BI/數(shù)據(jù)分析/大數(shù)據(jù)等方向，都會(huì)經(jīng)常被面試/筆試考察到。而解決這個(gè)問題的核心在于窗口函數(shù)的使用，因此先來看一下什么是窗口函數(shù)

SQL窗口函數(shù)

一.窗口函數(shù)有什么用

在日常工作中，經(jīng)常會(huì)遇到需要在每組內(nèi)排名，比如下面的業(yè)務(wù)需求：

• 排名問題：每個(gè)部門按業(yè)績(jī)來排名
• topN問題：找出每個(gè)部門排名前N的員工進(jìn)行獎(jiǎng)勵(lì)
• 匯總問題：需要加總每個(gè)部門的業(yè)績(jī)加總，但是需要按照按照最細(xì)的維度呈現(xiàn)而非一張匯總表呈現(xiàn)

面對(duì)這類需求，就需要使用sql的高級(jí)功能窗口函數(shù)了。

二.什么是窗口函數(shù)

窗口函數(shù)，也叫OLAP函數(shù)（Online Anallytical Processing，聯(lián)機(jī)分析處理），可以對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理。

窗口函數(shù)的基本語(yǔ)法如下：

<窗口函數(shù)> over (partition by <用于分組的列名>
                order by <用于排序的列名>)

那么語(yǔ)法中的<窗口函數(shù)>都有哪些呢？

<窗口函數(shù)>的位置，可以放以下兩種函數(shù)：

1） 專用窗口函數(shù)，包括后面要講到的rank, dense_rank, row_number等專用窗口函數(shù)。
2） 聚合函數(shù)，如sum. avg, count, max, min等

因?yàn)榇翱诤瘮?shù)是對(duì)where或者group by子句處理后的結(jié)果進(jìn)行操作，所以窗口函數(shù)原則上只能寫在select子句中。

三.如何使用

接下來，就結(jié)合實(shí)例，給大家介紹幾種窗口函數(shù)的用法。

1.專用窗口函數(shù)rank

例如下圖，是班級(jí)表中的內(nèi)容

如果我們想在每個(gè)班級(jí)內(nèi)按成績(jī)排名，得到下面的結(jié)果。

以班級(jí)“1”為例，這個(gè)班級(jí)的成績(jī)“95”排在第1位，這個(gè)班級(jí)的“83”排在第4位。上面這個(gè)結(jié)果確實(shí)按我們的要求在每個(gè)班級(jí)內(nèi)，按成績(jī)排名了。

得到上面結(jié)果的sql語(yǔ)句代碼如下：

select *,
   rank() over (partition by 班級(jí)
                 order by 成績(jī) desc) as ranking
from 班級(jí)表

我們來解釋下這個(gè)sql語(yǔ)句里的select子句。rank是排序的函數(shù)。要求是“每個(gè)班級(jí)內(nèi)按成績(jī)排名”，這句話可以分為兩部分：

1）每個(gè)班級(jí)內(nèi)：按班級(jí)分組

partition by用來對(duì)表分組。在這個(gè)例子中，所以我們指定了按“班級(jí)”分組（partition by 班級(jí)）
2）按成績(jī)排名

order by子句的功能是對(duì)分組后的結(jié)果進(jìn)行排序，默認(rèn)是按照升序（asc）排列。在本例中（order by 成績(jī) desc）是按成績(jī)這一列排序，加了desc關(guān)鍵詞表示降序排列。

通過下圖，我們就可以理解partiition by（分組）和order by（在組內(nèi)排序）的作用了。

窗口函數(shù)具備了我們之前學(xué)過的group by子句分組的功能和order by子句排序的功能。那么，為什么還要用窗口函數(shù)呢？

這是因?yàn)椋?strong>group by分組匯總后改變了表的行數(shù)，一行只有一個(gè)類別。而partiition by和rank函數(shù)不會(huì)減少原表中的行數(shù)。例如下面統(tǒng)計(jì)每個(gè)班級(jí)的人數(shù)。

相信通過這個(gè)例子，你已經(jīng)明白了這個(gè)窗口函數(shù)的使用：

現(xiàn)在我們說回來，為什么叫“窗口”函數(shù)呢？這是因?yàn)閜artition by分組后的結(jié)果稱為“窗口”，這里的窗口不是我們家里的門窗，而是表示“范圍”的意思。

簡(jiǎn)單來說，窗口函數(shù)有以下功能：

• 同時(shí)具有分組和排序的功能
• 不減少原表的行數(shù)
• 語(yǔ)法如下:<窗口函數(shù)> over (partition by <用于分組的列名> order by <用于排序的列名>)

2.其他專業(yè)窗口函數(shù)

專用窗口函數(shù)rank, dense_rank, row_number有什么區(qū)別呢？

它們的區(qū)別我舉個(gè)例子，你們一下就能看懂：

select *,
   rank() over (order by 成績(jī) desc) as ranking,
   dense_rank() over (order by 成績(jī) desc) as dese_rank,
   row_number() over (order by 成績(jī) desc) as row_num
from 班級(jí)表

得到結(jié)果：

從上面的結(jié)果可以看出：

rank函數(shù): 這個(gè)例子中是5位，5位，5位，8位，也就是如果有并列名次的行，會(huì)占用下一名次的位置。比如正常排名是1，2，3，4，但是現(xiàn)在前3名是并列的名次，結(jié)果是：1，1，1，4。

dense_rank函數(shù): 這個(gè)例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是現(xiàn)在前3名是并列的名次，結(jié)果是：1，1，1，2。

row_number函數(shù): 這個(gè)例子中是5位，6位，7位，8位，也就是不考慮并列名次的情況。比如前3名是并列的名次，排名是正常的1，2，3，4。

這三個(gè)函數(shù)的區(qū)別如下：

最后，需要強(qiáng)調(diào)的一點(diǎn)是：在上述的這三個(gè)專用窗口函數(shù)中，函數(shù)后面的括號(hào)不需要任何參數(shù)，保持()空著就可以。

現(xiàn)在，大家對(duì)窗口函數(shù)有一個(gè)基本了解了嗎？

3.聚合函數(shù)作為窗口函數(shù)

聚和窗口函數(shù)和上面提到的專用窗口函數(shù)用法完全相同，只需要把聚合函數(shù)寫在窗口函數(shù)的位置即可，但是函數(shù)后面括號(hào)里面不能為空，需要指定聚合的列名。

我們來看一下窗口函數(shù)是聚合函數(shù)時(shí)，會(huì)出來什么結(jié)果：

select *,
   sum(成績(jī)) over (order by 學(xué)號(hào)) as current_sum,
   avg(成績(jī)) over (order by 學(xué)號(hào)) as current_avg,
   count(成績(jī)) over (order by 學(xué)號(hào)) as current_count,
   max(成績(jī)) over (order by 學(xué)號(hào)) as current_max,
   min(成績(jī)) over (order by 學(xué)號(hào)) as current_min
from 班級(jí)表

得到結(jié)果：

有發(fā)現(xiàn)什么嗎？我單獨(dú)用sum舉個(gè)例子：

如上圖，聚合函數(shù)sum在窗口函數(shù)中，是對(duì)自身記錄、及位于自身記錄以上的數(shù)據(jù)進(jìn)行求和的結(jié)果。比如0004號(hào)，在使用sum窗口函數(shù)后的結(jié)果，是對(duì)0001，0002，0003，0004號(hào)的成績(jī)求和，若是0005號(hào)，則結(jié)果是0001號(hào)~0005號(hào)成績(jī)的求和，以此類推。

不僅是sum求和，平均、計(jì)數(shù)、最大最小值，也是同理，都是針對(duì)自身記錄、以及自身記錄之上的所有數(shù)據(jù)進(jìn)行計(jì)算，現(xiàn)在再結(jié)合剛才得到的結(jié)果（下圖），是不是理解起來容易多了？

比如0005號(hào)后面的聚合窗口函數(shù)結(jié)果是：學(xué)號(hào)0001~0005五人成績(jī)的總和、平均、計(jì)數(shù)及最大最小值。

如果想要知道所有人成績(jī)的總和、平均等聚合結(jié)果，看最后一行即可。

這樣使用窗口函數(shù)有什么用呢？

聚合函數(shù)作為窗口函數(shù)，可以在每一行的數(shù)據(jù)里直觀的看到，截止到本行數(shù)據(jù)，統(tǒng)計(jì)數(shù)據(jù)是多少（最大值、最小值等）。同時(shí)可以看出每一行數(shù)據(jù)，對(duì)整體統(tǒng)計(jì)數(shù)據(jù)的影響。

4.注意事項(xiàng)

partition子句可是省略，省略就是不指定分組，結(jié)果如下，只是按成績(jī)由高到低進(jìn)行了排序：

select *,
   rank() over (order by 成績(jī) desc) as ranking
from 班級(jí)表

得到結(jié)果：

但是，這就失去了窗口函數(shù)的功能，所以一般不要這么使用。

四.總結(jié)

1.窗口函數(shù)語(yǔ)法

<窗口函數(shù)> over (partition by <用于分組的列名>
                order by <用于排序的列名>)

<窗口函數(shù)>的位置，可以放以下兩種函數(shù)：

1） 專用窗口函數(shù)，比如rank, dense_rank, row_number等

2） 聚合函數(shù)，如sum. avg, count, max, min等

2.窗口函數(shù)有以下功能：

1）同時(shí)具有分組（partition by）和排序（order by）的功能

2）不減少原表的行數(shù)，所以經(jīng)常用來在每組內(nèi)排名

3.注意事項(xiàng)

窗口函數(shù)原則上只能寫在select子句中

解題思路

通過上述解釋，我們知道了什么是窗口函數(shù)，接下來就是如何利用窗口函數(shù)來解決這個(gè)問題.解決問題的關(guān)鍵是：如何判斷每個(gè)用戶連續(xù)

思路是先通過窗口函數(shù)對(duì)user_id分組排序后（rn），用登錄日期減去序號(hào)m,如果連續(xù)的話，則得到的這個(gè)日期（flag_date）會(huì)相同
即： flag_date=login_date-rn

+──────────+─────────────+─────+────────────+
| user_id  | login_date  | rn  | flag_date  |
+──────────+─────────────+─────+────────────+
| A        | 2019-09-02  | 1   | 2019-09-01 |
| A        | 2019-09-03  | 2   | 2019-09-01 |
| A        | 2019-09-04  | 3   | 2019-09-01 |
| B        | 2018-11-25  | 1   | 2018-11-24 |
| B        | 2018-12-31  | 2   | 2018-12-29 |
| C        | 2019-01-01  | 1   | 2018-12-31 |
| C        | 2019-04-04  | 2   | 2019-04-02 |
| C        | 2019-09-03  | 3   | 2019-08-31 |
| C        | 2019-09-04  | 4   | 2019-08-31 |
| C        | 2019-09-05  | 5   | 2019-08-31 |
+──────────+─────────────+─────+────────────+

然后我們只需要通過篩選出所有相同flag_date個(gè)數(shù)大于3即可得到結(jié)果。如果實(shí)現(xiàn)篩選出連續(xù)n天登錄用戶,這里相應(yīng)的改成n就可以了

代碼實(shí)現(xiàn)

在SQL Server中：

select user_id
from (
  select user_id,login_date,
    row_number() over(partition by user_id order by login_date) as
rn
  from last_3_day_test_table
) t
group by user_id,DATEADD(D,-t.rn,login_date)
having count(1)>=3;

在Mysql中（注意需要在Mysql 8.0及以上版本才支持開窗函數(shù)，低版本無法運(yùn)行）：

select user_id
from (
  select user_id,login_date,
   1 as rn
  from last_3_day_test_table
) as t
group by user_id,date_sub(login_date,interval t.rn day)
having count(1)>=3

兩者的區(qū)別就是在計(jì)算login_date-t.rn時(shí)，SQL Server中要使用DATEADD函數(shù)，且語(yǔ)法為：DATEADD(D,-t.rn,login_date)，而Mysql中直接使用date_sub 即可實(shí)現(xiàn)日期減去指定的時(shí)間間隔

其他解法與延展

附上另外的一種解法供參考，基于SQL server:

select
    b.user_id
from
(
    select
    user_id,login_date,lead(login_date,2,'1900/1/1') over(partition by user_id order by login_date desc) as date1
    from
    last_3_day_test_table a
    group by
    user_id,login_date
) as b
where
    DATEADD(D,-2,cast(b.login_date as date))
	=cast(b.date1 as date);

在這個(gè)解法中使用了另一個(gè)窗口函數(shù)： LEAD()函數(shù)。它提供對(duì)當(dāng)前行之后的指定物理偏移量的行的訪問。簡(jiǎn)單來說就是通過使用LEAD()函數(shù)，可以返回當(dāng)前行的下一行的數(shù)據(jù)或下n行的數(shù)據(jù)。

LEAD()函數(shù)對(duì)于將當(dāng)前行的值與后續(xù)行的值進(jìn)行比較非常有用。

LEAD()函數(shù)的語(yǔ)法為：

LEAD(return_value ,offset [,default]) 
over (partition by <用于分組的列名>
                order by <用于排序的列名>)

在上面語(yǔ)法中，

return_value： 基于指定偏移量的后續(xù)行的返回值，返回值必須求值為單個(gè)值。簡(jiǎn)單來說就是偏移行后去哪一列的值返回
offset： 是從當(dāng)前行所需偏移的行數(shù)，用于訪問數(shù)據(jù)。offset可以是表達(dá)式，子查詢或列，其值為正整數(shù)。如果未明確指定，則offset的默認(rèn)值為1。如果offset超出分區(qū)范圍，則該函數(shù)返回default。
default： 偏移超出分區(qū)范圍后的默認(rèn)值，如果未指定，則默認(rèn)為NULL。

本文參考文章：https://zhuanlan.zhihu.com/p/92654574

到此這篇關(guān)于SQL實(shí)現(xiàn)篩選出連續(xù)3天登錄用戶與窗口函數(shù)的示例代碼的文章就介紹到這了,更多相關(guān)SQL篩選登錄用戶與窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論