:可以實現在窗口中實現逐行累加
其他 avg、count、min、max 的用法壹樣
#要先有壹個統計出每個月總額的表,這裏就是 t_access_amount 表,如下圖壹
# partition by uid:根據uid 分組,order by month :根據月份排序,
rows between unbounded preceding and current_row:選擇 無邊界的前面的行和當前行之間的行,最後是求 sum 即和。得到下圖二
# 是在窗口求和,而窗口的定義時按照 uid 分區 order by 排序得到的。得到壹個字段
preceding:前面的,後來的,往序號變大的方向
following:往後
2 preceding :表示前2行
3 following :表示後3行
unbounded preceding:表示從第1行開始,從前面的起點
unbounded following:表示最後壹行,從後面的終點
註意:使用 rows between 時,按order by 順序編號(沒有指定order by 會默認排序)需要左邊是我號右邊是大編號
註意:上面顯示的排序結果不太對,所以看起來好像結果是相反的壹樣,實際上單獨拿出來運行是對的,可以看以相同方式排序的 rn 編號。
註意:這上面這些都不支持使用 rows between 語句,row_number() 展示出來的排序會和查詢字段的最後壹個 over(order by)裏面的順序壹樣
:分組?TOPN,即可以分組後排序,便於找到最好的幾條數據
eg:有如下數據,要查出每種性別中年齡最大的2條數據
1,18,a,male
2,19,b,male
3,22,c,female
4,16,d,female
5,30,e,male
6,26,f,female
分析:如果使用按性別分組,是可以分出兩條數據,但是分組的缺點是只能查出聚合函數(聚合函數只能產生壹組中的壹個值)和分組依據。而這裏是要求多條數據(2個)
:用於將分組數據按照順序切分成 n 片(不壹定是平均),然後每壹份都編號為1-n,這樣就可以拿到想要那壹份數據。如果切片不均勻,默認增加第壹個切片的分布,例如,14 條記錄切3片就切不好,就會切成 6、4、4,其中6那份編號為1。
註意:可以不指定 order by
應用:
統計各個 cookieid,pv 數最多的前1/3天的數據
:生成數據項再分組中的排名,排名相等會在名次中留下空位
:生成數據項再分組中的排名,排名相等不會再名次中留下空位
註意:上面兩者都需要指定 order by,不然排名都是1
根據上圖,可知區別:
rank():按順序編號,相同分組排序有相同的排名,但是會占位,後面的排名隔壹位,就是成績壹樣名次相同,但是後面的名次得低兩位。
dense_rank():按順序編號,相同分組排序有相同的排名,後面排名順序不邊就是有並列第幾名的情況。
row_number() :按順序編號,不會有相同的編號,即使分組排序是相同的。
:小於等於當前值的行數/分組內總行數,註意這個要指定排序方式,不然全都是1
:分組內當前行的(rank 值-1)/(分組內總行數-1)
總結:排序、切片、編號、的都需要使用 order by,不然會全都是1,但是除了 row_number() 因為這個函數編號不重復且順延,所以還是會有編號,但是不確定編號邏輯。
:用於獲得窗口內往上第n行的值,n>=0
第壹個參數為列名
第二個參數為往上第n行(可選,默認為1)
第三個參數為默認值(當往上第n行為NULL時,取默認值,如不指定,則為NULL)
:與lag 相反,用於獲取窗口內往下第n 行的值,n>=0
第壹個參數為列名
第二個參數為往上第n行(可選,默認為1)
第三個參數為默認值(當往上第n行為NULL時,取默認值,如不指定,則為NULL)
:取分組內排序後,截止到當前行,最後壹個值
:取分組內排序後,第壹個值
註意:使用窗口分析函數時,要特別註意 order by 的使用,如果使用的不恰當會導致統計的不是我們想要的。 row_number() over() 的展示排序好像時根據最後壹個字段的over(order by)來展示的 。
這幾個分析函數通常用於 olap 中,不能累加,而且需要根據不同維度上鉆和下鉆的指標統計,比如分 時、分、天、月的 UV 數
:表示結果屬於哪壹個分組集合,註意中間是兩個下劃線
第壹列時按照 month 進行分組的,
第二列時按照 day 進行分組的
第三列時按照 對應month、day 分組統計出來的結果
第四列 grouping__id 表示這壹組結果屬於哪個分組集合
註意:grouping sets 裏面就是說明以什麽分組,上面的group by 是指定可以進行組合的分組字段sets 裏面的只能使用這裏指定的字段,
如,這裏 grouping sets(month , day) 表示分別根據 month、day 字段分組, ?grouping sets(month,day,(month,day)) 則表示分別根據 month、day、month和day 分組。?
這裏group by 和 grouping sets 可以搭配使用,不是這裏的專屬,並且這裏也可以不用 groupind sets
:根據group by 的維度的所有組合進行聚合,就是省略 grouping sets ,直接將group by 後面的字段以各種可能的形式分組,然後union all 得到結果。
等價於下面的語句
:是 cube 的子集,以最左側的維度為主,從該維度進行層級聚合
上面可以實現壹個叫上鉆的效果:
月天的uv==》月的uv==》總的uv