> ## 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

> avgMergeState combinator를 사용하는 예시

<div id="description">
  ## 설명
</div>

[`MergeState`](/ko/reference/functions/aggregate-functions/combinators#-state) combinator는
[`avg`](/ko/reference/functions/aggregate-functions/avg)
함수에 적용할 수 있으며, `AverageFunction(avg, T)` 유형의 부분 집계 상태를 머지하여
새로운 중간 집계 상태를 반환합니다.

<div id="example-usage">
  ## 사용 예시
</div>

`MergeState` combinator는 사전 집계된 상태를 결합하고, 추가 처리를 위해
최종화하지 않은 상태로 유지해야 하는 다단계 집계
시나리오에서 특히 유용합니다. 이를 보여주기 위해, 개별 서버 성능 메트릭을
여러 단계에 걸친 계층적 집계로 변환하는 예시를 살펴보겠습니다: 서버 수준 → 리전 수준
→ 데이터센터 수준.

먼저 원시 데이터를 저장할 테이블을 생성합니다:

```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);
```

서버 수준의 집계 대상 테이블을 생성하고, 여기에 대한 삽입 트리거로 동작하는 Incremental
materialized view를 정의합니다:

```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;
```

리전 및 데이터센터 수준에도 같은 방식으로 적용합니다:

```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;

-- 데이터센터 수준 테이블 및 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;
```

이어서 원본 테이블에 샘플 원시 데이터를 삽입합니다:

```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);
```

각 수준에 대해 쿼리 3개씩 작성하겠습니다:

<Tabs>
  <Tab title="서비스 수준">
    ```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="리전 수준">
    ```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="데이터센터 수준">
    ```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>

다음과 같이 데이터를 더 삽입할 수 있습니다:

```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);
```

데이터센터 수준의 성능을 다시 살펴보겠습니다. 전체
집계 과정이 자동으로 업데이트된 것을 확인할 수 있습니다:

```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 │
└────────────┴────────────────────┘
```

<div id="see-also">
  ## 관련 항목
</div>

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