ClickHouse Cheatsheet 08 — Time-Series Patterns

toYYYYMM(ts)
toDate(ts)
toStartOfMinute(ts)
toStartOfHour(ts)
toStartOfDay(ts)
toStartOfMonth(ts)
toStartOfInterval(ts, INTERVAL 5 MINUTE)
toRelativeMinuteNum(ts)
dateDiff('hour', a, b)

SELECT
    toStartOfInterval(ts, INTERVAL 1 MINUTE) AS m,
    count(),
    avg(value)
FROM events
WHERE ts >= now() - INTERVAL 1 HOUR
GROUP BY m
ORDER BY m;

CREATE TABLE events (...)
ENGINE = MergeTree()
ORDER BY (ts, user_id)
TTL ts + INTERVAL 30 DAY;

TTL ts + INTERVAL 30 DAY,
    ts + INTERVAL 7 DAY GROUP BY toStartOfDay(ts) SET value = sum(value)

TTL ts + INTERVAL 7 DAY TO VOLUME 'cold'

CREATE MATERIALIZED VIEW events_hourly TO events_hourly_target AS
SELECT
    toStartOfHour(ts) AS hour,
    user_id,
    countState() AS count_state,
    sumState(value) AS sum_state
FROM events
GROUP BY hour, user_id;

SELECT user_id, sum(score * exp(-(now() - ts) / 86400)) AS decayed
FROM events GROUP BY user_id ORDER BY decayed DESC LIMIT 10;

SELECT
    user_id, ts, value,
    avg(value) OVER (PARTITION BY user_id ORDER BY ts ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)
FROM events;

SELECT
    toStartOfMinute(ts) AS m,
    quantile(0.99)(latency)
FROM events GROUP BY m;

WITH (SELECT avg(value), stddevSamp(value) FROM events) AS (mean, sd)
SELECT * FROM events WHERE abs(value - mean) > 3 * sd;