HiveSQL分析函數實踐詳解

目錄

一、視窗函數概述：

1.視窗函數的分類

2.視窗函數與普通聚合函數的差別：

二、視窗函數的基本用法

1.基本文法

2.設定視窗的方法

1）window_name

2）partition by 子句

3) order by子ve句

4）rows 指定視窗大小

3.開窗函數中加order by 和 不加 order by的差別

三、視窗函數用法舉例

1.序号函數：row_number() / rank() / dense_rank()（面試重點）

2.分布函數：percent_rank() / cume_dist()

3.前後函數 lag(expr,n,defval)、lead(expr,n,defval)（面試重點）

4.頭尾函數：FIRST_VALUE(expr),LAST_VALUE(expr)

5.聚合函數+視窗函數聯合使用

四、面試題

1.使用者行為分析

2.學生成績分析

一、視窗函數概述：

視窗函數也稱為OLAP函數，OLAP 是OnLine Analytical Processing 的簡稱，意思是對資料庫資料進行實時分析處理。例如，市場分析、建立财務報表、建立計劃等日常性商務工作。視窗函數就是為了實作OLAP 而添加的标準SQL 功能。

1. 視窗函數的分類

按照功能劃分：

• 序号函數：row_number() / rank() / dense_rank()
• 分布函數：percent_rank() / cume_dist()
• 前後函數：lag() / lead()
• 頭尾函數：first_val() / last_val()
• 聚合函數+視窗函數聯合：
• 求和 sum() over()
• 求最大/小 max()/min() over()
• 求平均 avg() over()
• 其他函數：nth_value() / nfile()

如上，視窗函數的用法多種多樣，不僅有專門的的視窗函數，還可以與聚合函數配合使用。

2. 視窗函數與普通聚合函數的差別：

聚合函數是将多條記錄聚合為一條；視窗函數是每條記錄都會執行，有幾條記錄執行完還是幾條。

視窗函數兼具GROUP BY 子句的分組功能以及ORDER BY 子句的排序功能。但是，PARTITION BY 子句并不具備 GROUP BY 子句的彙總功能。

舉例：若原表中有id一樣的10行資料，使用GROUP BY，傳回的結果是将多條記錄聚合成一條；而使用 rank() 等視窗函數并不會減少原表中 記錄的行數，結果中仍然包含 10 行資料。

視窗函數兼具分組和排序兩種功能。

二、視窗函數的基本用法

如有基礎資料：

drop table if exists exam_record;
CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '使用者ID',
    exam_id int NOT NULL COMMENT '試卷ID',
    start_time datetime NOT NULL COMMENT '開始時間',
    submit_time datetime COMMENT '送出時間',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
 
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:02', 84),
(1006, 9001, '2021-09-01 12:11:01', '2021-09-01 12:31:01', 89),
(1006, 9002, '2021-09-06 10:01:01', '2021-09-06 10:21:01', 81),
(1005, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 81),
(1005, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81),
(1004, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 71),
(1004, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 91),
(1004, 9002, '2021-09-05 10:01:01', '2021-09-05 10:21:01', 80),
(1004, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 80);
 
select uid,score from exam_record;
           

1. 基本文法

<視窗函數> OVER ([PARTITION BY <列名清單>] ORDER BY <排序列名清單> [rows between 開始位置 and 結束位置])
           

其中：

<視窗函數>：指需要使用的分析函數，如row_number()、sum()等。

over() : 用來指定函數執行的視窗範圍，這個資料視窗大小可能會随着行的變化而變化；

如果括号中什麼都不寫，則意味着視窗包含滿足WHERE條件的所有行，視窗函數基于所有行進行計算。如：

select 
    uid,
    score,
    sum(score) over() as sum_score
from exam_record 
           

結果：

sum(score) over() as sum_score 會聚合所有的資料，将結果接到每一行的後面(視窗函數不會改變結果原表行數)。

2. 設定視窗的方法

如果不為空,則支援以下4中文法來設定視窗。

