hive05_視窗函式

視窗函式

視窗函式可以更加靈活地對一定範圍內的資料進行操作和分析，它能夠為每行資料劃分一個視窗，然後對視窗範圍內的資料進行計算，最後將計算結果返回給該行資料；

舉個例子，區別於 GroupBy，GroupBy 對分組範圍內的資料進行聚合統計，得到當前分組的一條結果；視窗函式對每一條資料處理，展開一個視窗範圍，然後分析（聚合、篩選）得到一條結果；GroupBy 結果等於分組數、視窗函式結果等於資料總數。

基礎語法

視窗函式語法主要分為視窗、函式兩部分。其中視窗用於定義計算範圍，函式用於定義計算邏輯。

函式：一般為聚合函式，比如 max()、min()、sum()、count()、avg()，因為視窗往往涉及到多條資料，需要對這些資料聚合再執行相關操作。

視窗：視窗範圍的定義分為兩種型別，一種是基於行的，一種是基於值的。

視窗函式基本語法：

select order_id, order_date, amount, 函式(amount) over (視窗範圍) total_amount from order_info;

基於行的視窗函式

select xxx, xxx, ..., func(xxx) over (order by [column] rows between xxx and xxx) from table_name；

視窗範圍是指實際計算（分派到不同節點 MapReduce 任務相關資料）時的資料，所以為了能明確資料行的範圍，需要對資料按欄位排序。

其中視窗的起始和結束範圍有以下幾種情況：

• 起始為 unbounded preceding、[num] preceding；
  • 結束為 [num] preceding、current now、[num] following、unbounded following；
• 起始為 current now；
  • 結束為 current now、[num] following、unbounded following；
• 起始為 [num] following；
  • 結束為 [num] following、unbounded following；

基於值的視窗函式

select xxx, xxx, ...., func(xxx) over (order by [column] range between xxx and xxx) from table_name;

區別於基於行的視窗函式，基於值的 order by [column] 並沒有實際意義，只是為了宣告基於哪個欄位進行視窗函式的劃分。

當視窗範圍劃分時包含了數字，order by [column] 欄位必須為數字型別，否則視窗函式無效。

靜態視窗 Partition By

select xxx, xxx, ..., func(xxx) over (partition by [column] order by [column] rows between xxx and xxx) from table_name;

在劃分視窗範圍時，可以使用 PartitionBy 實現與 GroupBy 相同的分組劃分，且使用聚合函式。這種視窗的大小，在每條資料進行處理時，已經等於資料的分組結果了，所以視窗為靜態的。

注意 over(xxx) 中的內容也可以不寫，其中:

• partitionBy 省略不寫，表示不分割槽；
• orderBy 省略不寫，表示不排序；
• rows|range between xxx and xxx 省略不寫，表示使用其預設值；預設值為 range between unbounded preceding and current row、rows between unbounded preceding and unbounded following；

Hive 中的視窗函式 API

SUM、AVG、MIN、MAX

注意使用時 SQL 語句加和不加 order by 的區別，加了 order by 表示分組內從起點到當前行的 PV 累積；不加表示分組內所有值的累加求和。

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

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

ROW_NUMBER、RANK、DENSE_RANK、NTILE

• ROW_NUMBER：表示從1開始，按照順序生成分組內記錄的序列；

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

• RANK、DENSE_RANK：
  • RANK：生成資料項在分組中的排名，排名相等會在名次中留下空位；
  • DENSE_RANK：生成資料項在分組中的排名，排名相等會在名次中不會留下空位；
  • NTILE：將有序集合平均分配到指定數量的桶中，將桶號分配給每一行，若不能平均分配，則優先分配編號較小的桶，並且各個桶中能放的行數最多相差1；使用時可以根據桶號，選取前或者後 n分之幾的資料。

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;

LAG、LEAD、FIRST_VALUE、LAST_VALUE

• LAG(col, n, DEFAULT)：統計視窗內往上第 n 行值，其中第一個引數為列名，第二個為 n 行，第三個為預設值；

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;

• LEAD(col, n, DEFAULT)：統計視窗內往下第 n 行值。

