CODEWITHSUNDEEP
clickhouse
James May
James May

Reputation: 1537

FILTER WHERE at count in ClickHouse

I'm trying to migrate one of my Postgres tables at ClickHouse. Here what I came up with at ClickHouse:

CREATE TABLE loads(
    country_id UInt16,
    partner_id UInt32,
    is_unique UInt8,
    ip String,
    created_at DateTime
) ENGINE=MergeTree PARTITION BY toYYYYMM(created_at) ORDER BY (created_at);

is_unique here is a Boolean with 0 or 1. I wanna know count for aggregates: country_id, partner_id and created_at, but also I wanna know how much from these loads are unique loads. At Postgres it looks like:

SELECT
    count(*) AS loads,
    count(*) FILTER (WHERE is_unique) AS uniq,
    country_id,
    partner_id,
    created_at::date AS ts
FROM loads
GROUP BY ts, country_id, partner_id

Is it possible at ClickHouse or should I think again about how to aggregate the data? I didn't find any clues at manual except count can get expr instead of asterisk, but count(is_unique = 1) doesn't work and just returns the same amount as count(*).

Upvotes: 5

Views: 5524

Answers (1)

James May
James May

Reputation: 1537

I just found an answer in minutes after posting:

SELECT count(*), countIf(is_unique = 1) /* .. */

Good luck.

Upvotes: 14

Related Questions