function Learning in Postgresql/hive

Some of the functions currently in PostgreSQL are also present in hive, so this is illustrated today with PostgreSQL as an example, which can be applied to hive as well.

1. Create a table

CREATE TABLE Employee (empid int, DeptID int, salary DECIMAL (10, 2));

2. Import data

INSERT into employee VALUES (1, 10, 5500.00); INSERT into Employee VALUES (2, 10, 4500.00); INSERT into Employee VALUES (3, 20, 1900.00); INSERT into Employee VALUES (4, 20, 4800.00);  INSERT into employee VALUES (6, 40, 14500.00); INSERT into employee VALUES (7, 40, 44500.00); INSERT into employee VALUES (10, 40, 44500.00); INSERT into employee VALUES (11, 40, 44501.00); INSERT into employee VALUES (8, 50, 6500.00); INSERT into employee VALUES (9, 50, 7500.00);

3. Application

-the highest-paid select * FROM (select E.*,row_number () over (partition by E.deptid ORDER BY e.salary Desc) rank from employee E ) Eewhere Ee.rank = 1;

--should be rank, for the same salary should be shown out select * FROM (select E.*,rank () over (partition by E.deptid ORDER BY e.salary desc) rank from Empl Oyee e) Eewhere ee.rank = 1;

The difference between--rank and Dense_rank is that the former occupies a bit, and the latter does not occupy a bit--ntile can be regarded as the average allocation of an ordered set of data to the number of expr specified in the bucket, the bucket number assigned to each row. --If the bucket is not evenly distributed, the buckets of the smaller buckets are allocated additional rows, and the maximum number of rows that can be placed in each bucket is 1.  Select E.*, Ntile (3) over (partition by E.deptid ORDER BY e.salary Desc) ntile, rank ()-over (partition by E.deptid order by e.salary desc) rank, Dense_rank () through (partition by E.deptid ORDER BY e.salary Desc) drank, row_number ()-Over (Partit Ion by E.deptid ORDER BY e.salary Desc) rownum, First_value (Salary) through (partition by E.deptid ORDER BY e.salary Desc) FV , Last_value (Salary) over (partition by E.deptid ORDER by e.salary ASC) LV from employee E

Well, that's all, as you can see by example, the application of each function is very clear.

function Learning in Postgresql/hive