SELECT cookieid, createtime, url, 
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY create_time) 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;

• FIRST_VALUE：去分組內排序後，截止到當前行的第一個值；

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

• LAST_VALUE：分組排序後，截止當前行的最後一個值；

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

如果想要取分組內最後一個值，應當透過 FIRST_VALUE 倒序排序：

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;

CUME_DIST

• 小於等於當前值的行數/分組內總行數 的比例值，Order 預設順序。

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;

GROUPING SETS、GORUPING__ID、CUBE、ROLLUP

• GROUPING SETS：將不同維度的 GROUP BY 結果集進行 UNION ALL；

視窗函式實際用例

獲取第幾高的資料值

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

獲取第二高的薪水資訊：

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

連續出現的數字

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

獲取連續出現至少三次的數字：

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 t1.n2 = Num and t1.n3 = Num;

統計當月累計下單總和

按照下單時間分組，sum() 函式累計計算總金額。

create table order_info
(
    order_id     string, --訂單id
    user_id      string, -- 使用者id
    user_name    string, -- 使用者姓名
    order_date   string, -- 下單日期
    order_amount int     -- 訂單金額
);

SELECT 
  order_id, 
  user_id, 
  user_name, 
  order_date, 
  order_amount, 
  sum(order_amount) over(partition by substring(order_date, 1, 7) order by order_date rows between unbounded preceding and current row) sum_so_far 
FROM order_info;

統計使用者每次下單距離上次下單的天數

按照使用者id 分組，使用者下單時間排序，透過 lag 視窗查詢上一行的資訊。

SELECT
  order_id, 
  user_id, 
  user_name, 
  order_date, 
  order_amount, 
  IFNULL (datediff(order_date, last_order_date), 0) diff
FROM
  (
    SELECT
      order_id, 
      user_id, 
      user_name, 
      order_date, 
      order_amount,
      lag(order_date, 1, null) over(partition by user_id order by order_date) last_order_date
    FROM order_info
  ) t1

統計所有下單記錄及每個使用者的每個下單記錄所在月份的首/末次下單日期

SELECT
  order_id, 
  user_id, 
  user_name, 
  order_date, 
  order_amount, 
  first_value(order_date) over(partition by user_id, substring(order_date, 1, 7) order by order_date) first_date,
  last_value(order_date) over(partition by user_id, substring(order_date, 1, 7) order by order_date) last_date
FROM 
  order_info;

連續 N 天登入

-- 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  |
+----+-------------+

獲取至少連續5天登入的使用者資訊，結果按 id 排序，思路如下：

• 去重：可能單人一天登入多次；
• 排序：對登入日期排序，然後建立視窗函式 row_number() 標記序號；
• 差值：計算登入日誌與排序間的差值，找到連續登入的記錄，可以透過 date_sub(xxx, n) 獲取前 n 天的日期值；
• 過濾：最終結果按照 id 分組後，每個組內應當有多行值具有相同的日期值，透過 groupby 按照日期分組後 having 過濾掉總次數小於5的記錄；

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

中級SQL

環境準備

• 使用者資訊表
  

• 商品資訊表
  

• 訂單資訊表
  

• 訂單明細表
  

• 登入明細表
  

• 商品價格變更明細表
  

• 配送資訊表
  

• 好友關係表
  

• 收藏資訊表
  

DROP TABLE IF EXISTS user_login_detail;
CREATE TABLE user_login_detail
(
    `user_id`    string comment '使用者id',
    `ip_address` string comment 'ip地址',
    `login_ts`   string comment '登入時間',
    `logout_ts`  string comment '登出時間'
) COMMENT '使用者登入明細表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

需求

• 查詢累計銷量排名第二的商品

SELECT sku_id
FROM (
    SELECT sku_id           //過濾排名第二的
    FROM (
        SELECT sku_id,        //最內層查詢各商品銷量的排名——dense_rank
            order_num,
            dense_rank() over(order by order_num desc) rk
        FROM (
          SELECT sku_id,
                sum(sku_num) order_num
          FROM order_detail
          GROUP BY sku_id
        ) t1
    ) t2
    WHERE rk = 2
) t3 
  RIGHT JOIN             //沒有第二名返回 null
