uniqueCount() vs count()

Hello,
I have a query like below and some uniqueCount() values are bigger then count() results. I know that, there is a tip in the document ( shared doc link as well) about optimization of queries that inspect more than 256 unique values but how can I count some unique values based on date or categorical values which are more than 256, close to 100k? I couldn’t take all the unique value to my DWH, but couldn’t calculate accurate result of unique count function as well.

SELECT uniqueCount(sessionId) as unique_count, count(sessionId) as normal_count from Session where appName in ('X') FACET dateOf(timestamp) SINCE 1 week ago Limit Max

New relic docs

Hi @efe.turkoglu - Given your query time span, I would suggest you add LIMIT MAX to your query as a start.

Thank you for your suggestion @stefan_garnham , I edited but nothing changed significantly.

Hi,
Can you please use a small time like 1 week to 10 mins and remove dateOf(timestamp)?

SELECT uniqueCount(sessionId) as unique_count, count(sessionId) as normal_count from Session where appName in ('X') FACET dateOf(timestamp) SINCE 1 week ago Limit Max
SELECT count(sessionId) as unique_count, count(sessionId) as normal_count from Session where appName in ('X') SINCE 10 minutes ago 

Do you still see the same issue when you do that?
Can you share the screenshot of the outputs?

Feel free to reach out in case of any questions.

Thanks,
Suman

Hello @sjyoti

We would like to calculate those values for our KPI board, it must be month-based but I changed the calculation to week-based, and you can see the results from the attached pictures.

First I used SINCE 1 week ago limit max on week-based calculation, result showed that unique count is less than normal count like expected.
Then I used SINCE 1 day ago limit maxon day-based calculation, result showed that unique count is greater than normal count. It looks like If I use large time range like week-based, the results will be safer.


Hello @efe.turkoglu! Can you provide a link to those queries for me and I’ll be happy to take a look. This might be due to data aggregation but would like to see what’s happening in these queries to be sure.