1）window_name

給視窗指定一個别名。如果SQL中涉及的視窗較多,采用别名可以看起來更清晰易讀，如：

--給視窗指定别名：WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)
SELECT
    uid,
    score,
    rank() OVER my_window_name AS rk_num,
    row_number() OVER my_window_name AS row_num
FROM exam_record
WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)
           

2）partition by 子句

視窗按照哪些字段進行分組,視窗函數在不同的分組上分别執行，如：

執行個體1：

SELECT
    uid,
    score,
    sum(score) OVER(PARTITION BY uid) AS sum_score
FROM exam_record
           

結果：

sum(score) OVER(PARTITION BY uid) AS sum_score 會按照 uid 分組，分别求和，展示在每個分組的末尾。

如果我想看某個uid有多少行記錄，并标明序号該如何實作？使用序号函數row_number()請看：

SELECT
    uid,
    score,
    row_number() OVER(PARTITION BY uid) AS row_num
FROM exam_record
           

可以看到，row_number()按照uid分組并從上到下按照順序标号。我們看到1004中的score是無序的，如果想按照score降序排名應該怎麼做呢？(實際場景：成績排名)

可以結合 order by 子句實作

3）order by子句

按照哪些字段進行排序,視窗函數将按照排序後的記錄順序進行編号，如：

SELECT
    uid,
    score,
    row_number() OVER(PARTITION BY uid ORDER BY score desc) AS row_num
FROM exam_record
           

這樣就實作了每個uid内的分數降序排名，order by 後面可以跟多個列名，大家可以試一試。

當order by 與聚合類函數連用時，特别需要注意了解，如下面幾個例子：

先看前面的例子，單獨使用 partition by uid

SELECT
    uid,
    score,
    sum(score) OVER(PARTITION BY uid) AS sum_score
FROM exam_record
           

結果：

單獨使用order by uid

SELECT
    uid,
    score,
    sum(score) OVER(ORDER BY uid) AS sum_score
FROM exam_record
           

結果：

注意觀察uid 從1004->1005的變化，兩條SQL的結果有什麼不同：

• partition by 按照uid分組，分别對score求和，”接到每一行的末尾“
• 分組内求和，分組間互相獨立。
• order by 按照uid排序，對”序号“相同的元素進行求和，不同”序号“的數累加求和
• 如果将”序号“認為是分組的話，個人了解這是一個分組求和并累加的過程
• 即分組内求和，分組間累加。

再看，order by score 的例子

SELECT
    uid,
    score,
    sum(score) OVER(ORDER BY score) AS sum_score
FROM exam_record
           

結果：

總結一下：

如果使⽤環境為hive，over()開窗函數前分排序函數和聚合函數兩種。

當為排序函數，如row_number(),rank()等時，over中的order by隻起到窗⼝内排序作⽤。

當為聚合函數，如max，min，count等時，over中的order by不僅起到窗⼝内排序，還起到窗⼝内從目前⾏到之前所有⾏的聚合（多了⼀個範圍）。

4）rows 指定視窗大小

a.先看個例子，按照順序，求score的平均值：

SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc) AS avg_score
FROM exam_record
           

注意score相同的部分：

如果想要sql先按照score降序排列，每一行計算從第一行到目前行的score平均值，該怎麼計算呢？——想辦法做一個不重複的key

實作：

SELECT
    uid,
    score,
    row_score,
    avg(score) OVER(ORDER BY row_score) AS avg_score
FROM (
    SELECT
        uid,
        score,
        row_number() OVER(ORDER BY score desc) AS row_score
    FROM exam_record
) res
           

現在改下需求，希望求"我與前兩名的平均值"應該怎麼實作呢？

分析一下，"我與前兩名"指的是目前行以及前兩行總共三行資料求平均，也就是說，我們需要限定視窗的範圍或者視窗大小。

b.引入視窗架構

指定視窗大小，又稱為視窗架構。架構是對視窗進行進一步分區，架構有兩種範圍限定方式：

