Hive the data and takes a specified piece of data __hadoop

hive data, and take one of them as neededData case:

Name ADX tran_id cost TS
CK 5 125.168.10.0 33.00 1407234660
CK 5 187.18.99.00 33.32 1407234661
CK 5 125.168.10.0 33.24 1407234661

Only the first two lines of records, because the third line of the tran_id and the first line of repetition, so you need to repeat the last line of the row to remove.

Programme I:

Selectt1.tran_id,t2.name,t2.cost

From (selectdistinct tran_id from table) T1

Join table T2 ont1.tran_id=t2.tran_id

Analysis:
If you use distinct, you need to put tran_id in the first column, the data found is very unfriendly.

Programme II:

select*

From

Select *,row_number () over (Partitionby Tran_idorder by timestamp ASC) num from table

) T

Wheret.num=1;

Analysis:

Row_number () over (partition by Tran_idorder by timestamp desc) num takes num=1
The meaning is to group by TRAN_ID first, and in descending order within the group, the value computed by the Row_number () function represents the sequential number (which is contiguous and unique within a group) of a tran_id group timestamp.
So finally go directly to each group within the first (Num=1) can be.

Ps:

Basic usage of row_number () over function
Syntax: Row_number () Over (PARTITION by column)
Simply say Row_number () starts with 1 and returns a number for each grouped record, where the Row_number () over (order by XLH DESC) returns a sequence number for each XLH record that is in descending order after the XLH column.
Example:
XLH row_num
1700 1
1500 2
1085 3
710 4

Row_number () over (PARTITION by COL1 Order by COL2) is grouped according to COL1, sorted internally by COL2, and the value of this function represents the sequential number of each group's internal sort (the number is contiguous and unique within the group).

Instance:

Data is displayed as
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: According to the division, show the salary level of each department
Expected results:
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
SQL script:
SELECT *, Row_number () over (partition by Deptidorder by salary desc) rank from employee