Essql COUNT() vs Measure Unique (Canvas Metric)

I am using Kibana Canvas and making a Metric element over how many logins the system has logged. I ran into a big offset in counts.

When using this query in essql:
SELECT reference_ as logins FROM "*canvas"

and in "Display" using Unique on "logins" I get a count of 342.

When using
SELECT COUNT(DISTINCT reference_) as logins FROM "*canvas"

and in "Display" using Value on "logins" I get a count of 1010.

I looked at this question and it seems the unique measure is just an approximation.

But why is the difference so big? And am I sure to get the full count by doing it as example 2 in essql?

Not 100% about this, but it's possible the 342 you get to be over a limited number of documents: Canvas Metric Element: Unique count. Also, the documentation seems to mention this default value:

From ES-SQL point of view, the first SELECT just retrieves some documents. The second one, on other hand, is running a cardinality aggregation on reference_ field and does actually count the unique, non-null terms in that field. I would trust the second query, and would look for clues (like the one I mentioned above) on what kind of count does Canvas calculate on what set of documents.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.