一種是使用 ROWS 子句，通過指定目前行之前或之後的固定數目的行來限制分區中的行數。

另一種是使用 RANGE 子句，按照排列序列的目前值，根據相同值來确定分區中的行數。

文法：

ORDER BY 字段名 RANGE|ROWS 邊界規則0 | [BETWEEN 邊界規則1 AND 邊界規則2]
           

RANGE | ROWS的差別是什麼？

RANGE表示按照值的範圍進行範圍的定義，而ROWS表示按照行的範圍進行範圍的定義；邊界規則的可取值見下表：

需要注意：

• 當使用架構時，必須要有 order by 子句，如果僅指定了order by 子句而未指定架構，那麼預設架構将采用 RANGE UNBOUNDED PRECEDING AND CURRENT ROW（表示目前行以及一直到第一行的資料）。
• 如果視窗函數沒有指定 order by 子句，也就不存在 ROWS/RANGE 視窗的計算。
• PS: RANGE 隻支援使用 UNBOUNDED 和 CURRENT ROW 視窗架構分隔符。

OK，回到前面的需求：求"我與前兩名的平均值"。因為要"我與前兩名"，是以我們會用到規則 2 PRECEDING

之前2行的記錄
之前1行的記錄
自身（目前記錄）
 
SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc rows 2 PRECEDING) AS avg_score
FROM exam_record
           

如果要求目前行及前後一行呢？

之前1行的記錄
自身（目前記錄）
之後1行的記錄
 
SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc rows between 1 PRECEDING and 1 FOLLOWING) AS avg_score
FROM exam_record
           

結果略~

其他組合舉例：

1.第一行到目前行
ORDER BY score desc rows UNBOUNDED PRECEDING
 
2.第一行到前一行(不含目前行)
ORDER BY score desc rows between UNBOUNDED PRECEDING and 1 PRECEDING 
 
3.第一行到後一行(包含目前行)
ORDER BY score desc rows between UNBOUNDED PRECEDING and 1 FOLLOWING
 
4.目前行到最後一行
ORDER BY score desc rows between CURRENT ROW and UNBOUNDED FOLLOWING 
注意，這種寫法是錯誤的
ORDER BY score desc rows UNBOUNDED FOLLOWING -- 錯誤示範
 
5.前一行到最後一行(包含目前行)
ORDER BY score desc rows between 1 PRECEDING and UNBOUNDED FOLLOWING
 
6.後一行到最後一行(不含目前行)
ORDER BY score desc rows between 1 FOLLOWING and UNBOUNDED FOLLOWING
 
7.前一行到後一行(包含目前行) 
ORDER BY score desc rows between 1 PRECEDING and 1 FOLLOWING 
           

3. 開窗函數中加order by 和 不加 order by的差別

如果使⽤環境為hive，over()開窗函數前分排序函數和聚合函數兩種。

當為排序函數，如row_number(),rank()等時，over中的order by隻起到窗⼝内排序作⽤。

當為聚合函數，如max，min，count等時，over中的order by不僅起到窗⼝内排序，還起到窗⼝内從目前⾏到之前所有⾏的聚合（多了⼀個範圍）。

如：

-- sql ①
select id, dept, salary, min(salary) over(partition by dept) min_sal from dept;  
 
-- sql ②
select id, dept, salary, min(salary) over(partition by dept order by id) min_sal from dept; 
           

上⾯①②中的min_salary字段的值會不⼀樣，原因是②中使⽤order by後，等同于 min(salary) over(partition by dept order by userid range between unbounded preceding and current row )，當然可以在order by後使⽤架構⼦句，即rows，range等，如果沒有寫架構⼦句，就預設在窗⼝範圍中目前⾏到之前所有⾏的資料進⾏統計。

再看個例子：

