Is there a way to count exact DISTINCT_COUNT(field) using native queries ? SKETCH does not gives us the exact count, there are some cases where exact distinct_count is must and needed.

I think you can just do a group by query like:

{
  "queryType": "groupBy",
  "dataSource": "your_data_source",
  "intervals": [
    "2023-01-01/2023-12-31"
  ],
  "granularity": "all",
  "dimensions": [
    "your_field"
  ],
  "aggregations": [
    {
      "type": "count",
      "name": "count"
    }
  ]
}

this will give just the event count (including duplicate). What i want is how many unique stores are open by city.

you would group by the stores to get unique stores

but i really do not need the actual store id, just the

count(unique (store)) by city where status=open

something like this.

yeah…that’s why you have the count aggregator

i get this part, but i was wondering if there is way without adding store in the group by. Like we use ThetaSketch(StoreId), but instead of approximate it gives us the exact count.

There is also a distinct count aggregator (https://druid.apache.org/docs/latest/development/extensions-contrib/distinctcount/) but I have never used it. (It is also a contrib extension so not sure how reliable)

there are some steps mentioned to be follow. seems like not an option

distinct count aggregator extension mentioned is not reliable for most use cases - it depends on specific conditions to be present before it can give the correct result. If you do something like:

SELECT COUNT(DISTINCT col) FROM tbl (exact results)

in Druid, and see the native plan, you’d see that it gets planned as a group by query. That’s the Druid’s way of saying that we need to group by the rows, and count the number of results returned. That abstraction is hidden from the user because SQL is a declarative language. You won’t see that we are fetching the columns, however we do fetch that, and Druid’s group by query is a way to tell the engine what exactly it needs to do. I suppose count distinct in other databases is done in a similar fashion using hashing+sorting. TLDR; You need to group by to get a distinct count, and there’s no other alternative that I know can do that for you if you need exact counts. Datasketches work without actual values because they are probablistic datastructures, and store a lot of bit vectors, and not the actual values.

thanks make sense.

Theta sketches will be exact (modulo 64-bit hash collisions) up to some point depending on the configured size of the sketch