Hive common window functions

1, Overview

1. Definition

Window functions are a special set of functions

  • Scan multiple input lines to calculate each output value, and generate a row structure for each row of data
  • Complex calculation and aggregation can be realized through window functions
  • By function, it can be divided into: sequence (sorting), aggregation and analysis

2. Grammar

function (arg1,..., arg n) over ([partition by <...>] [order by <....>] [<window_clause>])
  • partition by is similar to group by. If it is not specified, the entire result set will be used
  • The window can be defined only after the order by clause is specified
  • Multiple window functions can be used at the same time
  • The calculation result of the filter function must be outside the first layer

3. Presentation data

Create employee_contract table
Its fields are as follows:

+---------------+-----------------------------+----------+--+
|   col_name    |          data_type          | comment  |
+---------------+-----------------------------+----------+--+
| name          | string                      |          |
| work_place    | array<string>               |          |
| sex_age       | struct<sex:string,age:int>  |          |
| skills_score  | map<string,int>             |          |
| depart_title  | map<string,array<string>>   |          |
+---------------+-----------------------------+----------+--+

The data in the table are as follows:

+-------------------------+-------------------------------+----------------------------+---------------------------------+----------------------------------------+--+
| employee_external.name  | employee_external.work_place  | employee_external.sex_age  | employee_external.skills_score  |     employee_external.depart_title     |
+-------------------------+-------------------------------+----------------------------+---------------------------------+----------------------------------------+--+
| Michael                 | ["Montreal","Toronto"]        | {"sex":"Male","age":30}    | {"DB":80}                       | {"Product":["Developer","Lead"]}       |
| Will                    | ["Montreal"]                  | {"sex":"Male","age":35}    | {"Perl":85}                     | {"Product":["Lead"],"Test":["Lead"]}   |
| Shelley                 | ["New York"]                  | {"sex":"Female","age":27}  | {"Python":80}                   | {"Test":["Lead"],"COE":["Architect"]}  |
| Lucy                    | ["Vancouver"]                 | {"sex":"Female","age":57}  | {"Sales":89,"HR":94}            | {"Sales":["Lead"]}                     |
+-------------------------+-------------------------------+----------------------------+---------------------------------+----------------------------------------+--+

2, Window function sequence

1,row_number()

Output different serial numbers for all values, and the serial numbers are unique and continuous

(1) Rank the salary of all employees, from low to high, for all employees

select name,dept_num,salary,
row_number() over(order by salary) as rn
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-----+--+
|   name   | dept_num  | salary  | rn  |
+----------+-----------+---------+-----+--+
| Wendy    | 1000      | 4000    | 1   |
| Will     | 1000      | 4000    | 2   |
| Lily     | 1001      | 5000    | 3   |
| Michael  | 1000      | 5000    | 4   |
| Yun      | 1002      | 5500    | 5   |
| Lucy     | 1000      | 5500    | 6   |
| Jess     | 1001      | 6000    | 7   |
| Mike     | 1001      | 6400    | 8   |
| Steven   | 1000      | 6400    | 9   |
| Wei      | 1002      | 7000    | 10  |
| Richard  | 1002      | 8000    | 11  |
+----------+-----------+---------+-----+--+

(2) Sort each employee's salary by Department

select name,dept_num,salary,
row_number() over(partition by dept_num order by salary) as rn
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-----+--+
|   name   | dept_num  | salary  | rn  |
+----------+-----------+---------+-----+--+
| Yun      | 1002      | 5500    | 1   |
| Wei      | 1002      | 7000    | 2   |
| Richard  | 1002      | 8000    | 3   |
| Wendy    | 1000      | 4000    | 1   |
| Will     | 1000      | 4000    | 2   |
| Michael  | 1000      | 5000    | 3   |
| Lucy     | 1000      | 5500    | 4   |
| Steven   | 1000      | 6400    | 5   |
| Lily     | 1001      | 5000    | 1   |
| Jess     | 1001      | 6000    | 2   |
| Mike     | 1001      | 6400    | 3   |
+----------+-----------+---------+-----+--+

2,rank()

For the same value, output the same sequence number, and skip the next sequence number (1,1,3)

(1) Rank the salary of all employees, from low to high, for all employees

