Hi Team, What is the recommended way to implement ...
# general
s
Hi Team, What is the recommended way to implement count distinct use-case in native queries. We need the exact count. Is Group-By Query recommended ?
j
Hi Suraj, try writing it in SQL and then Explain to see the native representation. You should see nested Group By queries in native.
s
Is there any operator for the same ?
j
I don't know of any ... here is all that is listed for count distinct aggregators: https://druid.apache.org/docs/latest/querying/aggregations#count-distinct Possibly someone has implemented a custom extension? But seems like it wouldn't be worth it since you would end up doing about the same as a nested group by.
s
yeah, I have checked these. Looks like, I will go with Group By
a
every time I see someone insists on exact count I want to ask how exact is exact. unless you are counting money (in which case you probably need a transactional database) you might want to consider if you can tolerate some approximation. most probably, your data has some amount of noise anyway. suppose your distinct count is a 6-digit number. what if the last one is rounded? what if it is rounded to the nearest thousand and is much cheaper to compute? how much would you pay to have an additional digit computed exactly?
🙌 1
g
+1 to @Alexander Saydakov's comment. The DataSketches functions give you a lot of control over approximations and in many cases are a good choice If you really do need exact, what the SQL planner would do in exact mode is build a two-layer groupBy. The inner query groups on the thing you want to count, and the outer query does a
count
aggregator. You could do the same technique in native queries
👍 2
r
I also having same issue select count(d."orders_id"),count(sub."customers_city"), ((count(d."orders_id") * 100 + count(sub."customers_city") * 100)) as "dcity_by_hundred", sum(d."customers_id") as "customers_id_sum", sub."customers_city" from ( select DISTINCT a."customers_city" from testing_excel_db_COM101 as a ) as sub inner join testing_excel_db_COM101 d on sub.customers_city=d.customers_city group by sub."customers_city" this is my query giving wrong distinct count i need exact distinct count COUNT(DISTINCT "columns") giving wrong value,I need exact count
s
for SQL queries, you can use "useApproximateCountDistinct = false"
r
but for large group by data it is not working and taking too long time to get data
a
that is why you may want to consider some approximation
you can control the trade-off between error and performance. I am not sure what the default is. perhaps about +-1-2% with 95% confidence
brute force distinct counts get very expensive very quickly
biggest benefit from using approximation is not when you do this one-off approximate count distinct, but when you design your system with sketches, which allow data hypercube to have "additive" distinct count metrics
👍 2
r
I have a transactional database in which I store the values of transactions done by the company on various assets like purchase, expenses. so here I want to calculate the exact the dcount values for each transaction can you please suggest me some of the exact method which can help me to do this
a
I am afraid that there is no magic. if you really need exact distinct counts and your data sets are large, your processing will be very expensive
a
I haven't looked in depth but https://github.com/apache/druid/pull/18021 could be handy, if you are doing an exact count of a long column
j
If it fits without your call structure, cardinality and performance requirements, you could potentially use a hack of splitting up the query into batches of nonoverlapping ranges of the value to execute smaller distinct counts, e.g.:
Copy code
select (select count(distinct page) from wikipedia where page < 'M') 
     + (select count(distinct page) from wikipedia where page >= 'M')