79221950

Date: 2024-11-25 06:59:06
Score: 3.5
Natty:
Report link

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?

Reasons:
  • RegEx Blacklisted phrase (2.5): Could you share
  • Probably link only (1):
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Ends in question mark (2):
  • High reputation (-1):
Posted by: Slach