Details
Major Description
For standard, no-partial avg result, hive currently returns double as the result type.
hive> desc test; OK d int None Time taken: 0.051 seconds, Fetched: 1 row(s) hive> explain select avg(`d`) from test; ... Reduce Operator Tree: Group By Operator aggregations: expr: avg(VALUE._col0) bucketGroup: false mode: mergepartial outputColumnNames: _col0 Select Operator expressions: expr: _col0 type: double
However, exact types including integers and decimal should yield exact type. Here is what MySQL does:
mysql> desc test; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | b | tinyint(1) | YES | | NULL | | | d | double | YES | | NULL | | | s | varchar(5) | YES | | NULL | | | dd | decimal(5,2) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ mysql> create table test62 as select avg(i) from test; mysql> desc test62; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | avg(i) | decimal(14,4) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 1 row in set (0.00 sec)