uniq vs uniqExact

[kriticar]

Hi,

I have seen on https://clickhouse.yandex/docs/en/query_language/agg_functions/reference/#agg_function-uniq

uniq¶

Calculates the approximate number of different values of the argument.

uniq(x[, ...])

and

uniqExact¶

Calculates the exact number of different argument values.
uniqExact(x[, ...])


Use the uniqExact function if you absolutely need an exact result. Otherwise use the uniq function.

The uniqExact function uses more memory than uniq, because the size of the state has unbounded growth as the number of different values increases.

What does it mean approximate and exact in this case?

Who needs approximate result?

Is uniq reliable?

Regards.

[Denis Zhuravlev]

>What does it mean approximate and exact in this case?

It means that result will be accurate with +/- 0.00% precision.

>Who needs approximate result?

everybody who needs speed.

>Is uniq reliable?

yes

create table u(I Int64) Engine=MergeTree() order by tuple();

insert into u select cityHash64(number) from numbers(100000000);

select uniq(I) from u;

┌───uniq(I)─┐
│ 100107766 │
└───────────┘

1 rows in set. Elapsed: 0.310 sec. Processed 100.00 million rows

select uniqExact(I) from u;

┌─uniqExact(I)─┐
│    100000000 │
└──────────────┘

1 rows in set. Elapsed: 8.840 sec. Processed 100.00 million rows

As you can see uniq is 20 times faster. But brought 0.1% error.

There is more functions:

https://clickhouse.yandex/docs/en/query_language/agg_functions/reference/#agg_function-uniq

SELECT uniqCombined(20, I) FROM u

┌─uniqCombined(20, I)─┐
│           100001110 │
└─────────────────────┘

error is 0.001%

[kriticar]

Thanks for clarification Denis.

Regards.

[kriticar]

I have just executed two successive queries:

select startOf15Min, uniqExactMerge(uniqExactCusts) uniqExactCusts
from interval_sums_agg
where startOf15MinDay = today()
group by startOf15Min
order by startOf15Min

lasted 27 seconds

select startOf15Min, uniqExact(customerId)
from indicators_15_min
where startOf15MinDay = today()
group by startOf15Min
order by startOf15Min

lasted 3 seconds

Average difference between results is 1%.

It is way higher than +/- 0.00% precision.

For example, uniq shows

2.673.280,00 vs uniqExact 2.682.861,00 The difference is 9.581,00

it is about

1,003584%

difference.

Looks like uniq is not so precise.

Regards.

On Wednesday, 16 October 2019 15:26:37 UTC+2, Denis Zhuravlev wrote:

[Denis Zhuravlev]

>it is about

>1,003584%

>difference.

>Looks like uniq is not so precise.

You don't understand.

If one needs response time less 100ms, one use different tricks: calculation by 1% of data (sampling), uniq and so on. In these cases always will be some inaccuracy but it does not matter. 

[kriticar]

OK, in that case, it is not for me, because I have to provide exact numbers.