? ? 這一周主要學習了 Hive 的一些基礎知識，學習了多個 Hive 窗口函數(shù)，雖然感覺這些窗口函數(shù)沒有實際的應用意義，但還是都了解了一下。
sum（）over（）
：可以實現(xiàn)在窗口中實現(xiàn)逐行累加
其他 avg、count、min、max 的用法一樣
#要先有一個統(tǒng)計出每個月總額的表，這里就是 t_access_amount 表，如下圖一
# partition by uid：根據(jù)uid 分組，order by month ：根據(jù)月份排序，
rows between unbounded preceding and current_row：選擇 無邊界的前面的行和當前行之間的行，最后是求 sum 即和。得到下圖二
# 是在窗口求和，而窗口的定義時按照 uid 分區(qū) order by 排序得到的。得到一個字段
select?uid，month，amount，
sum（amount）over（partition by uid order by month rows between unbounded preceding and??current_row ）?as?accumulate?from?t_access_amount；
preceding：前面的，后來的，往序號變大的方向
following：往后
2 preceding ：表示前2行
3 following ：表示后3行
unbounded preceding：表示從第1行開始，從前面的起點
unbounded following：表示最后一行，從后面的終點
注意：使用 rows between 時，按order by 順序編號（沒有指定order by 會默認排序）需要左邊是小編號右邊是大編號
rows between unbounded following and current row??是錯的，應該寫作
rows between current row and unbounded following 是當前行到終點行
rows between current row and 1 preceding? ?也是錯的，應該寫作
rows between 1 preceding andcurrent row 表示前1行和當前行
select
???cookieid,
???createtime,
???pv,
? ?# 得到顯示的排序編號
? ?row_number() over(partition by cookieid order by createtime) as rn,
? ?# 從前面的起點到當前位置，這里是從分區(qū)最后一行到當前行的和
???sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv1,
? ?# 和上面一樣（加 order by 和不加效果不一樣）
???sum(pv) over (partition by cookieid order by createtime) as pv2,
#省略了 rows betweent 窗口函數(shù)，表示分區(qū)的所有數(shù)據(jù)
???sum(pv) over (partition by cookieid) as pv3,
? ?# 前面3行+當前后
???sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4,
# 前面3行+當前行+后面1行
???sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5,
? ?# 當前行+后面所有行
???sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from cookie1;
注意：上面顯示的排序結(jié)果不太對，所以看起來好像結(jié)果是相反的一樣，實際上單獨拿出來運行是對的，可以看以相同方式排序的 rn 編號。
2、row_numver（）over（）函數(shù)、NTILE、RANK、DENSE_RANK
注意：這上面這些都不支持使用 rows between 語句，row_number() 展示出來的排序會和查詢字段的最后一個 over(order by)里面的順序一樣
：分組?TOPN，即可以分組后排序，便于找到最好的幾條數(shù)據(jù)
eg：有如下數(shù)據(jù)，要查出每種性別中年齡最大的2條數(shù)據(jù)
1,18，a，male
2,19，b，male
3,22，c，female
4，16，d，female
5，30，e，male
6，26，f，female
分析：如果使用按性別分組，是可以分出兩條數(shù)據(jù)，但是分組的缺點是只能查出聚合函數(shù)（聚合函數(shù)只能產(chǎn)生一組中的一個值）和分組依據(jù)。而這里是要求多條數(shù)據(jù)（2個）
# 正確操作，這里先 partition by sex字段分組，然后根據(jù)每一組的 age 字段降序排序。得到的序號 1,2,3 等取名為 rn ，然后通過 where 判斷 前兩個就是結(jié)果
#?rn?字段是一個分組標記?序號，如下圖是中間（select ...）括號的結(jié)果
select * from
（select id，age，sex，row_number( ) over（partition by sex order by age desc）as rn from t_user）tmp
where rn<3；
NTILE(n)?
：用于將分組數(shù)據(jù)按照順序切分成 n 片（不一定是平均），然后每一份都編號為1-n，這樣就可以拿到想要那一份數(shù)據(jù)。如果切片不均勻，默認增加第一個切片的分布，例如，14 條記錄切3片就切不好，就會切成 6、4、4，其中6那份編號為1。
注意：可以不指定 order by
select
??cookieid,
??createtime,
??pv,
??ntile(2) over (partition by cookieid order by createtime) as rn2, --分組內(nèi)將數(shù)據(jù)分成2片
??ntile(3) over (partition by cookieid order by createtime) as rn3, --分組內(nèi)將數(shù)據(jù)分成3片
??ntile(4) over (order by createtime) as rn4 --將所有數(shù)據(jù)分成4片
from cookie2
order by cookieid,createtime;
應用：
? ? 統(tǒng)計各個 cookieid，pv 數(shù)最多的前1/3天的數(shù)據(jù)
select * from(
select *,ntile(3) over(partition by cookieid order by pv desc) as sn3 from cookie2) a where a.sn3=1;
RANK()
：生成數(shù)據(jù)項再分組中的排名，排名相等會在名次中留下空位
dense_rank()
：生成數(shù)據(jù)項再分組中的排名，排名相等不會再名次中留下空位
注意：上面兩者都需要指定 order by，不然排名都是1
select
??cookieid,
??createtime,
??pv,
??rank() over (partition by cookieid order by pv desc) as r1,
??dense_rank() over (partition by cookieid order by pv desc) as r2,
??row_number() over (partition by cookieid order by pv desc) as rn
from cookie.cookie2
where cookieid='cookie1';
根據(jù)上圖，可知區(qū)別：
? ? rank()：按順序編號，相同分組排序有相同的排名，但是會占位，后面的排名隔一位，就是成績一樣名次相同，但是后面的名次得低兩位。
? ? dense_rank()：按順序編號，相同分組排序有相同的排名，后面排名順序不邊就是有并列第幾名的情況。
? ? row_number() ：按順序編號，不會有相同的編號，即使分組排序是相同的。
cume_dist
：小于等于當前值的行數(shù)/分組內(nèi)總行數(shù)，注意這個要指定排序方式，不然全都是1
select?
? *,
? # 對 pv 降序排序后，計算小于等于當前行 pv 值得行數(shù)占總行數(shù)得比分
? cume_dist() over(order by pv) as rn1,
? # 計算cookieid 分組內(nèi)，小于等于當前行 pv 值的行數(shù)占總行數(shù)的比分
? cume_dist() over(partition by cookieid order by pv) as rn2?
from cookie3;
percent_rank
：分組內(nèi)當前行的（rank 值-1）/（分組內(nèi)總行數(shù)-1）
select?
? * ,
? # 求得 rank 值
? rank() over(order by pv) as r1,
? # 求得總共有多少行，這里用 sum(1) 效果一樣
? count(1) over() as c1 ,
? # 得到的結(jié)果其實就是 rank -1/count(1) -1
? percent_rank() over(order by pv) as p1??
from cookie3;
總結(jié)：排序、切片、編號、的都需要使用 order by，不然會全都是1，但是除了 row_number() 因為這個函數(shù)編號不重復且順延，所以還是會有編號，但是不確定編號邏輯。
lag
：用于獲得窗口內(nèi)往上第n行的值，n>=0
第一個參數(shù)為列名
第二個參數(shù)為往上第n行（可選，默認為1）
第三個參數(shù)為默認值（當往上第n行為NULL時，取默認值，如不指定，則為NULL）
# 就是用往上多少行的數(shù)據(jù)來替換當前行，可以為0，但是不能為負數(shù)
select
? ?*,
? ?row_number() over(partition by cookieid order by createtime) as r1,
? ?lag(createtime,1) over(partition by cookieid order by createtime) as la1,
? ?lag(createtime,2,'this is tidai') over(partition by cookieid order by createtime) as la2 from cookie4;
lead
：與lag 相反，用于獲取窗口內(nèi)往下第n 行的值，n>=0
第一個參數(shù)為列名
第二個參數(shù)為往上第n行（可選，默認為1）
第三個參數(shù)為默認值（當往上第n行為NULL時，取默認值，如不指定，則為NULL）
select
???*,
???row_number() over(partition by cookieid order by createtime) as r1,
???lead(createtime,1) over(partition by cookieid order by createtime) as la1,
???lead(createtime,2,'this is tidai') over(partition by cookieid order by createtime) as la2 from cookie4;
last_value
：取分組內(nèi)排序后，截止到當前行，最后一個值
first_value
：取分組內(nèi)排序后，第一個值
select?
? *,
? # 展示排序好像和最后一個 over 里面的 order by 有關
? row_number() over(partition by cookieid order by createtime) as r1,
? # 獲取最后一個值，但是其實都還是自己，因為只到當前行?
? last_value(url) over(partition by cookieid order by createtime) as l1,
# 轉(zhuǎn)換一下 order by 為desc，這樣第一個就是之前的最后一個，可以避免 rn 為1的寫法，但是如果有多個字段去重那還是取 rn=1 的方便些
? first_value(url) over(partition by cookieid order by createtime desc ) as f1 from cookie4;
注意：使用窗口分析函數(shù)時，要特別注意 order by 的使用，如果使用的不恰當會導致統(tǒng)計的不是我們想要的。row_number() over() 的展示排序好像時根據(jù)最后一個字段的over（order by）來展示的。
grouping sets 、grouping__id、cube、rollup
這幾個分析函數(shù)通常用于 olap 中，不能累加，而且需要根據(jù)不同維度上鉆和下鉆的指標統(tǒng)計，比如分 時、分、天、月的 UV 數(shù)
grouping__id
：表示結(jié)果屬于哪一個分組集合，注意中間是兩個下劃線
select?
? month ,
? day,
? count(distinct cookieid) as uv,
? GROUPING__ID?
from cookie5 group by month ,day?
grouping sets(month,day)?
order by GROUPING__ID;
第一列時按照 month 進行分組的，
第二列時按照 day 進行分組的
第三列時按照 對應month、day 分組統(tǒng)計出來的結(jié)果
第四列 grouping__id 表示這一組結(jié)果屬于哪個分組集合
注意：grouping sets 里面就是說明以什么分組，上面的group by 是指定可以進行組合的分組字段sets 里面的只能使用這里指定的字段，
如，這里 grouping sets(month , day) 表示分別根據(jù) month、day 字段分組，? ? ? ?? ? ? ??grouping sets(month,day,(month,day)) 則表示分別根據(jù) month、day、month和day 分組。??
這里group by 和 grouping sets 可以搭配使用，不是這里的專屬，并且這里也可以不用 groupind sets
select
??month ,
??day,
??count(distinct cookieid) as uv,
??GROUPING__ID
from cookie5 group by month ,day
grouping sets(month,day,(month,day))
order by GROUPING__ID;
# 上面的語句執(zhí)行結(jié)果等價于下面的
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__IDFROM cookie5GROUP BY month
UNION ALL
SELECTNULL,day,COUNT(DISTINCT cookieid) AS uv,2 ASGROUPING__ID FROM cookie5GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 ASGROUPING__ID FROM cookie5GROUP BY month,day
cube
：根據(jù)group by 的維度的所有組合進行聚合，就是省略 grouping sets ，直接將group by 后面的字段以各種可能的形式分組，然后union all 得到結(jié)果。
select?
? month,
? day,
? count(distinct cookieid) as uv,
? grouping__id?
from cookie5?
group by month,day?with cube
order by grouping__id;
等價于下面的語句
SELECTNULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM cookie5 -- 不分組
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM cookie5GROUP BY month? --只對 month 分組
UNION ALL
SELECTNULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM cookie5GROUP BY day? --只對 day 分組
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM cookie5GROUP BY month,day? -- 對 month、day 分組
rollup
：是 cube 的子集，以最左側(cè)的維度為主，從該維度進行層級聚合
# 是cube 的子集，以 month 為主，保留 month、month+day、不指定分組 三種情況
select?
? month,
? day,
? count(distinct cookieid) as uv ,
? grouping__id?
from cookie5?
group by month,day with rollup?
order by grouping__id;
上面可以實現(xiàn)一個叫上鉆的效果：
? ? 月天的uv==》月的uv==》總的uv