(
  SELECT 1
) t4
ON 1= 1;

• 查詢至少連續三天下單的使用者

思路：（1）首先根據訂單表按照使用者id、下單日期分組去重；（2）統計每一行行數，根據使用者id分組、下單日期排序，並用當前日期減去當前行號得到flag欄位，如果有一批連續的日期，flag的結果應該相同；（3）按照使用者id分組，統計flag的總數是否大於等於3；

SELECT distinct user_id
FROM (
    SELECT user_id,        // 按照flag欄位分組並統計總行數大於3的個數
    FROM (
        SELECT user_id,    // 統計每一行日期減去行號的結果值
          create_date,
          date_sub(create_date, row_number() over(partition by user_id order by create_date)) flag
        FROM (
          SELECT user_id,  // 分組去重
              create_date
          FROM order_info
          GROUP BY user_id, create_date
        ) t1
    ) t2
    GROUP BY user_id, flag
    HAVING count(flag) >= 3
)t3;

• 查詢各品類銷售商品的種數及銷量最高的商品

思路：（1）先根據訂單明細表查詢各商品的售賣總數；（2）統計各商品所屬品類的商品種數、各品類中按照商品售賣總數排序獲取售賣第一的商品並展示。

SELECT category_id,
       category_name,
       sku_id,
       name,
       order_num,
       sku_cnt
FROM (
    SELECT od.sku_id,
           sku.name
           sku.category_id,
           cate.category_name,
           order_num
           rank() over(partition by sku.category_id order by order_num desc) rk,    // 按品類分組，給每個品類下商品按售賣數排序編號
           count(distinct od.sku_id) over(partition by sku.category_id) sku_cnt     // 統計每個品類下商品的種類個數
    FROM (
        SELECT sku_id,              // 先查詢訂單明細表獲取各種商品的售賣總數
          sum(sku_num) order_num
        FROM order_detail
        GROUP BY sku_id
    ) od
    LEFT JOIN sku_info sku ON od.sku_id = sku.id
    LEFT JOIN category_info cate ON sku.category_id = cate.id
) t1
WHERE rk = 1;

• 查詢使用者累計消費金額及VIP等級

詳細需求：
從訂單資訊表(order_info)中統計每個使用者截止其每個下單日期的累積消費金額，以及每個使用者在其每個下單日期的VIP等級。
使用者vip等級根據累積消費金額計算，計算規則如下：
設累積消費總額為X，
若0=<X<10000,則vip等級為普通會員
若10000<=X<30000,則vip等級為青銅會員
若30000<=X<50000,則vip等級為白銀會員
若50000<=X<80000,則vip為黃金會員
若80000<=X<100000,則vip等級為白金會員
若X>=100000,則vip等級為鑽石會員

SELECT user_id,
       create_date,
       sum_so_far
       case
          when sum_so_far >= 10000 then '鑽石會員'
          when sum_so_far >= 80000 then '白金會員'
          when sum_so_far >= 50000 then '黃金會員'
          when sum_so_far >= 30000 then '白銀會員'
          when sum_so_far >= 10000 then '青銅會員'
          when sum_so_far >= 0 then '普通會員'
          end vip_level
FROM (
  SELECT user_id,
        create_date,                                    // 截止當前日期累計消費總金額
        sum(total_amount_per_day) over(partition by user_id order by create_date) sum_so_far
  FROM (
      SELECT user_id,                                   // 按使用者id、建立時間分組，統計每個訂單金額總數
        create_date, 
        sum(total_amount) total_amount_per_day
      FROM order_info
      GROUP BY user_id, create_date
  ) t1
) t2

• 查詢首次下單後第二天連續下單使用者比率

需求：從 order_info 表中查詢首次下單後第二天仍然連續下單使用者的比例。
思路：（1）類似於TOPN問題，分組後排序然後獲取前 N 名資料即可；（2）然後透過 count() 函式獲取滿足條件的資料行數與總行數的比例求出最終比例值。

SELECT 
    count(if(datediff(second_date, first_date) = 1, 1, 0)) / count(*)