select name,dept_num,salary,
rank() over(order by salary) as rn
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-----+--+
|   name   | dept_num  | salary  | rn  |
+----------+-----------+---------+-----+--+
| Wendy    | 1000      | 4000    | 1   |
| Will     | 1000      | 4000    | 1   |
| Lily     | 1001      | 5000    | 3   |
| Michael  | 1000      | 5000    | 3   |
| Yun      | 1002      | 5500    | 5   |
| Lucy     | 1000      | 5500    | 5   |
| Jess     | 1001      | 6000    | 7   |
| Mike     | 1001      | 6400    | 8   |
| Steven   | 1000      | 6400    | 8   |
| Wei      | 1002      | 7000    | 10  |
| Richard  | 1002      | 8000    | 11  |
+----------+-----------+---------+-----+--+

3,dense_rank()

For the same value, the same serial number is output, and the next serial number is continuous (1,1,2)

(1) Rank the salary of all employees, from low to high, for all employees

select name,dept_num,salary,
dense_rank() over(order by salary) as rn
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-----+--+
|   name   | dept_num  | salary  | rn  |
+----------+-----------+---------+-----+--+
| Wendy    | 1000      | 4000    | 1   |
| Will     | 1000      | 4000    | 1   |
| Lily     | 1001      | 5000    | 2   |
| Michael  | 1000      | 5000    | 2   |
| Yun      | 1002      | 5500    | 3   |
| Lucy     | 1000      | 5500    | 3   |
| Jess     | 1001      | 6000    | 4   |
| Mike     | 1001      | 6400    | 5   |
| Steven   | 1000      | 6400    | 5   |
| Wei      | 1002      | 7000    | 6   |
| Richard  | 1002      | 8000    | 7   |
+----------+-----------+---------+-----+--+

(2) Group by department to get the employees with the lowest salary in each department

select name,dept_num,salary from
(select name,dept_num,salary,
dense_rank() over(partition by dept_num order by salary) as rn
from employee_contract) t
where t.rn=1;

The operation results are as follows:

+--------+-----------+---------+--+
|  name  | dept_num  | salary  |
+--------+-----------+---------+--+
| Yun    | 1002      | 5500    |
| Wendy  | 1000      | 4000    |
| Will   | 1000      | 4000    |
| Lily   | 1001      | 5000    |
+--------+-----------+---------+--+

4,ntile(n)

The ordered data set is evenly allocated to n buckets, the bucket number is allocated to each row, and the first or last n parts of the data are selected according to the bucket number

(1) Bucket query

select name,dept_num,salary,
ntile(2) over(partition by dept_num order by salary)
as nlite
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+--------+--+
|   name   | dept_num  | salary  | nlite  |
+----------+-----------+---------+--------+--+
| Yun      | 1002      | 5500    | 1      |
| Wei      | 1002      | 7000    | 1      |
| Richard  | 1002      | 8000    | 2      |
| Wendy    | 1000      | 4000    | 1      |
| Will     | 1000      | 4000    | 1      |
| Michael  | 1000      | 5000    | 1      |
| Lucy     | 1000      | 5500    | 2      |
| Steven   | 1000      | 6400    | 2      |
| Lily     | 1001      | 5000    | 1      |
| Jess     | 1001      | 6000    | 1      |
| Mike     | 1001      | 6400    | 2      |
+----------+-----------+---------+--------+--+

5,percent_rank()

(current ranking - 1) / (total number of rows - 1), the percentage ranking of values relative to a group of values, that is, the top percentage

(1) Query salary ranking percentage

select name,dept_num,salary,
percent_rank() over(order by salary)
as pr
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+------+--+
|   name   | dept_num  | salary  |  pr  |
+----------+-----------+---------+------+--+
| Wendy    | 1000      | 4000    | 0.0  |
| Will     | 1000      | 4000    | 0.0  |
| Lily     | 1001      | 5000    | 0.2  |
| Michael  | 1000      | 5000    | 0.2  |
| Yun      | 1002      | 5500    | 0.4  |
| Lucy     | 1000      | 5500    | 0.4  |
| Jess     | 1001      | 6000    | 0.6  |
| Mike     | 1001      | 6400    | 0.7  |
| Steven   | 1000      | 6400    | 0.7  |
| Wei      | 1002      | 7000    | 0.9  |
| Richard  | 1002      | 8000    | 1.0  |
+----------+-----------+---------+------+--+

