What is natural language to SQL?什么是自然语言转 SQL(NL2SQL)?
Natural language to SQL converts an everyday data question into a reviewable SQL draft grounded in a known relational schema. A dependable workflow must also resolve business meaning, constrain accessible data, ask for clarification when necessary, control execution, and verify the returned result.
自然语言转 SQL(NL2SQL)是把用户用中文等日常语言提出的数据问题,结合数据库 Schema、业务定义与权限约束,转换为可评审 SQL 草稿的过程。可靠系统不能只追求“生成得像 SQL”,还要澄清口径、限制可访问数据、控制执行,并验证返回结果是否真正回答了原问题。
The phrase “show revenue for our best customers last quarter” looks simple but leaves at least five unresolved decisions: revenue before or after refunds, “best” by revenue or margin, customer versus account grain, calendar versus fiscal quarter, and reporting versus transaction currency. A system that silently chooses defaults may produce polished SQL and the wrong decision.
“显示上季度最佳客户的收入”看起来很简单,却至少留下五个未解决的决策:收入是否扣除退款;“最佳”按收入还是利润;粒度是客户还是账户;上季度是自然季度还是财务季度;采用报告币种还是交易币种。系统若静默选择默认值,可能生成漂亮 SQL,却支持了错误决策。
Separate NL2SQL from search and broader natural language query区分 NL2SQL、自然语言查询与语义搜索
Natural language query, or NLQ, describes the user interface and intent layer: a person asks for information without writing the underlying formal language. Text-to-SQL is one implementation path. Keyword search and semantic search retrieve existing documents or records; conversational analytics can maintain context across several questions; a BI Q&A interface may compile a question into a semantic-model expression rather than exposing SQL.
自然语言查询(NLQ)描述的是用户交互与意图层:用户无需编写底层形式化语言,就能请求信息。Text-to-SQL 是其中一种实现路径;关键词搜索和语义搜索主要检索已有文档或记录;对话式分析能够在多轮问题间保持上下文;BI 问答界面也可能把问题编译为语义模型表达式,而不是直接暴露 SQL。
| Approach方式 | Primary output主要输出 | Best suited to适合场景 | Main failure mode主要失败模式 |
|---|---|---|---|
| Keyword search关键词搜索 | Matching documents or records匹配文档或记录 | Known terms and indexed text已知术语与索引文本 | Vocabulary mismatch词汇不匹配 |
| Semantic search语义搜索 | Conceptually similar content概念相似内容 | Discovery over unstructured material非结构化资料发现 | Relevant text mistaken for a computed answer把相关文本误当成计算答案 |
| Text-to-SQLText-to-SQL | SQL statementSQL 语句 | Relational data with known schema已知模式的关系型数据 | Valid syntax with wrong semantics语法有效但语义错误 |
| Semantic-model NLQ语义模型 NLQ | Governed measure and dimensions受治理指标与维度 | Repeated business metrics重复使用的业务指标 | Incomplete model or missing synonym模型不完整或缺少同义词 |
| Conversational analytics对话式分析 | Multi-step analysis and explanation多步骤分析与解释 | Exploration with follow-up questions包含追问的探索分析 | Stale or incorrectly inherited context上下文过期或错误继承 |
A production experience may combine several approaches. The important design rule is to expose the answer path: whether the result came from retrieved text, computed data, a governed metric, or a generated query. Users should not infer calculation evidence from a fluent narrative.
生产系统可能组合多种方式。关键设计原则是明确答案路径:结果究竟来自检索文本、数据计算、受治理指标还是生成查询。不能让用户因为叙述流畅,就误以为系统已经提供计算证据。
Build natural language to SQL as a controlled pipeline把中文问题转成 SQL 的过程设计成受控流水线
Treat NLQ as a system, not a single model call. Each stage should have an explicit input, output, policy, telemetry record, and failure behavior. A strong system can ask for clarification or decline a request instead of forcing every prompt into executable SQL.
应把 NLQ 视为系统,而不是单次模型调用。每个阶段都应具有明确输入、输出、策略、遥测记录与失败行为。可靠系统应能够主动澄清或拒绝请求,而不是把每个提示都强行转换为可执行 SQL。
- Authenticate and establish scope.认证并建立范围。 Resolve user identity, role, permitted domains, geography, and session policy before retrieving schema context. 在检索模式上下文之前,确认用户身份、角色、允许领域、地域与会话策略。
- Normalize the request.规范化请求。 Detect language, entities, comparison, time, grain, desired output, and any references to earlier turns. 识别语言、实体、比较、时间、粒度、输出形式与对先前轮次的引用。
- Detect ambiguity and risk.检测歧义与风险。 Identify undefined metrics, competing time fields, missing populations, sensitive outputs, and potentially expensive operations. 识别未定义指标、多个时间字段、缺失总体、敏感输出与潜在高成本操作。
- Ground in governed meaning.对齐受治理语义。 Retrieve approved metrics, dimensions, synonyms, relationships, policies, verified examples, and representative values. 检索获批指标、维度、同义词、关系、策略、已验证实例与代表值。
- Plan the answer.规划答案。 Select sources, define result grain, choose joins and filters, decide whether clarification is mandatory, and state assumptions. 选择数据源、定义结果粒度、确定连接与筛选、判断是否必须澄清并说明假设。
- Compile and validate the operation.编译并验证操作。 Generate dialect-specific SQL or another query form, parse it, resolve referenced objects, and apply static policy and cost checks. 生成方言专属 SQL 或其他查询形式,完成解析与对象解析,并应用静态策略与成本检查。
- Execute inside a sandboxed boundary.在隔离边界内执行。 Use least privilege, read-only mode, parameter binding, row and time limits, workload isolation, and governed exports. 使用最小权限、只读模式、参数绑定、行数与时间限制、工作负载隔离及受控导出。
- Validate and present evidence.验证并展示证据。 Check invariants and result shape, then show definitions, filters, time range, source freshness, assumptions, SQL, and warnings beside the answer. 检查不变条件和结果形状,再在答案旁展示定义、筛选、时间范围、数据新鲜度、假设、SQL 与警告。
- Capture feedback without auto-promoting it.记录反馈,但不自动提升为规则。 Store reviewed corrections as candidates for semantic-model or verified-query changes under human approval. 把经过评审的纠正保存为语义模型或已验证查询候选,必须经人工批准后生效。
Resolve meaning before generating SQL生成 SQL 之前先解决业务含义
Consider the request: “Which enterprise customers grew the most last quarter?” A weak pipeline immediately searches for tables containing customer, enterprise, and revenue. A governed pipeline first produces an interpretation record.
考虑这个请求:“上季度增长最快的企业客户有哪些?”薄弱系统会立刻搜索包含“客户”“企业”“收入”的表;受治理系统则会先生成一份解释记录。
| Question component问题组成 | Competing interpretations可能解释 | Resolution解决方式 |
|---|---|---|
| Enterprise customer企业客户 | Current CRM segment, contract tier at period end, or historical segment during each transaction当前 CRM 细分、期末合同等级,或每笔交易发生时的历史细分 | Use governed segment definition or ask使用受治理细分定义,或向用户澄清 |
| Growth增长 | Absolute change, percentage change, recurring revenue, gross revenue, margin, or usage绝对增量、增长率、经常性收入、总收入、利润或使用量 | Select an approved metric and label the unit选择获批指标并标明单位 |
| Last quarter上季度 | Calendar, fiscal, regional fiscal calendar, or rolling 90 days自然季度、财务季度、地区财务日历或滚动 90 天 | Resolve from tenant policy and user timezone根据租户策略与用户时区确定 |
| Comparison period比较期间 | Previous quarter or same quarter last year上一季度或去年同期 | Ask when no default is governed没有受治理默认值时必须询问 |
| “Most”“最快” | Top N, threshold, all positive growers, or statistically meaningful changeTop N、阈值、全部正增长对象或统计显著变化 | Request N or apply a disclosed report policy询问 N,或采用明确披露的报表策略 |
| Eligibility合格条件 | Include new customers, churned customers, mergers, zero baselines, and currency changes?是否包含新客户、流失客户、合并主体、零基线与币种变化? | State cohort and exceptional-case rules明确队列与异常处理规则 |
Better clarification: “Should growth mean the absolute change in recognized net revenue versus the previous fiscal quarter, and should new customers without a prior-quarter baseline be listed separately?” This asks one decision-rich question instead of exposing internal table names.
更好的澄清问题:“增长是否指已确认净收入相较上一财务季度的绝对变化?没有上季度基线的新客户是否单独列出?”这类问题一次解决多个关键决策,又不会向用户暴露内部表名。
After clarification, record the result contract: one row per governed customer account; recognized net revenue in reporting currency; current and comparison fiscal-quarter boundaries; minimum baseline rule; deterministic ranking; and separate treatment for new accounts. Only then should the system select schema objects and generate SQL.
澄清之后,应记录结果契约:每个受治理客户账户一行;使用报告币种的已确认净收入;明确当前与对比财务季度边界;规定最小基线;采用确定性排名;新账户单独处理。完成这些步骤后,系统才应选择数据库对象并生成 SQL。
Give the system a minimal, governed context packet为系统提供最小且受治理的上下文包
More context is not automatically better. Dumping an entire warehouse catalog into a prompt increases noise, disclosure risk, and the chance of selecting similarly named but unrelated objects. Retrieve only the authorized domain context needed for the request, and include both meaning and structure.
上下文并非越多越好。把整个数据仓库目录全部放进提示词,会增加噪声、披露风险,也更容易选中名称相似但无关的对象。应只检索当前请求需要且用户有权访问的领域上下文,并同时包含业务语义与技术结构。
User role, tenant, permitted domains, data classifications, row policies, export rules, and execution tier.
用户角色、租户、允许领域、数据分类、行级策略、导出规则与执行等级。
Database engine and version, default catalog and schema, timezone, locale, and supported functions.
数据库引擎与版本、默认目录和模式、时区、区域设置与支持函数。
Approved metrics, dimensions, synonyms, default filters, units, currencies, calendars, and owners.
获批指标、维度、同义词、默认筛选、单位、币种、日历与负责人。
Authorized tables and columns, types, keys, cardinality, effective dates, constraints, and freshness.
获批表与字段、类型、键、基数、生效日期、约束与新鲜度。
Sanitized categorical examples and ranges that help map user terms without exposing sensitive rows.
用于映射用户术语的脱敏类别示例与范围,不暴露敏感明细。
Reviewed question, interpretation, query, expected result properties, and applicability boundary.
经过评审的问题、解释、查询、预期结果属性与适用边界。
Version the context packet and log which version produced an answer. When a metric, table, synonym, or policy changes, re-run the affected evaluation set before promotion. A generated query without traceable context cannot be reproduced reliably.
上下文包需要版本化,并记录每个答案使用的版本。当指标、表、同义词或策略变化时,应在发布前重新运行受影响的评估集。没有可追踪上下文的生成查询无法可靠复现。
Use a semantic layer to govern business meaning使用语义层治理业务含义
A database schema describes storage; it rarely defines every
business concept. A column named revenue may be
booked, billed, recognized, gross, net, local-currency, or
converted. A semantic layer gives natural language stable
objects to target and gives reviewers an owner for each
definition.
数据库模式描述存储结构,却很少完整定义业务概念。名为
revenue
的字段可能表示预订收入、开票收入、已确认收入、总额、净额、本币金额或换算金额。语义层为自然语言提供稳定的目标对象,也为每个定义指定可追责负责人。
| Semantic element语义元素 | Required content必要内容 | Example validation验证示例 |
|---|---|---|
| Metric指标 | Formula, eligible population, grain, unit, time basis, owner, version公式、合格总体、粒度、单位、时间口径、负责人和版本 | Reconcile to an approved control period与获批控制期间核对 |
| Dimension维度 | Business meaning, values, hierarchy, null policy, effective dating业务含义、取值、层级、空值策略与生效时间 | Check coverage, uniqueness, and history overlap检查覆盖率、唯一性与历史重叠 |
| Relationship关系 | Keys, direction, cardinality, required scope, time condition键、方向、基数、必要范围与时间条件 | Measure match loss and row multiplication测量匹配损失与行数膨胀 |
| Synonym同义词 | Allowed phrase, locale, mapped concept, ambiguity exclusions允许短语、语言区域、映射概念与歧义排除 | Paraphrase tests across user groups跨用户群体同义改写测试 |
| Default默认值 | Timezone, calendar, currency, population, display precision时区、日历、币种、总体与显示精度 | Show the applied default beside every answer在每个答案旁展示已应用默认值 |
| Policy策略 | Roles, objects, row filters, sensitive fields, output restrictions角色、对象、行筛选、敏感字段与输出限制 | Authorization tests with denied and allowed cases使用允许与拒绝案例测试授权 |
Do not let a language model silently rewrite governed definitions. It may suggest missing synonyms, descriptions, metrics, or verified examples, but a domain owner should review and version the change. The Snowflake Cortex Analyst evaluation documentation provides one current example of evaluating semantic views with verified question-and-query pairs and result comparison.
不能让语言模型静默改写受治理定义。系统可以建议缺失的同义词、描述、指标或验证实例,但必须由领域负责人评审并进行版本管理。Snowflake Cortex Analyst 评估文档展示了一种当前做法:使用已验证问题与查询对评估语义视图,并比较执行结果。
Link user concepts to the correct schema and grain把用户概念对齐到正确模式与粒度
Schema linking maps phrases to tables, columns, values, keys,
and relationships. Retrieval by name alone is insufficient:
account_id can mean billing account, product
workspace, legal entity, or CRM account, and different domains
may contain identically named date and amount fields.
模式对齐负责把用户短语映射到表、字段、值、键与关系。只按名称检索并不可靠:account_id
可能表示计费账户、产品工作区、法律实体或 CRM
账户;不同领域也可能存在同名日期和金额字段。
- Retrieve by governed domain.按受治理领域检索。 Narrow candidates using user role, intent, metric owner, and approved subject area before comparing names. 在比较名称之前,使用用户角色、意图、指标负责人和获批主题域缩小候选范围。
- Resolve values as well as columns.同时解析取值与字段。 “EMEA” may map to a region code, hierarchy node, sales territory, or a maintained list of countries. “EMEA” 可能映射到区域代码、层级节点、销售区域或受维护的国家列表。
- Construct complete relationship paths.构建完整关系路径。 Include tenant, effective-date, version, and bridge conditions—not merely similarly named IDs. 除相似 ID 外,还要包含租户、生效日期、版本与桥接条件。
- Predict cardinality.预测关系基数。 Declare whether each edge is one-to-one, many-to-one, one-to-many, or many-to-many at the requested grain. 按请求粒度声明每条关系是一对一、多对一、一对多还是多对多。
- Score confidence and alternatives.评估置信度与替代方案。 Keep the top plausible mappings and trigger clarification when differences would change the answer materially. 保留最合理的多个映射,当差异会显著改变答案时触发澄清。
Do not expose unauthorized schema through clarification. Ask in business terms—“billing customer or product workspace?”—instead of listing hidden tables and fields. Authorization must filter retrieval and error messages, not only final execution.
不要在澄清过程中泄露未授权模式。应使用业务术语提问,例如“指计费客户还是产品工作区?”,而不是列出隐藏表和字段。授权必须同时约束上下文检索、错误信息与最终执行。
Generate a query plan before generating SQL text先生成查询计划,再生成 SQL 文本
A structured intermediate plan makes generation reviewable. It should state the target population, output grain, measures, dimensions, time range, joins, filters, null rules, ranking, parameters, and expected result shape. SQL is then a compilation target rather than the only record of intent.
结构化中间计划能够让生成过程可评审。它应说明目标总体、输出粒度、指标、维度、时间范围、连接、筛选、空值规则、排名、参数与预期结果形状。这样 SQL 就是编译目标,而不是唯一的意图记录。
{
"population": "governed enterprise accounts",
"grain": "one row per account",
"metric": "recognized_net_revenue",
"current_period": "previous_fiscal_quarter",
"comparison": "preceding_fiscal_quarter",
"new_account_rule": "separate cohort",
"ranking": "absolute change desc, account_id",
"limit": 10,
"execution": "read_only_preview"
}
Validate this record before compilation: every concept must map to an approved definition; every source must be authorized; every join path must have a declared cardinality; time boundaries must be materialized as typed parameters; and any unresolved choice must be displayed or clarified.
编译之前应验证该记录:每个概念都必须映射到获批定义;每个数据源都必须经过授权;每条连接路径都要声明基数;时间边界需要具体化为带类型参数;任何未解决选择都必须展示或澄清。
WITH quarterly_revenue AS (
SELECT
r.account_id,
CASE
WHEN r.posted_at >= :current_start
AND r.posted_at < :current_end THEN 'current'
WHEN r.posted_at >= :prior_start
AND r.posted_at < :prior_end THEN 'prior'
END AS period_name,
SUM(r.reporting_net_revenue) AS revenue
FROM recognized_revenue r
JOIN governed_account_segment s
ON s.tenant_id = r.tenant_id
AND s.account_id = r.account_id
AND r.posted_at >= s.valid_from
AND r.posted_at < s.valid_to
WHERE s.segment_code = :enterprise_segment
AND r.posted_at >= :prior_start
AND r.posted_at < :current_end
GROUP BY r.account_id, period_name
)
SELECT
account_id,
MAX(revenue) FILTER (WHERE period_name = 'current') AS current_revenue,
MAX(revenue) FILTER (WHERE period_name = 'prior') AS prior_revenue
FROM quarterly_revenue
GROUP BY account_id;
This PostgreSQL-oriented draft deliberately stops before ranking because the “new account” cohort still needs explicit treatment. A production compiler should not quietly coerce a null prior-period value to zero and rank new accounts as infinite growth. It should encode the agreed cohort rule and then test it.
这段 PostgreSQL 风格草稿故意在排名之前停止,因为“新账户”队列仍需明确处理。生产编译器不能静默把上一期间 NULL 转换成零,再把新账户排名为无限增长;它应编码已约定的队列规则并进行测试。
Ask fewer, higher-value clarification questions提出更少但价值更高的澄清问题
Clarification has a cost: every extra question interrupts the user. Silence also has a cost when the system chooses a consequential definition. Ask only when plausible interpretations would materially change population, measure, grain, time, security, or action.
澄清存在交互成本,每增加一个问题都会打断用户;但系统静默选择关键定义同样有成本。只有当合理解释会显著改变总体、指标、粒度、时间、安全或行动时,才需要提问。
| Situation情况 | Preferred behavior建议行为 | Example示例 |
|---|---|---|
| One governed default exists存在唯一受治理默认值 | Apply and disclose it应用并披露 | “Using the company fiscal calendar.”“使用公司财务日历。” |
| Two material interpretations存在两个重大解释 | Ask a choice in business language用业务语言让用户选择 | “Recognized revenue or invoiced amount?”“指已确认收入还是开票金额?” |
| Minor display ambiguity轻微展示歧义 | Choose reversible presentation and disclose选择可逆展示并披露 | Default to a table with an optional chart默认显示表格,可选图表 |
| Unauthorized alternative替代解释未授权 | Do not reveal it; answer within authorized scope or decline不披露;在授权范围回答或拒绝 | Do not list restricted payroll fields不要列出受限薪资字段 |
| No defensible interpretation没有可辩护解释 | Abstain and state what is missing拒绝并说明缺失信息 | No approved definition for “engaged customer”“活跃客户”没有获批定义 |
Track clarification quality as an evaluation dimension. A system can generate the correct query after asking ten unnecessary questions and still be unusable. Measure clarification rate, user completion rate, correction rate after clarification, and the share of consequential ambiguities that were missed.
澄清质量应作为独立评估维度。系统即使在询问十个不必要问题后生成正确查询,也可能无法使用。应测量澄清触发率、用户完成率、澄清后的纠正率,以及遗漏重大歧义的比例。
Represent conversational context as explicit state把对话上下文表示为显式状态
Follow-up questions such as “only Europe,” “compare that with last year,” or “now show margin” depend on prior state. Do not rely on an unstructured transcript alone. Maintain a structured state containing population, metric, dimensions, filters, time range, grain, ordering, presentation, assumptions, source version, and authorization scope.
“只看欧洲”“与去年同期比较”“现在显示利润率”等追问依赖先前状态。不能只依赖非结构化对话记录;应维护包含总体、指标、维度、筛选、时间范围、粒度、排序、展示、假设、源版本与授权范围的结构化状态。
“Show margin” may replace revenue, while “also show margin” adds a measure. Preserve the distinction.
“显示利润率”可能替换收入,“同时显示利润率”则新增指标,必须区分。
Keep prior filters only when the user can see them and the follow-up does not reset scope.
只有用户可见且追问未重置范围时,才继承先前筛选。
Re-evaluate policy on every turn; a session change or requested domain can invalidate prior context.
每轮重新评估策略;会话变化或新领域请求可能使先前上下文失效。
Expire or reconfirm long-lived assumptions when data, metric versions, or reporting periods change.
当数据、指标版本或报告期间变化时,应让长期假设过期或重新确认。
Show a compact “current interpretation” panel before execution so users can inspect inherited state. Include a one-click reset. Conversation should reduce repeated work without creating invisible filters that make an answer impossible to explain.
执行前应展示简洁的“当前解释”面板,让用户检查继承状态,并提供一键重置。对话的目的应是减少重复工作,而不是制造无法解释的隐藏筛选。
Enforce security before, during, and after generation在生成前、生成中与生成后执行安全控制
Natural language query adds an expressive interface to data access; it must not create a parallel authorization system. A model may help interpret intent, but deterministic policy should control which metadata can be retrieved, which operations can be generated, which credentials execute them, and which results can be disclosed or exported.
自然语言查询为数据访问增加了更灵活的入口,但不能创建一套平行授权体系。模型可以帮助解释意图,但必须由确定性策略控制:允许检索哪些元数据、允许生成哪些操作、使用什么凭据执行,以及哪些结果可以披露或导出。
| Control point控制点 | Required controls必要控制 | Failure prevented防止问题 |
|---|---|---|
| Context retrieval上下文检索 | Identity-aware metadata filters, domain allow-list, sensitive-name redaction身份感知元数据筛选、领域允许列表与敏感名称脱敏 | Schema and policy disclosure模式与策略泄露 |
| Interpretation意图解释 | Risk classification, sensitive-intent detection, mandatory clarification rules风险分类、敏感意图检测与强制澄清规则 | Hidden expansion of scope范围被静默扩大 |
| Query compilation查询编译 | Statement and function allow-lists, object resolution, parameter binding, parser checks语句与函数允许列表、对象解析、参数绑定与解析检查 | Injection and unauthorized operations注入与未授权操作 |
| Pre-execution执行前 | Read-only transaction, plan inspection, estimated row and cost threshold, timeout只读事务、计划检查、估算行数与成本阈值及超时 | Resource exhaustion and writes资源耗尽与数据写入 |
| Database execution数据库执行 | Least-privilege role, row and column policy, workload isolation, audited connection最小权限角色、行列策略、工作负载隔离与审计连接 | Privilege bypass and noisy-neighbor impact权限绕过与共享资源影响 |
| Result handling结果处理 | Row limits, aggregation thresholds, masking, download policy, safe rendering行数限制、聚合阈值、脱敏、下载策略与安全渲染 | Sensitive output and downstream injection敏感输出与下游注入 |
| Logging and feedback日志与反馈 | Data minimization, retention controls, access logs, reviewed promotion workflow数据最小化、保留策略、访问日志与评审发布流程 | Sensitive prompts in logs and poisoned examples日志敏感信息与示例污染 |
Generated SQL should use bind parameters for values. Dynamic identifiers require an allow-list mapping to known objects; raw user text must not become a table name, sort expression, or SQL fragment. A read-only role reduces impact but does not prevent expensive scans or sensitive exports, so resource and output controls remain necessary.
生成 SQL 应对输入值使用绑定参数。动态标识符必须通过允许列表映射到已知对象,原始用户文本不能直接变成表名、排序表达式或 SQL 片段。只读角色能够降低影响,却无法阻止昂贵扫描或敏感导出,因此仍需资源与输出控制。
Use the OWASP GenAI Security Project as a threat-model input, then translate general model risks into database-specific tests: unauthorized schema discovery, instruction injection through stored text, excessive agency, sensitive result disclosure, unsafe output rendering, and unreviewed feedback becoming trusted context.
可以把 OWASP GenAI Security Project作为威胁建模输入,再把通用模型风险转化为数据库专属测试:未授权模式发现、存储文本中的指令注入、过度代理权限、敏感结果披露、不安全输出渲染,以及未经评审的反馈被提升为可信上下文。
Evaluate the answer path, not only the SQL string评估完整答案路径,而不只是 SQL 字符串
Exact string match is a useful diagnostic but a poor standalone accuracy measure. SQL can differ in aliases, join order, subquery structure, equivalent predicates, or aggregation layout while returning the same correct result. Conversely, a query can match a stored reference closely and still be wrong after schema, data, metric, or time-policy changes.
SQL 字符串完全匹配可用于诊断,却不适合作为唯一准确率指标。别名、连接顺序、子查询结构、等价条件和聚合布局可能不同,但仍返回同一正确结果;反过来,一条查询即使与历史参考高度相似,也可能因模式、数据、指标或时间策略变化而出错。
| Metric指标 | What it measures测量内容 | Limitation局限 |
|---|---|---|
| Parse success解析成功率 | SQL is syntactically valid for the target dialectSQL 对目标方言语法有效 | Says nothing about meaning or permissions无法证明语义或权限正确 |
| Object grounding对象对齐率 | Referenced objects and functions are approved and exist引用对象和函数存在且获批 | Correct objects can still be joined incorrectly正确对象仍可能被错误连接 |
| Execution success执行成功率 | The query runs inside the test boundary查询在测试边界内成功运行 | A wrong query often runs successfully错误查询通常也能成功运行 |
| Result equivalence结果等价率 | Returned rows or aggregates equal an expected result返回记录或汇总等于预期结果 | A small fixture may not expose hidden defects小型测试数据可能无法暴露隐藏缺陷 |
| Semantic acceptance语义验收率 | Population, grain, metric, time, and decision match the reviewed intent总体、粒度、指标、时间与决策符合评审意图 | Requires explicit business rules and expert review需要明确业务规则与专家评审 |
| Clarification quality澄清质量 | The system asks when needed and avoids needless interruption系统在必要时提问,并避免无谓打断 | Must be evaluated with ambiguous questions必须使用歧义问题评估 |
| Safe abstention安全拒绝率 | Unsupported or unauthorized requests are declined correctly正确拒绝不支持或未授权请求 | Over-abstention can make the system unusable过度拒绝会降低可用性 |
| Operational quality运行质量 | Latency, cost, scanned data, timeouts, and concurrency impact延迟、成本、扫描量、超时与并发影响 | Fast answers are not necessarily correct快速答案不一定正确 |
A release decision should combine these metrics by risk tier. For low-risk exploration, users may inspect a non-executed SQL draft. For governed self-service, require result-equivalence and policy tests. For an automated operational action, require much stronger evidence, change control, monitoring, and a safe fallback.
发布决策应按风险等级组合这些指标。低风险探索可以让用户检查未执行 SQL 草稿;受治理自助分析应要求结果等价与策略测试;自动化业务操作则需要更强证据、变更控制、持续监控与安全回退。
Build a versioned natural language query evaluation set构建版本化自然语言查询评估集
An evaluation item should contain more than a question and one SQL string. Store the user role, authorized domain, question, conversation state, required clarifications, accepted interpretation, semantic definitions, expected result properties, one or more valid queries, fixture or snapshot version, security outcome, and maximum operational budget.
评估样例不能只有问题和一条 SQL 字符串。还应保存用户角色、授权领域、问题、对话状态、必要澄清、已接受解释、语义定义、预期结果属性、一个或多个有效查询、测试数据或快照版本、安全结果与最大运行预算。
High-frequency metrics, dimensions, filters, comparisons, and standard time ranges.
高频指标、维度、筛选、比较与标准时间范围。
Multi-table joins, effective-dated dimensions, bridges, deduplication, subqueries, and windows.
多表连接、生效日期维度、桥接表、去重、子查询与窗口。
Competing metrics, vague periods, missing populations, unclear ranking, and underspecified follow-ups.
多个候选指标、模糊期间、缺失总体、不明确排名与不完整追问。
Nulls, zero denominators, ties, empty results, late data, currency changes, new and churned entities.
空值、零分母、并列、空结果、延迟数据、币种变化、新增与流失实体。
Unauthorized schema, sensitive attributes, prompt injection in stored text, writes, and oversized exports.
未授权模式、敏感属性、存储文本指令注入、写操作与超量导出。
Paraphrases, abbreviations, typos, multilingual terms, domain jargon, and adversarial wording.
同义改写、缩写、拼写错误、多语言术语、领域黑话与对抗性措辞。
Split verified examples used for runtime guidance from held-out evaluation items. Otherwise, the system may reproduce memorized query patterns without demonstrating generalization. The Snowflake evaluation workflow explicitly removes selected verified queries from the semantic view during an evaluation so they cannot guide the generation being measured.
应把运行时用于引导的已验证实例,与留出的评估样例分开。否则系统可能只是复现记忆中的查询模式,而没有证明泛化能力。Snowflake 的评估流程会在评估期间把选中的已验证查询暂时移出语义视图,避免它们参与正在测量的生成过程。
Public text-to-SQL benchmarks are useful for research comparability but do not replace a private evaluation set reflecting your schema, policies, business definitions, user language, and operational constraints. Spider 2.0, for example, focuses on more realistic enterprise text-to-SQL workflows; production acceptance still requires organization-specific evidence.
公开 Text-to-SQL 基准适合研究比较,但不能替代反映组织自身模式、策略、业务定义、用户语言与运行约束的私有评估集。比如 Spider 2.0关注更贴近真实企业的 Text-to-SQL 工作流;生产验收仍然需要组织专属证据。
Test whether equivalent questions stay equivalent测试等价问题是否保持等价答案
A trustworthy NLQ system should preserve meaning across harmless wording changes and change behavior when the user changes a material condition. Build metamorphic tests: transformations where the expected relationship between outputs is known even if a single exact SQL string is not.
可信 NLQ 系统应在无害措辞变化下保持语义不变,并在用户改变重大条件时相应改变行为。可以构建蜕变测试:即使没有唯一标准 SQL,也能够预先知道输入变换后输出之间应满足的关系。
| Transformation变换 | Expected property预期属性 | Failure revealed发现问题 |
|---|---|---|
| Paraphrase with same entities and period实体和期间不变的同义改写 | Same interpretation and equivalent result解释相同且结果等价 | Vocabulary brittleness词汇脆弱性 |
| Change one region filter只改变地区筛选 | Only the region predicate and dependent result change仅地区条件及相关结果变化 | Entangled intent or stale context意图耦合或上下文过期 |
| Add “including zero activity”增加“包含零活动对象” | Population expands without changing the metric definition总体扩大但指标定义不变 | Incorrect join direction or null policy连接方向或空值策略错误 |
| Swap calendar and fiscal quarter切换自然季度与财务季度 | Typed boundaries change according to governed calendars带类型边界按受治理日历改变 | Hard-coded dates or ignored terminology硬编码日期或术语被忽略 |
| Ask an unauthorized equivalent提出等价但未授权的问题 | Policy outcome remains denial without schema leakage保持拒绝且不泄露模式 | Prompt-based authorization bypass通过提示绕过授权 |
| Rename or deprecate a source field重命名或弃用源字段 | Evaluation fails safely until context is updated在更新上下文前安全失败 | Stale schema grounding模式对齐过期 |
Run these tests across model, prompt, semantic-model, schema, driver, and database-version changes. A passing average can hide severe regressions in one high-value metric or user group, so report results by domain, complexity, language, ambiguity, and risk tier.
模型、提示词、语义模型、数据库模式、驱动或数据库版本变化后,都应重新运行这些测试。平均通过率可能掩盖某个高价值指标或用户群体的严重回归,因此需要按领域、复杂度、语言、歧义与风险等级分层报告。
Log enough to reproduce an answer without over-collecting记录足够复现答案的信息,同时避免过度采集
A production trace should connect the question to its interpretation, context versions, policy decisions, generated operation, execution evidence, and displayed answer. It should not indiscriminately store sensitive prompts, raw rows, credentials, or full schemas.
生产追踪应把问题与解释、上下文版本、策略决策、生成操作、执行证据和展示答案关联起来,但不能无差别保存敏感提示、原始记录、凭据或完整数据库模式。
- Request and conversation identifiers, user role or pseudonymous authorization class, and declared purpose.
- Normalized intent, selected metric and dimensions, filters, time range, grain, assumptions, and clarifications.
- Semantic-model, schema, policy, prompt, model, and application versions.
- Retrieved object identifiers and confidence—not unauthorized object contents.
- Query fingerprint, parsed objects, statement class, static-policy outcome, and bound-parameter types.
- Plan or cost summary, execution role, elapsed time, rows scanned and returned, timeout, and error class.
- Validation checks, warnings, user correction, reviewer disposition, and whether the result was exported or acted upon.
- 请求与对话标识、用户角色或匿名化授权类别,以及声明目的。
- 规范化意图、选中指标与维度、筛选、时间范围、粒度、假设与澄清。
- 语义模型、数据库模式、策略、提示、模型与应用版本。
- 检索对象标识与置信度,而不是未授权对象内容。
- 查询指纹、解析对象、语句类型、静态策略结果与绑定参数类型。
- 计划或成本摘要、执行角色、耗时、扫描与返回行数、超时及错误类别。
- 验证检查、警告、用户纠正、评审结论,以及结果是否被导出或用于行动。
Define retention, redaction, and access rules before collecting telemetry. Use sampled or tokenized values when the exact literal is unnecessary. The audit trail should explain a decision without becoming a second uncontrolled data warehouse.
采集遥测之前,应先定义保留、脱敏与访问规则。不需要精确字面值时,应使用抽样或令牌化数据。审计轨迹需要能够解释决策,却不能变成第二个不受控制的数据仓库。
Match automation to the consequence of a wrong answer根据错误答案后果决定自动化程度
Do not apply one release threshold to every natural language query. A draft used by an analyst for exploration has a different consequence from a query that allocates inventory, changes prices, files a regulatory report, or triggers customer communication.
不能对所有自然语言查询采用同一发布门槛。分析师用于探索的草稿,与用于库存分配、价格调整、监管报送或客户沟通的查询,其错误后果完全不同。
| Tier等级 | Permitted experience允许体验 | Minimum controls最低控制 | Example示例 |
|---|---|---|---|
| 1 · Learn1 · 学习 | Generate SQL against synthetic schemas without execution针对合成模式生成 SQL,不执行 | No secrets, clear demo disclosure, visible assumptions不含密钥、明确演示声明、假设可见 | Public NL2SQL tester公开 NL2SQL 测试器 |
| 2 · Explore2 · 探索 | Read-only preview in a sandbox or curated dataset在沙箱或精选数据集中只读预览 | Least privilege, row and time limits, SQL and definition visibility最小权限、行数与时间限制、SQL 和定义可见 | Analyst discovery分析师探索 |
| 3 · Governed self-service3 · 受治理自助 | Execute approved question classes over production read replicas在生产只读副本执行获批问题类别 | Semantic layer, evaluation gates, policy enforcement, audit, feedback review语义层、评估门槛、策略执行、审计与反馈评审 | Recurring BI questions重复 BI 问题 |
| 4 · Decision support4 · 决策支持 | Provide reviewed evidence for high-impact human decisions为高影响人工决策提供经评审证据 | Named owner, independent controls, mandatory review, reproducible snapshot明确负责人、独立控制、强制评审与可复现快照 | Finance or capacity planning财务或容量规划 |
| 5 · Automated action5 · 自动行动 | Trigger a bounded operational workflow only after separate authorization仅在独立授权后触发受限业务流程 | Deterministic guardrails, approval or dual control, rollback, monitoring, kill switch确定性护栏、审批或双人控制、回滚、监控与停止开关 | Strictly limited exception handling严格限定的异常处理 |
Moving up a tier requires stronger evidence, not merely a higher average accuracy score. Review the worst credible failure, the number of people and records affected, detectability, reversibility, and the time available to intervene.
提高部署等级需要更强证据,而不只是更高平均准确率。应评估最严重可信故障、受影响人员与记录数量、可检测性、可逆性以及人工干预时间。
Write natural language queries as compact result contracts把自然语言查询写成简洁结果契约
Users should not need SQL vocabulary, but a few concrete details dramatically improve interpretation. A useful question identifies the population, measure, grouping, period, comparison, exception rules, desired output, and any known dialect or dataset constraint.
用户不需要掌握 SQL 术语,但几个具体信息就能显著提高解释质量。有效问题应尽量说明总体、指标、分组、期间、比较、异常规则、期望输出,以及已知方言或数据集限制。
“Show weekly recognized net revenue for active enterprise accounts in EMEA for the last 13 complete weeks, in reporting currency, including zero-revenue weeks.”
“显示 EMEA 有效企业账户最近 13 个完整周的每周已确认净收入,使用报告币种,并包含零收入周。”
“Compare 30-day activation rates for customers first onboarded in Q1 versus Q2; define activation as one successful production workflow.”
“比较 Q1 与 Q2 首次加入客户的 30 天激活率;激活定义为至少完成一次成功生产工作流。”
“For each region, list the five products with highest net margin last complete month; include all products tied at fifth place.”
“按地区列出上一个完整月份净利润最高的五个产品;第五名并列的产品全部保留。”
“Find posted invoices with no active account mapping at posting time; return counts by legal entity and a 20-row sanitized sample.”
“查找入账时没有有效账户映射的已入账发票;按法律实体返回数量,并提供 20 行脱敏样本。”
“Keep the same population and time range, replace revenue with gross margin, and group by account owner instead of region.”
“保持总体与时间范围不变,把收入替换为毛利,并把地区分组改为账户负责人。”
“Also return total eligible accounts, unmatched segment mappings, data freshness, and the SQL used so I can review the result.”
“同时返回合格账户总数、未匹配细分映射、数据新鲜度和所用 SQL,便于我评审结果。”
If the user gives a short question, the system can help construct this contract through targeted clarification and disclosed defaults. Do not force users to know physical table names or memorize metric IDs; that is the semantic layer's job.
如果用户的问题很短,系统可以通过针对性澄清与披露默认值帮助形成结果契约。不能要求用户了解物理表名或记忆指标 ID,这正是语义层应承担的工作。
Diagnose natural language query failures by stage按阶段诊断自然语言查询失败
| Observed symptom观察症状 | Likely stage可能阶段 | Evidence to inspect检查证据 | Corrective direction改进方向 |
|---|---|---|---|
| Wrong metric but plausible answer指标错误但答案合理 | Intent or semantic grounding意图或语义对齐 | Selected definition, synonyms, ambiguity candidates, applied defaults选中定义、同义词、歧义候选与应用默认值 | Clarify or improve governed metric descriptions增加澄清或改进受治理指标描述 |
| Invented table or column虚构表或字段 | Schema retrieval or generation模式检索或生成 | Retrieved object set, version, parser, catalog resolution检索对象集、版本、解析器与目录解析 | Constrain generation to resolved object IDs限制生成只能使用已解析对象 ID |
| Totals inflated总额膨胀 | Relationship planning关系规划 | Join path, key uniqueness, row growth, output grain连接路径、键唯一性、行数膨胀与输出粒度 | Add cardinality metadata and join invariants增加基数元数据与连接不变条件 |
| Correct SQL, misleading explanationSQL 正确但解释误导 | Result narration结果叙述 | Returned rows, narrative claims, unit and period labels返回记录、叙述声明、单位与期间标签 | Generate narrative only from validated result metadata只根据已验证结果元数据生成叙述 |
| First turn correct, follow-up wrong首轮正确、追问错误 | Conversation state对话状态 | Before-and-after structured state and reset behavior前后结构化状态与重置行为 | Separate replace, refine, and reset operations区分替换、细化与重置操作 |
| Slow or unsafe query查询缓慢或不安全 | Compilation and execution policy编译与执行策略 | Plan estimate, statement class, functions, limits, execution role计划估算、语句类型、函数、限制与执行角色 | Tighten policy gate and workload isolation收紧策略门槛与工作负载隔离 |
| Regression after model update模型更新后回归 | Release process发布流程 | Held-out evaluation by domain and changed components按领域划分的留出评估与变更组件 | Canary, rollback, and targeted context changes灰度、回滚与针对性上下文调整 |
Fix the narrowest causal layer. Adding prompt instructions is not a universal remedy: a duplicated dimension belongs in data quality or the relationship model; missing row security belongs in policy; a stale metric belongs in semantic governance; an expensive plan may need a curated aggregate or workload rule.
应修复最窄的因果层。增加提示词并非万能方案:维度重复属于数据质量或关系模型问题;行级安全缺失属于策略问题;指标过期属于语义治理;昂贵计划可能需要精选聚合表或工作负载规则。
Roll out natural language query in measured stages分阶段、可测量地上线自然语言查询
- Choose one bounded domain.选择一个边界清晰的领域。 Start with named owners, stable metrics, documented schema, representative users, and reversible decisions. 优先选择负责人明确、指标稳定、模式有文档、用户有代表性且决策可逆的领域。
- Create the context and evaluation set together.同时建设上下文与评估集。 Every semantic definition should have positive, boundary, ambiguity, and authorization tests. 每个语义定义都应配套正例、边界、歧义与授权测试。
- Begin with non-executed drafts.从不执行草稿开始。 Let analysts inspect interpretation and SQL, record corrections, and identify missing model elements. 让分析师检查解释与 SQL,记录纠正,并识别缺失模型元素。
- Add sandboxed read-only execution.增加沙箱只读执行。 Apply policies, plan checks, budgets, validated result shapes, and independent reconciliation. 应用策略、计划检查、预算、已验证结果形状与独立核对。
- Run a shadow pilot.运行影子试点。 Compare system answers with the existing analyst workflow without letting results drive decisions. 将系统答案与现有分析流程比较,但暂不允许结果直接驱动决策。
- Promote by question class.按问题类别逐步发布。 Approve specific metrics, dimensions, and patterns that pass thresholds; keep unsupported classes behind review. 只发布通过阈值的特定指标、维度与模式;未支持类别继续保留评审。
- Monitor and roll back.持续监控并支持回滚。 Watch correction, abstention, policy, latency, cost, and domain regressions with versioned rollback paths. 监控纠正、拒绝、策略、延迟、成本和领域回归,并保留版本化回滚路径。
Go-live evidence: publish the approved question classes, unsupported requests, metric and schema owners, evaluation date, pass thresholds, known limitations, escalation path, and rollback owner. “The model looked good in a demo” is not a release criterion.
上线证据:应发布获批问题类别、不支持请求、指标与模式负责人、评估日期、通过阈值、已知限制、升级路径与回滚负责人。“演示时看起来不错”不是发布标准。
Inspect how an everyday question becomes SQL输入中文业务问题,检查 SQL 草稿
Use the InfiniSynapse NL2SQL Query Tester with its built-in synthetic examples. Review the visible planning steps and generated SQL structure without connecting a database. Treat the output as an educational draft, then validate every object, definition, join, filter, dialect rule, and expected result in your own governed environment.
先使用 InfiniSynapse NL2SQL Query Tester 的合成 Schema 输入一个中文业务问题,观察问题契约、规划步骤与生成 SQL 结构,无需连接真实数据库。输出仅是候选草稿;仍需在受治理环境中核对表字段、指标定义、连接、筛选、SQL 方言和预期结果。
Open NL2SQL Query Tester打开 NL2SQL Query Tester The free tester uses synthetic schemas and runs in the browser. Do not submit credentials, personal data, secrets, or sensitive schema details.免费测试器使用合成模式并在浏览器中运行。不要提交凭据、个人数据、密钥或敏感模式信息。Natural language to SQL frequently asked questions自然语言转 SQL(NL2SQL)常见问题
NL2SQL converts a data question written in everyday language into a reviewable SQL draft grounded in database schema, business definitions, and authorization boundaries.
NL2SQL 是把用户用中文或其他自然语言表达的数据问题,结合数据库模式、业务定义和权限约束,转换为可审查 SQL 的技术流程。
Not exactly. NL2SQL is one implementation of natural language query for relational databases; broader natural language query can also call semantic models, search, APIs, or other data sources.
不完全相同。NL2SQL 是自然语言查询在关系型数据库中的一种实现;自然语言查询还可能调用语义模型、搜索、API 或其他数据源。
Business terms, time boundaries, metric definitions, table relationships, or result grain may be misunderstood. Successful parsing proves syntax, not business correctness.
因为业务术语、时间边界、统计口径、表关系和结果粒度可能被误解。语法通过只能证明 SQL 可解析,不能证明业务语义正确。
Use a versioned question set with expected results and measure execution success, result equivalence, semantic acceptance, clarification quality, safe abstention, latency, cost, and regressions.
使用版本化问题集和预期结果,评估执行成功率、结果等价性、语义验收率、澄清质量、安全拒绝、延迟、成本与回归。
At minimum, provide the target SQL dialect, approved tables and columns, keys and relationships, metric definitions, synonyms, time rules, result grain, representative values, and authorization policy.
至少需要目标 SQL 方言、允许访问的表和字段、键与关系、指标定义、同义词、时间规则、结果粒度、代表值、权限策略和已验证案例。
Do not run it directly by default. Start with synthetic data and read-only environments, then apply allow-lists, row and time limits, least privilege, plan checks, result validation, and human approval.
不应默认直接运行。应先使用合成数据和只读环境,并配置允许列表、行数与时间限制、最小权限、执行计划检查、结果验证和人工审批。
Natural language to SQL sources and evaluation references自然语言转 SQL 与评估参考资料
- 阿里云 PolarDB:自然语言到 SQL 分析
- Snowflake: Cortex Analyst REST API
- Snowflake: Cortex Analyst Evaluations
- Microsoft Learn: Power BI Q&A Limitations
- Spider: Cross-Domain Text-to-SQL Benchmark
- Spider 2.0: Real-World Enterprise Text-to-SQL Workflows
- OWASP GenAI Security Project
Product documentation describes specific implementations, and public benchmarks support comparative research. Neither proves suitability for a particular organization. Validate natural language query behavior against the actual schema, data, business definitions, language, policy, workload, and decision consequence.
产品文档描述的是特定实现,公开基准适合比较研究,两者都不能证明系统适合某个具体组织。必须根据真实数据库模式、数据、业务定义、语言、策略、工作负载与决策后果验证自然语言查询行为。