# 表資料為：exam_record(uid,exam_id,start_time,end_time,score)
# (1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 100),
# (1001, 9001, '2021-09-02 09:01:01', '2021-09-01 09:31:00', 100),
# (1001, 9001, '2021-09-03 09:01:01', '2021-09-01 09:31:00', 100),
# (1002, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 100),
# (1002, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 100),
# (1002, 9001, '2021-09-02 09:01:01', '2021-09-01 09:31:00', 100); 
-- 執行下面的sql
select 
    uid,
    exam_id,
    start_time,
    sum(score) over(partition by uid) as one,
    sum(score) over(partition by uid order by start_time) as two
from exam_record
           

得到結果：

需要注意表中标注的部分

三、視窗函數用法舉例

1. 序号函數：row_number() / rank() / dense_rank()（面試重點）

三者差別：

• RANK(): 并列排序，跳過重複序号——1、1、3
• ROW_NUMBER(): 順序排序——1、2、3
• DENSE_RANK(): 并列排序，不跳過重複序号——1、1、2

--給視窗指定别名：WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)
SELECT
    uid,
    score,
    rank() OVER my_window_name AS rk_num,
    row_number() OVER my_window_name AS row_num
FROM exam_record
WINDOW my_window_name AS (PARTITION BY uid ORDER BY score)
           

我們來探索一下，如果不使用視窗函數，如何實作分數排序呢？（使用自連接配接的方法）

-- 相當于 rank()
SELECT 
    P1.uid,
    P1.score,
    (SELECT 
        COUNT(P2.score)
    FROM exam_record P2 
    WHERE P2.score > P1.score) + 1 AS rank_1
FROM exam_record P1
ORDER BY rank_1;
           

這裡1234447..，如果想要7改為5呢，不跳過位次。相當于DENSE_RANK 函數。

隻需要改 COUNT(P2.score) 為 COUNT(distinct P2.score) 即可。

2. 分布函數：percent_rank() / cume_dist()

1）percent_rank():

percent_rank()函數将某個數值在資料集中的排位作為資料集的百分比值傳回，此處的百分比值的範圍為 0 到 1。此函數可用于計算值在資料集内的相對位置。如班級成績為例，傳回的百分數30%表示某個分數排在班級總分排名的前30%。

每行按照公式(rank-1) / (rows-1)進行計算。其中，rank為RANK()函數産生的序号，rows為目前視窗的記錄總行數。

SELECT
    uid,
    score,
    rank() OVER my_window_name AS rank_num,
    PERCENT_RANK() OVER my_window_name AS prk
FROM exam_record
WINDOW my_window_name AS (ORDER BY score desc)
           

2）cume_dist():

如果按升序排列，則統計：小于等于目前值的行數/總行數。

如果是降序排列，則統計：大于等于目前值的行數/總行數。

如:查詢小于等于目前成績（score）的比例。

SELECT
    uid,
    score,
    rank() OVER my_window_name AS rank_num,
    cume_dist() OVER my_window_name AS cume_dist_num
FROM exam_record
WINDOW my_window_name AS (ORDER BY score asc)
           

3. 前後函數 lag(expr,n,defval)、lead(expr,n,defval)（面試重點）

Lag()和Lead()分析函數可以在同一次查詢中取出同一字段的前N行的資料(Lag)和後N行的資料(Lead)作為獨立的列。

在實際應用當中，若要用到取今天和昨天的某字段內插補點時，Lag()和Lead()函數的應用就顯得尤為重要。當然，這種操作可以用表的自連接配接實作，但是LAG()和LEAD()與left join、rightjoin等自連接配接相比，效率更高，SQL更簡潔。下面我就對這兩個函數做一個簡單的介紹。

函數文法如下：

lag( exp_str,offset,defval) over(partition by .. order by …)
 
lead(exp_str,offset,defval) over(partition by .. order by …)
           

其中

• exp_str 是字段名
• Offset 是偏移量，即是上1個或上N個的值，假設目前行在表中排在第5行，offset 為3，則表示我們所要找的資料行就是表中的第2行（即5-3=2）。
• Defval 預設值，當兩個函數取 上N 或者 下N 個值，當在表中從目前行位置向前數N行已經超出了表的範圍時，lag() 函數将defval這個參數值作為函數的傳回值，若沒有指定預設值，則傳回NULL，那麼在數學運算中，總要給一個預設值才不會出錯。