3, Window function - aggregate

1,count()

Count, can be used with distinct

(1) Count the number of people in each department

select name,dept_num,salary,
count(*) over(partition by dept_num) as rc
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-----+--+
|   name   | dept_num  | salary  | rc  |
+----------+-----------+---------+-----+--+
| Lucy     | 1000      | 5500    | 5   |
| Steven   | 1000      | 6400    | 5   |
| Wendy    | 1000      | 4000    | 5   |
| Will     | 1000      | 4000    | 5   |
| Michael  | 1000      | 5000    | 5   |
| Mike     | 1001      | 6400    | 3   |
| Jess     | 1001      | 6000    | 3   |
| Lily     | 1001      | 5000    | 3   |
| Richard  | 1002      | 8000    | 3   |
| Yun      | 1002      | 5500    | 3   |
| Wei      | 1002      | 7000    | 3   |
+----------+-----------+---------+-----+--+

2,sum()

Sum

(1) The sum of the wages of each department

select name,dept_num,salary,
sum(salary) over(partition by dept_num order by salary) as sum_salary
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------------+--+
|   name   | dept_num  | salary  | sum_salary  |
+----------+-----------+---------+-------------+--+
| Wendy    | 1000      | 4000    | 8000        |
| Will     | 1000      | 4000    | 8000        |
| Michael  | 1000      | 5000    | 13000       |
| Lucy     | 1000      | 5500    | 18500       |
| Steven   | 1000      | 6400    | 24900       |
| Lily     | 1001      | 5000    | 5000        |
| Jess     | 1001      | 6000    | 11000       |
| Mike     | 1001      | 6400    | 17400       |
| Yun      | 1002      | 5500    | 5500        |
| Wei      | 1002      | 7000    | 12500       |
| Richard  | 1002      | 8000    | 20500       |
+----------+-----------+---------+-------------+--+

(2) Ask for the sum of everyone's wages

select name,dept_num,salary,
sum(salary) over(order by salary) as sum_salary
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------------+--+
|   name   | dept_num  | salary  | sum_salary  |
+----------+-----------+---------+-------------+--+
| Wendy    | 1000      | 4000    | 8000        |
| Will     | 1000      | 4000    | 8000        |
| Lily     | 1001      | 5000    | 18000       |
| Michael  | 1000      | 5000    | 18000       |
| Yun      | 1002      | 5500    | 29000       |
| Lucy     | 1000      | 5500    | 29000       |
| Jess     | 1001      | 6000    | 35000       |
| Mike     | 1001      | 6400    | 47800       |
| Steven   | 1000      | 6400    | 47800       |
| Wei      | 1002      | 7000    | 54800       |
| Richard  | 1002      | 8000    | 62800       |
+----------+-----------+---------+-------------+--+

3,avg(),max(),min()

Average, maximum, minimum

select name,dept_num,salary,
avg(salary) over(partition by dept_num) as avgDept,
min(salary) over(partition by dept_num) as minDept,
max(salary) over(partition by dept_num) as maxDept
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+--------------------+----------+----------+--+
|   name   | dept_num  | salary  |      avgdept       | mindept  | maxdept  |
+----------+-----------+---------+--------------------+----------+----------+--+
| Lucy     | 1000      | 5500    | 4980.0             | 4000     | 6400     |
| Steven   | 1000      | 6400    | 4980.0             | 4000     | 6400     |
| Wendy    | 1000      | 4000    | 4980.0             | 4000     | 6400     |
| Will     | 1000      | 4000    | 4980.0             | 4000     | 6400     |
| Michael  | 1000      | 5000    | 4980.0             | 4000     | 6400     |
| Mike     | 1001      | 6400    | 5800.0             | 5000     | 6400     |
| Jess     | 1001      | 6000    | 5800.0             | 5000     | 6400     |
| Lily     | 1001      | 5000    | 5800.0             | 5000     | 6400     |
| Richard  | 1002      | 8000    | 6833.333333333333  | 5500     | 8000     |
| Yun      | 1002      | 5500    | 6833.333333333333  | 5500     | 8000     |
| Wei      | 1002      | 7000    | 6833.333333333333  | 5500     | 8000     |
+----------+-----------+---------+--------------------+----------+----------+--+

