Apache Hive Essentials - Second Edition

Window aggregate functions

Using regular aggregate functions in window functions brings more flexibility than GROUP BY, which requires all grouped columns in the select list. Since Hive v2.2.0, DISTINCT has been supported for use with aggregate functions in window functions:

1. Prepare the table and data for demonstration:

      > CREATE TABLE IF NOT EXISTS employee_contract (      > name string,      > dept_num int,      > employee_id int,      > salary int,      > type string,      > start_date date      > )      > ROW FORMAT DELIMITED      > FIELDS TERMINATED BY '|'      > STORED as TEXTFILE;      No rows affected (0.282 seconds)      > LOAD DATD INPATH '/tmp/hivedemo/data/employee_contract.txt'      > OVERWRITE INTO TABLE employee_contract;      No rows affected (0.48 seconds)

2. The regular aggregations are used ...