impala在一个select中执行多个count distinct时会报错,比如执行
会报错
这时有几种方法:
1 使用近似值
1.1 set APPX_COUNT_DISTINCT = true
1.2 count distinct改为ndv,即ndv(column_a)
这两种方法底层实现是一样的,设置APPX_COUNT_DISTINCT会自动将count distinct改写为ndv,ndv全称为(number of distinct values),用到
Cardinality(基数计数),底层实现是类似HLLC(Hyper LogLog Counting)这种概率算法,详见参考;
An aggregate function that returns an approximate value similar to the result of COUNT(DISTINCT )
, the "number of distinct values". It is much faster than the combination of COUNT
and DISTINCT
, and uses a constant amount of memory and thus is less memory-intensive for columns with high cardinality.
2 使用精确值
改写为多个子查询然后join,比如
参考:
ndv
http://impala.apache.org/docs/build/html/topics/impala_ndv.html#ndv
APPX_COUNT_DISTINCT
http://impala.apache.org/docs/build/html/topics/impala_appx_count_distinct.html
其他