1. 借鑒

網(wǎng)名在線生成器
 HIVE over() 超全講解
 Hive分析窗口函數(shù)(一) SUM,AVG,MIN,MAX

2. 開始

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

db_company.hotel_order 數(shù)據(jù)下載
表結(jié)構(gòu)如下：

create external table db_company.hotel_order
(
  id bigint,
  name string,
  order_date string,
  price bigint,
  code string
)
row format delimited
fields terminated by ',';

加載數(shù)據(jù)

load data local inpath '/opt/envs/datas/hotel/hotel_order.txt' into table db_company.hotel_order;

窗口函數(shù)

OVER()
指定分析函數(shù)工作的數(shù)據(jù)窗口大小
注意點(diǎn)：
① over前面必須是指定的函數(shù)
② 括號(hào)里面可以寫內(nèi)容，限定窗口大小，如果不寫，表示對(duì)查詢出來的所有數(shù)據(jù)集進(jìn)行開窗
③ 為每一組數(shù)據(jù)都進(jìn)行開窗

那么括號(hào)里可以寫那些內(nèi)容呢？

內(nèi)容	釋義
CURRENT ROW	表示當(dāng)前行
n PRECEDING	表示往前n行數(shù)據(jù)
n FOLLOWING	表示往后n行數(shù)據(jù)
UNBOUNDED PRECEDING	表示從前面的起點(diǎn)
UNBOUNDED FOLLOWING	表示到后面的終點(diǎn)

那個(gè)又有哪些指定的函數(shù)呢？

內(nèi)容	釋義
MAX(col)???????????????????? ???????????????????????????????	取最大值
MIN(col)	取最小值
AVG(col)	取平均值
COUNT(col)	取總數(shù)
LAG(col,n)	往前第n行數(shù)據(jù)，第三個(gè)參數(shù)為默認(rèn)值，可選
LEAD(col,n)	往后第n行數(shù)據(jù)，第三個(gè)參數(shù)為默認(rèn)值，可選
NTILE(n)	把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中，各個(gè)組有編號(hào)，編號(hào)從1開始，對(duì)于每一行，NTILE返回此行所屬的組的編號(hào)。注意：n必須為int類型。
RANK()	排序相同時(shí)會(huì)重復(fù)，總數(shù)不會(huì)變(并列) [4個(gè)人，排名為：1，1，3，4]
DENSE_RANK()	排序相同時(shí)會(huì)重復(fù)，總數(shù)會(huì)減少 [4個(gè)人，排名為：1，1，2，3]
ROW_NUMBER()	會(huì)根據(jù)順序計(jì)算 [4個(gè)人，排名為：1，2，3，4]

栗子

我們通過一些栗子來看下窗口函數(shù)

① 查詢2020-06，每個(gè)人入住總數(shù)以及總?cè)胱∪藬?shù)
```
select name, count(*), count(*) over()
from db_company.hotel_order
where date_format(order_date, 'yyyy-MM') = '2020-06' group by name;
```
結(jié)果如下：
```
name    _c1     count_window_0
齊鈺    2       6
酒博瀚  3       6
碧千易  2       6
源采文  2       6
掌蓉城  2       6
豐雅麗  4       6
```
我們分析一下，因?yàn)槲覀兪褂昧薱ount()和count() over()。看樣子后面就多了一個(gè)over()為啥結(jié)果前面和后面不一樣呢？group by之后，count()表示統(tǒng)計(jì)各個(gè)分組之后的數(shù)量，而count() over()則是對(duì)統(tǒng)計(jì)分組的數(shù)量(因?yàn)閛ver里面我們沒有寫內(nèi)容，且over前面的聚合函數(shù)式count)。

② 查詢用戶的入住明細(xì)以及所有用戶的入住總金額

select *, sum(price) over()
from db_company.hotel_order;

結(jié)果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
6       源采文  2020-06-02      103     00174553        1461
6       源采文  2020-06-01      103     00174553        1461
5       掌蓉城  2020-06-02      103     01341433        1461
5       掌蓉城  2020-06-01      103     01341433        1461
4       酒博瀚  2020-06-04      101     00277553        1461
4       酒博瀚  2020-06-03      101     00277553        1461
4       酒博瀚  2020-06-02      101     00277553        1461
3       齊鈺    2020-06-02      101     02371493        1461
3       齊鈺    2020-06-01      101     02371493        1461
2       碧千易  2020-06-13      93      02371493        1461
2       碧千易  2020-06-13      93      02371493        1461
1       豐雅麗  2020-06-13      91      02371493        1461
1       豐雅麗  2020-06-03      89      10271563        1461
1       豐雅麗  2020-06-02      89      10271563        1461
1       豐雅麗  2020-06-01      89      10271563        1461

