title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/use-cases/time-series/06_materialized-view-rollup.mdx)

> This tutorial shows you how to maintain pre-aggregated roll-ups from a high-volume events table using [**materialized views**](/docs/materialized-views).
> You’ll create three objects: a raw table, a rollup table, and the materialized view that writes into the rollup automatically.

## When to use this pattern[​](#when-to-use "Direct link to When to use this pattern")

Use this pattern when:

- You have an **append-only events stream** (clicks, pageviews, IoT, logs).
- Most queries are **aggregations** over time ranges (per minute/hour/day).
- You want **consistent sub-second reads** without re-scanning all raw rows.

Create the raw events table

## Create the raw events table[​](#create-raw-events-table "Direct link to Create the raw events table")

```
CREATE TABLE events_raw
(
    event_time   DateTime,
    user_id      UInt64,
    country      LowCardinality(String),
    event_type   LowCardinality(String),
    value        Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 90 DAY DELETE
```

**Notes**

- `PARTITION BY toYYYYMM(event_time)` keeps partitions small and easy to drop.
- `ORDER BY (event_time, user_id)` supports time-bounded queries + secondary filter.
- `LowCardinality(String)` saves memory for categorical dimensions.
- `TTL` cleans up raw data after 90 days (tune to your retention requirements).

Design the rollup (aggregated) table

## Design the rollup (aggregated) table[​](#design-rollup "Direct link to Design the rollup (aggregated) table")

We’ll pre-aggregate to **hourly** granularity.
Choose your grain to match the most common analysis window.

```
CREATE TABLE events_rollup_1h
(
    bucket_start  DateTime,            -- start of the hour
    country       LowCardinality(String),
    event_type    LowCardinality(String),
    users_uniq    AggregateFunction(uniqExact, UInt64),
    value_sum     AggregateFunction(sum, Float64),
    value_avg     AggregateFunction(avg, Float64),
    events_count  AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(bucket_start)
ORDER BY (bucket_start, country, event_type)
```

We store **aggregate states** (e.g., `AggregateFunction(sum, ...)`) which compactly represent partial aggregates and can be merged or finalized later.

Create a materialized view that populates the rollup

## Create a materialized view that populates the rollup[​](#create-materialized-view-to-populate-rollup "Direct link to Create a materialized view that populates the rollup")

This materialized view fires automatically on inserts into `events_raw` and writes **aggregate states** into the rollup.

```
CREATE MATERIALIZED VIEW mv_events_rollup_1h
TO events_rollup_1h
AS
SELECT
    toStartOfHour(event_time) AS bucket_start,
    country,
    event_type,
    uniqExactState(user_id)   AS users_uniq,
    sumState(value)           AS value_sum,
    avgState(value)           AS value_avg,
    countState()              AS events_count
FROM events_raw
GROUP BY bucket_start, country, event_type;
```

Insert some sample data

## Insert some sample data[​](#insert-some-sample-data "Direct link to Insert some sample data")

Insert some sample data:

```
INSERT INTO events_raw VALUES
    (now() - INTERVAL 4 SECOND, 101, 'US', 'view', 1),
    (now() - INTERVAL 3 SECOND, 101, 'US', 'click', 1),
    (now() - INTERVAL 2 SECOND, 202, 'DE', 'view', 1),
    (now() - INTERVAL 1 SECOND, 101, 'US', 'view', 1);
```

Querying the rollup

## Querying the rollup[​](#querying-the-rollup "Direct link to Querying the rollup")

You can either **merge** states at read time, or **finalize** them:

- Merge at read time- Finalize with -Final

```
SELECT
    bucket_start,
    country,
    event_type,
    uniqExactMerge(users_uniq) AS users,
    sumMerge(value_sum)        AS value_sum,
    avgMerge(value_avg)        AS value_avg,
    countMerge(events_count)   AS events
FROM events_rollup_1h
WHERE bucket_start >= now() - INTERVAL 1 DAY
GROUP BY ALL
ORDER BY bucket_start, country, event_type;
```

```
SELECT
    bucket_start,
    country,
    event_type,
    uniqExactMerge(users_uniq) AS users,
    sumMerge(value_sum)        AS value_sum,
    avgMerge(value_avg)        AS value_avg,
    countMerge(events_count)   AS events
FROM events_rollup_1h
WHERE bucket_start >= now() - INTERVAL 1 DAY
GROUP BY ALL
ORDER BY bucket_start, country, event_type
SETTINGS final = 1;  -- or use SELECT ... FINAL
```

  

Tip

If you expect reads to always hit the rollup, you can create a **second materialized view** that writes *finalized* numbers to a “plain” `MergeTree` table at the same 1h grain.
States give more flexibility while finalized numbers give slightly simpler reads.

Filter on fields in the primary key for best performance

## Filter on fields in the primary key for best performance[​](#filtering-performance "Direct link to Filter on fields in the primary key for best performance")

You can use the `EXPLAIN` command to see how the index is used to prune data:

```
EXPLAIN indexes=1
SELECT *
FROM events_rollup_1h
WHERE bucket_start BETWEEN now() - INTERVAL 3 DAY AND now()
  AND country = 'US';
```