FROM (
    SELECT user_id,                                 // 劃分兩次日期
        min(create_date) first_date,
        max(create_date) second_date
    FROM (
        SELECT user_id,                             // 建立日期排序，求首次和第二次下單日期
          create_date,
          rank() over(partition by user_id order by create_date) rk
        FROM (
            SELECT user_id,                         // 按使用者id、建立時間分組
                create_date
            FROM order_info
            GROUP BY user_id, create_date
        ) t1
    ) t2
    WHERE rk <= 2
    GROUP BY user_id
)

• 求商品銷售年份、銷售數量、銷售金額

需求：從訂單明細表(order_detail)統計每個商品銷售首年的年份，銷售數量和銷售總額。

SELECT
    year(create_date),
    sum(sku_num),
    sum(price * sku_num)
FROM (
    SELECT sku_id,
        create_date,
        sku_num,
        price,
        rank() over(partition by sku_id order by year(create_date)) rk  // 根據年份排序給每個訂單標識序號
    FROM order_detail
    GROUP BY sku_id, create_date
) t1
WHERE rk = 1
GROUP BY sku_id, year(create_date);

• 篩選去年總銷量小於 100 的商品

需求：從 order_detail 中篩選去年（2021年）總銷量小於 100 的商品及銷量，同時不考慮上架小於一個月的商品。

SELECT
    t1.sku_id,
    name,
    order_num
FROM (
    SELECT sku_id,                                      // 過濾總銷量大於 100 的商品
          sum(sku_num) order_num
    FROM order_detail
    WHERE year(create_date) = '2021' AND sku_id in (    // 子查詢篩選上架大於一個月的商品
        SELECT sku_id
        FROM sku_info
        WHERE datediff('2022-01-10', from_date) > 30
    )
    GROUP BY sku_id
    having sum(sku_num) < 100
) t1 left join sku_info t2 ON t1.sku_id = t2.sku_id;

• 查詢每日新使用者數

需求：從使用者登入明細表查詢每天新增的使用者數。

SELECT 
    login_ts,
    count(distinct t1.user_id) new_user_count
FROM (
    SELECT user_id,
          login_ts,
          lag(login_ts, 1) over(partition by user_id order by login_ts) last_login_ts
    FROM user_login_detail
) t1
WHERE datediff(login_ts, IFNLL(last_login_ts, 0)) > 1 
ORDER BY login_ts;

• 統計每個商品的銷量最高的日期

需求：從 order_detail 中統計每種商品銷售件數最多的日期及當日銷量，如果有同一商品多日銷量並列情況，取其中最小日期。

SELECT sku_id,
       create_date,
       sum_num
FROM (
    SELECT sku_id,
          create_date,
          sum_num
    FROM (
        SELECT sku_id,          // 每種商品按銷售額倒序排序、按照建立日期升序排序
              create_date,
              sum_num,
              row_number() over(partition by sku_id order by sum_num desc, create_date asc) rk
        FROM (
            SELECT sku_id,      // 查詢每種商品每天的銷售總額
                  create_date,
                  sum(sku_num) sum_num
            FROM order_detail
            GROUP BY sku_id, create_date
        ) t1
    ) t2
)
WHERE rk = 1;

• 查詢銷售件數高於品類平均數的商品

從 order_detail 中查詢累計銷售件數高於其所屬品類平均數的商品。

SELECT
    sku_id,
    name,
    sum_num,
    cate_avg_num
FROM (
    SELECT
        od.sku_id,                      // 統計同一品類中商品銷售數平均值
        category_id,
        name,
        sum_num,
        avg(sum_num) over(partition by category_id) cate_avg_num
    FROM (
        SELECT sku_id,                  // 統計每種商品的售賣總數
              sum(sku_num) sum_num
        FROM order_detail
        GROUP BY sku_id
    ) od LEFT JOIN (                    // 聯合商品品類
        SELECT sku_id,
              category_id,
        FROM sku_info
    ) sku ON od.sku_id = sku.sku_id
) t1
WHERE sum_num > cate_avg_num;           // 最外層過濾

• 使用者註冊、登入、下單綜合統計

