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

其他

https://stackoverflow.com/questions/39236076/impala-all-distinct-aggregate-functions-need-to-have-the-same-set-of-parameters

05-11 13:55