> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# avgMergeState

> Example of using the avgMergeState combinator

<h2 id="description">
  Description
</h2>

The [`MergeState`](/reference/functions/aggregate-functions/combinators#-state) combinator
can be applied to the [`avg`](/reference/functions/aggregate-functions/avg)
function to merge partial aggregate states of type `AverageFunction(avg, T)` and
return a new intermediate aggregation state.

<h2 id="example-usage">
  Example usage
</h2>

The `MergeState` combinator is particularly useful for multi-level aggregation
scenarios where you want to combine pre-aggregated states and maintain them as
states (rather than finalizing them) for further processing. To illustrate, we'll
look at an example in which we transform individual server performance metrics
into hierarchical aggregations across multiple levels: Server level → Region level
→ Datacenter level.

First we create a table to store the raw data:

```sql theme={null}
CREATE TABLE raw_server_metrics
(
    timestamp DateTime DEFAULT now(),
    server_id UInt32,
    region String,
    datacenter String,
    response_time_ms UInt32
)
ENGINE = MergeTree()
ORDER BY (region, server_id, timestamp);
```

We'll create a server-level aggregation target table and define an Incremental
materialized view acting as an insert trigger to it:

```sql theme={null}
CREATE TABLE server_performance
(
    server_id UInt32,
    region String,
    datacenter String,
    avg_response_time AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (region, server_id);

CREATE MATERIALIZED VIEW server_performance_mv
TO server_performance
AS SELECT
    server_id,
    region,
    datacenter,
    avgState(response_time_ms) AS avg_response_time
FROM raw_server_metrics
GROUP BY server_id, region, datacenter;
```

We'll do the same for the regional and datacenter levels:

```sql theme={null}
CREATE TABLE region_performance
(
    region String,
    datacenter String,
    avg_response_time AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (datacenter, region);

CREATE MATERIALIZED VIEW region_performance_mv
TO region_performance
AS SELECT
    region,
    datacenter,
    avgMergeState(avg_response_time) AS avg_response_time
FROM server_performance
GROUP BY region, datacenter;

-- datacenter level table and materialized view

CREATE TABLE datacenter_performance
(
    datacenter String,
    avg_response_time AggregateFunction(avg, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY datacenter;

CREATE MATERIALIZED VIEW datacenter_performance_mv
TO datacenter_performance
AS SELECT
      datacenter,
      avgMergeState(avg_response_time) AS avg_response_time
FROM region_performance
GROUP BY datacenter;
```

We'll then insert sample raw data into the source table:

```sql theme={null}
INSERT INTO raw_server_metrics (timestamp, server_id, region, datacenter, response_time_ms) VALUES
    (now(), 101, 'us-east', 'dc1', 120),
    (now(), 101, 'us-east', 'dc1', 130),
    (now(), 102, 'us-east', 'dc1', 115),
    (now(), 201, 'us-west', 'dc1', 95),
    (now(), 202, 'us-west', 'dc1', 105),
    (now(), 301, 'eu-central', 'dc2', 145),
    (now(), 302, 'eu-central', 'dc2', 155);
```

We'll write three queries for each of the levels:

<Tabs>
  <Tab title="Service level">
    ```sql theme={null}
    SELECT
        server_id,
        region,
        avgMerge(avg_response_time) AS avg_response_ms
    FROM server_performance
    GROUP BY server_id, region
    ORDER BY region, server_id;
    ```

    ```response theme={null}
    ┌─server_id─┬─region─────┬─avg_response_ms─┐
    │       301 │ eu-central │             145 │
    │       302 │ eu-central │             155 │
    │       101 │ us-east    │             125 │
    │       102 │ us-east    │             115 │
    │       201 │ us-west    │              95 │
    │       202 │ us-west    │             105 │
    └───────────┴────────────┴─────────────────┘
    ```
  </Tab>

  <Tab title="Regional level">
    ```sql theme={null}
    SELECT
        region,
        datacenter,
        avgMerge(avg_response_time) AS avg_response_ms
    FROM region_performance
    GROUP BY region, datacenter
    ORDER BY datacenter, region;
    ```

    ```response theme={null}
    ┌─region─────┬─datacenter─┬────avg_response_ms─┐
    │ us-east    │ dc1        │ 121.66666666666667 │
    │ us-west    │ dc1        │                100 │
    │ eu-central │ dc2        │                150 │
    └────────────┴────────────┴────────────────────┘
    ```
  </Tab>

  <Tab title="Datacenter level">
    ```sql theme={null}
    SELECT
        datacenter,
        avgMerge(avg_response_time) AS avg_response_ms
    FROM datacenter_performance
    GROUP BY datacenter
    ORDER BY datacenter;
    ```

    ```response theme={null}
    ┌─datacenter─┬─avg_response_ms─┐
    │ dc1        │             113 │
    │ dc2        │             150 │
    └────────────┴─────────────────┘
    ```
  </Tab>
</Tabs>

We can insert more data:

```sql theme={null}
INSERT INTO raw_server_metrics (timestamp, server_id, region, datacenter, response_time_ms) VALUES
    (now(), 101, 'us-east', 'dc1', 140),
    (now(), 201, 'us-west', 'dc1', 85),
    (now(), 301, 'eu-central', 'dc2', 135);
```

Let's check the datacenter-level performance again. Notice how the entire
aggregation chain updated automatically:

```sql theme={null}
SELECT
    datacenter,
    avgMerge(avg_response_time) AS avg_response_ms
FROM datacenter_performance
GROUP BY datacenter
ORDER BY datacenter;
```

```response theme={null}
┌─datacenter─┬────avg_response_ms─┐
│ dc1        │ 112.85714285714286 │
│ dc2        │                145 │
└────────────┴────────────────────┘
```

<h2 id="see-also">
  See also
</h2>

* [`avg`](/reference/functions/aggregate-functions/avg)
* [`AggregateFunction`](/reference/data-types/aggregatefunction)
* [`Merge`](/reference/functions/aggregate-functions/combinators#-merge)
* [`MergeState`](/reference/functions/aggregate-functions/combinators#-mergestate)