What makes a SQL query complex? 什么让 SQL 查询变得复杂？
A complex SQL query combines several interacting transformations whose grain, cardinality, predicates, ordering, and business rules must remain consistent from source to final output. Complexity is not line count. A short correlated expression can be harder to reason about than a long sequence of named stages with explicit contracts.
复杂 SQL 查询组合多个相互作用的转换，并要求粒度、基数、条件、顺序与业务规则从源头到最终输出始终一致。 复杂度并不等于行数。一个简短的相关表达式，可能比一组具有明确契约的命名阶段更难理解。
The safest starting point is an output contract: one sentence defining one row, required columns, population, time semantics, duplicate policy, and acceptance tests. Then build small relational stages and validate each grain transition. Optimize only after the result is correct and the actual execution mechanism is known.
最安全的起点是输出契约：用一句话定义一行代表什么、所需列、数据总体、时间语义、重复策略和验收测试。随后构建小型关系阶段，并验证每次粒度转换。只有结果正确且真实执行机制已知后才进行优化。
Write the grain before writing the SELECT list 在编写 SELECT 列表前先写粒度
Examples of precise grain are “one row per tenant and invoice,” “one row per account per UTC calendar month,” or “one row per session containing its first qualifying conversion.” Include the key expected to be unique. If the key cannot be stated, reviewers cannot tell whether a join duplicates rows or an aggregate collapses distinct facts.
精确粒度示例包括“每租户每发票一行”“每账户每 UTC 日历月一行”或“每会话一行并包含首次符合条件的转化”。还要写明预期唯一键。如果无法说明该键，评审者就无法判断连接是否复制记录，或聚合是否压缩了不同事实。
The contract also defines exclusions, late-arriving data, effective dates, timezone, currency conversion, null policy, and whether the result is point-in-time reproducible. Store this context beside the query. SQL alone rarely communicates all assumptions required to interpret a business metric.
契约还应定义排除项、延迟数据、有效日期、时区、货币换算、空值政策，以及结果是否可按时间点重现。应把这些背景与查询一起保存；仅凭 SQL 很少能表达解释业务指标所需的所有假设。
Decompose the query into relational responsibilities 把查询拆解为关系职责
A useful architecture separates source normalization, population
filtering, key selection, deduplication, joins, aggregation,
window analysis, and final presentation. A stage should perform
one meaningful grain transition or policy decision. Names such
as
eligible_accounts
,
invoice_totals
, and
ranked_events
communicate more than
cte1
.
实用架构会分离源数据规范化、总体过滤、键选择、去重、连接、聚合、窗口分析和最终展示。每个阶段应只执行一次有意义的粒度转换或政策决策。
eligible_accounts
、
invoice_totals
和
ranked_events
等名称比
cte1
更有信息。
WITH eligible_accounts AS (...),
invoice_totals AS (
SELECT tenant_id, account_id, month_start,
SUM(amount) AS revenue
FROM normalized_invoices
GROUP BY tenant_id, account_id, month_start
),
compared AS (
SELECT i.*,
LAG(revenue) OVER (
PARTITION BY tenant_id, account_id
ORDER BY month_start
) AS prior_revenue
FROM invoice_totals i
)
SELECT * FROM compared;CTEs improve reviewability but are not automatic optimization barriers or performance improvements. Database behavior varies. Use named stages to express intent, then inspect whether the engine inlines, materializes, repeats, or reorders them.
CTE 能改善可评审性，但不会自动成为优化屏障或性能提升手段。数据库行为各不相同。应使用命名阶段表达意图，再检查数据库对它们进行内联、物化、重复还是重新排序。
Control fan-out before adding more logic 在增加更多逻辑前控制扇出
Most serious errors in complex SQL begin with an unexamined join. For every join, state the expected relationship: one-to-one, many-to-one, one-to-many, or many-to-many. Verify key uniqueness on both sides using the actual filtered populations. A declared constraint may not cover effective dates, tenant keys, or snapshots used by the query.
复杂 SQL 中最严重的错误大多始于未检查的连接。对每个连接都应说明预期关系：一对一、多对一、一对多或多对多，并在真实过滤总体上验证两侧键的唯一性。已声明约束可能并未覆盖查询使用的有效日期、租户键或快照。
When the final query sums revenue after a many-to-many join, duplication can produce plausible but inflated totals. Pre-aggregate the many side to the required key or choose one row with a documented deterministic rule before joining. Measure row counts before and after each join, plus unmatched keys in both directions.
最终查询在多对多连接后汇总收入时，重复可能产生看似合理但被放大的总额。应先把多的一侧聚合到所需键，或用有文档的确定性规则选出一行再连接。每次连接前后都要测量行数，并检查双向未匹配键。
Place predicates where their meaning is preserved 把条件放在能保持其含义的位置
A predicate in
WHERE
, a join condition, an aggregate
HAVING
, or a post-window filter answers a different question. Moving a
right-table predicate from an outer join's
ON
clause to
WHERE
can eliminate unmatched left rows. Filtering detail before
aggregation differs from filtering groups after aggregation.
WHERE
、连接条件、聚合后的
HAVING
或窗口后的过滤回答不同问题。把外连接右表条件从
ON
移到
WHERE
可能删除未匹配左侧记录；聚合前过滤明细与聚合后过滤分组也含义不同。
Classify each predicate as population, relationship, measurement, qualification, or presentation. Write a plain-language sentence for it. Pushdown can reduce work, but only when it does not change outer-join preservation, window population, or aggregate inputs. Confirm both semantics and the actual plan.
应把每个条件分类为总体、关系、度量、资格或展示，并用自然语言说明。条件下推可能减少工作，但前提是不改变外连接保留、窗口总体或聚合输入。语义与实际计划都必须确认。
Aggregate at one declared grain per stage 每个阶段只在一个声明粒度上聚合
Mixing invoice-line, invoice, account-month, and
account-lifetime measures in one SELECT makes accidental double
counting hard to see. Create one stage for each aggregation
level, declare its key, and join only after dimensions are
compatible. Distinguish
COUNT(*)
,
COUNT(column)
, and
COUNT(DISTINCT key)
; each answers a different question.
在同一 SELECT
中混合发票明细、发票、账户月度和账户生命周期度量，会让意外重复计算难以发现。应为每个聚合层建立一个阶段并声明其键，只有维度兼容后才连接。还要区分
COUNT(*)
、
COUNT(column)
和
COUNT(DISTINCT key)
，因为它们回答不同问题。
Distinct is not a general repair for duplicates. It can conceal an incorrect join while dropping legitimate repeated facts. Find the mechanism, decide the intended grain, and resolve duplication at the earliest stage where the business rule is known.
DISTINCT 不是重复数据的通用修复。它可能掩盖错误连接，同时删除合法重复事实。应找到产生重复的机制，确定预期粒度，并在最早能够明确业务规则的阶段解决。
Protect partitions, order, and frame semantics 保护分区、排序与窗口框架语义
Window functions preserve rows while calculating across related rows. Their risk comes from hidden sequence assumptions. State the partition entity, provide deterministic ordering when peers matter, and specify the frame when cumulative or moving calculations depend on it. The default frame can include peers or stop at the current ordering value in ways reviewers do not expect.
窗口函数在跨相关记录计算时保留每条记录，其风险来自隐藏的序列假设。应说明分区实体，在并列值重要时提供确定性排序，并在累计或移动计算依赖窗口框架时显式指定。默认框架可能包含并列记录，或在当前排序值结束，与评审者预期不同。
Calculate reusable windows in one named stage. Preserve raw values, ordering keys, and derived outputs for reconciliation. Check sort costs, partition skew, spills, and whether slightly different window specifications force repeated work.
应在一个命名阶段计算可复用窗口，并保留原始值、排序键与派生输出用于核对。还要检查排序成本、分区倾斜、溢写，以及细微不同的窗口定义是否迫使数据库重复工作。
Choose nested shapes by contract, not habit 按契约选择嵌套形态，而非按习惯
Scalar subqueries require at most one row. Set subqueries interact with NULL through three-valued logic. Correlated subqueries can express existence elegantly but may also trigger repeated work. Derived tables can isolate a transformation yet hide grain if aliases are vague. Label the expected shape and validate its cardinality.
标量子查询最多允许一行；集合子查询通过三值逻辑与 NULL 交互；相关子查询能优雅表达存在性，也可能造成重复工作；派生表可以隔离转换，但别名模糊时会隐藏粒度。应标记预期形态并验证其基数。
Use
EXISTS
when the question is whether any match exists. Prefer
NOT EXISTS
over nullable
NOT IN
for anti-existence. Refactor repeated lookups or deep nesting
when it improves clarity or removes a measured bottleneck, then
prove equivalence.
问题是“是否存在任意匹配”时使用
EXISTS
；反存在判断中，相比可返回 NULL 的
NOT IN
，更应考虑
NOT EXISTS
。当重构能提升清晰度或消除已测量瓶颈时再处理重复查找与深层嵌套，并证明等价。
Build a monthly revenue and retention query safely 安全构建月度收入与留存查询
Assume the output is one row per tenant, account, and UTC month. Normalize invoice timestamps and currency first. Aggregate paid invoice lines to invoice, then account-month, so later joins cannot multiply revenue. Create an account-month spine if zero-activity months must remain visible. Join eligible accounts using the complete tenant-account key.
假设输出粒度为每租户、账户和 UTC 月一行。先规范发票时间戳与货币；把已支付发票明细先聚合到发票，再聚合到账户月度，避免后续连接放大收入。若零活动月份必须可见，应建立账户月份骨架，并使用完整租户-账户键连接合格账户。
Apply LAG over each account to obtain prior-month revenue, but only after the continuous month spine exists. Derive retention status from current and prior values with explicit rules for new, retained, expanded, contracted, and churned. Finally join descriptive dimensions that are unique for the report's effective date.
只有连续月份骨架建立后，才在每个账户内使用 LAG 取得上月收入。基于当前与上一值，使用明确规则派生新增、留存、扩张、收缩与流失状态。最后连接在报表有效日期上唯一的描述维度。
At each stage, assert unique keys and reconcile revenue totals to a trusted ledger population. This design may be longer than one nested statement, but every grain change and business decision is inspectable.
每个阶段都要断言唯一键，并把收入总额与可信账本总体核对。该设计可能比单条深层嵌套语句更长，但每次粒度变化和业务决策都可检查。
Test invariants and adversarial data before performance 在性能测试前验证不变量与对抗性数据
- Assert uniqueness at every declared stage grain.
- Record row counts and unmatched keys before and after joins.
- Test duplicate dimension rows and many-to-many relationships.
- Test NULL keys, NULL measures, zero denominators, and empty populations.
- Test date boundaries, timezone changes, late data, and effective-date overlaps.
- Preserve intermediate reconciliation columns during review.
- Compare old and new outputs in both difference directions.
- Validate representative large and skewed entities, not only averages.
- 在每个声明阶段粒度上断言唯一性。
- 记录连接前后行数与双向未匹配键。
- 测试重复维度记录和多对多关系。
- 测试空键、空度量、零分母和空总体。
- 测试日期边界、时区变化、延迟数据和有效期重叠。
- 评审期间保留中间核对列。
- 双向比较新旧输出差异。
- 验证代表性大型与倾斜实体，而不只看平均值。
Read the actual plan as evidence of work 把实际计划当作工作量证据
Inspect actual versus estimated rows, join algorithms, scan and seek patterns, sorts, hashes, spills, exchanges, memory grants, repeated operators, and predicate placement. Large estimate errors often explain unsuitable join choices or memory behavior. Identify the operator that dominates elapsed time or resource use and trace the data mechanism feeding it.
应检查实际与估算行数、连接算法、扫描与查找模式、排序、哈希、溢写、交换、内存授予、重复算子和条件位置。巨大的估算误差经常解释不合适的连接选择或内存行为。应找出主导耗时或资源的算子，并追踪为其提供数据的机制。
An index recommendation is a hypothesis, not a verdict. Evaluate key order, selectivity, included columns, write amplification, storage, maintenance, and whether a structural rewrite reduces more work. Re-test with representative parameters, warm and cold considerations, and concurrency where relevant.
索引建议只是待验证假设，不是结论。应评估键顺序、选择性、包含列、写放大、存储、维护，以及结构重构是否能减少更多工作。还要使用代表性参数、冷热状态和必要的并发重新测试。
Change one mechanism and preserve the contract 每次改变一个机制并保持契约
Useful interventions include pre-aggregating before a join, replacing repeated scalar lookups with one set-based relation, separating independent window specifications, reducing width before a sort, eliminating redundant distinct operations, or materializing a genuinely reused expensive stage. Tie each change to plan evidence.
有效措施包括连接前预聚合、把重复标量查找改为一次集合关系、分离独立窗口定义、排序前减少记录宽度、消除冗余 DISTINCT，或物化真正被重复使用的昂贵阶段。每个改动都应对应执行计划证据。
Use one controlled change at a time when possible. Re-run equivalence checks first, then benchmark repeated executions with identical data and parameter sets. Report median and tail behavior, resource changes, affected workloads, and remaining limitations. A faster result that changes population or duplicates money is not an optimization.
尽可能一次只做一个受控改动。先重新运行等价性检查，再使用相同数据和参数集重复基准测试。报告中位与尾部表现、资源变化、受影响负载和剩余限制。更快但改变总体或重复金额的结果不是优化。
Diagnose doubled revenue after a feature join 诊断增加功能连接后收入翻倍
Suppose a monthly report adds product tags and revenue doubles for some accounts. The account-month fact is joined to multiple tag rows before the final SUM. Compare row counts at the account-month grain, count tags per product, and preserve invoice identifiers through a diagnostic version. The many-to-many fan-out becomes visible.
假设月度报表增加产品标签后，部分账户收入翻倍。账户月度事实在最终 SUM 前连接到多个标签记录。应在账户月度粒度比较行数，统计每产品标签数量，并在诊断版本中保留发票标识，多对多扇出就会显现。
The fix depends on intent. If tags are filters, use EXISTS. If one category is required, define a deterministic category rule. If revenue must be allocated across tags, define an allocation model whose weights reconcile to one. Do not add DISTINCT to the final projection; it cannot reliably repair multiplied measures.
修复方式取决于意图：标签只是过滤条件时使用 EXISTS；需要一个类别时定义确定性类别规则；收入必须分配到多个标签时，建立权重合计为一的分配模型。不要在最终投影添加 DISTINCT，它无法可靠修复被放大的度量。
Inspect the full complex SQL statement 检查完整复杂 SQL 语句
Paste a sanitized complete query into the InfiniSynapse SQL Complexity Checker to review joins, CTEs, nested layers, aggregates, windows, filters, and repeated structures together. Use its structural review to focus human analysis, then verify meaning and runtime with your database's actual plan.
将脱敏后的完整查询粘贴到 InfiniSynapse SQL Complexity Checker，一起检查连接、CTE、嵌套层、聚合、窗口、过滤与重复结构。用结构审查聚焦人工分析，再通过数据库实际计划验证含义与运行表现。
Open SQL Complexity Checker 打开 SQL 复杂度检查器 Remove credentials, secrets, personal data, and sensitive literals. 请移除凭据、密钥、个人数据和敏感字面值。Complex SQL queries frequently asked questions 复杂 SQL 查询常见问题
Interacting grains, joins, predicates, aggregation, windows, nested logic, and business rules create complexity.
相互作用的粒度、连接、条件、聚合、窗口、嵌套逻辑和业务规则造成复杂度。
Define the output grain, population, keys, time semantics, and acceptance tests first.
先定义输出粒度、总体、键、时间语义和验收测试。
They can improve clarity, but performance depends on the database and actual plan.
它们可以提升清晰度，但性能取决于数据库和实际计划。
Compare bidirectional differences, duplicates, NULLs, aggregates, invariants, and edge cases.
比较双向差异、重复、NULL、聚合、不变量和边界情况。
Actual rows, estimate errors, joins, access paths, sorts, spills, memory, and repeated work.
实际行数、估算误差、连接、访问路径、排序、溢写、内存和重复工作。