🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 89 (from laksa152)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa089.int.ahrefs:8124/' \
  -H 'Content-Type: text/plain' \
  -H 'X-ClickHouse-Database: crawler3' \
  -H 'Authorization: Basic YXBpOg==' \
  -d 'SELECT getAhrefsURLFromUnparsed(src_unparsed) AS found_url, ifNull(toUnixTimestamp(download_stamp), 0) AS crawl_time, ifNull(toUnixTimestamp(props_url_first_seen), 0) AS first_indexed_time, download_http_code AS http_code, src_unparsed AS src_unparsed, src_root_hash AS src_root_hash, history_drop_reason AS history_drop_reason, meta_title AS meta_title, meta_descriptions AS meta_descriptions, attrs_boilerpipe_text AS attrs_boilerpipe_text, attrs_markdown AS attrs_markdown, attrs_readable_markdown AS attrs_readable_markdown, meta_canonical AS meta_canonical FROM crawler3.page_info_local FINAL PREWHERE (src_root_hash, src_unparsed) IN ((getAhrefsRootHashFromUnparsed(getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree","crawl_time":1775769459,"first_indexed_time":1740098389,"http_code":200,"src_unparsed":"com,clickhouse!\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree s443","src_root_hash":"12633450985039531489","history_drop_reason":null,"meta_title":"AggregatingMergeTree table engine | ClickHouse Docs","meta_descriptions":["Replaces all rows with the same primary key (or more accurately, with the same [sorting key](..\/..\/..\/engines\/table-engines\/mergetree-family\/mergetree.md)) with a single row (within a single data part) that stores a combination of states of aggregate functions."],"attrs_boilerpipe_text":"The engine inherits from\nMergeTree\n, altering the logic for data parts merging. ClickHouse replaces all rows with the same primary key (or more accurately, with the same\nsorting key\n) with a single row (within a single data part) that stores a combination of states of aggregate functions.\nYou can use\nAggregatingMergeTree\ntables for incremental data aggregation, including for aggregated materialized views.\nYou can see an example of how to use the AggregatingMergeTree and Aggregate functions in the below video:\nThe engine processes all columns with the following types:\nAggregateFunction\nSimpleAggregateFunction\nIt is appropriate to use\nAggregatingMergeTree\nif it reduces the number of rows by orders.\nCreating a table\n\nCREATE\nTABLE\n[\nIF\nNOT\nEXISTS\n]\n[\ndb\n.\n]\ntable_name\n[\nON\nCLUSTER cluster\n]\n(\nname1\n[\ntype1\n]\n[\nDEFAULT\n|\nMATERIALIZED\n|\nALIAS expr1\n]\n,\nname2\n[\ntype2\n]\n[\nDEFAULT\n|\nMATERIALIZED\n|\nALIAS expr2\n]\n,\n.\n.\n.\n)\nENGINE\n=\nAggregatingMergeTree\n(\n)\n[\nPARTITION\nBY\nexpr\n]\n[\nORDER\nBY\nexpr\n]\n[\nSAMPLE\nBY\nexpr\n]\n[\nTTL expr\n]\n[\nSETTINGS name\n=\nvalue\n,\n.\n.\n.\n]\nFor a description of request parameters, see\nrequest description\n.\nQuery clauses\nWhen creating an\nAggregatingMergeTree\ntable, the same\nclauses\nare required as when creating a\nMergeTree\ntable.\nDeprecated Method for Creating a Table\nSELECT and INSERT\n\nTo insert data, use\nINSERT SELECT\nquery with aggregate -State- functions.\nWhen selecting data from\nAggregatingMergeTree\ntable, use\nGROUP BY\nclause and the same aggregate functions as when inserting data, but using the\n-Merge\nsuffix.\nIn the results of\nSELECT\nquery, the values of\nAggregateFunction\ntype have implementation-specific binary representation for all of the ClickHouse output formats. For example, if you dump data into\nTabSeparated\nformat with a\nSELECT\nquery, then this dump can be loaded back using an\nINSERT\nquery.\nExample of an aggregated materialized view\n\nThe following example assumes that you have a database named\ntest\n. Create it if it doesn't already exist using the command below:\nCREATE\nDATABASE\ntest\n;\nNow create the table\ntest.visits\nthat contains the raw data:\nCREATE\nTABLE\ntest\n.\nvisits\n(\nStartDate DateTime64\nNOT\nNULL\n,\nCounterID UInt64\n,\nSign Nullable\n(\nInt32\n)\n,\nUserID Nullable\n(\nInt32\n)\n)\nENGINE\n=\nMergeTree\nORDER\nBY\n(\nStartDate\n,\nCounterID\n)\n;\nNext, you need an\nAggregatingMergeTree\ntable that will store\nAggregationFunction\ns that keep track of the total number of visits and the number of unique users.\nCreate an\nAggregatingMergeTree\nmaterialized view that watches the\ntest.visits\ntable, and uses the\nAggregateFunction\ntype:\nCREATE\nTABLE\ntest\n.\nagg_visits\n(\nStartDate DateTime64\nNOT\nNULL\n,\nCounterID UInt64\n,\nVisits AggregateFunction\n(\nsum\n,\nNullable\n(\nInt32\n)\n)\n,\nUsers AggregateFunction\n(\nuniq\n,\nNullable\n(\nInt32\n)\n)\n)\nENGINE\n=\nAggregatingMergeTree\n(\n)\nORDER\nBY\n(\nStartDate\n,\nCounterID\n)\n;\nCreate a materialized view that populates\ntest.agg_visits\nfrom\ntest.visits\n:\nCREATE\nMATERIALIZED\nVIEW\ntest\n.\nvisits_mv\nTO\ntest\n.\nagg_visits\nAS\nSELECT\nStartDate\n,\nCounterID\n,\nsumState\n(\nSign\n)\nAS\nVisits\n,\nuniqState\n(\nUserID\n)\nAS\nUsers\nFROM\ntest\n.\nvisits\nGROUP\nBY\nStartDate\n,\nCounterID\n;\nInsert data into the\ntest.visits\ntable:\nINSERT\nINTO\ntest\n.\nvisits\n(\nStartDate\n,\nCounterID\n,\nSign\n,\nUserID\n)\nVALUES\n(\n1667446031000\n,\n1\n,\n3\n,\n4\n)\n,\n(\n1667446031000\n,\n1\n,\n6\n,\n3\n)\n;\nThe data is inserted in both\ntest.visits\nand\ntest.agg_visits\n.\nTo get the aggregated data, execute a query such as\nSELECT ... GROUP BY ...\nfrom the materialized view\ntest.visits_mv\n:\nSELECT\nStartDate\n,\nsumMerge\n(\nVisits\n)\nAS\nVisits\n,\nuniqMerge\n(\nUsers\n)\nAS\nUsers\nFROM\ntest\n.\nvisits_mv\nGROUP\nBY\nStartDate\nORDER\nBY\nStartDate\n;\n┌───────────────StartDate─┬─Visits─┬─Users─┐\n│ 2022-11-03 03:27:11.000 │      9 │     2 │\n└─────────────────────────┴────────┴───────┘\nAdd another couple of records to\ntest.visits\n, but this time try using a different timestamp for one of the records:\nINSERT\nINTO\ntest\n.\nvisits\n(\nStartDate\n,\nCounterID\n,\nSign\n,\nUserID\n)\nVALUES\n(\n1669446031000\n,\n2\n,\n5\n,\n10\n)\n,\n(\n1667446031000\n,\n3\n,\n7\n,\n5\n)\n;\nRun the\nSELECT\nquery again, which will return the following output:\n┌───────────────StartDate─┬─Visits─┬─Users─┐\n│ 2022-11-03 03:27:11.000 │     16 │     3 │\n│ 2022-11-26 07:00:31.000 │      5 │     1 │\n└─────────────────────────┴────────┴───────┘\nIn some cases, you might want to avoid pre-aggregating rows at insert time to shift the cost of aggregation from insert time\nto merge time. Ordinarily, it is necessary to include the columns which are not part of the aggregation in the\nGROUP BY\nclause of the materialized view definition to avoid an error. However, you can make use of the\ninitializeAggregation\nfunction with setting\noptimize_on_insert = 0\n(it is turned on by default) to achieve this. Use of\nGROUP BY\nis no longer required in this case:\nCREATE\nMATERIALIZED\nVIEW\ntest\n.\nvisits_mv\nTO\ntest\n.\nagg_visits\nAS\nSELECT\nStartDate\n,\nCounterID\n,\ninitializeAggregation\n(\n'sumState'\n,\nSign\n)\nAS\nVisits\n,\ninitializeAggregation\n(\n'uniqState'\n,\nUserID\n)\nAS\nUsers\nFROM\ntest\n.\nvisits\n;\nNote\nWhen using\ninitializeAggregation\n, an aggregate state is created for each individual row without grouping.\nEach source row produces one row in the materialized view, and the actual aggregation happens later when the\nAggregatingMergeTree\nmerges parts. This is only true if\noptimize_on_insert = 0\n.\nRelated content\n\nBlog:\nUsing Aggregate Combinators in ClickHouse","attrs_markdown":"[Skip to main content](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n\n- [English](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n\n[Skip to main content](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\n- [Products](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n  \n  - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https:\/\/clickhouse.com\/cloud)\n  - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https:\/\/clickhouse.com\/cloud\/bring-your-own-cloud)\n  - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https:\/\/clickhouse.com\/clickhouse)\n  - [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n  \n  [Discover more than 100 integrations.](https:\/\/clickhouse.com\/integrations)\n- [Use cases](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n  \n  - [Real-time analytics](https:\/\/clickhouse.com\/use-cases\/real-time-analytics)\n  - [Machine Learning & Generative AI](https:\/\/clickhouse.com\/use-cases\/machine-learning-and-data-science)\n  - [Business Intelligence](https:\/\/clickhouse.com\/use-cases\/data-warehousing)\n  - [Logs, Events, Traces](https:\/\/clickhouse.com\/use-cases\/observability)\n  - [All use cases](https:\/\/clickhouse.com\/use-cases)\n  \n  [All use cases](https:\/\/clickhouse.com\/use-cases)\n- [Documentation](https:\/\/clickhouse.com\/docs)\n- [Resources](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n  \n  - [User stories](https:\/\/clickhouse.com\/user-stories)\n  - [Blog](https:\/\/clickhouse.com\/blog)\n  - [Events](https:\/\/clickhouse.com\/company\/events)\n  - [Learning and certification](https:\/\/clickhouse.com\/learn)\n  - [Comparison](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n    - [BigQuery](https:\/\/clickhouse.com\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.com\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.com\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.com\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.com\/comparison\/snowflake)\n  - [Video](https:\/\/clickhouse.com\/videos)\n  - [Demo](https:\/\/clickhouse.com\/demos)\n- [Pricing](https:\/\/clickhouse.com\/pricing)\n- [Contact](https:\/\/clickhouse.com\/company\/contact?loc=nav)\n\n[46\\.8k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n[Sign in](https:\/\/console.clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461)\n\n[Get started](https:\/\/console.clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461)\n\n[Get started](https:\/\/clickhouse.com\/docs\/introduction-clickhouse)\n\n[Cloud](https:\/\/clickhouse.com\/docs\/cloud\/overview)\n\n[Manage data](https:\/\/clickhouse.com\/docs\/updating-data)\n\n[Server admin](https:\/\/clickhouse.com\/docs\/guides\/manage-and-deploy-index)\n\n[Reference](https:\/\/clickhouse.com\/docs\/sql-reference)\n\n[Integrations](https:\/\/clickhouse.com\/docs\/integrations)\n\n[ClickStack](https:\/\/clickhouse.com\/docs\/use-cases\/observability\/clickstack\/overview)\n\n[chDB](https:\/\/clickhouse.com\/docs\/chdb)\n\n[About](https:\/\/clickhouse.com\/docs\/about)\n\n[Knowledge Base](https:\/\/clickhouse.com\/docs\/knowledgebase)\n\n[English](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n\n- [English](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [日本語](https:\/\/clickhouse.com\/docs\/jp\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [中文](https:\/\/clickhouse.com\/docs\/zh\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [Русский](https:\/\/clickhouse.com\/docs\/ru\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n- [한국어](https:\/\/clickhouse.com\/docs\/ko\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n\n[Search`Ctrl``K`](https:\/\/clickhouse.com\/docs\/search)\n\n- [Introduction](https:\/\/clickhouse.com\/docs\/sql-reference)\n  - [Syntax](https:\/\/clickhouse.com\/docs\/sql-reference\/syntax)\n  - [Input and Output Formats](https:\/\/clickhouse.com\/docs\/sql-reference\/formats)\n  - [Data types](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types)\n  - [Statements](https:\/\/clickhouse.com\/docs\/sql-reference\/statements)\n  - [Operators](https:\/\/clickhouse.com\/docs\/sql-reference\/operators)\n- [Engines](https:\/\/clickhouse.com\/docs\/engines)\n  - [Database Engines](https:\/\/clickhouse.com\/docs\/engines\/database-engines)\n  - [Table Engines](https:\/\/clickhouse.com\/docs\/engines\/table-engines)\n    - [MergeTree Family](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family)\n      - [MergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/mergetree)\n      - [Replicated\\*](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/replication)\n      - [Custom Partitioning Key](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/custom-partitioning-key)\n      - [ReplacingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/replacingmergetree)\n      - [CoalescingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/coalescingmergetree)\n      - [SummingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/summingmergetree)\n      - [AggregatingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n      - [CollapsingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/collapsingmergetree)\n      - [VersionedCollapsingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/versionedcollapsingmergetree)\n      - [GraphiteMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/graphitemergetree)\n      - [Exact and Approximate Vector Search](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/annindexes)\n      - [Full-text Search with Text Indexes](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/textindexes)\n    - [Log family](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/log-family)\n    - [Integrations](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/integrations)\n    - [Special](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/special)\n- [Functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions)\n  - [Regular functions](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/regular-functions)\n  - [Aggregate functions](https:\/\/clickhouse.com\/docs\/sql-reference\/aggregate-functions)\n  - [Table functions](https:\/\/clickhouse.com\/docs\/sql-reference\/table-functions)\n  - [Window functions](https:\/\/clickhouse.com\/docs\/sql-reference\/window-functions)\n- [Formats](https:\/\/clickhouse.com\/docs\/interfaces\/formats)\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)\n\n- [Engines](https:\/\/clickhouse.com\/docs\/engines)\n- [Table Engines](https:\/\/clickhouse.com\/docs\/engines\/table-engines)\n- [MergeTree Family](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family)\n- AggregatingMergeTree\n\n[Edit this page](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/engines\/table-engines\/mergetree-family\/aggregatingmergetree.md)\n\n# AggregatingMergeTree table engine\nThe engine inherits from [MergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/versionedcollapsingmergetree), altering the logic for data parts merging. ClickHouse replaces all rows with the same primary key (or more accurately, with the same [sorting key](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/mergetree)) with a single row (within a single data part) that stores a combination of states of aggregate functions.\n\nYou can use `AggregatingMergeTree` tables for incremental data aggregation, including for aggregated materialized views.\n\nYou can see an example of how to use the AggregatingMergeTree and Aggregate functions in the below video:\n\nThe engine processes all columns with the following types:\n\n- [`AggregateFunction`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/aggregatefunction)\n- [`SimpleAggregateFunction`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/simpleaggregatefunction)\n\nIt is appropriate to use `AggregatingMergeTree` if it reduces the number of rows by orders.\n\n## Creating a table[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#creating-a-table \"Direct link to Creating a table\")\n```\nCREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]\n(\n    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],\n    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],\n    ...\n) ENGINE = AggregatingMergeTree()\n[PARTITION BY expr]\n[ORDER BY expr]\n[SAMPLE BY expr]\n[TTL expr]\n[SETTINGS name=value, ...]\n```\nFor a description of request parameters, see [request description](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/create\/table).\n\n**Query clauses**\n\nWhen creating an `AggregatingMergeTree` table, the same [clauses](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/mergetree) are required as when creating a `MergeTree` table.\n\nDeprecated Method for Creating a Table\n\nNote\n\nDo not use this method in new projects and, if possible, switch the old projects to the method described above.\n```\nCREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]\n(\n    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],\n    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],\n    ...\n) ENGINE [=] AggregatingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity)\n```\nAll of the parameters have the same meaning as in `MergeTree`.\n## SELECT and INSERT[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#select-and-insert \"Direct link to SELECT and INSERT\")\nTo insert data, use [INSERT SELECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into) query with aggregate -State- functions. When selecting data from `AggregatingMergeTree` table, use `GROUP BY` clause and the same aggregate functions as when inserting data, but using the `-Merge` suffix.\n\nIn the results of `SELECT` query, the values of `AggregateFunction` type have implementation-specific binary representation for all of the ClickHouse output formats. For example, if you dump data into `TabSeparated` format with a `SELECT` query, then this dump can be loaded back using an `INSERT` query.\n\n## Example of an aggregated materialized view[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#example-of-an-aggregated-materialized-view \"Direct link to Example of an aggregated materialized view\")\nThe following example assumes that you have a database named `test`. Create it if it doesn't already exist using the command below:\n```\nCREATE DATABASE test;\n```\nNow create the table `test.visits` that contains the raw data:\n```\nCREATE TABLE test.visits\n (\n    StartDate DateTime64 NOT NULL,\n    CounterID UInt64,\n    Sign Nullable(Int32),\n    UserID Nullable(Int32)\n) ENGINE = MergeTree ORDER BY (StartDate, CounterID);\n```\nNext, you need an `AggregatingMergeTree` table that will store `AggregationFunction`s that keep track of the total number of visits and the number of unique users.\n\nCreate an `AggregatingMergeTree` materialized view that watches the `test.visits` table, and uses the [`AggregateFunction`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/aggregatefunction) type:\n```\nCREATE TABLE test.agg_visits (\n    StartDate DateTime64 NOT NULL,\n    CounterID UInt64,\n    Visits AggregateFunction(sum, Nullable(Int32)),\n    Users AggregateFunction(uniq, Nullable(Int32))\n)\nENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID);\n```\nCreate a materialized view that populates `test.agg_visits` from `test.visits`:\n```\nCREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits\nAS SELECT\n    StartDate,\n    CounterID,\n    sumState(Sign) AS Visits,\n    uniqState(UserID) AS Users\nFROM test.visits\nGROUP BY StartDate, CounterID;\n```\nInsert data into the `test.visits` table:\n```\nINSERT INTO test.visits (StartDate, CounterID, Sign, UserID)\n VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3);\n```\nThe data is inserted in both `test.visits` and `test.agg_visits`.\n\nTo get the aggregated data, execute a query such as `SELECT ... GROUP BY ...` from the materialized view `test.visits_mv`:\n```\nSELECT\n    StartDate,\n    sumMerge(Visits) AS Visits,\n    uniqMerge(Users) AS Users\nFROM test.visits_mv\nGROUP BY StartDate\nORDER BY StartDate;\n```\n```\n┌───────────────StartDate─┬─Visits─┬─Users─┐\n│ 2022-11-03 03:27:11.000 │      9 │     2 │\n└─────────────────────────┴────────┴───────┘\n```\nAdd another couple of records to `test.visits`, but this time try using a different timestamp for one of the records:\n```\nINSERT INTO test.visits (StartDate, CounterID, Sign, UserID)\n VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5);\n```\nRun the `SELECT` query again, which will return the following output:\n```\n┌───────────────StartDate─┬─Visits─┬─Users─┐\n│ 2022-11-03 03:27:11.000 │     16 │     3 │\n│ 2022-11-26 07:00:31.000 │      5 │     1 │\n└─────────────────────────┴────────┴───────┘\n```\nIn some cases, you might want to avoid pre-aggregating rows at insert time to shift the cost of aggregation from insert time to merge time. Ordinarily, it is necessary to include the columns which are not part of the aggregation in the `GROUP BY` clause of the materialized view definition to avoid an error. However, you can make use of the [`initializeAggregation`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/other-functions#initializeAggregation) function with setting `optimize_on_insert = 0` (it is turned on by default) to achieve this. Use of `GROUP BY` is no longer required in this case:\n```\nCREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits\nAS SELECT\n    StartDate,\n    CounterID,\n    initializeAggregation('sumState', Sign) AS Visits,\n    initializeAggregation('uniqState', UserID) AS Users\nFROM test.visits;\n```\nNote\n\nWhen using `initializeAggregation`, an aggregate state is created for each individual row without grouping. Each source row produces one row in the materialized view, and the actual aggregation happens later when the `AggregatingMergeTree` merges parts. This is only true if `optimize_on_insert = 0`.\n## Related content[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#related-content \"Direct link to Related content\")\n- Blog: [Using Aggregate Combinators in ClickHouse](https:\/\/clickhouse.com\/blog\/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)\n\n[Previous SummingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/summingmergetree)\n\n[Next CollapsingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/collapsingmergetree)\n\n- [Creating a table](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#creating-a-table)\n- [SELECT and INSERT](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#select-and-insert)\n- [Example of an aggregated materialized view](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#example-of-an-aggregated-materialized-view)\n- [Related content](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#related-content)\n\nWas this page helpful?\n\n###### Try ClickHouse Cloud for FREE\nSeparation of storage and compute, automatic scaling, built-in SQL console, and lots more. \\$300 in free credits when signing up.\n\n[Try it for Free](https:\/\/console.clickhouse.cloud\/signUp?loc=doc-card-banner&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n© 2016–2026 ClickHouse, Inc.\n\n[Trademark](https:\/\/clickhouse.com\/legal\/trademark-policy)·[Privacy](https:\/\/clickhouse.com\/legal\/privacy-policy)·[Security](https:\/\/trust.clickhouse.com\/)·[Terms of Service](https:\/\/clickhouse.com\/legal\/agreements\/terms-of-service)\n\n![](https:\/\/static.scarf.sh\/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91)\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\n- Get started▼\n- Cloud▼\n- Manage data▼\n- Server admin▼\n- Reference▼\n- Integrations▼\n- ClickStack▼\n- chDB▼\n- About▼\n\n[![ClickHouse](https:\/\/clickhouse.com\/docs\/img\/ch_logo_docs.svg)](https:\/\/clickhouse.com\/)\n\nEN\n\nmain-menu\n\n- Introduction▼\n- Engines▼\n  - Database Engines▼\n  - Table Engines▼\n    - MergeTree Family▼\n      - [MergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/mergetree)\n      - [Replicated\\*](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/replication)\n      - [Custom Partitioning Key](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/custom-partitioning-key)\n      - [ReplacingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/replacingmergetree)\n      - [CoalescingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/coalescingmergetree)\n      - [SummingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/summingmergetree)\n      - [AggregatingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree)\n      - [CollapsingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/collapsingmergetree)\n      - [VersionedCollapsingMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/versionedcollapsingmergetree)\n      - [GraphiteMergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/graphitemergetree)\n      - [Exact and Approximate Vector Search](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/annindexes)\n      - [Full-text Search with Text Indexes](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/textindexes)\n    - Log family▼\n    - Integrations▼\n    - Special▼\n- Functions▼\n- Formats▼\n- [Data Lakes](https:\/\/clickhouse.com\/docs\/sql-reference\/datalakes)","attrs_readable_markdown":"The engine inherits from [MergeTree](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/versionedcollapsingmergetree), altering the logic for data parts merging. ClickHouse replaces all rows with the same primary key (or more accurately, with the same [sorting key](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/mergetree)) with a single row (within a single data part) that stores a combination of states of aggregate functions.\n\nYou can use `AggregatingMergeTree` tables for incremental data aggregation, including for aggregated materialized views.\n\nYou can see an example of how to use the AggregatingMergeTree and Aggregate functions in the below video:\n\nThe engine processes all columns with the following types:\n\n- [`AggregateFunction`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/aggregatefunction)\n- [`SimpleAggregateFunction`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/simpleaggregatefunction)\n\nIt is appropriate to use `AggregatingMergeTree` if it reduces the number of rows by orders.\n\n## Creating a table[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#creating-a-table \"Direct link to Creating a table\")\n```\nCREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]\n(\n    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],\n    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],\n    ...\n) ENGINE = AggregatingMergeTree()\n[PARTITION BY expr]\n[ORDER BY expr]\n[SAMPLE BY expr]\n[TTL expr]\n[SETTINGS name=value, ...]\n```\nFor a description of request parameters, see [request description](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/create\/table).\n\n**Query clauses**\n\nWhen creating an `AggregatingMergeTree` table, the same [clauses](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/mergetree) are required as when creating a `MergeTree` table.\n\nDeprecated Method for Creating a Table\n\n## SELECT and INSERT[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#select-and-insert \"Direct link to SELECT and INSERT\")\nTo insert data, use [INSERT SELECT](https:\/\/clickhouse.com\/docs\/sql-reference\/statements\/insert-into) query with aggregate -State- functions. When selecting data from `AggregatingMergeTree` table, use `GROUP BY` clause and the same aggregate functions as when inserting data, but using the `-Merge` suffix.\n\nIn the results of `SELECT` query, the values of `AggregateFunction` type have implementation-specific binary representation for all of the ClickHouse output formats. For example, if you dump data into `TabSeparated` format with a `SELECT` query, then this dump can be loaded back using an `INSERT` query.\n\n## Example of an aggregated materialized view[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#example-of-an-aggregated-materialized-view \"Direct link to Example of an aggregated materialized view\")\nThe following example assumes that you have a database named `test`. Create it if it doesn't already exist using the command below:\n```\nCREATE DATABASE test;\n```\nNow create the table `test.visits` that contains the raw data:\n```\nCREATE TABLE test.visits\n (\n    StartDate DateTime64 NOT NULL,\n    CounterID UInt64,\n    Sign Nullable(Int32),\n    UserID Nullable(Int32)\n) ENGINE = MergeTree ORDER BY (StartDate, CounterID);\n```\nNext, you need an `AggregatingMergeTree` table that will store `AggregationFunction`s that keep track of the total number of visits and the number of unique users.\n\nCreate an `AggregatingMergeTree` materialized view that watches the `test.visits` table, and uses the [`AggregateFunction`](https:\/\/clickhouse.com\/docs\/sql-reference\/data-types\/aggregatefunction) type:\n```\nCREATE TABLE test.agg_visits (\n    StartDate DateTime64 NOT NULL,\n    CounterID UInt64,\n    Visits AggregateFunction(sum, Nullable(Int32)),\n    Users AggregateFunction(uniq, Nullable(Int32))\n)\nENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID);\n```\nCreate a materialized view that populates `test.agg_visits` from `test.visits`:\n```\nCREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits\nAS SELECT\n    StartDate,\n    CounterID,\n    sumState(Sign) AS Visits,\n    uniqState(UserID) AS Users\nFROM test.visits\nGROUP BY StartDate, CounterID;\n```\nInsert data into the `test.visits` table:\n```\nINSERT INTO test.visits (StartDate, CounterID, Sign, UserID)\n VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3);\n```\nThe data is inserted in both `test.visits` and `test.agg_visits`.\n\nTo get the aggregated data, execute a query such as `SELECT ... GROUP BY ...` from the materialized view `test.visits_mv`:\n```\nSELECT\n    StartDate,\n    sumMerge(Visits) AS Visits,\n    uniqMerge(Users) AS Users\nFROM test.visits_mv\nGROUP BY StartDate\nORDER BY StartDate;\n```\n```\n┌───────────────StartDate─┬─Visits─┬─Users─┐\n│ 2022-11-03 03:27:11.000 │      9 │     2 │\n└─────────────────────────┴────────┴───────┘\n```\nAdd another couple of records to `test.visits`, but this time try using a different timestamp for one of the records:\n```\nINSERT INTO test.visits (StartDate, CounterID, Sign, UserID)\n VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5);\n```\nRun the `SELECT` query again, which will return the following output:\n```\n┌───────────────StartDate─┬─Visits─┬─Users─┐\n│ 2022-11-03 03:27:11.000 │     16 │     3 │\n│ 2022-11-26 07:00:31.000 │      5 │     1 │\n└─────────────────────────┴────────┴───────┘\n```\nIn some cases, you might want to avoid pre-aggregating rows at insert time to shift the cost of aggregation from insert time to merge time. Ordinarily, it is necessary to include the columns which are not part of the aggregation in the `GROUP BY` clause of the materialized view definition to avoid an error. However, you can make use of the [`initializeAggregation`](https:\/\/clickhouse.com\/docs\/sql-reference\/functions\/other-functions#initializeAggregation) function with setting `optimize_on_insert = 0` (it is turned on by default) to achieve this. Use of `GROUP BY` is no longer required in this case:\n```\nCREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits\nAS SELECT\n    StartDate,\n    CounterID,\n    initializeAggregation('sumState', Sign) AS Visits,\n    initializeAggregation('uniqState', UserID) AS Users\nFROM test.visits;\n```\nNote\n\nWhen using `initializeAggregation`, an aggregate state is created for each individual row without grouping. Each source row produces one row in the materialized view, and the actual aggregation happens later when the `AggregatingMergeTree` merges parts. This is only true if `optimize_on_insert = 0`.\n## Related content[](https:\/\/clickhouse.com\/docs\/engines\/table-engines\/mergetree-family\/aggregatingmergetree#related-content \"Direct link to Related content\")\n- Blog: [Using Aggregate Combinators in ClickHouse](https:\/\/clickhouse.com\/blog\/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)","meta_canonical":null}

3. Robots.txt Check

Query:

Response:

4. Spam/Ban Check

Query:

Response:

5. Seen Status Check

ℹ️ Skipped - page is already crawled

📄

INDEXABLE

✅

CRAWLED

2 days ago

🤖

ROBOTS ALLOWED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	0.1 months ago
History drop	PASS	`isNull(history_drop_reason)`	No drop reason
Spam/ban	PASS	`fh_dont_index != 1 AND ml_spam_score = 0`	ml_spam_score=0
Canonical	PASS	`meta_canonical IS NULL OR = '' OR = src_unparsed`	Not set

Page Details

Property	Value
URL	https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree
Last Crawled	2026-04-09 21:17:39 (2 days ago)
First Indexed	2025-02-21 00:39:49 (1 year ago)
HTTP Status Code	200
Meta Title	AggregatingMergeTree table engine \| ClickHouse Docs
Meta Description	Replaces all rows with the same primary key (or more accurately, with the same [sorting key](../../../engines/table-engines/mergetree-family/mergetree.md)) with a single row (within a single data part) that stores a combination of states of aggregate functions.
Meta Canonical	null
Boilerpipe Text	The engine inherits from MergeTree , altering the logic for data parts merging. ClickHouse replaces all rows with the same primary key (or more accurately, with the same sorting key ) with a single row (within a single data part) that stores a combination of states of aggregate functions. You can use AggregatingMergeTree tables for incremental data aggregation, including for aggregated materialized views. You can see an example of how to use the AggregatingMergeTree and Aggregate functions in the below video: The engine processes all columns with the following types: AggregateFunction SimpleAggregateFunction It is appropriate to use AggregatingMergeTree if it reduces the number of rows by orders. Creating a table CREATE TABLE [ IF NOT EXISTS ] [ db . ] table_name [ ON CLUSTER cluster ] ( name1 [ type1 ] [ DEFAULT \| MATERIALIZED \| ALIAS expr1 ] , name2 [ type2 ] [ DEFAULT \| MATERIALIZED \| ALIAS expr2 ] , . . . ) ENGINE = AggregatingMergeTree ( ) [ PARTITION BY expr ] [ ORDER BY expr ] [ SAMPLE BY expr ] [ TTL expr ] [ SETTINGS name = value , . . . ] For a description of request parameters, see request description . Query clauses When creating an AggregatingMergeTree table, the same clauses are required as when creating a MergeTree table. Deprecated Method for Creating a Table SELECT and INSERT To insert data, use INSERT SELECT query with aggregate -State- functions. When selecting data from AggregatingMergeTree table, use GROUP BY clause and the same aggregate functions as when inserting data, but using the -Merge suffix. In the results of SELECT query, the values of AggregateFunction type have implementation-specific binary representation for all of the ClickHouse output formats. For example, if you dump data into TabSeparated format with a SELECT query, then this dump can be loaded back using an INSERT query. Example of an aggregated materialized view The following example assumes that you have a database named test . Create it if it doesn't already exist using the command below: CREATE DATABASE test ; Now create the table test.visits that contains the raw data: CREATE TABLE test . visits ( StartDate DateTime64 NOT NULL , CounterID UInt64 , Sign Nullable ( Int32 ) , UserID Nullable ( Int32 ) ) ENGINE = MergeTree ORDER BY ( StartDate , CounterID ) ; Next, you need an AggregatingMergeTree table that will store AggregationFunction s that keep track of the total number of visits and the number of unique users. Create an AggregatingMergeTree materialized view that watches the test.visits table, and uses the AggregateFunction type: CREATE TABLE test . agg_visits ( StartDate DateTime64 NOT NULL , CounterID UInt64 , Visits AggregateFunction ( sum , Nullable ( Int32 ) ) , Users AggregateFunction ( uniq , Nullable ( Int32 ) ) ) ENGINE = AggregatingMergeTree ( ) ORDER BY ( StartDate , CounterID ) ; Create a materialized view that populates test.agg_visits from test.visits : CREATE MATERIALIZED VIEW test . visits_mv TO test . agg_visits AS SELECT StartDate , CounterID , sumState ( Sign ) AS Visits , uniqState ( UserID ) AS Users FROM test . visits GROUP BY StartDate , CounterID ; Insert data into the test.visits table: INSERT INTO test . visits ( StartDate , CounterID , Sign , UserID ) VALUES ( 1667446031000 , 1 , 3 , 4 ) , ( 1667446031000 , 1 , 6 , 3 ) ; The data is inserted in both test.visits and test.agg_visits . To get the aggregated data, execute a query such as SELECT ... GROUP BY ... from the materialized view test.visits_mv : SELECT StartDate , sumMerge ( Visits ) AS Visits , uniqMerge ( Users ) AS Users FROM test . visits_mv GROUP BY StartDate ORDER BY StartDate ; ┌───────────────StartDate─┬─Visits─┬─Users─┐ │ 2022-11-03 03:27:11.000 │ 9 │ 2 │ └─────────────────────────┴────────┴───────┘ Add another couple of records to test.visits , but this time try using a different timestamp for one of the records: INSERT INTO test . visits ( StartDate , CounterID , Sign , UserID ) VALUES ( 1669446031000 , 2 , 5 , 10 ) , ( 1667446031000 , 3 , 7 , 5 ) ; Run the SELECT query again, which will return the following output: ┌───────────────StartDate─┬─Visits─┬─Users─┐ │ 2022-11-03 03:27:11.000 │ 16 │ 3 │ │ 2022-11-26 07:00:31.000 │ 5 │ 1 │ └─────────────────────────┴────────┴───────┘ In some cases, you might want to avoid pre-aggregating rows at insert time to shift the cost of aggregation from insert time to merge time. Ordinarily, it is necessary to include the columns which are not part of the aggregation in the GROUP BY clause of the materialized view definition to avoid an error. However, you can make use of the initializeAggregation function with setting optimize_on_insert = 0 (it is turned on by default) to achieve this. Use of GROUP BY is no longer required in this case: CREATE MATERIALIZED VIEW test . visits_mv TO test . agg_visits AS SELECT StartDate , CounterID , initializeAggregation ( 'sumState' , Sign ) AS Visits , initializeAggregation ( 'uniqState' , UserID ) AS Users FROM test . visits ; Note When using initializeAggregation , an aggregate state is created for each individual row without grouping. Each source row produces one row in the materialized view, and the actual aggregation happens later when the AggregatingMergeTree merges parts. This is only true if optimize_on_insert = 0 . Related content Blog: Using Aggregate Combinators in ClickHouse
Markdown	[Skip to main content](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [English](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [日本語](https://clickhouse.com/docs/jp/engines/table-engines/mergetree-family/aggregatingmergetree) - [中文](https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/aggregatingmergetree) - [Русский](https://clickhouse.com/docs/ru/engines/table-engines/mergetree-family/aggregatingmergetree) - [한국어](https://clickhouse.com/docs/ko/engines/table-engines/mergetree-family/aggregatingmergetree) [Skip to main content](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) - [Products](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [ClickHouse Cloud Best way to use ClickHouse. Available on AWS, GCP, and Azure.](https://clickhouse.com/cloud) - [BYOC (Bring Your Own Cloud) The fully managed ClickHouse Cloud service, Can be deployed in your AWS account.](https://clickhouse.com/cloud/bring-your-own-cloud) - [ClickHouse Set up a database with open-source ClickHouse. ClickHouse](https://clickhouse.com/clickhouse) - [Discover more than 100 integrations.](https://clickhouse.com/integrations) [Discover more than 100 integrations.](https://clickhouse.com/integrations) - [Use cases](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [Real-time analytics](https://clickhouse.com/use-cases/real-time-analytics) - [Machine Learning & Generative AI](https://clickhouse.com/use-cases/machine-learning-and-data-science) - [Business Intelligence](https://clickhouse.com/use-cases/data-warehousing) - [Logs, Events, Traces](https://clickhouse.com/use-cases/observability) - [All use cases](https://clickhouse.com/use-cases) [All use cases](https://clickhouse.com/use-cases) - [Documentation](https://clickhouse.com/docs) - [Resources](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [User stories](https://clickhouse.com/user-stories) - [Blog](https://clickhouse.com/blog) - [Events](https://clickhouse.com/company/events) - [Learning and certification](https://clickhouse.com/learn) - [Comparison](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [BigQuery](https://clickhouse.com/comparison/bigquery) - [PostgreSQL](https://clickhouse.com/comparison/postgresql) - [Redshift](https://clickhouse.com/comparison/redshift) - [Rockset](https://clickhouse.com/comparison/rockset) - [Snowflake](https://clickhouse.com/comparison/snowflake) - [Video](https://clickhouse.com/videos) - [Demo](https://clickhouse.com/demos) - [Pricing](https://clickhouse.com/pricing) - [Contact](https://clickhouse.com/company/contact?loc=nav) [46\.8k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [Search`Ctrl``K`](https://clickhouse.com/docs/search) [Sign in](https://console.clickhouse.cloud/signIn?loc=docs-nav-signIn-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461) [Get started](https://console.clickhouse.cloud/signUp?loc=docs-nav-signUp-cta&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461) [Get started](https://clickhouse.com/docs/introduction-clickhouse) [Cloud](https://clickhouse.com/docs/cloud/overview) [Manage data](https://clickhouse.com/docs/updating-data) [Server admin](https://clickhouse.com/docs/guides/manage-and-deploy-index) [Reference](https://clickhouse.com/docs/sql-reference) [Integrations](https://clickhouse.com/docs/integrations) [ClickStack](https://clickhouse.com/docs/use-cases/observability/clickstack/overview) [chDB](https://clickhouse.com/docs/chdb) [About](https://clickhouse.com/docs/about) [Knowledge Base](https://clickhouse.com/docs/knowledgebase) [English](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [English](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [日本語](https://clickhouse.com/docs/jp/engines/table-engines/mergetree-family/aggregatingmergetree) - [中文](https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/aggregatingmergetree) - [Русский](https://clickhouse.com/docs/ru/engines/table-engines/mergetree-family/aggregatingmergetree) - [한국어](https://clickhouse.com/docs/ko/engines/table-engines/mergetree-family/aggregatingmergetree) [Search`Ctrl``K`](https://clickhouse.com/docs/search) - [Introduction](https://clickhouse.com/docs/sql-reference) - [Syntax](https://clickhouse.com/docs/sql-reference/syntax) - [Input and Output Formats](https://clickhouse.com/docs/sql-reference/formats) - [Data types](https://clickhouse.com/docs/sql-reference/data-types) - [Statements](https://clickhouse.com/docs/sql-reference/statements) - [Operators](https://clickhouse.com/docs/sql-reference/operators) - [Engines](https://clickhouse.com/docs/engines) - [Database Engines](https://clickhouse.com/docs/engines/database-engines) - [Table Engines](https://clickhouse.com/docs/engines/table-engines) - [MergeTree Family](https://clickhouse.com/docs/engines/table-engines/mergetree-family) - [MergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) - [Replicated\](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication) - [Custom Partitioning Key](https://clickhouse.com/docs/engines/table-engines/mergetree-family/custom-partitioning-key) - [ReplacingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree) - [CoalescingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/coalescingmergetree) - [SummingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/summingmergetree) - [AggregatingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [CollapsingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/collapsingmergetree) - [VersionedCollapsingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/versionedcollapsingmergetree) - [GraphiteMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/graphitemergetree) - [Exact and Approximate Vector Search](https://clickhouse.com/docs/engines/table-engines/mergetree-family/annindexes) - [Full-text Search with Text Indexes](https://clickhouse.com/docs/engines/table-engines/mergetree-family/textindexes) - [Log family](https://clickhouse.com/docs/engines/table-engines/log-family) - [Integrations](https://clickhouse.com/docs/engines/table-engines/integrations) - [Special](https://clickhouse.com/docs/engines/table-engines/special) - [Functions](https://clickhouse.com/docs/sql-reference/functions) - [Regular functions](https://clickhouse.com/docs/sql-reference/functions/regular-functions) - [Aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions) - [Table functions](https://clickhouse.com/docs/sql-reference/table-functions) - [Window functions](https://clickhouse.com/docs/sql-reference/window-functions) - [Formats](https://clickhouse.com/docs/interfaces/formats) - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes) - [Engines](https://clickhouse.com/docs/engines) - [Table Engines](https://clickhouse.com/docs/engines/table-engines) - [MergeTree Family](https://clickhouse.com/docs/engines/table-engines/mergetree-family) - AggregatingMergeTree [Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree.md) # AggregatingMergeTree table engine The engine inherits from [MergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/versionedcollapsingmergetree), altering the logic for data parts merging. ClickHouse replaces all rows with the same primary key (or more accurately, with the same [sorting key](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree)) with a single row (within a single data part) that stores a combination of states of aggregate functions. You can use `AggregatingMergeTree` tables for incremental data aggregation, including for aggregated materialized views. You can see an example of how to use the AggregatingMergeTree and Aggregate functions in the below video: The engine processes all columns with the following types: - [`AggregateFunction`](https://clickhouse.com/docs/sql-reference/data-types/aggregatefunction) - [`SimpleAggregateFunction`](https://clickhouse.com/docs/sql-reference/data-types/simpleaggregatefunction) It is appropriate to use `AggregatingMergeTree` if it reduces the number of rows by orders. ## Creating a table[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#creating-a-table "Direct link to Creating a table") ``` CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT\|MATERIALIZED\|ALIAS expr1], name2 [type2] [DEFAULT\|MATERIALIZED\|ALIAS expr2], ... ) ENGINE = AggregatingMergeTree() [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [TTL expr] [SETTINGS name=value, ...] ``` For a description of request parameters, see [request description](https://clickhouse.com/docs/sql-reference/statements/create/table). Query clauses* When creating an `AggregatingMergeTree` table, the same [clauses](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) are required as when creating a `MergeTree` table. Deprecated Method for Creating a Table Note Do not use this method in new projects and, if possible, switch the old projects to the method described above. ``` CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT\|MATERIALIZED\|ALIAS expr1], name2 [type2] [DEFAULT\|MATERIALIZED\|ALIAS expr2], ... ) ENGINE [=] AggregatingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity) ``` All of the parameters have the same meaning as in `MergeTree`. ## SELECT and INSERT[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#select-and-insert "Direct link to SELECT and INSERT") To insert data, use [INSERT SELECT](https://clickhouse.com/docs/sql-reference/statements/insert-into) query with aggregate -State- functions. When selecting data from `AggregatingMergeTree` table, use `GROUP BY` clause and the same aggregate functions as when inserting data, but using the `-Merge` suffix. In the results of `SELECT` query, the values of `AggregateFunction` type have implementation-specific binary representation for all of the ClickHouse output formats. For example, if you dump data into `TabSeparated` format with a `SELECT` query, then this dump can be loaded back using an `INSERT` query. ## Example of an aggregated materialized view[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#example-of-an-aggregated-materialized-view "Direct link to Example of an aggregated materialized view") The following example assumes that you have a database named `test`. Create it if it doesn't already exist using the command below: ``` CREATE DATABASE test; ``` Now create the table `test.visits` that contains the raw data: ``` CREATE TABLE test.visits ( StartDate DateTime64 NOT NULL, CounterID UInt64, Sign Nullable(Int32), UserID Nullable(Int32) ) ENGINE = MergeTree ORDER BY (StartDate, CounterID); ``` Next, you need an `AggregatingMergeTree` table that will store `AggregationFunction`s that keep track of the total number of visits and the number of unique users. Create an `AggregatingMergeTree` materialized view that watches the `test.visits` table, and uses the [`AggregateFunction`](https://clickhouse.com/docs/sql-reference/data-types/aggregatefunction) type: ``` CREATE TABLE test.agg_visits ( StartDate DateTime64 NOT NULL, CounterID UInt64, Visits AggregateFunction(sum, Nullable(Int32)), Users AggregateFunction(uniq, Nullable(Int32)) ) ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID); ``` Create a materialized view that populates `test.agg_visits` from `test.visits`: ``` CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits AS SELECT StartDate, CounterID, sumState(Sign) AS Visits, uniqState(UserID) AS Users FROM test.visits GROUP BY StartDate, CounterID; ``` Insert data into the `test.visits` table: ``` INSERT INTO test.visits (StartDate, CounterID, Sign, UserID) VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3); ``` The data is inserted in both `test.visits` and `test.agg_visits`. To get the aggregated data, execute a query such as `SELECT ... GROUP BY ...` from the materialized view `test.visits_mv`: ``` SELECT StartDate, sumMerge(Visits) AS Visits, uniqMerge(Users) AS Users FROM test.visits_mv GROUP BY StartDate ORDER BY StartDate; ``` ``` ┌───────────────StartDate─┬─Visits─┬─Users─┐ │ 2022-11-03 03:27:11.000 │ 9 │ 2 │ └─────────────────────────┴────────┴───────┘ ``` Add another couple of records to `test.visits`, but this time try using a different timestamp for one of the records: ``` INSERT INTO test.visits (StartDate, CounterID, Sign, UserID) VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5); ``` Run the `SELECT` query again, which will return the following output: ``` ┌───────────────StartDate─┬─Visits─┬─Users─┐ │ 2022-11-03 03:27:11.000 │ 16 │ 3 │ │ 2022-11-26 07:00:31.000 │ 5 │ 1 │ └─────────────────────────┴────────┴───────┘ ``` In some cases, you might want to avoid pre-aggregating rows at insert time to shift the cost of aggregation from insert time to merge time. Ordinarily, it is necessary to include the columns which are not part of the aggregation in the `GROUP BY` clause of the materialized view definition to avoid an error. However, you can make use of the [`initializeAggregation`](https://clickhouse.com/docs/sql-reference/functions/other-functions#initializeAggregation) function with setting `optimize_on_insert = 0` (it is turned on by default) to achieve this. Use of `GROUP BY` is no longer required in this case: ``` CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits AS SELECT StartDate, CounterID, initializeAggregation('sumState', Sign) AS Visits, initializeAggregation('uniqState', UserID) AS Users FROM test.visits; ``` Note When using `initializeAggregation`, an aggregate state is created for each individual row without grouping. Each source row produces one row in the materialized view, and the actual aggregation happens later when the `AggregatingMergeTree` merges parts. This is only true if `optimize_on_insert = 0`. ## Related content[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#related-content "Direct link to Related content") - Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) [Previous SummingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/summingmergetree) [Next CollapsingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/collapsingmergetree) - [Creating a table](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#creating-a-table) - [SELECT and INSERT](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#select-and-insert) - [Example of an aggregated materialized view](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#example-of-an-aggregated-materialized-view) - [Related content](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#related-content) Was this page helpful? ###### Try ClickHouse Cloud for FREE Separation of storage and compute, automatic scaling, built-in SQL console, and lots more. \$300 in free credits when signing up. [Try it for Free](https://console.clickhouse.cloud/signUp?loc=doc-card-banner&glxid=d7cca8d0-d518-41d8-b4ff-08301e0b31a4&pagePath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&origPath=%2Fdocs%2Fengines%2Ftable-engines%2Fmergetree-family%2Faggregatingmergetree&utm_ga=GA1.1.1947800976.1775769461) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) © 2016–2026 ClickHouse, Inc. [Trademark](https://clickhouse.com/legal/trademark-policy)·[Privacy](https://clickhouse.com/legal/privacy-policy)·[Security](https://trust.clickhouse.com/)·[Terms of Service](https://clickhouse.com/legal/agreements/terms-of-service) ![](https://static.scarf.sh/a.png?x-pxid=e6377503-591b-4886-9398-e69c7fee0b91) [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN - Get started▼ - Cloud▼ - Manage data▼ - Server admin▼ - Reference▼ - Integrations▼ - ClickStack▼ - chDB▼ - About▼ [![ClickHouse](https://clickhouse.com/docs/img/ch_logo_docs.svg)](https://clickhouse.com/) EN main-menu - Introduction▼ - Engines▼ - Database Engines▼ - Table Engines▼ - MergeTree Family▼ - [MergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) - [Replicated\*](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replication) - [Custom Partitioning Key](https://clickhouse.com/docs/engines/table-engines/mergetree-family/custom-partitioning-key) - [ReplacingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree) - [CoalescingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/coalescingmergetree) - [SummingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/summingmergetree) - [AggregatingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree) - [CollapsingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/collapsingmergetree) - [VersionedCollapsingMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/versionedcollapsingmergetree) - [GraphiteMergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/graphitemergetree) - [Exact and Approximate Vector Search](https://clickhouse.com/docs/engines/table-engines/mergetree-family/annindexes) - [Full-text Search with Text Indexes](https://clickhouse.com/docs/engines/table-engines/mergetree-family/textindexes) - Log family▼ - Integrations▼ - Special▼ - Functions▼ - Formats▼ - [Data Lakes](https://clickhouse.com/docs/sql-reference/datalakes)
Readable Markdown	The engine inherits from [MergeTree](https://clickhouse.com/docs/engines/table-engines/mergetree-family/versionedcollapsingmergetree), altering the logic for data parts merging. ClickHouse replaces all rows with the same primary key (or more accurately, with the same [sorting key](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree)) with a single row (within a single data part) that stores a combination of states of aggregate functions. You can use `AggregatingMergeTree` tables for incremental data aggregation, including for aggregated materialized views. You can see an example of how to use the AggregatingMergeTree and Aggregate functions in the below video: The engine processes all columns with the following types: - [`AggregateFunction`](https://clickhouse.com/docs/sql-reference/data-types/aggregatefunction) - [`SimpleAggregateFunction`](https://clickhouse.com/docs/sql-reference/data-types/simpleaggregatefunction) It is appropriate to use `AggregatingMergeTree` if it reduces the number of rows by orders. ## Creating a table[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#creating-a-table "Direct link to Creating a table") ``` CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT\|MATERIALIZED\|ALIAS expr1], name2 [type2] [DEFAULT\|MATERIALIZED\|ALIAS expr2], ... ) ENGINE = AggregatingMergeTree() [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [TTL expr] [SETTINGS name=value, ...] ``` For a description of request parameters, see [request description](https://clickhouse.com/docs/sql-reference/statements/create/table). Query clauses When creating an `AggregatingMergeTree` table, the same [clauses](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree) are required as when creating a `MergeTree` table. Deprecated Method for Creating a Table ## SELECT and INSERT[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#select-and-insert "Direct link to SELECT and INSERT") To insert data, use [INSERT SELECT](https://clickhouse.com/docs/sql-reference/statements/insert-into) query with aggregate -State- functions. When selecting data from `AggregatingMergeTree` table, use `GROUP BY` clause and the same aggregate functions as when inserting data, but using the `-Merge` suffix. In the results of `SELECT` query, the values of `AggregateFunction` type have implementation-specific binary representation for all of the ClickHouse output formats. For example, if you dump data into `TabSeparated` format with a `SELECT` query, then this dump can be loaded back using an `INSERT` query. ## Example of an aggregated materialized view[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#example-of-an-aggregated-materialized-view "Direct link to Example of an aggregated materialized view") The following example assumes that you have a database named `test`. Create it if it doesn't already exist using the command below: ``` CREATE DATABASE test; ``` Now create the table `test.visits` that contains the raw data: ``` CREATE TABLE test.visits ( StartDate DateTime64 NOT NULL, CounterID UInt64, Sign Nullable(Int32), UserID Nullable(Int32) ) ENGINE = MergeTree ORDER BY (StartDate, CounterID); ``` Next, you need an `AggregatingMergeTree` table that will store `AggregationFunction`s that keep track of the total number of visits and the number of unique users. Create an `AggregatingMergeTree` materialized view that watches the `test.visits` table, and uses the [`AggregateFunction`](https://clickhouse.com/docs/sql-reference/data-types/aggregatefunction) type: ``` CREATE TABLE test.agg_visits ( StartDate DateTime64 NOT NULL, CounterID UInt64, Visits AggregateFunction(sum, Nullable(Int32)), Users AggregateFunction(uniq, Nullable(Int32)) ) ENGINE = AggregatingMergeTree() ORDER BY (StartDate, CounterID); ``` Create a materialized view that populates `test.agg_visits` from `test.visits`: ``` CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits AS SELECT StartDate, CounterID, sumState(Sign) AS Visits, uniqState(UserID) AS Users FROM test.visits GROUP BY StartDate, CounterID; ``` Insert data into the `test.visits` table: ``` INSERT INTO test.visits (StartDate, CounterID, Sign, UserID) VALUES (1667446031000, 1, 3, 4), (1667446031000, 1, 6, 3); ``` The data is inserted in both `test.visits` and `test.agg_visits`. To get the aggregated data, execute a query such as `SELECT ... GROUP BY ...` from the materialized view `test.visits_mv`: ``` SELECT StartDate, sumMerge(Visits) AS Visits, uniqMerge(Users) AS Users FROM test.visits_mv GROUP BY StartDate ORDER BY StartDate; ``` ``` ┌───────────────StartDate─┬─Visits─┬─Users─┐ │ 2022-11-03 03:27:11.000 │ 9 │ 2 │ └─────────────────────────┴────────┴───────┘ ``` Add another couple of records to `test.visits`, but this time try using a different timestamp for one of the records: ``` INSERT INTO test.visits (StartDate, CounterID, Sign, UserID) VALUES (1669446031000, 2, 5, 10), (1667446031000, 3, 7, 5); ``` Run the `SELECT` query again, which will return the following output: ``` ┌───────────────StartDate─┬─Visits─┬─Users─┐ │ 2022-11-03 03:27:11.000 │ 16 │ 3 │ │ 2022-11-26 07:00:31.000 │ 5 │ 1 │ └─────────────────────────┴────────┴───────┘ ``` In some cases, you might want to avoid pre-aggregating rows at insert time to shift the cost of aggregation from insert time to merge time. Ordinarily, it is necessary to include the columns which are not part of the aggregation in the `GROUP BY` clause of the materialized view definition to avoid an error. However, you can make use of the [`initializeAggregation`](https://clickhouse.com/docs/sql-reference/functions/other-functions#initializeAggregation) function with setting `optimize_on_insert = 0` (it is turned on by default) to achieve this. Use of `GROUP BY` is no longer required in this case: ``` CREATE MATERIALIZED VIEW test.visits_mv TO test.agg_visits AS SELECT StartDate, CounterID, initializeAggregation('sumState', Sign) AS Visits, initializeAggregation('uniqState', UserID) AS Users FROM test.visits; ``` Note When using `initializeAggregation`, an aggregate state is created for each individual row without grouping. Each source row produces one row in the materialized view, and the actual aggregation happens later when the `AggregatingMergeTree` merges parts. This is only true if `optimize_on_insert = 0`. ## Related content[](https://clickhouse.com/docs/engines/table-engines/mergetree-family/aggregatingmergetree#related-content "Direct link to Related content") - Blog: [Using Aggregate Combinators in ClickHouse](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
Shard	89 (laksa)
Root Hash	12633450985039531489
Unparsed URL	com,clickhouse!/docs/engines/table-engines/mergetree-family/aggregatingmergetree s443