用途：

• 傳回位于目前行的前n行的expr的值：LAG(expr,n)
• 傳回位于目前行的後n行的expr的值：LEAD(expr,n)

舉例：查詢前1名同學及後一名同學的成績和目前同學成績的內插補點(隻排分數，不按uid分組)

先将前一名和後一名的分數與目前行的分數放在一起：

SELECT
    uid,
    score,
    LAG(score,1,0) OVER my_window_name AS `前一名分數`,
    LEAD(score,1,0) OVER my_window_name AS `後一名分數`
FROM exam_record
WINDOW my_window_name AS (ORDER BY score desc)
           

再做diff：

SELECT
    uid,
    score,
    score - `前一名分數` AS `與前一名分差`,
    score - `後一名分數` AS `與後一名分差`
FROM (
    SELECT
        uid,
        score,
        LAG(score,1,0) OVER my_window_name AS `前一名分數`,
        LEAD(score,1,0) OVER my_window_name AS `後一名分數`
    FROM exam_record
    WINDOW my_window_name AS (ORDER BY score desc)
) res
           

4. 頭尾函數：FIRST_VALUE(expr),LAST_VALUE(expr)

用途：

• 傳回第一個expr的值：FIRST_VALUE(expr)
• 傳回最後一個expr的值：LAST_VALUE(expr)

應用場景：截止到目前成績，按照分數排序查詢第1個和最後1個的分數

SELECT
        uid,
        score,
        FIRST_VALUE(score) OVER my_window_name AS `第一行分數`,
        LAST_VALUE(score) OVER my_window_name AS `最後一行分數`
    FROM exam_record
    WINDOW my_window_name AS (ORDER BY score desc)
           

5. 聚合函數+視窗函數聯合使用

聚合函數也可以用于視窗函數。

原因就在于視窗函數的執行順序（邏輯上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之後，在ORDER BY，LIMIT，SELECT DISTINCT之前。它執行時GROUP BY的聚合過程已經完成了，是以不會再産生資料聚合。

注:視窗函數是在where之後執行的，是以如果where子句需要用視窗函數作為條件，需要多一層查詢，在子查詢外面進行

前面基本用法中已經有部分舉例，如：

SELECT
        uid,
        score,
        sum(score) OVER my_window_name AS sum_score,
        max(score) OVER my_window_name AS max_score,
        min(score) OVER my_window_name AS min_score,
        avg(score) OVER my_window_name AS avg_score
    FROM exam_record
    WINDOW my_window_name AS (ORDER BY score desc)
           

mysql> SELECT 
    ->     stu_id, 
    ->     lesson_id, 
    ->     score, 
    ->     create_time,
    ->     FIRST_VALUE(score) OVER w AS first_score, -- 按照lesson_id分區，create_time升序，取第一個score值
    ->     LAST_VALUE(score) OVER w AS last_score -- 按照lesson_id分區，create_time升序，取最後一個score值
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> WINDOW w AS (PARTITION BY lesson_id ORDER BY create_time)
    -> ;
+--------+-----------+-------+-------------+-------------+------------+
| stu_id | lesson_id | score | create_time | first_score | last_score |
+--------+-----------+-------+-------------+-------------+------------+
|      3 | L001      |   100 | 2018-08-07  |         100 |        100 |
|      1 | L001      |    98 | 2018-08-08  |         100 |         98 |
|      2 | L001      |    84 | 2018-08-09  |         100 |         99 |
|      4 | L001      |    99 | 2018-08-09  |         100 |         99 |
|      3 | L002      |    91 | 2018-08-07  |          91 |         91 |
|      1 | L002      |    86 | 2018-08-08  |          91 |         86 |
|      2 | L002      |    90 | 2018-08-09  |          91 |         90 |
|      4 | L002      |    88 | 2018-08-10  |          91 |         88 |
+--------+-----------+-------+-------------+-------------+------------+
           

