資料彙總和採樣

標籤：跳過   ota   彙總   分析   組合   erro   case when   cer   net   

基礎彙總 -- 常見的彙總函式 count, sum, avg, max, min-- 彙總函式不允許嵌套 如：avg(count(*)) error!set hive.map.aggr=true;      -- mapper端預彙總，提高效能，但消耗較多記憶體#注意：不能直接select沒有出現在group by從句中的欄位，否則報錯 why?select name, gender_age.gender, count(*) as row_cnt  -- error!from employeegroup by gender_age.gender;如何select沒有出現在group by中的欄位？    1)使用collect_set函數；select gender_age.gender, collect_set(gender_age.age)[0] as random_age, count(*) as row_cntfrom employeegroup by gender_age.gender;-- select中使用多個彙總函式select gender_age.gender, avg(gender_age.age) as avg_age, count(*) as row_cntfrom employeegroup by gender_age.gender;    2)流量分析函數(不需要 group by)-- 彙總函式和條件函數連用-- 求男性的平均年齡select sum(case when gender_age.gender = "Male" then gender_age.age else 0 end) /sum(case when gender_age.gender = "Male" then 1 else null end) as man_age_avg    -- 0 == nullfrom employee;select avg(gender_age.age) as avg_agefrom employeewhere gender_age.gender = ‘Male‘group by gender_age.gender;# 第一種方法效率要比第二種高-- 求age_sum, woman_age_sumselect sum(coalesce(gender_age.age, 0)) as age_sum,sum(if(gender_age.gender = "Female", gender_age.age, 0)) as woman_age_sumfrom employee;-- 彙總函式中使用distinct -- 統計員工中有幾種性別，幾種不同的姓名select count(distinct gender_age.gender) as sex_uni_cnt,count(distinct name) as name_uni_cntfrom employee;#注意：count和distinct一起使用時，使用一個reducer，會降低效能，解決方案：分開使用select count(*) as sex_uni_cntfrom (select distinct gender_age.gender from employee) a;#注意：彙總時若遇到為null值的欄位，會忽略該行，如：-- 建立含有null行的表create table t as select * from (select employee_id - 99 as val1,employee_id - 98 as val2 from employee_hrwhere employee_id <= 101union allselect null as val1, 2 as val2 from employee_hrwhere employee_id = 100) a;+---------+---------+--+| t.val1  | t.val2  |+---------+---------+--+| 1       | 2       || NULL    | 2       || 2       | 3       |+---------+---------+--+select sum(val1 + val2) from t;     --第二行會被忽略，解決方案：select sum(coalesce(val1, 0) + val2) from t;進階彙總-- grouping sets:用union all串連多個group by的結果集，在job中的某一階段完成更高效內層的grouping sets定義在每個union all中的group by要實現的資料。select name, work_place[0] as main_place, count(employee_id) as emp_id_cntfrom employee_idgroup by name, work_place[0] grouping sets( (name, work_place[0]) );  -- 1個參數，毋需union all<==>select name, work_place[0] as main_place, count(employee_id) as emp_id_cntfrom employee_idgroup by name, work_place[0];select name, work_place[0] as main_place, count(employee_id) as emp_id_cntfrom employee_idgroup by name, work_place[0] grouping sets((name), (work_place[0])); -- union all串連兩個group by結果集<==>select name, null as main_place, count(employee_id) as emp_id_cntfrom employee_idgroup by nameunion allselect null as name, work_place[0] as main_place, count(employee_id) as employee_id_cntfrom employee_idgroup by work_place[0];select name, work_place[0] as main_place, count(employee_id) as employee_id_cntfrom employee_idgroup by name, work_place[0] grouping sets((name, work_place[0]), name, work_place[0], ());  -- () 不分組#煮魚：grouping sets的內容為表或記錄的別名所指向的列時，可能會出現問題，官方已修複。如：select gender_age.gender, gender_age.age,count(name) as name_uni_cntfrom employeegroup by gender_age.gender, gender_age.age grouping sets((gender_age.gender, gender_age.age));-- 匯總 rollup ：group by從句的擴充，其效率高，查詢開銷最小。 建立n + 1級彙總，n為group by中的分組列group by a, b, c with rollup <==> grouping set((a, b, c), (a, b), (a), ())-- cube  對分組欄位所有可能的組合進行匯總group by a, b, c with cube -- grouping_id函數：顯示欄位是否彙總的位向量的10進位形式select grouping_id, bin(cast(grouping_id as bigint)) as bit_vector,name, start_date, count(employee_id) emp_id_contfrom employee_hrgroup by start_date, name with cube order by start_date;-- having 對組的資料集進行過濾,過濾掉一些不需要的組將員工按年齡分組，統計:人數<=3的年齡組select gender_age.age, count(distinct employee_id) as cntfrom employee_idgroup by gender_age.age     having cnt <= 1;<==>select a.age, a.cntfrom (select gender_age.age as age, count(distinct employee_id) as cnt    -- 內查詢中的欄位一定要起個別名from employee_idgroup by gender_age.age) awhere a.cnt <= 3;  -- where中不支援UDAF函數，要起個別名??????分析函數比group by更靈活，更實用，更容易， 更有力，其在指定視窗內統計Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_clause>])Function(arg1,..., argn) :    標準彙總函式：SUM, COUNT and AVG支援‘distinct‘(Hive 2.1.0),能夠後跟order by或視窗從句(Hive 2.2.0)        如查詢出不同的部門中有多少人：count(distict employee_id) partition by apart    分析函數：        row_number: 根據partition by和order by給元素編號，每組從1開始        排序函數(如下)以及Lead， log在over中使用時不支援開窗：            ntile():將排序好的資料集分桶，並且給每行分配一個合適的桶號，適用於將資料等分？            rank():按照排序欄位在組內編號 如:升序的分數 60 60 80 90 -> 1 1 3 4     從1開始編號，相同的值編號相同            dense_rank():組內編號       如:60 60 80 90 -> 1 1 2 3                 cume_dist():升序時，定義為值小於或等於當前行值的行數除以所在分區或查詢結果集的總行數            percent_rank():對分組欄位按照rank()進行編號,只不過從零開始編號，求百分比：用它們的編號號除以最大的編號， 如：60 60 80 90 -> 0 0 1 2 -> 0/2 0/2 1/2 2/2    視窗函數：        lead(value[, offset[, default]]):訪問視窗(組)內指定欄位的行向下前進offset行的資料        lag(value[, offset[, default]]):訪問視窗(組)內指定列的行向上滯後offset行的資料        first_value(value[, boolea]):從有序的結果集中返回第一個值, boolea為true時跳過null值        last_value(value[, boolea]):從有序的結果集中返回最後一個值over:    標準的彙總函式(Hive 2.1.0)    partition by :基本類型的一個或多個欄位    order by：任意類型的一個或多個欄位    window_clause：分區內開窗        作用：細顆粒度        類型：          ??：升序時，star_expr必須小於end_expr,否則整列值為null或報異常          行類型視窗 範圍類型視窗          (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)          (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)          (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING   -- 當前行下方的某個範圍內開窗                [num]告訴視窗函數執行時參考的記錄數,會將目前記錄之前或之後的num條記錄納入統計範圍內，                [unbounded] 將目前記錄之前或之後的所有記錄納入統計範圍內          兩視窗的不同點：range僅支援一個排序欄位，且此欄位只能為數字或日期。          若定義了order by,缺失視窗從句。預設為：range between unbounded preceding and current row 視窗為分區的起始行到當前行          order by和視窗從句都缺失。預設為：rows between unbounded preceding and unbounded following 視窗為當前分區的所有行select name, dept_num, salary,count(*) over (partition by dept_num) as row_cnt,  -- 分區內求和sum(salary) over (partition by dept_num order by dept_num) as deptTotal,   -- 分區內資料累加求和sum(salary) over (order by dept_num) as runingTotol1,   -- 表內資料累加求和sum(salary) over (order by dept_num, name rows unbounded preceding) as runingTotol2  -- dept_num全域排序，name局部排序from employee_contract;select name, dept_num, salary,count(*) over (partition by dept_num) as row_cnt,sum(salary) over(partition by dept_num order by dept_num) as deptTotal,  sum(salary) over(order by dept_num) as runingTotol1,   sum(salary) over(order by dept_num, name rows unbounded preceding) as runingTotol2from employee_contractorder by dept_num, name;<==>獨立的視窗從句格式:select name, dept_num, salary,count(*) over row_cnt,sum(salary) over deptTotal,  sum(salary) over runingTotol1,   sum(salary) over runingTotol2from employee_contractorder by dept_num, namewindowrow_cnt as (partition by dept_num),overdeptTotal as (partition by dept_num order by dept_num),runingTotol1 as (order by dept_num),runingTotol2 as (order by dept_num, name rows unbounded preceding);-- 範圍查詢select name, dept_num, salary, start_year,max(salary) over (partition by dept_num order by start_year range between 2 preceding and current row) as win1from (select name, dept_num, salary, year(start_date) as start_yearfrom employee_contract) a;抽樣1. 隨機取樣??select name from employee_iddistribute by rand() sort by rand()limit 5;2. 分桶表抽樣SELECT * FROM <BucketTable_Name> TABLESAMPLE(BUCKET <specified bucket number to sample> OUT OF <total number of buckets> ON [colname|RAND()]) table_alias;select name from employee_id_bucketstablesample(bucket 1 out of 2 on rand()) a;3. 塊抽樣SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;按百分比大小抽樣select name from employee_id_bucketstablesample (20 percent) a;按行數抽樣select name from employee_idtablesample (5 rows) a;按位元組大小抽樣select name from employee_idtablesample (700B) a;

參考

Oracle開發之：視窗函數
LanguageManual WindowingAndAnalytics
PERCENT_RANK
CUME_DIST (Transact-SQL)
《Hive Essentials》
《Programing Hive》
《Practical Hive》

資料彙總和採樣