[
https://issues.apache.org/jira/browse/PHOENIX-418?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16107832#comment-16107832
]
Julian Hyde commented on PHOENIX-418:
-------------------------------------
In CALCITE-1588 [~gian] points out that Oracle, BigQuery and MemSQL support
{{APPROX_COUNT_DISTINCT}}. I also see it in VoltDB.
A quick survey of other databases:
* Vertica has {{APPROXIMATE_COUNT_DISTINCT}}
* Redshift has {{[ APPROXIMATE ] COUNT ( [ DISTINCT | ALL ] * | expression )}}.
* In PostgreSQL you can bolt on your own hyperloglog function, but there
doesn't seem to have a unified approach.
* I don't see anything in DB2 or MySQL
I think that is a sufficient de facto standard to support
{{APPROX_COUNT_DISTINCT}} in Calcite.
Also I think Calcite should support an APPROXIMATE clause. Allowed both as a
clause in the SELECT statement, and also after the aggregate function (but
before the OVER clause, if present). Algorithm and any parameters go inside
parentheses. Examples:
{code}
select count(distinct name)
from person
APPROXIMATE ()
select count(distinct name)
from person
APPROXIMATE (ALGORITHM 'hll')
select count(distinct name)
from person
APPROXIMATE (ALGORITHM 'ABC' WITHIN 10 PERCENT)
select count(distinct name) APPROXIMATE ()
from person
select count(distinct name) APPROXIMATE (ALGORITHM 'hll')
from person
select count(distinct name)
APPROXIMATE (ALGORITHM 'ABC' WITHIN 10 PERCENT)
from person
{code}
For now, I think Phoenix should support APPROX_COUNT_DISTINCT. We could add
support for the APPROXIMATE clause later.
> Support approximate COUNT DISTINCT
> ----------------------------------
>
> Key: PHOENIX-418
> URL: https://issues.apache.org/jira/browse/PHOENIX-418
> Project: Phoenix
> Issue Type: Task
> Reporter: James Taylor
> Assignee: Ethan Wang
> Labels: gsoc2016
>
> Support an "approximation" of count distinct to prevent having to hold on to
> all distinct values (since this will not scale well when the number of
> distinct values is huge). The Apache Drill folks have had some interesting
> discussions on this
> [here](http://mail-archives.apache.org/mod_mbox/incubator-drill-dev/201306.mbox/%3CJIRA.12650169.1369931282407.88049.1370645900553%40arcas%3E).
> They recommend using [Welford's
> method](http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance_Online_algorithm).
> I'm open to having a config option that uses exact versus approximate. I
> don't have experience implementing an approximate implementation, so I'm not
> sure how much state is required to keep on the server and return to the
> client (other than realizing it'd be much less that returning all distinct
> values and their counts).
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
