---------開窗函數(shù)（窗口函數(shù)）-分組排序開窗函數(shù)------------------------------
-- 序號函數(shù)：row_number桂对，rank蟆技，dense_rank

-- 1.1準(zhǔn)備數(shù)據(jù)test1.txt
/*
cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
cookie2,2018-04-10,2
cookie2,2018-04-11,3
cookie2,2018-04-12,5
cookie2,2018-04-13,6
cookie2,2018-04-14,3
cookie2,2018-04-15,9
cookie2,2018-04-16,7
*/

create table test_ordinal_func
(
    cookie_id   string comment 'cookie id',
    create_time string comment '創(chuàng)建時(shí)間',
    pv          int comment '訪問次數(shù)'
) row format delimited fields terminated by ',';

load data local inpath '/export/data/test_window.txt' into table test_ordinal_func;

select *
from test_ordinal_func;

-- 使用開窗函數(shù)來實(shí)現(xiàn)分組并組內(nèi)排序
-- 需求1：按照cookie_id進(jìn)行分組，并且在每一組中按照pv進(jìn)行降序排序
-- partition by：分組
select *,
       -- 1 2 3 4 5
       row_number() over (partition by cookie_id order by pv desc ) row_number,
       -- 1 2 3 3 5
       rank() over (partition by cookie_id order by pv desc )       rank,
       -- 1 2 3 3 4
       dense_rank() over (partition by cookie_id order by pv desc ) dense_rank
from test_ordinal_func;

-- 需求：按照cookie_id進(jìn)行分組身腻，并且在每一組中按照pv進(jìn)行降序排序，選出每一組排名前三的信息（TopN問題）

-- 以下寫法報(bào)錯(cuò)部凑，因?yàn)镾QL的執(zhí)行順序：from where select蹭睡，where的時(shí)候，別名還沒有出現(xiàn)
/*
select *,
       dense_rank() over (partition by cookie_id order by pv desc ) as rk3 -- 1 2 3 3 4
from test_ordinal_func
where rk3 <= 3;
*/

-- 通過子查詢解決
select *
from (
         select *,
                dense_rank() over (partition by cookie_id order by pv desc ) as rk3 -- 1 2 3 3 4
         from test_ordinal_func
     ) as dense_rank
where dense_rank.rk3 <= 3;


-- 需求：查詢新冠疫情數(shù)據(jù)表中每一個(gè)州確診人數(shù)最多的縣TopN
select *
from (
         select *,
                -- partition by：后面可跟多個(gè)字段（同州同縣分一組）
                -- order by：后面可跟多個(gè)字段（主要條件相同比較次要條件。按確診病例降序懦胞，確證病例相同則按死亡病例降序）
                dense_rank() over (partition by state, county order by cases desc, deaths desc) as rk
         from covid2
     ) as dense_rank
where dense_rank.rk = 1;

-- 分組和排序的后邊都可以指定多個(gè)字段
/*
select *,
       dense_rank() over (partition by cookie_id,pv order by cookie_id,create_time desc ) as rk3 -- 1 2 3 3 4
from test_ordinal_func;
*/


---------開窗函數(shù)（窗口函數(shù)）-指定區(qū)間進(jìn)行統(tǒng)計(jì)開窗函數(shù)（聚合開窗函數(shù)）------------------------------

-- 3替久、使用聚合開窗函數(shù)進(jìn)行統(tǒng)計(jì)
-- 累加區(qū)間：從第1行累加到當(dāng)前行（默認(rèn)）
select cookie_id,
       create_time,
       pv,
       sum(pv) over (partition by cookie_id order by create_time) as pv1
from test_ordinal_func;

-- 等價(jià)上邊的寫法
select cookie_id,
       create_time,
       pv,
       sum(pv) over (partition by cookie_id order by create_time
           rows between unbounded preceding and current row) as pv2
from test_ordinal_func;

-- 累加區(qū)間：從前3行累加到當(dāng)前行
select cookie_id,
       create_time,
       pv,
       sum(pv) over (partition by cookie_id order by create_time
           rows between 3 preceding and current row)
from test_ordinal_func;

-- 累加區(qū)間：從前3行累加到下1行
select cookie_id,
       create_time,
       pv,
       sum(pv) over (partition by cookie_id order by create_time
           rows between 3 preceding and 1 following) as pv5
from test_ordinal_func;

-- 累加區(qū)間：從當(dāng)前行加到組的最后
select cookie_id,
       create_time,
       pv,
       sum(pv) over (partition by cookie_id order by create_time
           rows between current row and unbounded following) as pv6
from test_ordinal_func;

-- 以上的sum可以替換為avg，max躏尉，min
select cookie_id,
       create_time,
       pv,
       max(pv) over (partition by cookie_id order by create_time) as pv1
from test_ordinal_func;

select cookie_id,
       create_time,
       pv,
       min(pv) over (partition by cookie_id order by create_time) as pv1
from test_ordinal_func;

select cookie_id,
       create_time,
       pv,
       avg(pv) over (partition by cookie_id order by create_time) as pv1
from test_ordinal_func;


-- 前后函數(shù)：lag lead
-- lag
-- 將上1行數(shù)據(jù)放在當(dāng)前行
select cookie_id,
       create_time,
       pv,
       lag(create_time, 1) over (partition by cookie_id order by create_time)
from test_ordinal_func;

-- 將上2行數(shù)據(jù)放在當(dāng)前行
select cookie_id,
       create_time,
       pv,
       lag(create_time, 2) over (partition by cookie_id order by create_time)
from test_ordinal_func;

-- lead
-- 將下1行數(shù)據(jù)放在當(dāng)前行
select cookie_id,
       create_time,
       pv,
       lead(create_time, 1) over (partition by cookie_id order by create_time)
from test_ordinal_func;

-- 將下2數(shù)據(jù)放在當(dāng)前行
select cookie_id,
       create_time,
       pv,
       lead(create_time, 2) over (partition by cookie_id order by create_time)
from test_ordinal_func;