Could you share SHOW CREATE TABLE default.LogTTHC FORMAT Vertical
?
for ClickHouse SQL dialect better use uniqExact
and If
aggregation combinator
look detail in https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
and https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states
COUNT(DISTINCT + CASE
shall be the same with uniqExactIf
but it depends on count_distinct_implementation
setting
check
SELECT * FROM system.settings WHERE name='count_distinct_implementation' FORMAT Vertical
could you compare
SELECT
SiteId,
uniqExact(CodeProfile) AS totalUniqProfiles,
uniqExactIf(CodeProfile, CreatedReceiveLeve1 >= '2024-01-01' AND CreatedReceiveLeve1 <= '2024-10-01') AS uniqProfilesForDateRange
FROM
LogTTHC
WHERE
SiteId IN (60, 249)
GROUP BY
SiteId;
and
SELECT
SiteId,
uniqExact(CodeProfile) AS totalUniqProfiles,
uniqExactIf(CodeProfile, CreatedReceiveLeve1 >= '2024-01-01' AND CreatedReceiveLeve1 <= '2024-10-01') AS uniqProfilesForDateRange
FROM
LogTTHC
WHERE
SiteId IN (249)
GROUP BY
SiteId;
is this queries contains different result for SiteId=249
?