可以看到over() + 前面的聚合函數(shù)，不一定非得跟著group by，但是它的概念還是包含了組的。

③ 根據(jù)時(shí)間排序，將price進(jìn)行累加

select *, sum(price) over(order by order_date rows between UNBOUNDED PRECEDING and CURRENT ROW) 
from db_company.hotel_order;

結(jié)果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
3       齊鈺    2020-06-01      101     02371493        101
1       豐雅麗  2020-06-01      89      10271563        190
6       源采文  2020-06-01      103     00174553        293
5       掌蓉城  2020-06-01      103     01341433        396
6       源采文  2020-06-02      103     00174553        499
5       掌蓉城  2020-06-02      103     01341433        602
4       酒博瀚  2020-06-02      101     00277553        703
1       豐雅麗  2020-06-02      89      10271563        792
3       齊鈺    2020-06-02      101     02371493        893
1       豐雅麗  2020-06-03      89      10271563        982
4       酒博瀚  2020-06-03      101     00277553        1083
4       酒博瀚  2020-06-04      101     00277553        1184
2       碧千易  2020-06-13      93      02371493        1277
2       碧千易  2020-06-13      93      02371493        1370
1       豐雅麗  2020-06-13      91      02371493        1461

另外我還發(fā)現(xiàn)以下兩種寫法，雖然這兩種寫法跟我們的需求不符，但是通過對(duì)以下這兩種方式的觀察我們發(fā)現(xiàn)是先執(zhí)行over函數(shù)，再執(zhí)行order排序，所以以后使用over函數(shù)時(shí)需要注意這一點(diǎn)。
第一種：排序?qū)懺趏ver外面。

select *, sum(price) over(rows between UNBOUNDED PRECEDING and CURRENT ROW) 
from db_company.hotel_order
order by order_date;

這種方式的結(jié)果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
1       豐雅麗  2020-06-01      89      10271563        1461
3       齊鈺    2020-06-01      101     02371493        917
6       源采文  2020-06-01      103     00174553        206
5       掌蓉城  2020-06-01      103     01341433        412
3       齊鈺    2020-06-02      101     02371493        816
1       豐雅麗  2020-06-02      89      10271563        1372
4       酒博瀚  2020-06-02      101     00277553        715
5       掌蓉城  2020-06-02      103     01341433        309
6       源采文  2020-06-02      103     00174553        103
4       酒博瀚  2020-06-03      101     00277553        614
1       豐雅麗  2020-06-03      89      10271563        1283
4       酒博瀚  2020-06-04      101     00277553        513
1       豐雅麗  2020-06-13      91      02371493        1194
2       碧千易  2020-06-13      93      02371493        1010
2       碧千易  2020-06-13      93      02371493        1103

第二種，不根據(jù)order_date進(jìn)行排序

select *, sum(price) over( rows between UNBOUNDED PRECEDING and current row) 
from db_company.hotel_order;

結(jié)果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
6       源采文  2020-06-02      103     00174553        103
6       源采文  2020-06-01      103     00174553        206
5       掌蓉城  2020-06-02      103     01341433        309
5       掌蓉城  2020-06-01      103     01341433        412
4       酒博瀚  2020-06-04      101     00277553        513
4       酒博瀚  2020-06-03      101     00277553        614
4       酒博瀚  2020-06-02      101     00277553        715
3       齊鈺    2020-06-02      101     02371493        816
3       齊鈺    2020-06-01      101     02371493        917
2       碧千易  2020-06-13      93      02371493        1010
2       碧千易  2020-06-13      93      02371493        1103
1       豐雅麗  2020-06-13      91      02371493        1194
1       豐雅麗  2020-06-03      89      10271563        1283
1       豐雅麗  2020-06-02      89      10271563        1372
1       豐雅麗  2020-06-01      89      10271563        1461

拓展：解釋一下這個(gè)HQL
我們看下下面這個(gè)HQL

select *, sum(price) over(order by order_date) 
from db_company.hotel_order;

結(jié)果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
3       齊鈺    2020-06-01      101     02371493        396
1       豐雅麗  2020-06-01      89      10271563        396
6       源采文  2020-06-01      103     00174553        396
5       掌蓉城  2020-06-01      103     01341433        396
6       源采文  2020-06-02      103     00174553        893
5       掌蓉城  2020-06-02      103     01341433        893
4       酒博瀚  2020-06-02      101     00277553        893
1       豐雅麗  2020-06-02      89      10271563        893
3       齊鈺    2020-06-02      101     02371493        893
1       豐雅麗  2020-06-03      89      10271563        1083
4       酒博瀚  2020-06-03      101     00277553        1083
4       酒博瀚  2020-06-04      101     00277553        1184
2       碧千易  2020-06-13      93      02371493        1461
2       碧千易  2020-06-13      93      02371493        1461
1       豐雅麗  2020-06-13      91      02371493        1461

