🕷️ Crawler Inspector

URL Lookup

Direct Parameter Lookup

Raw Queries and Responses

1. Shard Calculation

Query:

Response:

Calculated Shard: 159 (from laksa078)

2. Crawled Status Check

Query:

curl -X POST \
  'http://laksa159.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.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function\')), getAhrefsUnparsedNoserviceFromURL(\'https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function\'))) FORMAT JSONEachRow'

Response:

{"found_url":"https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function","crawl_time":1760694076,"first_indexed_time":1729605366,"http_code":200,"src_unparsed":"cn,ac,clickhouse!\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function s443","src_root_hash":"6418803694421178359","history_drop_reason":null,"meta_title":"GROUPING | ClickHouse 文档 - ClickHouse 数据库","meta_descriptions":[],"attrs_boilerpipe_text":"GROUPING  ROLLUP  和  CUBE  是 GROUP BY 的修饰符。两者都计算小计。ROLLUP 接受列的有序列表，例如  (day, month, year) ，并在每个聚合级别计算小计，然后计算总计。CUBE 计算指定列的所有可能组合的小计。GROUPING 标识 ROLLUP 或 CUBE 返回的哪些行是超级聚合，哪些行是未修改的 GROUP BY 返回的行。 GROUPING 函数将多个列作为参数，并返回一个位掩码。 1  表示  ROLLUP  或  CUBE  修饰符返回的  GROUP BY  行是小计 0  表示  ROLLUP  或  CUBE  返回的行不是小计 GROUPING SETS  默认情况下，CUBE 修饰符计算传递给 CUBE 的列的所有可能组合的小计。GROUPING SETS 允许您指定要计算的特定组合。 分析分层数据是 ROLLUP、CUBE 和 GROUPING SETS 修饰符的一个很好的用例。此处的示例是一个包含有关跨两个数据中心安装的 Linux 发行版及其版本的数据表。按发行版、版本和位置查看数据可能很有价值。 加载示例数据  CREATE TABLE  servers  (  datacenter  VARCHAR ( 255 ) ,                          distro  VARCHAR ( 255 ) NOT NULL ,                          version  VARCHAR ( 50 ) NOT NULL ,                          quantity  INT ) ORDER BY ( datacenter ,  distro ,  version ) INSERT INTO  servers ( datacenter ,  distro ,  version ,  quantity ) VALUES ( 'Schenectady' , 'Arch' , '2022.08.05' , 50 ) , ( 'Westport' , 'Arch' , '2022.08.05' , 40 ) , ( 'Schenectady' , 'Arch' , '2021.09.01' , 30 ) , ( 'Westport' , 'Arch' , '2021.09.01' , 20 ) , ( 'Schenectady' , 'Arch' , '2020.05.01' , 10 ) , ( 'Westport' , 'Arch' , '2020.05.01' , 5 ) , ( 'Schenectady' , 'RHEL' , '9' , 60 ) , ( 'Westport' , 'RHEL' , '9' , 70 ) , ( 'Westport' , 'RHEL' , '7' , 80 ) , ( 'Schenectady' , 'RHEL' , '7' , 80 ) ┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch   │ 2020.05.01 │       10 │ │ Schenectady │ Arch   │ 2021.09.01 │       30 │ │ Schenectady │ Arch   │ 2022.08.05 │       50 │ │ Schenectady │ RHEL   │ 7          │       80 │ │ Schenectady │ RHEL   │ 9          │       60 │ │ Westport    │ Arch   │ 2020.05.01 │        5 │ │ Westport    │ Arch   │ 2021.09.01 │       20 │ │ Westport    │ Arch   │ 2022.08.05 │       40 │ │ Westport    │ RHEL   │ 7          │       80 │ │ Westport    │ RHEL   │ 9          │       70 │ └─────────────┴────────┴────────────┴──────────┘ 10 rows in set. Elapsed: 0.409 sec. 简单查询  获取每个数据中心按发行版划分的服务器计数 SELECT     datacenter ,     distro , SUM ( quantity )  qty FROM     servers GROUP BY     datacenter ,     distro ; ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL   │ 140 │ │ Westport    │ Arch   │  65 │ │ Schenectady │ Arch   │  90 │ │ Westport    │ RHEL   │ 150 │ └─────────────┴────────┴─────┘ 4 rows in set. Elapsed: 0.212 sec. SELECT     datacenter , SUM ( quantity )  qty FROM     servers GROUP BY     datacenter ; ┌─datacenter──┬─qty─┐ │ Westport    │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 2 rows in set. Elapsed: 0.277 sec.  SELECT     distro , SUM ( quantity )  qty FROM     servers GROUP BY     distro ; ┌─distro─┬─qty─┐ │ Arch   │ 155 │ │ RHEL   │ 290 │ └────────┴─────┘ 2 rows in set. Elapsed: 0.352 sec.  SELECT SUM ( quantity )  qty FROM     servers ; ┌─qty─┐ │ 445 │ └─────┘ 1 row in set. Elapsed: 0.244 sec.  将多个 GROUP BY 语句与 GROUPING SETS 进行比较  在没有 CUBE、ROLLUP 或 GROUPING SETS 的情况下分解数据 SELECT     datacenter ,     distro , SUM ( quantity )  qty FROM     servers GROUP BY     datacenter ,     distro UNION ALL SELECT     datacenter , null , SUM ( quantity )  qty FROM     servers GROUP BY     datacenter UNION ALL SELECT null ,     distro , SUM ( quantity )  qty FROM     servers GROUP BY     distro UNION ALL SELECT null , null , SUM ( quantity )  qty FROM     servers ; ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ       │ ᴺᵁᴸᴸ   │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport    │ ᴺᵁᴸᴸ   │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ   │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL   │ 140 │ │ Westport    │ Arch   │  65 │ │ Schenectady │ Arch   │  90 │ │ Westport    │ RHEL   │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ       │ Arch   │ 155 │ │ ᴺᵁᴸᴸ       │ RHEL   │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.527 sec.  使用 GROUPING SETS 获取相同的信息 SELECT     datacenter ,     distro , SUM ( quantity )  qty FROM     servers GROUP BY     GROUPING SETS ( ( datacenter , distro ) , ( datacenter ) , ( distro ) , ( ) ) ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL   │ 140 │ │ Westport    │ Arch   │  65 │ │ Schenectady │ Arch   │  90 │ │ Westport    │ RHEL   │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport    │        │ 215 │ │ Schenectady │        │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │            │        │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │            │ Arch   │ 155 │ │            │ RHEL   │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.427 sec. 将 CUBE 与 GROUPING SETS 进行比较  下一个查询中的 CUBE， CUBE(datacenter,distro,version)  提供了一个可能没有意义的层次结构。跨两个发行版查看 Version 没有意义（因为 Arch 和 RHEL 没有相同的发布周期或版本命名标准）。以下 GROUPING SETS 示例更合适，因为它在同一集合中对  distro  和  version  进行分组。 SELECT    datacenter ,    distro ,    version , SUM ( quantity ) FROM    servers GROUP BY    CUBE ( datacenter , distro , version ) ORDER BY    datacenter ,    distro ; ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │             │        │ 7          │           160 │ │             │        │ 2020.05.01 │            15 │ │             │        │ 2021.09.01 │            50 │ │             │        │ 2022.08.05 │            90 │ │             │        │ 9          │           130 │ │             │        │            │           445 │ │             │ Arch   │ 2021.09.01 │            50 │ │             │ Arch   │ 2022.08.05 │            90 │ │             │ Arch   │ 2020.05.01 │            15 │ │             │ Arch   │            │           155 │ │             │ RHEL   │ 9          │           130 │ │             │ RHEL   │ 7          │           160 │ │             │ RHEL   │            │           290 │ │ Schenectady │        │ 9          │            60 │ │ Schenectady │        │ 2021.09.01 │            30 │ │ Schenectady │        │ 7          │            80 │ │ Schenectady │        │ 2022.08.05 │            50 │ │ Schenectady │        │ 2020.05.01 │            10 │ │ Schenectady │        │            │           230 │ │ Schenectady │ Arch   │ 2022.08.05 │            50 │ │ Schenectady │ Arch   │ 2021.09.01 │            30 │ │ Schenectady │ Arch   │ 2020.05.01 │            10 │ │ Schenectady │ Arch   │            │            90 │ │ Schenectady │ RHEL   │ 7          │            80 │ │ Schenectady │ RHEL   │ 9          │            60 │ │ Schenectady │ RHEL   │            │           140 │ │ Westport    │        │ 9          │            70 │ │ Westport    │        │ 2020.05.01 │             5 │ │ Westport    │        │ 2022.08.05 │            40 │ │ Westport    │        │ 7          │            80 │ │ Westport    │        │ 2021.09.01 │            20 │ │ Westport    │        │            │           215 │ │ Westport    │ Arch   │ 2020.05.01 │             5 │ │ Westport    │ Arch   │ 2021.09.01 │            20 │ │ Westport    │ Arch   │ 2022.08.05 │            40 │ │ Westport    │ Arch   │            │            65 │ │ Westport    │ RHEL   │ 9          │            70 │ │ Westport    │ RHEL   │ 7          │            80 │ │ Westport    │ RHEL   │            │           150 │ └─────────────┴────────┴────────────┴───────────────┘ 39 rows in set. Elapsed: 0.355 sec.  上面的示例中的 Version 在未与发行版关联时可能没有意义，如果我们跟踪内核版本，它可能是有意义的，因为内核版本可以与任一发行版关联。使用 GROUPING SETS，如下一个示例所示，可能是更好的选择。 SELECT     datacenter ,     distro ,     version , SUM ( quantity ) FROM  servers GROUP BY     GROUPING SETS  ( ( datacenter ,  distro ,  version ) , ( datacenter ,  distro ) ) ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport    │ RHEL   │ 9          │            70 │ │ Schenectady │ Arch   │ 2022.08.05 │            50 │ │ Schenectady │ Arch   │ 2021.09.01 │            30 │ │ Schenectady │ RHEL   │ 7          │            80 │ │ Westport    │ Arch   │ 2020.05.01 │             5 │ │ Westport    │ RHEL   │ 7          │            80 │ │ Westport    │ Arch   │ 2021.09.01 │            20 │ │ Westport    │ Arch   │ 2022.08.05 │            40 │ │ Schenectady │ RHEL   │ 9          │            60 │ │ Schenectady │ Arch   │ 2020.05.01 │            10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL   │         │           140 │ │ Westport    │ Arch   │         │            65 │ │ Schenectady │ Arch   │         │            90 │ │ Westport    │ RHEL   │         │           150 │ └─────────────┴────────┴─────────┴───────────────┘ 14 rows in set. Elapsed: 1.036 sec. ","attrs_markdown":"[跳到主要内容](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.ac.cn\/docs\/img\/ch_logo_docs.svg)![ClickHouse](https:\/\/clickhouse.ac.cn\/docs\/img\/ch_logo_docs_dark.svg)](https:\/\/clickhouse.ac.cn\/)\n\n- [产品](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [ClickHouse Cloud 使用 ClickHouse 的最佳方式。 在 AWS、GCP 和 Azure 上可用。](https:\/\/clickhouse.ac.cn\/cloud)\n  - [ClickHouse 使用开源数据库启动 源代码 ClickHouse。](https:\/\/clickhouse.ac.cn\/clickhouse)\n  - [查看 100 多个集成](https:\/\/clickhouse.ac.cn\/integrations)\n  \n  [查看 100 多个集成](https:\/\/clickhouse.ac.cn\/integrations)\n- [用例](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [实时分析](https:\/\/clickhouse.ac.cn\/use-cases\/real-time-analytics)\n  - [机器学习和 GenAI](https:\/\/clickhouse.ac.cn\/use-cases\/machine-learning-and-data-science)\n  - [商业智能](https:\/\/clickhouse.ac.cn\/use-cases\/business-intelligence)\n  - [日志、事件和追踪](https:\/\/clickhouse.ac.cn\/use-cases\/logging-and-metrics)\n  - [所有用例](https:\/\/clickhouse.ac.cn\/use-cases)\n  \n  [所有用例](https:\/\/clickhouse.ac.cn\/use-cases)\n- [文档](https:\/\/clickhouse.ac.cn\/docs)\n- [资源](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [用户故事](https:\/\/clickhouse.ac.cn\/user-stories)\n  - [博客](https:\/\/clickhouse.ac.cn\/blog)\n  - [学习和认证](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n    - [ClickHouse 学院](https:\/\/clickhouse.ac.cn\/learn)\n    - [免费在线培训](https:\/\/clickhouse.ac.cn\/company\/news-events?category=Free+Training#upcoming-events)\n    - [ClickHouse 认证](https:\/\/clickhouse.ac.cn\/learn\/certification)\n  - [对比](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n    - [BigQuery](https:\/\/clickhouse.ac.cn\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.ac.cn\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.ac.cn\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.ac.cn\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.ac.cn\/comparison\/snowflake)\n  - [视频](https:\/\/clickhouse.ac.cn\/videos)\n  - [演示](https:\/\/clickhouse.ac.cn\/demos)\n- [定价](https:\/\/clickhouse.ac.cn\/pricing)\n- [联系我们](https:\/\/clickhouse.ac.cn\/company\/contact?loc=nav)\n\n[38\\.6k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[登录](https:\/\/clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta)\n\n[开始使用](https:\/\/clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta)\n\n[入门](https:\/\/clickhouse.ac.cn\/docs\/)\n\n[云](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/overview)\n\n[管理数据](https:\/\/clickhouse.ac.cn\/docs\/en\/updating-data)\n\n[服务器管理](https:\/\/clickhouse.ac.cn\/docs\/en\/architecture\/introduction)\n\n[SQL 参考](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference)\n\n[集成](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations)\n\n[chDB](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb)\n\n[关于](https:\/\/clickhouse.ac.cn\/docs\/en\/about-clickhouse)\n\n[知识库](https:\/\/clickhouse.ac.cn\/docs\/knowledgebase)\n\n- [英语](https:\/\/clickhouse.ac.cn\/docs\/)\n- [俄语](https:\/\/clickhouse.ac.cn\/docs\/ru)\n- [中文](https:\/\/clickhouse.ac.cn\/docs\/zh)\n\n深色模式\n\n搜索\n\n- [入门]()\n  - [介绍](https:\/\/clickhouse.ac.cn\/docs\/)\n  - [入门指南](https:\/\/clickhouse.ac.cn\/docs\/en\/guides\/creating-tables)\n  - [概念](https:\/\/clickhouse.ac.cn\/docs\/en\/concepts\/why-clickhouse-is-so-fast)\n  - [迁移指南](https:\/\/clickhouse.ac.cn\/docs\/en\/migrations\/bigquery)\n  - [用例指南](https:\/\/clickhouse.ac.cn\/docs\/en\/migrations\/bigquery)\n  - [示例数据集](https:\/\/clickhouse.ac.cn\/docs\/en\/getting-started\/example-datasets)\n- [云]()\n  - [开始使用](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/get-started)\n  - [最佳实践](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/bestpractices)\n  - [管理云](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/manage)\n  - [安全](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/security)\n  - [Cloud API](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/manage\/cloud-api)\n  - [迁移到云](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/migration)\n  - [云参考](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/reference\/architecture)\n- [管理数据]()\n  - [更新数据](https:\/\/clickhouse.ac.cn\/docs\/en\/updating-data)\n  - [数据建模](https:\/\/clickhouse.ac.cn\/docs\/en\/data-modeling\/schema-design)\n  - [删除数据](https:\/\/clickhouse.ac.cn\/docs\/en\/managing-data\/deleting-data\/overview)\n  - [性能和优化](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/optimizing-performance\/profile-guided-optimization)\n- [服务器管理]()\n  - [部署和扩展](https:\/\/clickhouse.ac.cn\/docs\/en\/architecture\/cluster-deployment)\n  - [安全和身份验证](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/external-authenticators\/http)\n  - [设置](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/settings)\n  - [工具和实用程序](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/utilities\/backupview)\n  - [系统表](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/system-tables\/asynchronous_insert_log)\n- [SQL 参考]()\n  - [介绍](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference)\n  - [函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/functions)\n  - [引擎](https:\/\/clickhouse.ac.cn\/docs\/en\/engines\/database-engines)\n  - [其他功能](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/operators)\n- [集成]()\n  - [ClickPipes](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/clickpipes)\n  - [数据格式](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/data-formats)\n  - [所有集成](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations)\n  - [客户端和驱动程序](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/sql-clients\/clickhouse-client-local)\n- [chDB]()\n  - [学习 chDB](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb)\n  - [指南](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb\/guides\/jupysql)\n  - [语言客户端](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb\/install\/python)\n- [关于]()\n  - [关于 ClickHouse](https:\/\/clickhouse.ac.cn\/docs\/en\/about-us\/adopters)\n  - [更新日志](https:\/\/clickhouse.ac.cn\/docs\/en\/whats-new\/security-changelog)\n  - [支持](https:\/\/clickhouse.ac.cn\/docs\/en\/about-us\/support)\n  - [开发和贡献](https:\/\/clickhouse.ac.cn\/docs\/en\/development\/developer-instruction)\n\n[跳到主要内容](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#__docusaurus_skipToContent_fallback)\n\n[![ClickHouse](https:\/\/clickhouse.ac.cn\/docs\/img\/ch_logo_docs.svg)![ClickHouse](https:\/\/clickhouse.ac.cn\/docs\/img\/ch_logo_docs_dark.svg)](https:\/\/clickhouse.ac.cn\/)\n\n- [产品](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [ClickHouse Cloud 使用 ClickHouse 的最佳方式。 在 AWS、GCP 和 Azure 上可用。](https:\/\/clickhouse.ac.cn\/cloud)\n  - [ClickHouse 使用开源数据库启动 源代码 ClickHouse。](https:\/\/clickhouse.ac.cn\/clickhouse)\n  - [查看 100 多个集成](https:\/\/clickhouse.ac.cn\/integrations)\n  \n  [查看 100 多个集成](https:\/\/clickhouse.ac.cn\/integrations)\n- [用例](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [实时分析](https:\/\/clickhouse.ac.cn\/use-cases\/real-time-analytics)\n  - [机器学习和 GenAI](https:\/\/clickhouse.ac.cn\/use-cases\/machine-learning-and-data-science)\n  - [商业智能](https:\/\/clickhouse.ac.cn\/use-cases\/business-intelligence)\n  - [日志、事件和追踪](https:\/\/clickhouse.ac.cn\/use-cases\/logging-and-metrics)\n  - [所有用例](https:\/\/clickhouse.ac.cn\/use-cases)\n  \n  [所有用例](https:\/\/clickhouse.ac.cn\/use-cases)\n- [文档](https:\/\/clickhouse.ac.cn\/docs)\n- [资源](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  \n  - [用户故事](https:\/\/clickhouse.ac.cn\/user-stories)\n  - [博客](https:\/\/clickhouse.ac.cn\/blog)\n  - [学习和认证](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n    - [ClickHouse 学院](https:\/\/clickhouse.ac.cn\/learn)\n    - [免费在线培训](https:\/\/clickhouse.ac.cn\/company\/news-events?category=Free+Training#upcoming-events)\n    - [ClickHouse 认证](https:\/\/clickhouse.ac.cn\/learn\/certification)\n  - [对比](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n    - [BigQuery](https:\/\/clickhouse.ac.cn\/comparison\/bigquery)\n    - [PostgreSQL](https:\/\/clickhouse.ac.cn\/comparison\/postgresql)\n    - [Redshift](https:\/\/clickhouse.ac.cn\/comparison\/redshift)\n    - [Rockset](https:\/\/clickhouse.ac.cn\/comparison\/rockset)\n    - [Snowflake](https:\/\/clickhouse.ac.cn\/comparison\/snowflake)\n  - [视频](https:\/\/clickhouse.ac.cn\/videos)\n  - [演示](https:\/\/clickhouse.ac.cn\/demos)\n- [定价](https:\/\/clickhouse.ac.cn\/pricing)\n- [联系我们](https:\/\/clickhouse.ac.cn\/company\/contact?loc=nav)\n\n[38\\.6k](https:\/\/github.com\/ClickHouse\/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav)\n\n[登录](https:\/\/clickhouse.cloud\/signIn?loc=docs-nav-signIn-cta)\n\n[开始使用](https:\/\/clickhouse.cloud\/signUp?loc=docs-nav-signUp-cta)\n\n[入门](https:\/\/clickhouse.ac.cn\/docs\/)\n\n[云](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/overview)\n\n[管理数据](https:\/\/clickhouse.ac.cn\/docs\/en\/updating-data)\n\n[服务器管理](https:\/\/clickhouse.ac.cn\/docs\/en\/architecture\/introduction)\n\n[SQL 参考](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference)\n\n[集成](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations)\n\n[chDB](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb)\n\n[关于](https:\/\/clickhouse.ac.cn\/docs\/en\/about-clickhouse)\n\n[知识库](https:\/\/clickhouse.ac.cn\/docs\/knowledgebase)\n\n- [英语](https:\/\/clickhouse.ac.cn\/docs\/)\n- [俄语](https:\/\/clickhouse.ac.cn\/docs\/ru)\n- [中文](https:\/\/clickhouse.ac.cn\/docs\/zh)\n\n深色模式\n\n搜索\n\n- [入门]()\n  - [介绍](https:\/\/clickhouse.ac.cn\/docs\/)\n  - [入门指南](https:\/\/clickhouse.ac.cn\/docs\/en\/guides\/creating-tables)\n  - [概念](https:\/\/clickhouse.ac.cn\/docs\/en\/concepts\/why-clickhouse-is-so-fast)\n  - [迁移指南](https:\/\/clickhouse.ac.cn\/docs\/en\/migrations\/bigquery)\n  - [用例指南](https:\/\/clickhouse.ac.cn\/docs\/en\/migrations\/bigquery)\n  - [示例数据集](https:\/\/clickhouse.ac.cn\/docs\/en\/getting-started\/example-datasets)\n- [云]()\n  - [开始使用](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/get-started)\n  - [最佳实践](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/bestpractices)\n  - [管理云](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/manage)\n  - [安全](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/security)\n  - [Cloud API](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/manage\/cloud-api)\n  - [迁移到云](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/migration)\n  - [云参考](https:\/\/clickhouse.ac.cn\/docs\/en\/cloud\/reference\/architecture)\n- [管理数据]()\n  - [更新数据](https:\/\/clickhouse.ac.cn\/docs\/en\/updating-data)\n  - [数据建模](https:\/\/clickhouse.ac.cn\/docs\/en\/data-modeling\/schema-design)\n  - [删除数据](https:\/\/clickhouse.ac.cn\/docs\/en\/managing-data\/deleting-data\/overview)\n  - [性能和优化](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/optimizing-performance\/profile-guided-optimization)\n- [服务器管理]()\n  - [部署和扩展](https:\/\/clickhouse.ac.cn\/docs\/en\/architecture\/cluster-deployment)\n  - [安全和身份验证](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/external-authenticators\/http)\n  - [设置](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/settings)\n  - [工具和实用程序](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/utilities\/backupview)\n  - [系统表](https:\/\/clickhouse.ac.cn\/docs\/en\/operations\/system-tables\/asynchronous_insert_log)\n- [SQL 参考]()\n  - [介绍](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference)\n  - [函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/functions)\n  - [引擎](https:\/\/clickhouse.ac.cn\/docs\/en\/engines\/database-engines)\n  - [其他功能](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/operators)\n- [集成]()\n  - [ClickPipes](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/clickpipes)\n  - [数据格式](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/data-formats)\n  - [所有集成](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations)\n  - [客户端和驱动程序](https:\/\/clickhouse.ac.cn\/docs\/en\/integrations\/sql-clients\/clickhouse-client-local)\n- [chDB]()\n  - [学习 chDB](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb)\n  - [指南](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb\/guides\/jupysql)\n  - [语言客户端](https:\/\/clickhouse.ac.cn\/docs\/en\/chdb\/install\/python)\n- [关于]()\n  - [关于 ClickHouse](https:\/\/clickhouse.ac.cn\/docs\/en\/about-us\/adopters)\n  - [更新日志](https:\/\/clickhouse.ac.cn\/docs\/en\/whats-new\/security-changelog)\n  - [支持](https:\/\/clickhouse.ac.cn\/docs\/en\/about-us\/support)\n  - [开发和贡献](https:\/\/clickhouse.ac.cn\/docs\/en\/development\/developer-instruction)\n\n搜索\n\n- [介绍](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference)\n  - [语法](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/syntax)\n  - [输入和输出格式](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/formats)\n  - [数据类型](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/data-types)\n  - [语句](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/statements)\n- [引擎]()\n  - [数据库引擎](https:\/\/clickhouse.ac.cn\/docs\/en\/engines\/database-engines)\n  - [表引擎](https:\/\/clickhouse.ac.cn\/docs\/en\/engines\/table-engines)\n- [函数]()\n  - [常规函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/functions)\n  - [聚合函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions)\n    - [聚合函数列表](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/reference)\n    - [组合器](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/combinators)\n    - [参数化](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/parametric-functions)\n    - [GROUPING](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function)\n  - [表函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/table-functions)\n  - [窗口函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/window-functions)\n- [其他功能]()\n  - [分布式 DDL](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/operators\/en\/sql-reference\/other\/distributed-ddl)\n  - [EXISTS](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/operators\/exists)\n  - [IN 运算符](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/operators\/in)\n\n- 函数\n- [聚合函数](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions)\n- GROUPING\n\n[编辑此页面](https:\/\/github.com\/ClickHouse\/ClickHouse\/tree\/master\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function.md)\n\n# GROUPING\n## GROUPING[](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#grouping-1 \"Direct link to GROUPING\")\n[ROLLUP](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/statements\/select\/group-by#rollup-modifier) 和 [CUBE](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/statements\/select\/group-by#cube-modifier) 是 GROUP BY 的修饰符。两者都计算小计。ROLLUP 接受列的有序列表，例如 `(day, month, year)`，并在每个聚合级别计算小计，然后计算总计。CUBE 计算指定列的所有可能组合的小计。GROUPING 标识 ROLLUP 或 CUBE 返回的哪些行是超级聚合，哪些行是未修改的 GROUP BY 返回的行。\n\nGROUPING 函数将多个列作为参数，并返回一个位掩码。\n\n- `1` 表示 `ROLLUP` 或 `CUBE` 修饰符返回的 `GROUP BY` 行是小计\n- `0` 表示 `ROLLUP` 或 `CUBE` 返回的行不是小计\n## GROUPING SETS[](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#grouping-sets \"Direct link to GROUPING SETS\")\n默认情况下，CUBE 修饰符计算传递给 CUBE 的列的所有可能组合的小计。GROUPING SETS 允许您指定要计算的特定组合。\n\n分析分层数据是 ROLLUP、CUBE 和 GROUPING SETS 修饰符的一个很好的用例。此处的示例是一个包含有关跨两个数据中心安装的 Linux 发行版及其版本的数据表。按发行版、版本和位置查看数据可能很有价值。\n\n### 加载示例数据[](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#load-sample-data \"Direct link to Load sample data\")\n```\nCREATE TABLE servers ( datacenter VARCHAR(255),\n                         distro VARCHAR(255) NOT NULL,\n                         version VARCHAR(50) NOT NULL,\n                         quantity INT\n                       )\n                        ORDER BY (datacenter, distro, version)\n```\n```\nINSERT INTO servers(datacenter, distro, version, quantity)\nVALUES ('Schenectady', 'Arch','2022.08.05',50),\n       ('Westport', 'Arch','2022.08.05',40),\n       ('Schenectady','Arch','2021.09.01',30),\n       ('Westport', 'Arch','2021.09.01',20),\n       ('Schenectady','Arch','2020.05.01',10),\n       ('Westport', 'Arch','2020.05.01',5),\n       ('Schenectady','RHEL','9',60),\n       ('Westport','RHEL','9',70),\n       ('Westport','RHEL','7',80),\n       ('Schenectady','RHEL','7',80)\n```\n```\nSELECT \n    *\nFROM\n    servers;\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─quantity─┐\n│ Schenectady │ Arch   │ 2020.05.01 │       10 │\n│ Schenectady │ Arch   │ 2021.09.01 │       30 │\n│ Schenectady │ Arch   │ 2022.08.05 │       50 │\n│ Schenectady │ RHEL   │ 7          │       80 │\n│ Schenectady │ RHEL   │ 9          │       60 │\n│ Westport    │ Arch   │ 2020.05.01 │        5 │\n│ Westport    │ Arch   │ 2021.09.01 │       20 │\n│ Westport    │ Arch   │ 2022.08.05 │       40 │\n│ Westport    │ RHEL   │ 7          │       80 │\n│ Westport    │ RHEL   │ 9          │       70 │\n└─────────────┴────────┴────────────┴──────────┘\n\n10 rows in set. Elapsed: 0.409 sec.\n```\n### 简单查询[](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#simple-queries \"Direct link to Simple queries\")\n获取每个数据中心按发行版划分的服务器计数\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter,\n    distro;\n```\n```\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n\n4 rows in set. Elapsed: 0.212 sec.\n```\n```\nSELECT\n    datacenter, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter;\n```\n```\n┌─datacenter──┬─qty─┐\n│ Westport    │ 215 │\n│ Schenectady │ 230 │\n└─────────────┴─────┘\n\n2 rows in set. Elapsed: 0.277 sec.\n```\n```\nSELECT\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    distro;\n```\n```\n\n┌─distro─┬─qty─┐\n│ Arch   │ 155 │\n│ RHEL   │ 290 │\n└────────┴─────┘\n\n2 rows in set. Elapsed: 0.352 sec.\n```\n```\nSELECT\n    SUM(quantity) qty\nFROM\n    servers;\n```\n```\n┌─qty─┐\n│ 445 │\n└─────┘\n\n1 row in set. Elapsed: 0.244 sec.\n```\n### 将多个 GROUP BY 语句与 GROUPING SETS 进行比较[](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets \"Direct link to Comparing multiple GROUP BY statements with GROUPING SETS\")\n在没有 CUBE、ROLLUP 或 GROUPING SETS 的情况下分解数据\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter,\n    distro\nUNION ALL\nSELECT\n    datacenter, \n    null,\n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    datacenter\nUNION ALL\nSELECT\n    null,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    distro\nUNION ALL\nSELECT\n    null,\n    null,\n    SUM(quantity) qty\nFROM\n    servers;\n```\n```\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ ᴺᵁᴸᴸ   │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │ ᴺᵁᴸᴸ   │ 215 │\n│ Schenectady │ ᴺᵁᴸᴸ   │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│ ᴺᵁᴸᴸ       │ Arch   │ 155 │\n│ ᴺᵁᴸᴸ       │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n\n9 rows in set. Elapsed: 0.527 sec.\n```\n使用 GROUPING SETS 获取相同的信息\n```\nSELECT\n    datacenter,\n    distro, \n    SUM (quantity) qty\nFROM\n    servers\nGROUP BY\n    GROUPING SETS(\n        (datacenter,distro),\n        (datacenter),\n        (distro),\n        ()\n    )\n```\n```\n┌─datacenter──┬─distro─┬─qty─┐\n│ Schenectady │ RHEL   │ 140 │\n│ Westport    │ Arch   │  65 │\n│ Schenectady │ Arch   │  90 │\n│ Westport    │ RHEL   │ 150 │\n└─────────────┴────────┴─────┘\n┌─datacenter──┬─distro─┬─qty─┐\n│ Westport    │        │ 215 │\n│ Schenectady │        │ 230 │\n└─────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │        │ 445 │\n└────────────┴────────┴─────┘\n┌─datacenter─┬─distro─┬─qty─┐\n│            │ Arch   │ 155 │\n│            │ RHEL   │ 290 │\n└────────────┴────────┴─────┘\n\n9 rows in set. Elapsed: 0.427 sec.\n```\n### 将 CUBE 与 GROUPING SETS 进行比较[](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#comparing-cube-with-grouping-sets \"Direct link to Comparing CUBE with GROUPING SETS\")\n下一个查询中的 CUBE，`CUBE(datacenter,distro,version)` 提供了一个可能没有意义的层次结构。跨两个发行版查看 Version 没有意义（因为 Arch 和 RHEL 没有相同的发布周期或版本命名标准）。以下 GROUPING SETS 示例更合适，因为它在同一集合中对 `distro` 和 `version` 进行分组。\n```\nSELECT\n   datacenter,\n   distro,\n   version,\n   SUM(quantity)\nFROM\n   servers\nGROUP BY\n   CUBE(datacenter,distro,version)\nORDER BY\n   datacenter,\n   distro;\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│             │        │ 7          │           160 │\n│             │        │ 2020.05.01 │            15 │\n│             │        │ 2021.09.01 │            50 │\n│             │        │ 2022.08.05 │            90 │\n│             │        │ 9          │           130 │\n│             │        │            │           445 │\n│             │ Arch   │ 2021.09.01 │            50 │\n│             │ Arch   │ 2022.08.05 │            90 │\n│             │ Arch   │ 2020.05.01 │            15 │\n│             │ Arch   │            │           155 │\n│             │ RHEL   │ 9          │           130 │\n│             │ RHEL   │ 7          │           160 │\n│             │ RHEL   │            │           290 │\n│ Schenectady │        │ 9          │            60 │\n│ Schenectady │        │ 2021.09.01 │            30 │\n│ Schenectady │        │ 7          │            80 │\n│ Schenectady │        │ 2022.08.05 │            50 │\n│ Schenectady │        │ 2020.05.01 │            10 │\n│ Schenectady │        │            │           230 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n│ Schenectady │ Arch   │            │            90 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ RHEL   │            │           140 │\n│ Westport    │        │ 9          │            70 │\n│ Westport    │        │ 2020.05.01 │             5 │\n│ Westport    │        │ 2022.08.05 │            40 │\n│ Westport    │        │ 7          │            80 │\n│ Westport    │        │ 2021.09.01 │            20 │\n│ Westport    │        │            │           215 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Westport    │ Arch   │            │            65 │\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ RHEL   │            │           150 │\n└─────────────┴────────┴────────────┴───────────────┘\n\n39 rows in set. Elapsed: 0.355 sec.\n```\n注意\n\n上面的示例中的 Version 在未与发行版关联时可能没有意义，如果我们跟踪内核版本，它可能是有意义的，因为内核版本可以与任一发行版关联。使用 GROUPING SETS，如下一个示例所示，可能是更好的选择。\n```\nSELECT\n    datacenter,\n    distro,\n    version,\n    SUM(quantity)\nFROM servers\nGROUP BY\n    GROUPING SETS (\n        (datacenter, distro, version),\n        (datacenter, distro))\n```\n```\n┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐\n│ Westport    │ RHEL   │ 9          │            70 │\n│ Schenectady │ Arch   │ 2022.08.05 │            50 │\n│ Schenectady │ Arch   │ 2021.09.01 │            30 │\n│ Schenectady │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2020.05.01 │             5 │\n│ Westport    │ RHEL   │ 7          │            80 │\n│ Westport    │ Arch   │ 2021.09.01 │            20 │\n│ Westport    │ Arch   │ 2022.08.05 │            40 │\n│ Schenectady │ RHEL   │ 9          │            60 │\n│ Schenectady │ Arch   │ 2020.05.01 │            10 │\n└─────────────┴────────┴────────────┴───────────────┘\n┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐\n│ Schenectady │ RHEL   │         │           140 │\n│ Westport    │ Arch   │         │            65 │\n│ Schenectady │ Arch   │         │            90 │\n│ Westport    │ RHEL   │         │           150 │\n└─────────────┴────────┴─────────┴───────────────┘\n\n14 rows in set. Elapsed: 1.036 sec.\n```\n\n[Previous Parametric](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/parametric-functions)\n\n[Next Table Functions](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/table-functions)\n\n- [GROUPING](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#grouping-1)\n- [GROUPING SETS](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#grouping-sets)\n  - [加载示例数据](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#load-sample-data)\n  - [简单查询](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#simple-queries)\n  - [将多个 GROUP BY 语句与 GROUPING SETS 进行比较](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets)\n  - [将 CUBE 与 GROUPING SETS 进行比较](https:\/\/clickhouse.ac.cn\/docs\/en\/sql-reference\/aggregate-functions\/grouping_function#comparing-cube-with-grouping-sets)\n\n© 2016–2025 ClickHouse, Inc.\n\n[商标](https:\/\/clickhouse.ac.cn\/legal\/trademark-policy)·[隐私](https:\/\/clickhouse.ac.cn\/legal\/privacy-policy)·[安全](https:\/\/trust.clickhouse.com\/)·[服务条款](https:\/\/clickhouse.ac.cn\/legal\/agreements\/terms-of-service)\n\n© 2016–2025 ClickHouse, Inc.\n\n[商标](https:\/\/clickhouse.ac.cn\/legal\/trademark-policy)·[隐私](https:\/\/clickhouse.ac.cn\/legal\/privacy-policy)·[安全](https:\/\/trust.clickhouse.com\/)·[服务条款](https:\/\/clickhouse.ac.cn\/legal\/agreements\/terms-of-service)\n\n© . This site is unofficial and not affiliated with ClickHouse, Inc.","attrs_readable_markdown":null,"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

5 months ago

🚫

ROBOTS BLOCKED

Page Info Filters

Filter	Status	Condition	Details
HTTP status	PASS	`download_http_code = 200`	HTTP 200
Age cutoff	PASS	`download_stamp > now() - 6 MONTH`	5.9 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.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function
Last Crawled	2025-10-17 09:41:16 (5 months ago)
First Indexed	2024-10-22 13:56:06 (1 year ago)
HTTP Status Code	200
Meta Title	GROUPING \| ClickHouse 文档 - ClickHouse 数据库
Meta Description	null
Meta Canonical	null
Boilerpipe Text	GROUPING ROLLUP 和 CUBE 是 GROUP BY 的修饰符。两者都计算小计。ROLLUP 接受列的有序列表，例如 (day, month, year) ，并在每个聚合级别计算小计，然后计算总计。CUBE 计算指定列的所有可能组合的小计。GROUPING 标识 ROLLUP 或 CUBE 返回的哪些行是超级聚合，哪些行是未修改的 GROUP BY 返回的行。 GROUPING 函数将多个列作为参数，并返回一个位掩码。 1 表示 ROLLUP 或 CUBE 修饰符返回的 GROUP BY 行是小计 0 表示 ROLLUP 或 CUBE 返回的行不是小计 GROUPING SETS 默认情况下，CUBE 修饰符计算传递给 CUBE 的列的所有可能组合的小计。GROUPING SETS 允许您指定要计算的特定组合。分析分层数据是 ROLLUP、CUBE 和 GROUPING SETS 修饰符的一个很好的用例。此处的示例是一个包含有关跨两个数据中心安装的 Linux 发行版及其版本的数据表。按发行版、版本和位置查看数据可能很有价值。加载示例数据 CREATE TABLE servers ( datacenter VARCHAR ( 255 ) , distro VARCHAR ( 255 ) NOT NULL , version VARCHAR ( 50 ) NOT NULL , quantity INT ) ORDER BY ( datacenter , distro , version ) INSERT INTO servers ( datacenter , distro , version , quantity ) VALUES ( 'Schenectady' , 'Arch' , '2022.08.05' , 50 ) , ( 'Westport' , 'Arch' , '2022.08.05' , 40 ) , ( 'Schenectady' , 'Arch' , '2021.09.01' , 30 ) , ( 'Westport' , 'Arch' , '2021.09.01' , 20 ) , ( 'Schenectady' , 'Arch' , '2020.05.01' , 10 ) , ( 'Westport' , 'Arch' , '2020.05.01' , 5 ) , ( 'Schenectady' , 'RHEL' , '9' , 60 ) , ( 'Westport' , 'RHEL' , '9' , 70 ) , ( 'Westport' , 'RHEL' , '7' , 80 ) , ( 'Schenectady' , 'RHEL' , '7' , 80 ) ┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ 9 │ 70 │ └─────────────┴────────┴────────────┴──────────┘ 10 rows in set. Elapsed: 0.409 sec. 简单查询获取每个数据中心按发行版划分的服务器计数 SELECT datacenter , distro , SUM ( quantity ) qty FROM servers GROUP BY datacenter , distro ; ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ 4 rows in set. Elapsed: 0.212 sec. SELECT datacenter , SUM ( quantity ) qty FROM servers GROUP BY datacenter ; ┌─datacenter──┬─qty─┐ │ Westport │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 2 rows in set. Elapsed: 0.277 sec. SELECT distro , SUM ( quantity ) qty FROM servers GROUP BY distro ; ┌─distro─┬─qty─┐ │ Arch │ 155 │ │ RHEL │ 290 │ └────────┴─────┘ 2 rows in set. Elapsed: 0.352 sec. SELECT SUM ( quantity ) qty FROM servers ; ┌─qty─┐ │ 445 │ └─────┘ 1 row in set. Elapsed: 0.244 sec. 将多个 GROUP BY 语句与 GROUPING SETS 进行比较在没有 CUBE、ROLLUP 或 GROUPING SETS 的情况下分解数据 SELECT datacenter , distro , SUM ( quantity ) qty FROM servers GROUP BY datacenter , distro UNION ALL SELECT datacenter , null , SUM ( quantity ) qty FROM servers GROUP BY datacenter UNION ALL SELECT null , distro , SUM ( quantity ) qty FROM servers GROUP BY distro UNION ALL SELECT null , null , SUM ( quantity ) qty FROM servers ; ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ ᴺᵁᴸᴸ │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ Arch │ 155 │ │ ᴺᵁᴸᴸ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.527 sec. 使用 GROUPING SETS 获取相同的信息 SELECT datacenter , distro , SUM ( quantity ) qty FROM servers GROUP BY GROUPING SETS ( ( datacenter , distro ) , ( datacenter ) , ( distro ) , ( ) ) ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ │ 215 │ │ Schenectady │ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ Arch │ 155 │ │ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.427 sec. 将 CUBE 与 GROUPING SETS 进行比较下一个查询中的 CUBE， CUBE(datacenter,distro,version) 提供了一个可能没有意义的层次结构。跨两个发行版查看 Version 没有意义（因为 Arch 和 RHEL 没有相同的发布周期或版本命名标准）。以下 GROUPING SETS 示例更合适，因为它在同一集合中对 distro 和 version 进行分组。 SELECT datacenter , distro , version , SUM ( quantity ) FROM servers GROUP BY CUBE ( datacenter , distro , version ) ORDER BY datacenter , distro ; ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ │ │ 7 │ 160 │ │ │ │ 2020.05.01 │ 15 │ │ │ │ 2021.09.01 │ 50 │ │ │ │ 2022.08.05 │ 90 │ │ │ │ 9 │ 130 │ │ │ │ │ 445 │ │ │ Arch │ 2021.09.01 │ 50 │ │ │ Arch │ 2022.08.05 │ 90 │ │ │ Arch │ 2020.05.01 │ 15 │ │ │ Arch │ │ 155 │ │ │ RHEL │ 9 │ 130 │ │ │ RHEL │ 7 │ 160 │ │ │ RHEL │ │ 290 │ │ Schenectady │ │ 9 │ 60 │ │ Schenectady │ │ 2021.09.01 │ 30 │ │ Schenectady │ │ 7 │ 80 │ │ Schenectady │ │ 2022.08.05 │ 50 │ │ Schenectady │ │ 2020.05.01 │ 10 │ │ Schenectady │ │ │ 230 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ │ 90 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ │ 9 │ 70 │ │ Westport │ │ 2020.05.01 │ 5 │ │ Westport │ │ 2022.08.05 │ 40 │ │ Westport │ │ 7 │ 80 │ │ Westport │ │ 2021.09.01 │ 20 │ │ Westport │ │ │ 215 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ Arch │ │ 65 │ │ Westport │ RHEL │ 9 │ 70 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴────────────┴───────────────┘ 39 rows in set. Elapsed: 0.355 sec. 上面的示例中的 Version 在未与发行版关联时可能没有意义，如果我们跟踪内核版本，它可能是有意义的，因为内核版本可以与任一发行版关联。使用 GROUPING SETS，如下一个示例所示，可能是更好的选择。 SELECT datacenter , distro , version , SUM ( quantity ) FROM servers GROUP BY GROUPING SETS ( ( datacenter , distro , version ) , ( datacenter , distro ) ) ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport │ RHEL │ 9 │ 70 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ Arch │ │ 65 │ │ Schenectady │ Arch │ │ 90 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴─────────┴───────────────┘ 14 rows in set. Elapsed: 1.036 sec.
Markdown	[跳到主要内容](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.ac.cn/docs/img/ch_logo_docs.svg)![ClickHouse](https://clickhouse.ac.cn/docs/img/ch_logo_docs_dark.svg)](https://clickhouse.ac.cn/) - [产品](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [ClickHouse Cloud 使用 ClickHouse 的最佳方式。在 AWS、GCP 和 Azure 上可用。](https://clickhouse.ac.cn/cloud) - [ClickHouse 使用开源数据库启动源代码 ClickHouse。](https://clickhouse.ac.cn/clickhouse) - [查看 100 多个集成](https://clickhouse.ac.cn/integrations) [查看 100 多个集成](https://clickhouse.ac.cn/integrations) - [用例](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [实时分析](https://clickhouse.ac.cn/use-cases/real-time-analytics) - [机器学习和 GenAI](https://clickhouse.ac.cn/use-cases/machine-learning-and-data-science) - [商业智能](https://clickhouse.ac.cn/use-cases/business-intelligence) - [日志、事件和追踪](https://clickhouse.ac.cn/use-cases/logging-and-metrics) - [所有用例](https://clickhouse.ac.cn/use-cases) [所有用例](https://clickhouse.ac.cn/use-cases) - [文档](https://clickhouse.ac.cn/docs) - [资源](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [用户故事](https://clickhouse.ac.cn/user-stories) - [博客](https://clickhouse.ac.cn/blog) - [学习和认证](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [ClickHouse 学院](https://clickhouse.ac.cn/learn) - [免费在线培训](https://clickhouse.ac.cn/company/news-events?category=Free+Training#upcoming-events) - [ClickHouse 认证](https://clickhouse.ac.cn/learn/certification) - [对比](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [BigQuery](https://clickhouse.ac.cn/comparison/bigquery) - [PostgreSQL](https://clickhouse.ac.cn/comparison/postgresql) - [Redshift](https://clickhouse.ac.cn/comparison/redshift) - [Rockset](https://clickhouse.ac.cn/comparison/rockset) - [Snowflake](https://clickhouse.ac.cn/comparison/snowflake) - [视频](https://clickhouse.ac.cn/videos) - [演示](https://clickhouse.ac.cn/demos) - [定价](https://clickhouse.ac.cn/pricing) - [联系我们](https://clickhouse.ac.cn/company/contact?loc=nav) [38\.6k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [登录](https://clickhouse.cloud/signIn?loc=docs-nav-signIn-cta) [开始使用](https://clickhouse.cloud/signUp?loc=docs-nav-signUp-cta) [入门](https://clickhouse.ac.cn/docs/) [云](https://clickhouse.ac.cn/docs/en/cloud/overview) [管理数据](https://clickhouse.ac.cn/docs/en/updating-data) [服务器管理](https://clickhouse.ac.cn/docs/en/architecture/introduction) [SQL 参考](https://clickhouse.ac.cn/docs/en/sql-reference) [集成](https://clickhouse.ac.cn/docs/en/integrations) [chDB](https://clickhouse.ac.cn/docs/en/chdb) [关于](https://clickhouse.ac.cn/docs/en/about-clickhouse) [知识库](https://clickhouse.ac.cn/docs/knowledgebase) - [英语](https://clickhouse.ac.cn/docs/) - [俄语](https://clickhouse.ac.cn/docs/ru) - [中文](https://clickhouse.ac.cn/docs/zh) 深色模式搜索 - [入门]() - [介绍](https://clickhouse.ac.cn/docs/) - [入门指南](https://clickhouse.ac.cn/docs/en/guides/creating-tables) - [概念](https://clickhouse.ac.cn/docs/en/concepts/why-clickhouse-is-so-fast) - [迁移指南](https://clickhouse.ac.cn/docs/en/migrations/bigquery) - [用例指南](https://clickhouse.ac.cn/docs/en/migrations/bigquery) - [示例数据集](https://clickhouse.ac.cn/docs/en/getting-started/example-datasets) - [云]() - [开始使用](https://clickhouse.ac.cn/docs/en/cloud/get-started) - [最佳实践](https://clickhouse.ac.cn/docs/en/cloud/bestpractices) - [管理云](https://clickhouse.ac.cn/docs/en/cloud/manage) - [安全](https://clickhouse.ac.cn/docs/en/cloud/security) - [Cloud API](https://clickhouse.ac.cn/docs/en/cloud/manage/cloud-api) - [迁移到云](https://clickhouse.ac.cn/docs/en/integrations/migration) - [云参考](https://clickhouse.ac.cn/docs/en/cloud/reference/architecture) - [管理数据]() - [更新数据](https://clickhouse.ac.cn/docs/en/updating-data) - [数据建模](https://clickhouse.ac.cn/docs/en/data-modeling/schema-design) - [删除数据](https://clickhouse.ac.cn/docs/en/managing-data/deleting-data/overview) - [性能和优化](https://clickhouse.ac.cn/docs/en/operations/optimizing-performance/profile-guided-optimization) - [服务器管理]() - [部署和扩展](https://clickhouse.ac.cn/docs/en/architecture/cluster-deployment) - [安全和身份验证](https://clickhouse.ac.cn/docs/en/operations/external-authenticators/http) - [设置](https://clickhouse.ac.cn/docs/en/operations/settings) - [工具和实用程序](https://clickhouse.ac.cn/docs/en/operations/utilities/backupview) - [系统表](https://clickhouse.ac.cn/docs/en/operations/system-tables/asynchronous_insert_log) - [SQL 参考]() - [介绍](https://clickhouse.ac.cn/docs/en/sql-reference) - [函数](https://clickhouse.ac.cn/docs/en/sql-reference/functions) - [引擎](https://clickhouse.ac.cn/docs/en/engines/database-engines) - [其他功能](https://clickhouse.ac.cn/docs/en/sql-reference/operators) - [集成]() - [ClickPipes](https://clickhouse.ac.cn/docs/en/integrations/clickpipes) - [数据格式](https://clickhouse.ac.cn/docs/en/integrations/data-formats) - [所有集成](https://clickhouse.ac.cn/docs/en/integrations) - [客户端和驱动程序](https://clickhouse.ac.cn/docs/en/integrations/sql-clients/clickhouse-client-local) - [chDB]() - [学习 chDB](https://clickhouse.ac.cn/docs/en/chdb) - [指南](https://clickhouse.ac.cn/docs/en/chdb/guides/jupysql) - [语言客户端](https://clickhouse.ac.cn/docs/en/chdb/install/python) - [关于]() - [关于 ClickHouse](https://clickhouse.ac.cn/docs/en/about-us/adopters) - [更新日志](https://clickhouse.ac.cn/docs/en/whats-new/security-changelog) - [支持](https://clickhouse.ac.cn/docs/en/about-us/support) - [开发和贡献](https://clickhouse.ac.cn/docs/en/development/developer-instruction) [跳到主要内容](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#__docusaurus_skipToContent_fallback) [![ClickHouse](https://clickhouse.ac.cn/docs/img/ch_logo_docs.svg)![ClickHouse](https://clickhouse.ac.cn/docs/img/ch_logo_docs_dark.svg)](https://clickhouse.ac.cn/) - [产品](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [ClickHouse Cloud 使用 ClickHouse 的最佳方式。在 AWS、GCP 和 Azure 上可用。](https://clickhouse.ac.cn/cloud) - [ClickHouse 使用开源数据库启动源代码 ClickHouse。](https://clickhouse.ac.cn/clickhouse) - [查看 100 多个集成](https://clickhouse.ac.cn/integrations) [查看 100 多个集成](https://clickhouse.ac.cn/integrations) - [用例](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [实时分析](https://clickhouse.ac.cn/use-cases/real-time-analytics) - [机器学习和 GenAI](https://clickhouse.ac.cn/use-cases/machine-learning-and-data-science) - [商业智能](https://clickhouse.ac.cn/use-cases/business-intelligence) - [日志、事件和追踪](https://clickhouse.ac.cn/use-cases/logging-and-metrics) - [所有用例](https://clickhouse.ac.cn/use-cases) [所有用例](https://clickhouse.ac.cn/use-cases) - [文档](https://clickhouse.ac.cn/docs) - [资源](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [用户故事](https://clickhouse.ac.cn/user-stories) - [博客](https://clickhouse.ac.cn/blog) - [学习和认证](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [ClickHouse 学院](https://clickhouse.ac.cn/learn) - [免费在线培训](https://clickhouse.ac.cn/company/news-events?category=Free+Training#upcoming-events) - [ClickHouse 认证](https://clickhouse.ac.cn/learn/certification) - [对比](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [BigQuery](https://clickhouse.ac.cn/comparison/bigquery) - [PostgreSQL](https://clickhouse.ac.cn/comparison/postgresql) - [Redshift](https://clickhouse.ac.cn/comparison/redshift) - [Rockset](https://clickhouse.ac.cn/comparison/rockset) - [Snowflake](https://clickhouse.ac.cn/comparison/snowflake) - [视频](https://clickhouse.ac.cn/videos) - [演示](https://clickhouse.ac.cn/demos) - [定价](https://clickhouse.ac.cn/pricing) - [联系我们](https://clickhouse.ac.cn/company/contact?loc=nav) [38\.6k](https://github.com/ClickHouse/ClickHouse?utm_source=clickhouse&utm_medium=website&utm_campaign=website-nav) [登录](https://clickhouse.cloud/signIn?loc=docs-nav-signIn-cta) [开始使用](https://clickhouse.cloud/signUp?loc=docs-nav-signUp-cta) [入门](https://clickhouse.ac.cn/docs/) [云](https://clickhouse.ac.cn/docs/en/cloud/overview) [管理数据](https://clickhouse.ac.cn/docs/en/updating-data) [服务器管理](https://clickhouse.ac.cn/docs/en/architecture/introduction) [SQL 参考](https://clickhouse.ac.cn/docs/en/sql-reference) [集成](https://clickhouse.ac.cn/docs/en/integrations) [chDB](https://clickhouse.ac.cn/docs/en/chdb) [关于](https://clickhouse.ac.cn/docs/en/about-clickhouse) [知识库](https://clickhouse.ac.cn/docs/knowledgebase) - [英语](https://clickhouse.ac.cn/docs/) - [俄语](https://clickhouse.ac.cn/docs/ru) - [中文](https://clickhouse.ac.cn/docs/zh) 深色模式搜索 - [入门]() - [介绍](https://clickhouse.ac.cn/docs/) - [入门指南](https://clickhouse.ac.cn/docs/en/guides/creating-tables) - [概念](https://clickhouse.ac.cn/docs/en/concepts/why-clickhouse-is-so-fast) - [迁移指南](https://clickhouse.ac.cn/docs/en/migrations/bigquery) - [用例指南](https://clickhouse.ac.cn/docs/en/migrations/bigquery) - [示例数据集](https://clickhouse.ac.cn/docs/en/getting-started/example-datasets) - [云]() - [开始使用](https://clickhouse.ac.cn/docs/en/cloud/get-started) - [最佳实践](https://clickhouse.ac.cn/docs/en/cloud/bestpractices) - [管理云](https://clickhouse.ac.cn/docs/en/cloud/manage) - [安全](https://clickhouse.ac.cn/docs/en/cloud/security) - [Cloud API](https://clickhouse.ac.cn/docs/en/cloud/manage/cloud-api) - [迁移到云](https://clickhouse.ac.cn/docs/en/integrations/migration) - [云参考](https://clickhouse.ac.cn/docs/en/cloud/reference/architecture) - [管理数据]() - [更新数据](https://clickhouse.ac.cn/docs/en/updating-data) - [数据建模](https://clickhouse.ac.cn/docs/en/data-modeling/schema-design) - [删除数据](https://clickhouse.ac.cn/docs/en/managing-data/deleting-data/overview) - [性能和优化](https://clickhouse.ac.cn/docs/en/operations/optimizing-performance/profile-guided-optimization) - [服务器管理]() - [部署和扩展](https://clickhouse.ac.cn/docs/en/architecture/cluster-deployment) - [安全和身份验证](https://clickhouse.ac.cn/docs/en/operations/external-authenticators/http) - [设置](https://clickhouse.ac.cn/docs/en/operations/settings) - [工具和实用程序](https://clickhouse.ac.cn/docs/en/operations/utilities/backupview) - [系统表](https://clickhouse.ac.cn/docs/en/operations/system-tables/asynchronous_insert_log) - [SQL 参考]() - [介绍](https://clickhouse.ac.cn/docs/en/sql-reference) - [函数](https://clickhouse.ac.cn/docs/en/sql-reference/functions) - [引擎](https://clickhouse.ac.cn/docs/en/engines/database-engines) - [其他功能](https://clickhouse.ac.cn/docs/en/sql-reference/operators) - [集成]() - [ClickPipes](https://clickhouse.ac.cn/docs/en/integrations/clickpipes) - [数据格式](https://clickhouse.ac.cn/docs/en/integrations/data-formats) - [所有集成](https://clickhouse.ac.cn/docs/en/integrations) - [客户端和驱动程序](https://clickhouse.ac.cn/docs/en/integrations/sql-clients/clickhouse-client-local) - [chDB]() - [学习 chDB](https://clickhouse.ac.cn/docs/en/chdb) - [指南](https://clickhouse.ac.cn/docs/en/chdb/guides/jupysql) - [语言客户端](https://clickhouse.ac.cn/docs/en/chdb/install/python) - [关于]() - [关于 ClickHouse](https://clickhouse.ac.cn/docs/en/about-us/adopters) - [更新日志](https://clickhouse.ac.cn/docs/en/whats-new/security-changelog) - [支持](https://clickhouse.ac.cn/docs/en/about-us/support) - [开发和贡献](https://clickhouse.ac.cn/docs/en/development/developer-instruction) 搜索 - [介绍](https://clickhouse.ac.cn/docs/en/sql-reference) - [语法](https://clickhouse.ac.cn/docs/en/sql-reference/syntax) - [输入和输出格式](https://clickhouse.ac.cn/docs/en/sql-reference/formats) - [数据类型](https://clickhouse.ac.cn/docs/en/sql-reference/data-types) - [语句](https://clickhouse.ac.cn/docs/en/sql-reference/statements) - [引擎]() - [数据库引擎](https://clickhouse.ac.cn/docs/en/engines/database-engines) - [表引擎](https://clickhouse.ac.cn/docs/en/engines/table-engines) - [函数]() - [常规函数](https://clickhouse.ac.cn/docs/en/sql-reference/functions) - [聚合函数](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions) - [聚合函数列表](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/reference) - [组合器](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/combinators) - [参数化](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/parametric-functions) - [GROUPING](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function) - [表函数](https://clickhouse.ac.cn/docs/en/sql-reference/table-functions) - [窗口函数](https://clickhouse.ac.cn/docs/en/sql-reference/window-functions) - [其他功能]() - [分布式 DDL](https://clickhouse.ac.cn/docs/en/sql-reference/operators/en/sql-reference/other/distributed-ddl) - [EXISTS](https://clickhouse.ac.cn/docs/en/sql-reference/operators/exists) - [IN 运算符](https://clickhouse.ac.cn/docs/en/sql-reference/operators/in) - 函数 - [聚合函数](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions) - GROUPING [编辑此页面](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/aggregate-functions/grouping_function.md) # GROUPING ## GROUPING[](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#grouping-1 "Direct link to GROUPING") [ROLLUP](https://clickhouse.ac.cn/docs/en/sql-reference/statements/select/group-by#rollup-modifier) 和 [CUBE](https://clickhouse.ac.cn/docs/en/sql-reference/statements/select/group-by#cube-modifier) 是 GROUP BY 的修饰符。两者都计算小计。ROLLUP 接受列的有序列表，例如 `(day, month, year)`，并在每个聚合级别计算小计，然后计算总计。CUBE 计算指定列的所有可能组合的小计。GROUPING 标识 ROLLUP 或 CUBE 返回的哪些行是超级聚合，哪些行是未修改的 GROUP BY 返回的行。 GROUPING 函数将多个列作为参数，并返回一个位掩码。 - `1` 表示 `ROLLUP` 或 `CUBE` 修饰符返回的 `GROUP BY` 行是小计 - `0` 表示 `ROLLUP` 或 `CUBE` 返回的行不是小计 ## GROUPING SETS[](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#grouping-sets "Direct link to GROUPING SETS") 默认情况下，CUBE 修饰符计算传递给 CUBE 的列的所有可能组合的小计。GROUPING SETS 允许您指定要计算的特定组合。分析分层数据是 ROLLUP、CUBE 和 GROUPING SETS 修饰符的一个很好的用例。此处的示例是一个包含有关跨两个数据中心安装的 Linux 发行版及其版本的数据表。按发行版、版本和位置查看数据可能很有价值。 ### 加载示例数据[](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#load-sample-data "Direct link to Load sample data") ``` CREATE TABLE servers ( datacenter VARCHAR(255), distro VARCHAR(255) NOT NULL, version VARCHAR(50) NOT NULL, quantity INT ) ORDER BY (datacenter, distro, version) ``` ``` INSERT INTO servers(datacenter, distro, version, quantity) VALUES ('Schenectady', 'Arch','2022.08.05',50), ('Westport', 'Arch','2022.08.05',40), ('Schenectady','Arch','2021.09.01',30), ('Westport', 'Arch','2021.09.01',20), ('Schenectady','Arch','2020.05.01',10), ('Westport', 'Arch','2020.05.01',5), ('Schenectady','RHEL','9',60), ('Westport','RHEL','9',70), ('Westport','RHEL','7',80), ('Schenectady','RHEL','7',80) ``` ``` SELECT * FROM servers; ``` ``` ┌─datacenter──┬─distro─┬─version────┬─quantity─┐ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ 9 │ 70 │ └─────────────┴────────┴────────────┴──────────┘ 10 rows in set. Elapsed: 0.409 sec. ``` ### 简单查询[](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#simple-queries "Direct link to Simple queries") 获取每个数据中心按发行版划分的服务器计数 ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY datacenter, distro; ``` ``` ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ 4 rows in set. Elapsed: 0.212 sec. ``` ``` SELECT datacenter, SUM (quantity) qty FROM servers GROUP BY datacenter; ``` ``` ┌─datacenter──┬─qty─┐ │ Westport │ 215 │ │ Schenectady │ 230 │ └─────────────┴─────┘ 2 rows in set. Elapsed: 0.277 sec. ``` ``` SELECT distro, SUM (quantity) qty FROM servers GROUP BY distro; ``` ``` ┌─distro─┬─qty─┐ │ Arch │ 155 │ │ RHEL │ 290 │ └────────┴─────┘ 2 rows in set. Elapsed: 0.352 sec. ``` ``` SELECT SUM(quantity) qty FROM servers; ``` ``` ┌─qty─┐ │ 445 │ └─────┘ 1 row in set. Elapsed: 0.244 sec. ``` ### 将多个 GROUP BY 语句与 GROUPING SETS 进行比较[](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets "Direct link to Comparing multiple GROUP BY statements with GROUPING SETS") 在没有 CUBE、ROLLUP 或 GROUPING SETS 的情况下分解数据 ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY datacenter, distro UNION ALL SELECT datacenter, null, SUM (quantity) qty FROM servers GROUP BY datacenter UNION ALL SELECT null, distro, SUM (quantity) qty FROM servers GROUP BY distro UNION ALL SELECT null, null, SUM(quantity) qty FROM servers; ``` ``` ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ ᴺᵁᴸᴸ │ 215 │ │ Schenectady │ ᴺᵁᴸᴸ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ ᴺᵁᴸᴸ │ Arch │ 155 │ │ ᴺᵁᴸᴸ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.527 sec. ``` 使用 GROUPING SETS 获取相同的信息 ``` SELECT datacenter, distro, SUM (quantity) qty FROM servers GROUP BY GROUPING SETS( (datacenter,distro), (datacenter), (distro), () ) ``` ``` ┌─datacenter──┬─distro─┬─qty─┐ │ Schenectady │ RHEL │ 140 │ │ Westport │ Arch │ 65 │ │ Schenectady │ Arch │ 90 │ │ Westport │ RHEL │ 150 │ └─────────────┴────────┴─────┘ ┌─datacenter──┬─distro─┬─qty─┐ │ Westport │ │ 215 │ │ Schenectady │ │ 230 │ └─────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ │ 445 │ └────────────┴────────┴─────┘ ┌─datacenter─┬─distro─┬─qty─┐ │ │ Arch │ 155 │ │ │ RHEL │ 290 │ └────────────┴────────┴─────┘ 9 rows in set. Elapsed: 0.427 sec. ``` ### 将 CUBE 与 GROUPING SETS 进行比较[](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#comparing-cube-with-grouping-sets "Direct link to Comparing CUBE with GROUPING SETS") 下一个查询中的 CUBE，`CUBE(datacenter,distro,version)` 提供了一个可能没有意义的层次结构。跨两个发行版查看 Version 没有意义（因为 Arch 和 RHEL 没有相同的发布周期或版本命名标准）。以下 GROUPING SETS 示例更合适，因为它在同一集合中对 `distro` 和 `version` 进行分组。 ``` SELECT datacenter, distro, version, SUM(quantity) FROM servers GROUP BY CUBE(datacenter,distro,version) ORDER BY datacenter, distro; ``` ``` ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ │ │ 7 │ 160 │ │ │ │ 2020.05.01 │ 15 │ │ │ │ 2021.09.01 │ 50 │ │ │ │ 2022.08.05 │ 90 │ │ │ │ 9 │ 130 │ │ │ │ │ 445 │ │ │ Arch │ 2021.09.01 │ 50 │ │ │ Arch │ 2022.08.05 │ 90 │ │ │ Arch │ 2020.05.01 │ 15 │ │ │ Arch │ │ 155 │ │ │ RHEL │ 9 │ 130 │ │ │ RHEL │ 7 │ 160 │ │ │ RHEL │ │ 290 │ │ Schenectady │ │ 9 │ 60 │ │ Schenectady │ │ 2021.09.01 │ 30 │ │ Schenectady │ │ 7 │ 80 │ │ Schenectady │ │ 2022.08.05 │ 50 │ │ Schenectady │ │ 2020.05.01 │ 10 │ │ Schenectady │ │ │ 230 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ │ Schenectady │ Arch │ │ 90 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ │ 9 │ 70 │ │ Westport │ │ 2020.05.01 │ 5 │ │ Westport │ │ 2022.08.05 │ 40 │ │ Westport │ │ 7 │ 80 │ │ Westport │ │ 2021.09.01 │ 20 │ │ Westport │ │ │ 215 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Westport │ Arch │ │ 65 │ │ Westport │ RHEL │ 9 │ 70 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴────────────┴───────────────┘ 39 rows in set. Elapsed: 0.355 sec. ``` 注意上面的示例中的 Version 在未与发行版关联时可能没有意义，如果我们跟踪内核版本，它可能是有意义的，因为内核版本可以与任一发行版关联。使用 GROUPING SETS，如下一个示例所示，可能是更好的选择。 ``` SELECT datacenter, distro, version, SUM(quantity) FROM servers GROUP BY GROUPING SETS ( (datacenter, distro, version), (datacenter, distro)) ``` ``` ┌─datacenter──┬─distro─┬─version────┬─sum(quantity)─┐ │ Westport │ RHEL │ 9 │ 70 │ │ Schenectady │ Arch │ 2022.08.05 │ 50 │ │ Schenectady │ Arch │ 2021.09.01 │ 30 │ │ Schenectady │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2020.05.01 │ 5 │ │ Westport │ RHEL │ 7 │ 80 │ │ Westport │ Arch │ 2021.09.01 │ 20 │ │ Westport │ Arch │ 2022.08.05 │ 40 │ │ Schenectady │ RHEL │ 9 │ 60 │ │ Schenectady │ Arch │ 2020.05.01 │ 10 │ └─────────────┴────────┴────────────┴───────────────┘ ┌─datacenter──┬─distro─┬─version─┬─sum(quantity)─┐ │ Schenectady │ RHEL │ │ 140 │ │ Westport │ Arch │ │ 65 │ │ Schenectady │ Arch │ │ 90 │ │ Westport │ RHEL │ │ 150 │ └─────────────┴────────┴─────────┴───────────────┘ 14 rows in set. Elapsed: 1.036 sec. ``` [Previous Parametric](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/parametric-functions) [Next Table Functions](https://clickhouse.ac.cn/docs/en/sql-reference/table-functions) - [GROUPING](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#grouping-1) - [GROUPING SETS](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#grouping-sets) - [加载示例数据](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#load-sample-data) - [简单查询](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#simple-queries) - [将多个 GROUP BY 语句与 GROUPING SETS 进行比较](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#comparing-multiple-group-by-statements-with-grouping-sets) - [将 CUBE 与 GROUPING SETS 进行比较](https://clickhouse.ac.cn/docs/en/sql-reference/aggregate-functions/grouping_function#comparing-cube-with-grouping-sets) © 2016–2025 ClickHouse, Inc. [商标](https://clickhouse.ac.cn/legal/trademark-policy)·[隐私](https://clickhouse.ac.cn/legal/privacy-policy)·[安全](https://trust.clickhouse.com/)·[服务条款](https://clickhouse.ac.cn/legal/agreements/terms-of-service) © 2016–2025 ClickHouse, Inc. [商标](https://clickhouse.ac.cn/legal/trademark-policy)·[隐私](https://clickhouse.ac.cn/legal/privacy-policy)·[安全](https://trust.clickhouse.com/)·[服务条款](https://clickhouse.ac.cn/legal/agreements/terms-of-service) © . This site is unofficial and not affiliated with ClickHouse, Inc.
Readable Markdown	null
Shard	159 (laksa)
Root Hash	6418803694421178359
Unparsed URL	cn,ac,clickhouse!/docs/en/sql-reference/aggregate-functions/grouping_function s443