需求：從使用者 user_login_detail、order_info 中查詢每個使用者的註冊日期、總登入次數以及在 2021 年的登入次數、訂單數和訂單總額。

SELECT login.user_id,
       register_date,
       login_count_2021,
       order_count_2021,
       total_amount_2021
FROM (
    SELECT user_id,                     // 最早登入日期、總登入次數、2021年總登入次數
          min(date_format(login_ts, 'yyy-MM-dd')),
          count(1) total_login_count,
          count(if(year(login_ts) = '2021', 1, null)) login_count_2021
    FROM user_login_detail
    GROUP BY user_id
) login JOIN (
    SELECT user_id,                     // 2021 年訂單數、訂單總額
          count(distinct(order_id)) order_count_2021,
          sum(total_amount) total_amount_2021
    FROM order_info
    WHERE year(create_date) = '2021'
    GROUP BY user_id
) ON login.user_id = order.user_id;

• 即時訂單比例

需求：訂單配送中，如果期望配送日期和下單日期相同，稱為即時訂單，如果期望配送日期和下單日期不同，稱為計劃訂單。請從配送資訊表（delivery_info）中求出每個使用者的首單（使用者的第一個訂單）中即時訂單的比例，保留兩位小數，以小數形式顯示。

SELECT round(sum(if(order_date = custom_date, 1, 0)) / count(*), 2) percentage
FROM (
    SELECT delivery_id,
          user_id,
          order_date,
          custom_date,
          row_number() over(partition by user_id order by order_date) rk
    FROM delivery_info
) t1
WHERE rk = 1;

• 向使用者推薦朋友收藏產品

需求：現需要請向所有使用者推薦其朋友收藏但是使用者自己未收藏的商品，請從好友關係表（friendship_info）和收藏表（favor_info）中查詢出應向哪位使用者推薦哪些商品。

SELECT 
    distinct t1.user_id,
    friend_favor.sku_id
FROM (
    SELECT user1_id user_id,
          user2_id friend_id
    FROM friendship_info
) t1
LEFT JOIN favor_info friend_favor ON t1.friend_id = friend_favor.user_id   // 查詢使用者和好友的收藏商品
LEFT JOIN favor_info user_favor ON t1.user_id = user_favor.user_id
AND friend_favor.sku_id = user_favor.sku_id                                // 聯合起來結果為空，說明好友收藏但是使用者未收藏
WHERE user_favor.sku_id is null;

• 查詢所有使用者的連續登入兩天及以上的日期區間

需求：從登入明細表（user_login_detail）中查詢出，所有使用者的連續登入兩天及以上的日期區間，以登入時間（login_ts）為準。

SELECT user_id,
       min(login_date) start_date,
       max(login_date) end_date
FROM (
    SELECT user_id,
          login_date,
          date_sub(login_date, rk) flag         // 每行日期值減去行號，如果連續登入會出現多行值相同
    FROM (
        SELECT
            user_id,                            // 記錄按照登入時間升序排序，並且記錄每行行號
            login_date,
            row_number() over(partition by user_id order by login_date) rk
        FROM (
            SELECT user_id,                     // 查詢使用者id、登入時間，並按照使用者id分組
                  date_format(login_ts, 'yyyy-MM-dd') login_date,
            FROM user_login_detail
            GROUP BY user_id, date_format(login_ts, 'yyyy-MM-dd')
        ) t1
    ) t2
) t3
GROUP BY user_id, flag
having count(*) >= 2;

• 男性和女性每日的購物金額統計

需求：從訂單資訊表（order_info）和使用者資訊表（user_info）中，分別統計每天男性和女性使用者的訂單總金額，如果當天男性或者女性沒有購物，則統計結果為0。

SELECT create_date,
       sum(if(gender = '男', total_amount, 0)) total_amount_male,
       sum(if(gender = '女', total_amount, 0)) total_amount_female
FROM order_info oi 
LEFT JOIN user_info ui ON oi.user_id = ui.user_id
GROUP BY create_date;

• 訂單金額趨勢分析

需求：查詢截止每天的最近3天內的訂單金額總和以及訂單金額日平均值，保留兩位小數，四捨五入。