我們上面說了，over不一定跟著group by，但是它的概念包含了組，就是說這個(gè)組里面可以一條也可以多條數(shù)據(jù)，也可以說每條成組或者多條成組。我們按照order_date進(jìn)行排序，我們也說了它會(huì)為每一組數(shù)據(jù)開窗，啥叫每一組？我們按照order_date排序，第一組的2020-06-01日期都分到了一組。如果你要問為什么？那有沒有比它小的？沒有。有沒有比它大的？有，比它大的分到它們對(duì)應(yīng)的組。那它是不是分為一組？是，所以[2020-06-01]分為一組。

所以總共分為以下組
[2020-06-01]，
[2020-06-01,2020-06-02]，
[2020-06-01,2020-06-02,2020-06-03]，
[2020-06-01,2020-06-02,2020-06-03,2020-06-04]，
[2020-06-01,2020-06-02,2020-06-03,2020-06-04,2020-06-13]
然后對(duì)組內(nèi)進(jìn)行sum(price)計(jì)算

④ 計(jì)算每個(gè)月的總金額

select *, sum(price) over(distribute by order_date) 
from db_company.hotel_order;

結(jié)果如下:

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
3       齊鈺    2020-06-01      101     02371493        396
1       豐雅麗  2020-06-01      89      10271563        396
6       源采文  2020-06-01      103     00174553        396
5       掌蓉城  2020-06-01      103     01341433        396
6       源采文  2020-06-02      103     00174553        497
5       掌蓉城  2020-06-02      103     01341433        497
4       酒博瀚  2020-06-02      101     00277553        497
1       豐雅麗  2020-06-02      89      10271563        497
3       齊鈺    2020-06-02      101     02371493        497
1       豐雅麗  2020-06-03      89      10271563        190
4       酒博瀚  2020-06-03      101     00277553        190
4       酒博瀚  2020-06-04      101     00277553        101
2       碧千易  2020-06-13      93      02371493        277
2       碧千易  2020-06-13      93      02371493        277
1       豐雅麗  2020-06-13      91      02371493        277

⑤ 計(jì)算每個(gè)用戶的累加金額

select *, sum(price) over(distribute by name sort by order_date) 
from db_company.hotel_order;

結(jié)果如下:

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0
1       豐雅麗  2020-06-01      89      10271563        89
1       豐雅麗  2020-06-02      89      10271563        178
1       豐雅麗  2020-06-03      89      10271563        267
1       豐雅麗  2020-06-13      91      02371493        358
5       掌蓉城  2020-06-01      103     01341433        103
5       掌蓉城  2020-06-02      103     01341433        206
6       源采文  2020-06-01      103     00174553        103
6       源采文  2020-06-02      103     00174553        206
2       碧千易  2020-06-13      93      02371493        186
2       碧千易  2020-06-13      93      02371493        186
4       酒博瀚  2020-06-02      101     00277553        101
4       酒博瀚  2020-06-03      101     00277553        202
4       酒博瀚  2020-06-04      101     00277553        303
3       齊鈺    2020-06-01      101     02371493        101
3       齊鈺    2020-06-02      101     02371493        202

⑥ 查詢每個(gè)用戶上次的入住時(shí)間
這里基于⑤，并且用到lag函數(shù)

select *, 
sum(price) over(distribute by name sort by order_date),
lag(order_date, 1) over(distribute by name sort by order_date)
from db_company.hotel_order;

結(jié)果如下：

hotel_order.id  hotel_order.name        hotel_order.order_date  hotel_order.price       hotel_order.code        sum_window_0    lag_window_1
1       豐雅麗  2020-06-01      89      10271563        89      NULL
1       豐雅麗  2020-06-02      89      10271563        178     2020-06-01
1       豐雅麗  2020-06-03      89      10271563        267     2020-06-02
1       豐雅麗  2020-06-13      91      02371493        358     2020-06-03
5       掌蓉城  2020-06-01      103     01341433        103     NULL
5       掌蓉城  2020-06-02      103     01341433        206     2020-06-01
6       源采文  2020-06-01      103     00174553        103     NULL
6       源采文  2020-06-02      103     00174553        206     2020-06-01
2       碧千易  2020-06-13      93      02371493        186     NULL
2       碧千易  2020-06-13      93      02371493        186     2020-06-13
4       酒博瀚  2020-06-02      101     00277553        101     NULL
4       酒博瀚  2020-06-03      101     00277553        202     2020-06-02
4       酒博瀚  2020-06-04      101     00277553        303     2020-06-03
3       齊鈺    2020-06-01      101     02371493        101     NULL
3       齊鈺    2020-06-02      101     02371493        202     2020-06-01

