hive 聚合函数 count 的区别

针对于这个问题，我们来实际操作得出结论：

建表：

CREATE TABLE test_a(
id int,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

生成7000003 条数据，其中7000000万条是null值，3条是有数据的

1   name1
2   name2
3   name3
\N  name1
......
\N  name7000000

用 count(*) 查询

hive (panniu)> select count(*) from test_a;
-- 过程省略
OK
7000003
-- 测了三次分别是
Time taken: 12.339 seconds, Fetched: 1 row(s)
Time taken: 11.018 seconds, Fetched: 1 row(s)
Time taken: 11.393 seconds, Fetched: 1 row(s)
-- 查看执行计划
hive (panniu)> explain select count(*) from test_a;
OK
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: test_a
            Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
            Select Operator
              Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
              Group By Operator
                aggregations: count()  -- 这有区别
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

用 count(1) 查询

hive (panniu)> select count(*) from test_a;
-- 过程省略
OK
7000003
-- 测了三次分别是
Time taken: 10.992 seconds, Fetched: 1 row(s)
Time taken: 12.217 seconds, Fetched: 1 row(s)
Time taken: 11.268 seconds, Fetched: 1 row(s)
-- 查看执行计划
hive (panniu)> explain select count(1) from test_a;
OK
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: test_a
            Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
            Select Operator
              Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: COMPLETE
              Group By Operator
                aggregations: count(1)  -- 这有区别
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

用count(id) 查询

hive (panniu)> select count(id) from test_a;
-- 过程省略
OK
3
-- 测了三次分别是
Time taken: 13.405 seconds, Fetched: 1 row(s)
Time taken: 12.248 seconds, Fetched: 1 row(s)
Time taken: 13.341 seconds, Fetched: 1 row(s)
-- 查看执行计划
hive (panniu)> explain select count(id) from test_a;
OK
STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: test_a
            Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int) 
              outputColumnNames: id  -- 查询id字段
              Statistics: Num rows: 7000003 Data size: 96888908 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count(id) -- 这有区别
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: bigint)
      Reduce Operator Tree:
        Group By Operator
          aggregations: count(VALUE._col0)
          mode: mergepartial
          outputColumnNames: _col0
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
          File Output Operator
            compressed: false
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

结论：

1）count(*) 和 count(1) 统计行数包含 null值；count(id) 统计行数不包含 null 值。

2）通过执行计划看不出三个有什么根本区别，只能在测试速度上做评判，评判如下：count(1) 比 count(*) 稍快， count(id) 比前两个稍慢。