4, Window function - Analysis

1,cume_dist

Rows less than or equal to the current value / total rows in the group

(1) Find the salary ranking ratio of everyone in each department

select name,dept_num,salary,
cume_dist() over(partition by dept_num
order by salary) as cd
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+---------------------+--+
|   name   | dept_num  | salary  |         cd          |
+----------+-----------+---------+---------------------+--+
| Wendy    | 1000      | 4000    | 0.4                 |
| Will     | 1000      | 4000    | 0.4                 |
| Michael  | 1000      | 5000    | 0.6                 |
| Lucy     | 1000      | 5500    | 0.8                 |
| Steven   | 1000      | 6400    | 1.0                 |
| Lily     | 1001      | 5000    | 0.3333333333333333  |
| Jess     | 1001      | 6000    | 0.6666666666666666  |
| Mike     | 1001      | 6400    | 1.0                 |
| Yun      | 1002      | 5500    | 0.3333333333333333  |
| Wei      | 1002      | 7000    | 0.6666666666666666  |
| Richard  | 1002      | 8000    | 1.0                 |
+----------+-----------+---------+---------------------+--+

2,lead´╝Ćlag(col,n)

The value of the nth row before / after a column (n optional, the default is 1)

(1) Calculate the salary of the next employee after the current employee by department grouping and employee salary sorting. The last ranking of each department is represented by the default value of NULL

select name,dept_num,salary,
lead(salary,1) over(partition by dept_num order by salary) as lead
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------+--+
|   name   | dept_num  | salary  | lead  |
+----------+-----------+---------+-------+--+
| Wendy    | 1000      | 4000    | 4000  |
| Will     | 1000      | 4000    | 5000  |
| Michael  | 1000      | 5000    | 5500  |
| Lucy     | 1000      | 5500    | 6400  |
| Steven   | 1000      | 6400    | NULL  |
| Lily     | 1001      | 5000    | 6000  |
| Jess     | 1001      | 6000    | 6400  |
| Mike     | 1001      | 6400    | NULL  |
| Yun      | 1002      | 5500    | 7000  |
| Wei      | 1002      | 7000    | 8000  |
| Richard  | 1002      | 8000    | NULL  |
+----------+-----------+---------+-------+--+

(2) Calculate the salary of the next employee after the current employee by department grouping and employee salary sorting. The last one in each department ranking is represented by a specified 0

select name,dept_num,salary,
lead(salary,1,0) over(partition by dept_num order by salary) as lead
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------+--+
|   name   | dept_num  | salary  | lead  |
+----------+-----------+---------+-------+--+
| Wendy    | 1000      | 4000    | 4000  |
| Will     | 1000      | 4000    | 5000  |
| Michael  | 1000      | 5000    | 5500  |
| Lucy     | 1000      | 5500    | 6400  |
| Steven   | 1000      | 6400    | 0     |
| Lily     | 1001      | 5000    | 6000  |
| Jess     | 1001      | 6000    | 6400  |
| Mike     | 1001      | 6400    | 0     |
| Yun      | 1002      | 5500    | 7000  |
| Wei      | 1002      | 7000    | 8000  |
| Richard  | 1002      | 8000    | 0     |
+----------+-----------+---------+-------+--+

(3) Calculate the salary of the previous employee of the current employee by department grouping and employee salary sorting. The first employee in each department is represented by the default value of NULL

select name,dept_num,salary,
lag(salary,1) over(partition by dept_num order by salary) as lag
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------+--+
|   name   | dept_num  | salary  |  lag  |
+----------+-----------+---------+-------+--+
| Wendy    | 1000      | 4000    | NULL  |
| Will     | 1000      | 4000    | 4000  |
| Michael  | 1000      | 5000    | 4000  |
| Lucy     | 1000      | 5500    | 5000  |
| Steven   | 1000      | 6400    | 5500  |
| Lily     | 1001      | 5000    | NULL  |
| Jess     | 1001      | 6000    | 5000  |
| Mike     | 1001      | 6400    | 6000  |
| Yun      | 1002      | 5500    | NULL  |
| Wei      | 1002      | 7000    | 5500  |
| Richard  | 1002      | 8000    | 7000  |
+----------+-----------+---------+-------+--+