⑦ 查詢每日每個(gè)人的訂單金額排名

select name, price, order_date,
rank() over(distribute by order_date sort by price desc),
dense_rank() over(distribute by order_date sort by price desc),
row_number() over(distribute by order_date sort by price desc)
from db_company.hotel_order;

結(jié)果如下:

name    price   order_date      rank_window_0   dense_rank_window_1     row_number_window_2
源采文  103     2020-06-01      1       1       1
掌蓉城  103     2020-06-01      1       1       2
齊鈺    101     2020-06-01      3       2       3
豐雅麗  89      2020-06-01      4       3       4
源采文  103     2020-06-02      1       1       1
掌蓉城  103     2020-06-02      1       1       2
酒博瀚  101     2020-06-02      3       2       3
齊鈺    101     2020-06-02      3       2       4
豐雅麗  89      2020-06-02      5       3       5
酒博瀚  101     2020-06-03      1       1       1
豐雅麗  89      2020-06-03      2       2       2
酒博瀚  101     2020-06-04      1       1       1
碧千易  93      2020-06-13      1       1       1
碧千易  93      2020-06-13      1       1       2
豐雅麗  91      2020-06-13      3       2       3

⑧ 查詢用戶在2020年，連續(xù)2天(或以上)的都有入住記錄，且訂單金額大于90元的流水。
分析文件
第一步：查詢2020年訂單金額大于90的訂單流水，并計(jì)為t1。

select 
  name, order_date, price, code
from 
  db_company.hotel_order
where 
  substring(order_date, 1, 4) = '2020' and price > 90;t1

第二步：查詢前一天后一天的日期數(shù)據(jù)，并計(jì)為t2。

select
  name, order_date, price, code,
  lag(order_date, 1, '0000-00-00') over(partition by name order by order_date) lag1,
  lead(order_date, 1, '0000-00-00') over(partition by name order by order_date) lead1
from t1;t2

第三步：計(jì)算時(shí)間差，并計(jì)為t3。
我們用：
Ⅰ. 當(dāng)前時(shí)間 - 前一天的時(shí)間（為0或者1即合法）。其中為0表示同一天有多筆訂單，為1表示前一天有訂單
Ⅱ. 當(dāng)前時(shí)間 - 后一天的時(shí)間（為0或則-1即合法）。其中為0表示同一天有多筆訂單，為-1表示后一天有訂單

select 
  name, order_date, price, code,
  datediff(order_date, lag1) diff_lag1,
  datediff(order_date, lead1) diff_lead1
from t2;t3

第四步：同步比對(duì)時(shí)間差，得出最后流水明細(xì)

select 
  name, order_date, price, code
from
  t3
where 
  diff_lag1 = 0 or diff_lag1  = 1 or diff_lead1 = -1 or diff_lead1 = 0;

最后的sql為：

select 
  name, order_date, price, code
from
(
  select 
    name, order_date, price, code,
    datediff(order_date, lag1) diff_lag1,
    datediff(order_date, lead1) diff_lead1
  from 
  (
    select
        name, order_date, price, code,
        lag(order_date, 1, '0000-00-00') over(partition by name order by order_date) lag1,
        lead(order_date, 1, '0000-00-00') over(partition by name order by order_date) lead1
    from 
    (
       select name, order_date, price, code
       from 
          db_company.hotel_order
       where 
          substring(order_date, 1, 4) = '2020' and price > 90
    )t1
  )t2
)t3
where 
  diff_lag1 = 0 or diff_lag1  = 1 or diff_lead1 = -1 or diff_lead1 = 0;

最后的結(jié)果如下：

name    order_date      price   code
掌蓉城  2020-06-01      103     01341433
掌蓉城  2020-06-02      103     01341433
源采文  2020-06-01      103     00174553
源采文  2020-06-02      103     00174553
碧千易  2020-06-13      93      02371493
碧千易  2020-06-13      93      02371493
酒博瀚  2020-06-02      101     00277553
酒博瀚  2020-06-03      101     00277553
酒博瀚  2020-06-04      101     00277553
齊鈺    2020-06-01      101     02371493
齊鈺    2020-06-02      101     02371493

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

Hive 1.2.1 窗口函數(shù)

Hive 1.2.1 窗口函數(shù)

1. 借鑒

2. 開始

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

窗口函數(shù)

栗子

3. 大功告成

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

Hive 1.2.1 窗口函數(shù)

1. 借鑒

2. 開始

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

窗口函數(shù)

栗子

3. 大功告成

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av