I was trying to generate a weekly case report with New Relic. The dataset was relatively big. A skeleton query looks like below:
select uniqueCount(key) from EVENT where (... Condition...) facet weekOf(resolvedTime) since 12 months ago limit max
The number was a little off from what it should be. I know that uniqueCount() function only returns approximation number if unique records inspecting are more than 256.
I then try to query data in a different approach.
select count(key) from(select filter(latest(resolvedTime), where ...conditions...) as 'Date' from Event facet key limit max) facet weekof(Date) since 12 months ago limit max
I found an even bigger number off on result. I used the second query for some events with smaller dataset, and the result was pretty good. I wonder if there is any limitation on filter() or facet() clause that is causing my problem.