On this page本文目录
What Is Data Aggregation?什么是数据聚合？
For the full topic map and the neighboring methods that support this workflow, continue with the complete data integration guide.
如需查看完整主题结构以及支撑本流程的相邻方法，请继续阅读完整的数据集成指南。
Data aggregation is the process of grouping detailed records at a declared grain and calculating summary measures—such as count, sum, average, minimum, maximum, or percentile—for each group. The output replaces many atomic rows with fewer rows designed for analysis, reporting, monitoring, or downstream calculation.
数据聚合是按照声明的粒度对明细记录分组，并为每个组计算计数、求和、平均值、最小值、最大值或百分位数等汇总指标的过程。输出会用更少的行替代大量原子记录，以支持分析、报告、监控或后续计算。
Aggregation is more than applying a function. Every result depends on five choices: the population of eligible rows, the grouping dimensions, the time or event window, the measure formula, and the output grain. “Monthly revenue by region” is meaningful only when revenue, month boundaries, regions, refunds, currencies, duplicate records, and late events have explicit definitions.
聚合不只是调用一个函数。每个结果都取决于五项选择：符合条件的记录总体、分组维度、时间或事件窗口、指标公式和输出粒度。“按区域统计月收入”只有在收入、月份边界、区域、退款、币种、重复记录和迟到事件都有明确定义时才有意义。
The term is sometimes used broadly for collecting data from multiple sources. Collection can precede aggregation, but the defining operation is summarization. A consolidated table that preserves every record is not yet aggregate data; a grouped result that reduces records to defined measures is.
这个术语有时被宽泛地用于描述从多个来源收集数据。收集可以发生在聚合之前，但决定性操作是汇总。保留每条记录的合并表还不是聚合数据；把记录压缩为明确定义指标的分组结果才是。
Why Aggregate Data—and When Not To为什么聚合数据，何时不应聚合
Data aggregation makes repeated questions easier to answer. Dashboards can read daily totals instead of scanning every transaction; monitoring systems can evaluate error rates by service and minute; analysts can compare customer behavior by cohort; finance teams can reconcile account balances by period. A smaller result may also reduce query work when the same summaries are reused.
数据聚合让重复问题更容易回答。仪表板可以读取每日总额，而不必扫描每笔交易；监控系统可以按服务和分钟评估错误率；分析人员可以按群组比较客户行为；财务团队可以按期间核对账户余额。当相同汇总被反复使用时，更小的结果也可能减少查询工作。
The question has stable dimensions and measures, detailed records are validated, repeated summaries are valuable, and consumers understand the resulting grain.
问题具有稳定维度和指标，明细记录已经验证，重复汇总具有价值，使用者理解结果粒度。
Investigation, anomaly diagnosis, causal analysis, audits, changing definitions, and record-level action require traceable atomic data rather than summaries alone.
调查、异常诊断、因果分析、审计、定义变化和记录级操作需要可追溯的原子数据，不能只保留汇总。
Do not aggregate merely to hide poor data quality or reduce storage. Summaries can conceal duplicates, outliers, missing groups, or incorrect joins. Retain a path from each output group back to its rule version, input snapshot, and eligible-record count.
不要为了掩盖数据质量问题或单纯减少存储而聚合。汇总可能隐藏重复、异常值、缺失分组或错误连接。每个输出组都应能追溯到规则版本、输入快照和符合条件的记录数量。
Define Grain, Dimensions, Measures, and Scope定义粒度、维度、指标与范围
The most important design decision is the grain: what one output row represents. Dimensions identify a group; measures summarize records inside it. Filters and windows define which records are eligible. Write these elements before writing SQL, configuring a pivot table, or asking an analysis tool to compute the result.
最重要的设计决策是粒度，即一行输出代表什么。维度标识分组，指标汇总组内记录，过滤条件和窗口定义哪些记录有资格进入计算。在编写SQL、配置数据透视表或要求分析工具计算之前，先写清这些要素。
| Element要素 | Question to answer需要回答的问题 | Example示例 |
|---|---|---|
| Population总体 | Which records are eligible?哪些记录有资格进入？ | Completed order lines excluding test accounts排除测试账户后的已完成订单行 |
| Grain粒度 | What does one output row mean?一行输出代表什么？ | One calendar day, region, and product category一个日历日、区域和产品类别 |
| Dimensions维度 | Which attributes define a group?哪些属性定义分组？ | Order date, region, category订单日期、区域、类别 |
| Measures指标 | What is calculated per group?每组计算什么？ | Orders, units, gross sales, refunds, net sales订单数、件数、销售额、退款、净销售额 |
| Window窗口 | Which time rule assigns records?按什么时间规则归属记录？ | UTC calendar day based on completion time按完成时间划分的UTC日历日 |
| Rule version规则版本 | Which definitions produced the result?哪个定义版本生成结果？ | Metric contract v3, effective date recorded指标契约v3，并记录生效日期 |
Choose the Right Data Aggregation Method选择正确的数据聚合方法
PostgreSQL documents aggregate functions as producing one result from a set of input values; GoogleSQL likewise describes an aggregate as summarizing the rows of a group into a single value. The function must match the business meaning and the data type. A mathematically valid expression can still be analytically wrong.
PostgreSQL文档把聚合函数定义为从一组输入值计算一个结果；GoogleSQL也把聚合描述为把一组行汇总为单个值。函数必须符合业务含义和数据类型。数学上有效的表达式仍可能在分析上错误。
| Method方法 | Use when适用情况 | Watch for注意事项 |
|---|---|---|
| COUNT | Counting eligible rows or events计算符合条件的行或事件 | Duplicate rows, null behavior, event versus entity counts重复行、空值行为、事件数与实体数混淆 |
| COUNT DISTINCT | Counting unique entities by a stable key按稳定键计算唯一实体 | Identity resolution, approximate algorithms, null keys身份解析、近似算法、空键 |
| SUM | Adding compatible quantities累加兼容数量 | Currency conversion, unit mismatch, refunds, join fanout币种转换、单位不一致、退款、连接扇出 |
| AVG | A group mean has a meaningful denominator组均值具有有意义的分母 | Average of averages, missing weights, skew, null exclusion平均值再平均、缺少权重、偏斜、空值排除 |
| MIN / MAX | Finding group boundaries or latest states寻找组边界或最新状态 | Ties, time zones, invalid outliers, unrelated companion columns并列、时区、无效异常值、无关伴随列 |
| Percentile百分位数 | Describing distribution and tail behavior描述分布和尾部行为 | Continuous versus discrete rules, sample size, approximation连续与离散规则、样本量、近似计算 |
| Weighted average加权平均 | Subgroups contribute different denominators子组具有不同分母 | Retain numerator and denominator; never average averages blindly保留分子与分母，不要盲目平均各组均值 |
A Repeatable Data Aggregation Process可重复的数据聚合流程
- Write the analytical question. State the decision, population, output grain, dimensions, measures, time rule, exclusions, owner, and refresh need.写出分析问题。说明决策、总体、输出粒度、维度、指标、时间规则、排除项、责任人和刷新需求。
- Profile detailed inputs. Check keys, types, duplicates, nulls, units, currencies, time zones, late records, category values, and update behavior.分析明细输入。检查键、类型、重复、空值、单位、币种、时区、迟到记录、类别值和更新行为。
- Normalize before grouping. Apply approved mappings, deduplicate at the declared key, align units and calendars, and record rejected rows rather than silently dropping them.分组前标准化。应用获准映射，按声明键去重，统一单位与日历，并记录拒绝行，而不是静默丢弃。
- Calculate at the lowest useful grain. Produce additive components, eligible-row count, distinct keys, and diagnostic fields that support later rollups and reconciliation.在最低有用粒度计算。生成可加组件、符合条件行数、唯一键和诊断字段，以支持后续上卷与核对。
- Validate independently. Compare totals with control queries or source reports, inspect sample groups, test empty and edge cases, and verify that lower-level groups recombine correctly.独立验证。将总额与控制查询或源报告比较，检查样本组，测试空组与边界情况，并验证低层组能正确重组。
- Publish definitions with results. Store grain, dimensions, formulas, time zone, rule version, refresh time, input coverage, owner, and known limitations.随结果发布定义。保存粒度、维度、公式、时区、规则版本、刷新时间、输入覆盖、责任人和已知限制。
- Monitor and recompute. Detect late data, source corrections, new categories, schema changes, and drift; define which windows are mutable and how backfills replace prior summaries.监控并重算。发现迟到数据、源端更正、新类别、Schema变化和漂移；定义哪些窗口可变，以及回填如何替换既有汇总。
Data Aggregation Example: Daily Regional Sales数据聚合示例：每日区域销售
This is a hypothetical example, not a customer case. A retailer has one row per order line. Each row includes order ID, line ID, completion timestamp, region, category, quantity, gross amount, discount, refund, currency, and status. The question is: “What were daily net sales and purchasing activity by region and category?”
以下是假设示例，不是客户案例。某零售商每个订单行一条记录，字段包括订单ID、行ID、完成时间、区域、类别、数量、销售额、折扣、退款、币种和状态。问题是：“各区域与类别每日的净销售额和购买活动如何？”
The team defines one output row as one UTC completion date, region, and canonical product category. It excludes test accounts and cancelled lines, converts approved currencies using the declared rate source and effective date, deduplicates by line ID, maps categories through a versioned table, and keeps late completions mutable for an approved window.
团队把一行输出定义为一个UTC完成日期、区域和标准产品类别。它排除测试账户和已取消订单行，使用声明的汇率来源与生效日期转换获准币种，按订单行ID去重，通过版本化表映射类别，并在获准窗口内允许迟到完成记录更新结果。
| Measure指标 | Formula公式 | Validation验证 |
|---|---|---|
| Order count订单数 | Distinct eligible order IDs符合条件的唯一订单ID数 | Compare with independent order-header count与独立订单头计数比较 |
| Units件数 | Sum of eligible quantities符合条件数量之和 | Reconcile category and regional totals to the day将类别与区域总数核对到每日总数 |
| Net sales净销售额 | Gross amount minus discounts and refunds after conversion转换后销售额减折扣与退款 | Compare components and overall total with finance control将组件与总体总额同财务控制数比较 |
| Average order value平均订单价值 | Net sales divided by distinct orders净销售额除以唯一订单数 | Recompute from retained numerator and denominator用保留的分子与分母重新计算 |
Why this matters: summing order counts from line-level rows would overcount multi-line orders, while averaging category averages would give small and large categories equal weight. Declaring the entity key and retaining numerators and denominators prevents both errors.
为什么重要：直接对订单行的订单数求和会重复计算多行订单，而对类别平均值再次取平均会让大小类别权重相同。声明实体键并保留分子与分母可以防止这两类错误。
How to Validate Aggregated Data如何验证聚合数据
Validation should be independent of the primary aggregation where possible. Repeating the same query in the same way can reproduce the same error. Use source control totals, separate query paths, accounting identities, sampled groups, and recombination checks.
在可能情况下，验证应独立于主要聚合。以相同方式重复同一查询可能重现同一错误。应使用源端控制总额、独立查询路径、会计恒等式、抽样分组和重组检查。
- Population check: eligible + excluded + rejected records should explain the input scope; investigate unexplained loss.总体检查：符合、排除和拒绝记录应能解释输入范围；调查无法解释的丢失。
- Uniqueness check: verify one output row per declared dimension combination and detect duplicate group keys.唯一性检查：验证每个声明维度组合只有一行输出，并检测重复分组键。
- Additivity check: lower-level additive measures should recombine to expected higher-level totals without duplication.可加性检查：低层可加指标应能无重复地重组为预期高层总额。
- Formula check: recompute ratios and averages from retained components; test zero denominators and null rules.公式检查：用保留组件重算比率与平均值，并测试零分母和空值规则。
- Change check: compare runs by new, changed, removed, late, and remapped groups rather than only final totals.变化检查：按新增、变化、移除、迟到和重新映射的组比较运行，而不是只看最终总额。
Use InfiniSynapse for Connected-Source Analysis使用InfiniSynapse分析已连接来源
InfiniSynapse's public website describes direct connections to supported databases and joint analysis across multiple sources without first requiring complex migration. That makes the web app relevant when the aggregation question can be answered over approved connected data and the team wants to test definitions before building another persistent summary.
InfiniSynapse官网描述了对受支持数据库的直接连接，以及无需先进行复杂迁移的多源联合分析。当聚合问题可以在获准的已连接数据上回答，并且团队希望在构建新持久汇总前测试定义时，Web App具有相关性。
Prepare read-only connection details, permitted schemas, entity keys, joins, filters, time zone, grouping dimensions, formulas, expected control totals, and privacy boundaries. Use those inputs to ask a precise analytical question and compare the returned groups with independent evidence.
请准备只读连接信息、允许访问的Schema、实体键、连接关系、过滤条件、时区、分组维度、公式、预期控制总额和隐私边界。使用这些输入提出精确分析问题，并将返回分组与独立证据比较。
InfiniSynapse should not be described as an ingestion service, ETL engine, persistent aggregate store, streaming processor, or scheduled materialization pipeline. Use dedicated data engineering systems when summaries must be durably refreshed, distributed, versioned, or recovered as production data products.
不应把InfiniSynapse描述为采集服务、ETL引擎、持久聚合存储、流处理器或计划物化管道。当汇总必须作为生产数据产品持久刷新、分发、版本化或恢复时，应使用专用数据工程系统。
Bring the metric contract, grouping grain, read-only access, and reconciliation totals. Use the InfiniSynapse web app to explore and validate connected-source summaries; retain dedicated pipelines when the result needs scheduled durable delivery.
请准备指标契约、分组粒度、只读访问和核对总额。使用InfiniSynapse Web App探索并验证已连接来源的汇总；如果结果需要计划性持久交付，则保留专用管道。
Analyze approved connected data分析获准的已连接数据Data Aggregation FAQ数据聚合常见问题
What is data aggregation?
什么是数据聚合？
Data aggregation groups detailed records at a declared grain and calculates summary measures such as counts, sums, averages, minima, maxima, or percentiles for each group.
数据聚合按照声明粒度对明细记录分组，并为每组计算计数、求和、平均值、最小值、最大值或百分位数等汇总指标。
What is an example of data aggregation?
数据聚合的例子是什么？
A retailer can group validated order lines by calendar day and region, then calculate order count, distinct customer count, units, gross sales, discounts, refunds, and net sales for each group.
零售商可以按日历日和区域对验证后的订单行分组，再为每组计算订单数、唯一客户数、件数、销售额、折扣、退款和净销售额。
How is data aggregation different from data integration?
数据聚合与数据集成有何不同？
Data integration connects, moves, or unifies data across systems. Data aggregation summarizes records into grouped results. Aggregation may be one transformation within an integration workflow, but neither term replaces the other.
数据集成跨系统连接、搬运或统一数据；数据聚合把记录汇总为分组结果。聚合可以是集成工作流中的一种转换，但两个术语不能互相替代。
How do you validate aggregated data?
如何验证聚合数据？
Freeze the input scope and rules, reconcile additive totals, compare group counts with independent control queries, inspect sample groups, test null and duplicate behavior, and verify that groups recombine to expected higher-level totals.
应固定输入范围与规则，核对可加总额，将分组计数与独立控制查询比较，检查样本组，测试空值和重复行为，并验证各组能重组为预期的高层总额。
Authoritative Sources and Next Steps权威来源与下一步
Use official engine documentation to confirm function syntax, null handling, ordering, approximation, and grouping behavior. Preserve the aggregation specification, mapping versions, input snapshot, rejected-record log, control totals, result, and approval. Re-run the highest-risk checks when definitions, source schemas, calendars, or execution engines change.
应使用官方引擎文档确认函数语法、空值处理、顺序、近似和分组行为。保留聚合规格、映射版本、输入快照、拒绝记录日志、控制总额、结果和审批记录。当定义、源Schema、日历或执行引擎变化时，重新运行最高风险检查。
- PostgreSQL documentation for aggregate functionsPostgreSQL聚合函数文档
- GoogleSQL documentation for aggregate function callsGoogleSQL聚合函数调用文档
- NIST guidance on de-identification techniques and governanceNIST关于去标识化技术与治理的指南
- InfiniSynapse guide to reliable data pipelinesInfiniSynapse可靠数据管道指南
- InfiniSynapse guide to identifying and governing data sourcesInfiniSynapse数据源识别与治理指南