四、面試題

1. 使用者行為分析

表1：使用者行為表tracking_log，大概字段有（user_id‘使用者編号’,opr_id‘操作編号’,log_time‘操作時間’）如下所示：

問題：

1）統計每天符合以下條件的使用者數：A操作之後是B操作，AB操作必須相鄰

分析：

（1）統計每天，是以需要按天分組統計求和

（2）A操作之後是B，且AB操作必須相鄰，那就涉及一個前後問題，是以想到用視窗函數中的lag()或lead()。

-- 使用 lead() 實作 
select 
    dt,
    count(1) as res_cnt
from (
    select
        user_id,
        date_format(log_time,"%Y%m%d") as dt,
        opr_id as curr_opr, -- 目前操作
        lead(opr_id,1) over(partition by user_id,date_format(log_time,"%Y%m%d") order by log_time) as next_opr -- 擷取 下一個操作
    from tracking_log
) res 
where curr_opr = "A" and next_opr="B" 
group by dt
 
---------------------------------
 
-- 使用 lag() 實作 
select 
    dt,
    count(1) as res_cnt
from (
    select
        user_id,
        date_format(log_time,"%Y%m%d") as dt,
        opr_id as curr_opr, -- 目前操作
        lag(opr_id,1) over(partition by user_id,date_format(log_time,"%Y%m%d") order by log_time) as before_opr -- 擷取 前一個操作
    from tracking_log
) res 
where before_opr = "A" and curr_opr="B" 
group by dt
           

2）統計使用者行為序列為A-B-D的使用者數,其中:A-B之間可以有任何其他浏覽記錄(如C,E等),B-D之間除了C記錄可以有任何其他浏覽記錄(如A,E等)

select 
    count(*)
from(
    select 
        user_id,
        group_concat(opr_id) ubp -- 先按照使用者分組，将組内的opr_id拼接起來
    from tracking_log
    group by user_id
) a
where ubp like '%A%B%D%' and ubp not like '%A%B%C%D%'
           

2. 學生成績分析

表：Enrollments (student_id, course_id) 是該表的主鍵。

1）查詢每位學生獲得的最高成績和它所對應的科目，若科目成績并列，取 course_id 最小的一門。查詢結果需按 student_id 增序進行排序。

分析：因為需要最高成績和所對應的科目，是以可采用視窗函數排序分組取第一個

select 
 student_id,
 course_id,
 grade
from (
 select
  student_id,
  course_id,
  grade,
  row_number() over(partition by student_id order by grade desc,course_id asc) as rank_num 
 from Enrollments 
) res 
where rank_num = 1 
order by student_id 
           

解法2：IN 解法

取成績在最大成績之中的學生的最小課程号的課程

select student_id,min(course_id)
from Enrollments
where (student_id,grade) in (
                     -- 先取最大成績
      select student_id,max(grade)
      from Enrollments
      group by student_id)
group by student_id
order by student_id;
           

2）查詢每一科目成績最高和最低分數的學生,輸出course_id,student_id,score

我們可以按科目查找成績最高的同學和最低分的同學，然後利用union連接配接起來

select 
 c_id,
 s_id
from(
 select 
  *,
  row_number() over(partition by c_id order by s_score desc) r
 from score
) a
where r = 1
 
union
 
select 
 c_id,
 s_id
from(
 select 
  *,
  row_number() over(partition by c_id order by s_score) r
 from score
) a
where r = 1;
           

解法2：case-when

select 
     c_id,
     max(case when r1 = 1 then s_id else null end) '最高分學生',
     max(case when r2 = 1 then s_id else null end) '最低分學生'
from(
     select 
          *,
          row_number() over(partition by c_id order by s_score desc) r1,
  row_number() over(partition by c_id order by s_score) r2
 from score
) a
group by c_id;