(4) Calculate the salary of the previous employee of the current employee by department grouping and employee salary sorting. The first employee in each department is represented by the specified value of 0

select name,dept_num,salary,
lag(salary,1,0) over(partition by dept_num order by salary) as lag
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------+--+
|   name   | dept_num  | salary  |  lag  |
+----------+-----------+---------+-------+--+
| Wendy    | 1000      | 4000    | 0     |
| Will     | 1000      | 4000    | 4000  |
| Michael  | 1000      | 5000    | 4000  |
| Lucy     | 1000      | 5500    | 5000  |
| Steven   | 1000      | 6400    | 5500  |
| Lily     | 1001      | 5000    | 0     |
| Jess     | 1001      | 6000    | 5000  |
| Mike     | 1001      | 6400    | 6000  |
| Yun      | 1002      | 5500    | 0     |
| Wei      | 1002      | 7000    | 5500  |
| Richard  | 1002      | 8000    | 7000  |
+----------+-----------+---------+-------+--+

3,firsvt_value,last_value

For the first and last value of this column so far

select name,dept_num,salary,
first_value(salary) over(partition by dept_num order by salary) as fv,
last_value(salary) over(partition by dept_num order by salary) as lv
from employee_contract;

The operation results are as follows:

+----------+-----------+---------+-------+-------+--+
|   name   | dept_num  | salary  |  fv   |  lv   |
+----------+-----------+---------+-------+-------+--+
| Wendy    | 1000      | 4000    | 4000  | 4000  |
| Will     | 1000      | 4000    | 4000  | 4000  |
| Michael  | 1000      | 5000    | 4000  | 5000  |
| Lucy     | 1000      | 5500    | 4000  | 5500  |
| Steven   | 1000      | 6400    | 4000  | 6400  |
| Lily     | 1001      | 5000    | 5000  | 5000  |
| Jess     | 1001      | 6000    | 5000  | 6000  |
| Mike     | 1001      | 6400    | 5000  | 6400  |
| Yun      | 1002      | 5500    | 5500  | 5500  |
| Wei      | 1002      | 7000    | 5500  | 7000  |
| Richard  | 1002      | 8000    | 5500  | 8000  |
+----------+-----------+---------+-------+-------+--+

5, Window function - window clause

1. Meaning

The window clause is described by the [< window_clause >] clause

  • Used to further subdivide the results and apply analysis functions
  • rank,ntile,dense_rank,cume_dist,percent_rank, lead, lag and row_ The number function is not supported with window clauses
  • Two types of window clauses are supported
    • Row type window
    • Range type window

2. Row window

(1) A window determined by the line number before or after the current line

(2) Syntax:

rows between <start_expr> and <end_expr>

(3)<start_ Expr > value

Value significance
unbounded preceding Window start position (group first line)
current row Current row
n preceding/following n lines before / after current line

(4)<end_ Expr > can be the following values

Value significance
unbounded following Window end position (last line of grouping)
current row Current row
n preceding/following n lines before / after current line

Example:

select
name, dept_num as dept, salary as sal,
max(salary) over (partition by dept_num order by name rows between 2 preceding and current row) win1,
max(salary) over (partition by dept_num order by name rows between 2 preceding and unbounded following) win2,
max(salary) over (partition by dept_num order by name rows between 1 preceding and 2 following) win3,
max(salary) over (partition by dept_num order by name rows between 2 preceding and 1 preceding) win4,
max(salary) over (partition by dept_num order by name rows between 1 following and 2 following) win5,
max(salary) over (partition by dept_num order by name rows between current row and current row) win6,
max(salary) over (partition by dept_num order by name rows between current row and 1 following) win7,
max(salary) over (partition by dept_num order by name rows between current row and unbounded following) win8,
max(salary) over (partition by dept_num order by name rows between unbounded preceding and current row) win9,
max(salary) over (partition by dept_num order by name rows between unbounded preceding and 1 following) win10,
max(salary) over (partition by dept_num order by name rows between unbounded preceding and unbounded following) win11,
max(salary) over (partition by dept_num order by name rows 2 preceding) win12
from employee_contract  order by dept, name;

The operation results are as follows:

+----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+--------+--+
|   name   | dept  |  sal  | win1  | win2  | win3  | win4  | win5  | win6  | win7  | win8  | win9  | win10  | win11  | win12  |
+----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+--------+--+
| Lucy     | 1000  | 5500  | 5500  | 6400  | 6400  | NULL  | 6400  | 5500  | 5500  | 6400  | 5500  | 5500   | 6400   | 5500   |
| Michael  | 1000  | 5000  | 5500  | 6400  | 6400  | 5500  | 6400  | 5000  | 6400  | 6400  | 5500  | 6400   | 6400   | 5500   |
| Steven   | 1000  | 6400  | 6400  | 6400  | 6400  | 5500  | 4000  | 6400  | 6400  | 6400  | 6400  | 6400   | 6400   | 6400   |
| Wendy    | 1000  | 4000  | 6400  | 6400  | 6400  | 6400  | 4000  | 4000  | 4000  | 4000  | 6400  | 6400   | 6400   | 6400   |
| Will     | 1000  | 4000  | 6400  | 6400  | 4000  | 6400  | NULL  | 4000  | 4000  | 4000  | 6400  | 6400   | 6400   | 6400   |
| Jess     | 1001  | 6000  | 6000  | 6400  | 6400  | NULL  | 6400  | 6000  | 6000  | 6400  | 6000  | 6000   | 6400   | 6000   |
| Lily     | 1001  | 5000  | 6000  | 6400  | 6400  | 6000  | 6400  | 5000  | 6400  | 6400  | 6000  | 6400   | 6400   | 6000   |
| Mike     | 1001  | 6400  | 6400  | 6400  | 6400  | 6000  | NULL  | 6400  | 6400  | 6400  | 6400  | 6400   | 6400   | 6400   |
| Richard  | 1002  | 8000  | 8000  | 8000  | 8000  | NULL  | 7000  | 8000  | 8000  | 8000  | 8000  | 8000   | 8000   | 8000   |
| Wei      | 1002  | 7000  | 8000  | 8000  | 8000  | 8000  | 5500  | 7000  | 7000  | 7000  | 8000  | 8000   | 8000   | 8000   |
| Yun      | 1002  | 5500  | 8000  | 8000  | 7000  | 8000  | NULL  | 5500  | 5500  | 5500  | 8000  | 8000   | 8000   | 8000   |
+----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+--------+--+

3. Range window

The range window is used to take the rows whose values in the group are within the specified range

  • The range value / range must be numeric or date type
  • Currently, only one order by column is supported

Example:

select name, dept_num as dept, salary as sal,
max(salary) over (partition by dept_num order by name rows between 2 preceding and current row) win1,
salary - 1000 as sal_r_start,salary as sal_r_end,
max(salary) over (partition by dept_num
order by name range between 1000 preceding and current row) win13
from employee_contract order by dept, name;

The operation results are as follows:

+----------+-------+-------+-------+--------------+------------+--------+--+
|   name   | dept  |  sal  | win1  | sal_r_start  | sal_r_end  | win13  |
+----------+-------+-------+-------+--------------+------------+--------+--+
| Lucy     | 1000  | 5500  | 5500  | 4500         | 5500       | 5500   |
| Michael  | 1000  | 5000  | 5500  | 4000         | 5000       | 5000   |
| Steven   | 1000  | 6400  | 6400  | 5400         | 6400       | 6400   |
| Wendy    | 1000  | 4000  | 6400  | 3000         | 4000       | 4000   |
| Will     | 1000  | 4000  | 6400  | 3000         | 4000       | 4000   |
| Jess     | 1001  | 6000  | 6000  | 5000         | 6000       | 6000   |
| Lily     | 1001  | 5000  | 6000  | 4000         | 5000       | 5000   |
| Mike     | 1001  | 6400  | 6400  | 5400         | 6400       | 6400   |
| Richard  | 1002  | 8000  | 8000  | 7000         | 8000       | 8000   |
| Wei      | 1002  | 7000  | 8000  | 6000         | 7000       | 7000   |
| Yun      | 1002  | 5500  | 8000  | 4500         | 5500       | 5500   |
+----------+-------+-------+-------+--------------+------------+--------+--+

Tags: Big Data hive

Posted by fishdish on Mon, 02 May 2022 05:28:04 +0300