Skip to main content

How to calculate YoY statistics in ClickHouse

You can get year over year (yoy) growth in ClickHouse with a simple query like this:

CREATE TABLE events
(
timestamp DateTime,
key LowCardinality(String),
value UInt32
) engine=MergeTree()
ORDER BY (key, timestamp)

Now insert some random data...

INSERT INTO events SELECT
now() - toIntervalYear(1),
toString(number % 10),
rand()
FROM numbers(1000000)

INSERT INTO events SELECT
now(),
toString(number % 10),
rand()
FROM numbers(1000000)

...and calculate the year over year growth with sumIf()!

WITH toYear(timestamp) AS year
SELECT
key,
sumIf(value, year = toYear(now())) AS this_year,
sumIf(value, year = (toYear(now()) - 1)) AS past_year,
round(this_year / past_year, 3) AS yoy
FROM events
WHERE key = '1'
GROUP BY key

┌─key─┬────────this_year─┬───────past_year─┬───yoy─┐
112872426453036864300705695190552.993
└─────┴──────────────────┴─────────────────┴───────┘