Hive Analysis Functions

Analysis functions supported by hive:

General Summary : http://www.07net01.com/linux/HIVE_chuangkoujifenxihanshu_yingyongchangjing_532180_1373989446.html

********************************************************************************************************

usage of Rank over: http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html

Raw data:

a b c
----------- ----------- ----
1 3 E
2 4 A
3 2 D
3 5 B
4 2 C
2 4 B

Requirements: Group A, b to rank in each group.

Select *,rank () over (partition by a, a, order by B) from Xxxx_tab;

The data are:

a b c rank
----------- ----------- ---- --------------------
1 3 E 1
2 4 A 1
2 4 B 1
3 2 D 1
3 5 B 2
4 2 C 1

Divided into 5 groups, line 2nd and line 3rd is a group, and each row is a group. Rank B in Group 2nd and line 3rd and listed as 1

*************************************************************************************************************** ************************

row_number () over usage : http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

Raw data:

Empid DeptID Salary

1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00

Requirements: Displays the salary level of each department according to the Department group

SQL script:

SELECT *, Row_number () over (partition by DeptID ORDER by salary desc) rank from employee

Expected Result:

Empid DeptID Salary Rank
----------- ----------- --------------------------------------- --------------------
1 10 5500.00 1
2 10 4500.00 2
4 20 4800.00 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8 50 6500.00 2

********************************************************************************************

Use of window functions : http://blog.csdn.net/cnham/article/details/6101199

Select Month,sum (tot_sales) month_sales, sum (SUM (tot_sales)) over (order by month rows between unbounded preceding and Unbo unded following) Total_sales from orders group by month.

Hive Analysis Functions