SQL Query Examples: From Questions to ResultsSQL 查询实例：12 个中文业务问题与完整语句

What makes an SQL query example useful?什么样的 SQL 查询实例才真正有用？

This guide uses twelve read-only PostgreSQL-style examples over one synthetic commerce model. The patterns are broadly recognizable across PostgreSQL, MySQL, BigQuery, Snowflake, SQL Server, and other systems, but functions, quoting, parameters, date arithmetic, and window-filter syntax differ. Treat every example as a reasoning pattern to adapt—not production-ready code for an unknown database.

本文围绕一套合成电商模型，给出十二个只读、偏 PostgreSQL 风格的示例。PostgreSQL、MySQL、BigQuery、Snowflake、SQL Server 等系统大多能识别这些模式，但函数、引号、参数、日期运算和窗口过滤语法并不完全一致。请把每个示例视为需要适配的推理模式，而不是可以直接用于未知数据库的生产代码。

Use one explicit schema for all twelve SQL query examples十二个 SQL 查询示例共用一套明确 Schema

Examples become easier to compare when tables, keys, units, and time fields stay stable. The synthetic model below represents customers placing orders that contain products. Payments are separated from orders because one order may have multiple attempts, refunds, or settlements. Monetary examples use the order header's total_amount unless the question explicitly requires item-level economics.

当表、键、单位和时间字段保持稳定时，不同示例才容易比较。下面的合成模型表示客户下单、订单包含商品；付款与订单分开，因为一个订单可能对应多次尝试、退款或结算。除非问题明确要求商品行级经济数据，金额示例统一使用订单头字段 total_amount。

customers 1 ─── * orders 1 ─── * order_items * ─── 1 products
                         │
                         └──────── * payments

Canonical reporting amount: orders.total_amount
Canonical completed-order rule: orders.status = 'completed'
Half-open date interval: date >= start AND date < next_period_start

The model deliberately exposes a common trap: joining orders to both order_items and payments can multiply rows. If an order has three item lines and two payment events, the combined join can produce six rows before aggregation. Decide the measure's source and pre-aggregate each one-to-many branch before combining it.

该模型刻意保留了一个常见陷阱：同时把 orders 连接到 order_items 和 payments 可能造成行乘法。如果一张订单有三个商品行和两个付款事件，聚合前的组合连接可能产生六行。必须先决定指标来自哪张表，并在合并前分别预聚合每个一对多分支。

Read every SQL example as a six-part analytical contract把每个 SQL 示例视为六部分分析契约

A natural-language question hides choices that SQL must make explicit. Before looking at the code, identify the measure, population, time boundary, grouping dimensions, row grain, and tie or null behavior. After looking at the code, verify that those choices survived translation.

自然语言问题隐藏着许多必须在 SQL 中显式化的选择。阅读代码前，先识别指标、总体、时间边界、分组维度、行粒度，以及并列值或 NULL 的处理方式；阅读代码后，再验证这些选择是否在翻译过程中被保留下来。

The examples use named placeholders such as :start_date. These are conceptual parameters, not a universal wire format. Bind them through the parameter mechanism of your driver, warehouse, BI tool, or orchestration layer. Never substitute untrusted values by concatenating strings.

示例使用 :start_date 这类命名占位符。它们表示概念参数，并不是所有系统通用的传输格式。请通过数据库驱动、数据仓库、BI 工具或编排层提供的参数机制绑定值，绝不能通过字符串拼接插入不可信输入。

SQL query example 1: filter completed orders and sort deterministicallySQL 查询示例1：筛选已完成订单并进行确定性排序

Business question: “List completed orders placed in April 2026, newest first.” The result grain is one row per order. A precise prompt should name the status, start date, exclusive next-period boundary, selected columns, and a stable secondary sort key.

业务问题：“列出 2026 年 4 月完成的订单，最新订单排在前面。”结果粒度为每张订单一行。精确提示应说明状态、开始日期、下期排除边界、所需列，以及稳定的第二排序键。

SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.total_amount
FROM orders AS o
WHERE o.status = 'completed'
  AND o.order_date >= DATE '2026-04-01'
  AND o.order_date <  DATE '2026-05-01'
ORDER BY
  o.order_date DESC,
  o.order_id DESC;

Why this shape: a half-open interval includes every timestamp on April 30 without relying on an assumed last second of the day. The secondary order_id sort makes output deterministic when multiple orders share the same timestamp. Selecting named columns also protects downstream consumers from silent column-order changes.

为什么这样写：半开区间能包含 4 月 30 日的所有时间戳，而不需要猜测当天“最后一秒”。当多张订单时间相同时，第二排序键 order_id 让输出保持确定。显式选择列还能避免上游增加字段后，下游因列顺序变化而悄然出错。

SQL query example 2: calculate monthly orders, revenue, and average order valueSQL 查询示例2：计算月度订单数、收入与平均订单金额

Business question: “For each month in 2026, show completed-order count, completed revenue, and average order value.” The result grain is one row per calendar month. Revenue and average order value both come from the one-row-per-order table, so the query must not join item or payment detail.

业务问题：“按 2026 年每个月展示已完成订单数、已完成收入和平均订单金额。”结果粒度为每个自然月一行。收入与平均订单金额都来自每订单一行的 orders 表，因此查询不应连接商品行或付款明细。

SELECT
  DATE_TRUNC('month', o.order_date) AS order_month,
  COUNT(*)                           AS completed_orders,
  SUM(o.total_amount)               AS completed_revenue,
  AVG(o.total_amount)               AS average_order_value
FROM orders AS o
WHERE o.status = 'completed'
  AND o.order_date >= DATE '2026-01-01'
  AND o.order_date <  DATE '2027-01-01'
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY order_month;

Interpretation: this returns only months that contain at least one completed order. If the report must show zero-activity months, join the aggregation to an approved calendar table or generated date series. Do not fill gaps in the presentation layer without documenting that behavior, because a missing month and a true zero are different observations.

解释：该查询只返回至少有一张已完成订单的月份。如果报表必须显示零活动月份，应把聚合结果连接到批准的日历表或日期序列。不要在展示层悄悄补零，因为“缺失月份”和“真实为零”代表不同观测。

SQL query example 3: find top customers with a controlled joinSQL 查询示例3：通过受控连接找出高价值客户

Business question: “Show the ten customers with the highest completed-order revenue in Q1 2026, including region and order count.” The result grain is one row per customer. The customer table provides descriptive attributes; the order table provides the count and amount.

业务问题：“展示 2026 年第一季度已完成订单收入最高的十位客户，并包含地区和订单数。”结果粒度为每位客户一行。customers 表提供描述属性，orders 表提供订单数与金额。

SELECT
  c.customer_id,
  c.customer_name,
  c.region,
  COUNT(*)             AS completed_orders,
  SUM(o.total_amount)  AS completed_revenue
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
  AND o.order_date >= DATE '2026-01-01'
  AND o.order_date <  DATE '2026-04-01'
GROUP BY
  c.customer_id,
  c.customer_name,
  c.region
ORDER BY
  completed_revenue DESC,
  c.customer_id
FETCH FIRST 10 ROWS ONLY;

Why this shape: the join remains one customer to many orders, and aggregation returns to one customer per row. customer_id is included in grouping even if names appear unique; names can change or collide. The final customer ID ordering resolves revenue ties deterministically. In MySQL or PostgreSQL, LIMIT 10 is a common alternative to the standard-style fetch clause.

为什么这样写：连接保持“一位客户对应多张订单”，聚合后回到“每位客户一行”。即使客户名看似唯一，也要按 customer_id 分组，因为名称可能变更或重复。最终以客户 ID 处理收入并列，让结果确定。在 MySQL 或 PostgreSQL 中，常见替代写法是 LIMIT 10。

SQL query example 4: keep customers with zero orders using LEFT JOINSQL 查询示例4：使用 LEFT JOIN 保留零订单客户

Business question: “For every customer created before 2026, show the number and value of completed orders in Q1 2026, including customers with none.” The result grain is one row per eligible customer. The date and status predicates belong in the join condition so they limit matching orders without removing unmatched customers.

业务问题：“针对 2026 年前创建的每位客户，展示 2026 年第一季度已完成订单数量与金额，包括没有订单的客户。”结果粒度为每位符合条件的客户一行。日期与状态条件应放在连接条件中，这样只限制匹配订单，而不会删除未匹配客户。

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id)                    AS completed_orders,
  COALESCE(SUM(o.total_amount), 0)     AS completed_revenue
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed'
 AND o.order_date >= DATE '2026-01-01'
 AND o.order_date <  DATE '2026-04-01'
WHERE c.created_at < DATE '2026-01-01'
GROUP BY
  c.customer_id,
  c.customer_name
ORDER BY c.customer_id;

Critical details: use COUNT(o.order_id), not COUNT(*). An unmatched customer still produces one null-extended row, so COUNT(*) would incorrectly report one order. SUM over no matches returns null and is intentionally converted to zero. Moving the order predicates into WHERE would reject null-extended rows and turn the outer join into an effective inner join.

关键细节：应使用 COUNT(o.order_id)，不能使用 COUNT(*)。未匹配客户仍会产生一条右侧补 NULL 的行，因此 COUNT(*) 会错误报告一张订单。无匹配时 SUM 返回 NULL，这里有意转为零。如果把订单条件移到 WHERE，补 NULL 的行会被拒绝，外连接实际上就变成了内连接。

SQL query example 5: compare order statuses with conditional aggregationSQL 查询示例5：使用条件聚合比较订单状态

Business question: “By region, compare completed, cancelled, and pending order counts in Q1 2026, and calculate the completed share of all orders.” The result grain is one row per region. One scan can produce several governed counts, but the numerator and denominator must use compatible populations.

业务问题：“按地区比较 2026 年第一季度已完成、已取消和待处理订单数，并计算已完成订单占全部订单的比例。”结果粒度为每个地区一行。一次扫描可以生成多个治理后的计数，但分子与分母必须使用相容总体。

SELECT
  c.region,
  COUNT(*) FILTER (WHERE o.status = 'completed') AS completed_orders,
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_orders,
  COUNT(*) FILTER (WHERE o.status = 'pending')   AS pending_orders,
  COUNT(*)                                        AS all_orders,
  ROUND(
    COUNT(*) FILTER (WHERE o.status = 'completed')::numeric
    / NULLIF(COUNT(*), 0),
    4
  ) AS completed_share
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01'
  AND o.order_date <  DATE '2026-04-01'
GROUP BY c.region
ORDER BY c.region;

Dialect note: PostgreSQL supports aggregate FILTER. A portable alternative is SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END). BigQuery supports COUNTIF. MySQL users commonly use conditional SUM. The business definition must remain identical even when syntax changes.

方言说明：PostgreSQL 支持聚合 FILTER。更通用的替代方式是 SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)；BigQuery 支持 COUNTIF；MySQL 常用条件 SUM。语法可以变化，但业务定义必须保持一致。

The denominator here includes every Q1 order, including statuses not displayed as separate columns. If the business wants completed share among only completed, cancelled, and pending orders, restrict both numerator and denominator to that set. Label the metric accordingly; “completion rate” is not self-defining.

这里的分母包含第一季度所有订单，包括未单独展示的其他状态。如果业务要求“已完成、已取消、待处理三类中的完成占比”，就要同时把分子与分母限制在该集合内。指标名称也必须对应定义；“完成率”本身并不能说明总体。

SQL query example 6: pre-aggregate one-to-many tables before joiningSQL 查询示例6：连接前先聚合一对多明细表

Business question: “For each completed order in April 2026, show order value, item quantity, successful payment amount, and the difference between paid and ordered value.” The result grain is one row per order. Both item lines and payment events are one-to-many children, so joining raw rows would create a many-to-many multiplication within each order.

业务问题：“针对 2026 年 4 月每张已完成订单，展示订单金额、商品数量、成功付款金额，以及已付金额与订单金额之间的差额。”结果粒度为每张订单一行。商品行和付款事件都是一对多子表，直接连接原始行会在订单内部形成多对多乘法。

WITH item_totals AS (
  SELECT
    oi.order_id,
    SUM(oi.quantity) AS item_quantity
  FROM order_items AS oi
  GROUP BY oi.order_id
),
payment_totals AS (
  SELECT
    p.order_id,
    SUM(p.amount) AS successful_payment_amount
  FROM payments AS p
  WHERE p.payment_status = 'succeeded'
  GROUP BY p.order_id
)
SELECT
  o.order_id,
  o.total_amount AS order_value,
  COALESCE(i.item_quantity, 0) AS item_quantity,
  COALESCE(p.successful_payment_amount, 0) AS paid_amount,
  COALESCE(p.successful_payment_amount, 0) - o.total_amount
    AS paid_minus_order_value
FROM orders AS o
LEFT JOIN item_totals AS i
  ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p
  ON p.order_id = o.order_id
WHERE o.status = 'completed'
  AND o.order_date >= DATE '2026-04-01'
  AND o.order_date <  DATE '2026-05-01'
ORDER BY o.order_id;

Why this shape: each CTE restores one row per order before the branches are combined. The final joins therefore preserve order grain. The query does not assume that item-line value equals the order header or that successful payments always equal completed order value; instead, it exposes the reconciliation difference for investigation.

为什么这样写：每个 CTE 在分支合并前先恢复到“每张订单一行”，因此最终连接能够保留订单粒度。查询不会假设商品行金额必然等于订单头金额，也不会假设成功付款必然等于已完成订单金额，而是显式展示核对差额以供调查。

SQL query example 7: find customers without recent completed ordersSQL 查询示例7：找出近期没有已完成订单的客户

Business question: “Find customers created before 2026 who had no completed order in the first half of 2026.” The result grain is one row per customer. This is an anti-semi-join: return an outer row only when no qualifying inner row exists.

业务问题：“找出 2026 年前创建、但在 2026 年上半年没有已完成订单的客户。”结果粒度为每位客户一行。这是反半连接：只有不存在符合条件的内部行时，才返回外部行。

SELECT
  c.customer_id,
  c.customer_name,
  c.region
FROM customers AS c
WHERE c.created_at < DATE '2026-01-01'
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'completed'
      AND o.order_date >= DATE '2026-01-01'
      AND o.order_date <  DATE '2026-07-01'
  )
ORDER BY c.customer_id;

Why NOT EXISTS: it expresses absence directly and avoids the null semantics that can make NOT IN surprising when the subquery contains null. A left join followed by WHERE o.order_id IS NULL can also work, but the qualifying predicates must stay in the join condition. NOT EXISTS makes the intended anti-match boundary easier to audit.

为什么使用 NOT EXISTS：它直接表达“不存在”，并避免子查询包含 NULL 时 NOT IN 可能出现的意外三值逻辑。LEFT JOIN 后使用 WHERE o.order_id IS NULL 也可以实现，但符合条件的谓词必须留在连接条件中。NOT EXISTS 更容易审查反匹配边界。

Do not label these customers “churned” unless the organization has a governed churn definition. The query proves only that no qualifying completed order exists in the selected interval. A new, dormant, seasonal, blocked, refunded, or externally served customer may require a different business classification.

除非组织已经治理了流失定义，否则不要把这些客户直接标记为“流失”。该查询只能证明选定区间内不存在符合条件的已完成订单。新客户、休眠客户、季节性客户、受限客户、退款客户或通过其他渠道服务的客户可能需要不同业务分类。

SQL query example 8: rank the top three products within each categorySQL 查询示例8：在每个类别内排名前三商品

Business question: “For completed orders in Q2 2026, return the three products with the highest item revenue in each category.” The output grain is one row per category-product. First aggregate order-item revenue at that grain, then rank the aggregated rows inside each category.

业务问题：“针对 2026 年第二季度已完成订单，返回每个类别中商品行收入最高的三件商品。”输出粒度为每个“类别—商品”一行。先在该粒度聚合订单商品收入，再在每个类别内部对聚合行排名。

WITH product_revenue AS (
  SELECT
    p.category,
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS item_revenue
  FROM orders AS o
  JOIN order_items AS oi
    ON oi.order_id = o.order_id
  JOIN products AS p
    ON p.product_id = oi.product_id
  WHERE o.status = 'completed'
    AND o.order_date >= DATE '2026-04-01'
    AND o.order_date <  DATE '2026-07-01'
  GROUP BY
    p.category,
    p.product_id,
    p.product_name
),
ranked_products AS (
  SELECT
    pr.*,
    ROW_NUMBER() OVER (
      PARTITION BY pr.category
      ORDER BY pr.item_revenue DESC, pr.product_id
    ) AS category_position
  FROM product_revenue AS pr
)
SELECT
  category,
  product_id,
  product_name,
  item_revenue,
  category_position
FROM ranked_products
WHERE category_position <= 3
ORDER BY category, category_position;

Tie decision: ROW_NUMBER returns exactly three rows per category when at least three products exist; product ID breaks equal-revenue ties. Use RANK when tied products should share a rank and the output may exceed three rows. Use DENSE_RANK when ranks should not have gaps. The prompt must state which interpretation the decision requires.

并列决策：当类别至少有三件商品时，ROW_NUMBER 恰好返回三行；商品 ID 用于打破收入并列。如果并列商品应共享名次、且输出可以超过三行，则使用 RANK；如果排名不应跳号，则使用 DENSE_RANK。提示必须说明决策需要哪种解释。

PostgreSQL evaluates window functions after WHERE, grouping, and ordinary aggregation, so the ranking is calculated in a separate query level and filtered outside it. BigQuery can express the last step with QUALIFY; that is a dialect feature, not portable standard syntax.

PostgreSQL 在 WHERE、分组和普通聚合之后计算窗口函数，因此排名需要在单独查询层中生成，再由外层筛选。BigQuery 可以用 QUALIFY 表达最后一步，但这是方言特性，不是可移植标准语法。

SQL query example 9: calculate monthly revenue, running total, and changeSQL 查询示例9：计算月度收入、累计值与环比变化

Business question: “For completed orders in 2026, show monthly revenue, cumulative year-to-date revenue, the prior month's revenue, and month-over-month change.” The result grain is one row per observed month. Aggregate first; apply windows second.

业务问题：“针对 2026 年已完成订单，展示月度收入、年内累计收入、上月收入和环比变化。”结果粒度为每个有观测值的月份一行。先聚合，再应用窗口函数。

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', o.order_date) AS revenue_month,
    SUM(o.total_amount)               AS revenue
  FROM orders AS o
  WHERE o.status = 'completed'
    AND o.order_date >= DATE '2026-01-01'
    AND o.order_date <  DATE '2027-01-01'
  GROUP BY DATE_TRUNC('month', o.order_date)
),
with_prior AS (
  SELECT
    mr.revenue_month,
    mr.revenue,
    SUM(mr.revenue) OVER (
      ORDER BY mr.revenue_month
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS year_to_date_revenue,
    LAG(mr.revenue) OVER (
      ORDER BY mr.revenue_month
    ) AS prior_month_revenue
  FROM monthly_revenue AS mr
)
SELECT
  revenue_month,
  revenue,
  year_to_date_revenue,
  prior_month_revenue,
  revenue - prior_month_revenue AS absolute_change,
  ROUND(
    (revenue - prior_month_revenue)
    / NULLIF(prior_month_revenue, 0),
    4
  ) AS change_rate
FROM with_prior
ORDER BY revenue_month;

Missing-month warning: LAG means prior returned row, not necessarily prior calendar month. If February has no row, April's prior returned row may be January or March depending on the data. For calendar-continuous analysis, join monthly totals to a calendar series first and choose whether missing activity means zero, unknown, or not applicable.

缺失月份警告：LAG 表示前一条返回行，并不必然表示前一个自然月。如果二月没有行，四月的前一行可能是三月，也可能因数据缺口而更早。进行连续日历分析时，应先把月度总额连接到日历序列，并明确缺失活动表示零、未知还是不适用。

The explicit ROWS frame makes the running-total rule visible. With non-unique ordering values, the default frame can include peers in ways that surprise reviewers. A month is unique in this CTE, but the explicit frame remains useful documentation.

显式 ROWS 窗口框架让累计规则清晰可见。当排序值不唯一时，默认框架可能把并列行一起纳入，导致审查者误解。本 CTE 中月份是唯一的，但显式框架仍然是有价值的文档。

SQL query example 10: return each customer's latest completed orderSQL 查询示例10：返回每位客户最新的已完成订单

Business question: “For every customer who has a completed order, return the latest completed order and its value.” The result grain is one row per customer with qualifying history. The phrase “latest” requires a deterministic rule when timestamps tie.

业务问题：“针对每位有已完成订单的客户，返回其最新已完成订单及金额。”结果粒度为每位有符合条件历史的客户一行。“最新”在时间戳并列时必须有确定规则。

WITH ranked_orders AS (
  SELECT
    o.customer_id,
    o.order_id,
    o.order_date,
    o.total_amount,
    ROW_NUMBER() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.order_date DESC, o.order_id DESC
    ) AS recency_position
  FROM orders AS o
  WHERE o.status = 'completed'
)
SELECT
  c.customer_id,
  c.customer_name,
  r.order_id,
  r.order_date,
  r.total_amount
FROM ranked_orders AS r
JOIN customers AS c
  ON c.customer_id = r.customer_id
WHERE r.recency_position = 1
ORDER BY c.customer_id;

Why not MAX(order_date) alone: the maximum timestamp identifies a value, not necessarily the rest of the row. Joining the maximum back can return multiple orders when timestamps tie. ROW_NUMBER keeps the complete row and applies an explicit tie-breaker. PostgreSQL's DISTINCT ON can solve the same task compactly, but it is PostgreSQL-specific.

为什么不能只用 MAX(order_date)：最大时间戳只识别一个值，并不能唯一确定该行其他字段。把最大值连接回原表，在时间并列时可能返回多张订单。ROW_NUMBER 保留完整行，并应用明确并列规则。PostgreSQL 的 DISTINCT ON 也能简洁解决该任务，但属于 PostgreSQL 特有语法。

This query excludes customers without a completed order. If the requirement says “every customer,” start from customers and left join the ranked result, then describe null order fields as no qualifying history rather than missing data.

该查询排除了没有已完成订单的客户。如果需求写的是“每位客户”，就应从 customers 出发，LEFT JOIN 排名结果，并把 NULL 订单字段解释为“没有符合条件的历史”，而不是笼统称为缺失数据。

SQL query example 11: calculate category revenue and share of totalSQL 查询示例11：计算类别收入及总体占比

Business question: “For completed Q2 2026 orders, show item revenue by product category and each category's share of item revenue.” The result grain is one row per category. First calculate category totals, then apply a window aggregate across those totals.

业务问题：“针对 2026 年第二季度已完成订单，展示按商品类别计算的商品行收入，以及各类别占商品行总收入的比例。”结果粒度为每个类别一行。先计算类别总额，再对这些总额应用窗口聚合。

WITH category_revenue AS (
  SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price) AS item_revenue
  FROM orders AS o
  JOIN order_items AS oi
    ON oi.order_id = o.order_id
  JOIN products AS p
    ON p.product_id = oi.product_id
  WHERE o.status = 'completed'
    AND o.order_date >= DATE '2026-04-01'
    AND o.order_date <  DATE '2026-07-01'
  GROUP BY p.category
)
SELECT
  category,
  item_revenue,
  ROUND(
    item_revenue
    / NULLIF(SUM(item_revenue) OVER (), 0),
    4
  ) AS revenue_share
FROM category_revenue
ORDER BY item_revenue DESC, category;

Measure boundary: this computes item-line revenue from quantity times unit price. It is not automatically equal to recognized revenue, paid cash, or the order header. Discounts, returns, tax, shipping, currency conversion, allocation, and accounting recognition can create legitimate differences. Name the measure “item revenue” unless a governed transformation makes it something else.

指标边界：这里通过数量乘单价计算商品行收入。它不会自动等于确认收入、已收现金或订单头金额；折扣、退货、税费、运费、汇率转换、分摊和会计确认都可能造成合理差异。除非经过治理转换，否则应把该指标明确称为“商品行收入”。

A window aggregate avoids a self-join to the grand total. The empty OVER () means every category row participates in one window. The null-safe denominator prevents division by zero if the selected population has no item revenue.

窗口聚合避免了把类别结果再连接到总体总额。空的 OVER () 表示所有类别行共同参与一个窗口；分母使用 NULL 安全处理，以防选定总体没有商品行收入时除零。

SQL query example 12: measure 30-day repeat purchase by first-order monthSQL 查询示例12：按首单月份衡量30天复购

Business question: “Group customers by their first completed-order month in 2026 and report how many placed another completed order within 30 days after the first order.” The output grain is one row per first-order month. The analysis requires a first event, a forward-looking event window, a customer-level flag, and a cohort aggregation.

业务问题：“按客户在 2026 年的首次已完成订单月份分组，报告其中有多少客户在首单后30天内再次完成订单。”输出粒度为每个首单月份一行。分析需要确定首次事件、后续事件窗口、客户级标记，再进行队列聚合。

WITH completed_orders AS (
  SELECT
    o.customer_id,
    o.order_id,
    o.order_date
  FROM orders AS o
  WHERE o.status = 'completed'
),
first_orders AS (
  SELECT
    co.customer_id,
    MIN(co.order_date) AS first_order_date
  FROM completed_orders AS co
  GROUP BY co.customer_id
),
eligible_first_orders AS (
  SELECT
    f.customer_id,
    f.first_order_date
  FROM first_orders AS f
  WHERE f.first_order_date >= DATE '2026-01-01'
    AND f.first_order_date <  DATE '2027-01-01'
    AND f.first_order_date
        <= CAST(:as_of_date AS timestamp) - INTERVAL '30 days'
),
customer_repeat_flags AS (
  SELECT
    e.customer_id,
    e.first_order_date,
    CASE WHEN EXISTS (
      SELECT 1
      FROM completed_orders AS later
      WHERE later.customer_id = e.customer_id
        AND later.order_date > e.first_order_date
        AND later.order_date <= e.first_order_date + INTERVAL '30 days'
    ) THEN 1 ELSE 0 END AS repeated_within_30_days
  FROM eligible_first_orders AS e
)
SELECT
  DATE_TRUNC('month', first_order_date) AS first_order_month,
  COUNT(*)                              AS new_customers,
  SUM(repeated_within_30_days)          AS repeat_customers_30d,
  ROUND(
    AVG(repeated_within_30_days::numeric),
    4
  ) AS repeat_rate_30d
FROM customer_repeat_flags
GROUP BY DATE_TRUNC('month', first_order_date)
ORDER BY first_order_month;

Censoring rule: a customer whose first order occurred near the data extract's end may not have a complete 30-day observation window. The :as_of_date condition includes only first orders with a mature observation window. If the report must show incomplete cohorts, label and separate them; otherwise recent cohorts will be biased downward.

删失规则：如果客户首单接近数据提取结束日期，就可能没有完整30天观察窗口。查询中的 :as_of_date 条件只纳入观察窗口已经成熟的首单。如果报表必须展示未成熟队列，应明确标记并单独呈现，否则近期队列会出现向下偏差。

Definition choices: decide whether the second order must be strictly later by timestamp, whether same-day separate orders count, whether refunded orders invalidate either event, which timezone defines a day, and whether the first completed order means lifetime first or first within 2026. This example uses lifetime first completed order and then selects cohorts whose first event occurred in 2026.

定义选择：必须决定第二张订单是否必须在时间戳上严格晚于首单、同日独立订单是否计入、退款是否使任一事件失效、一天由哪个时区定义，以及“首次已完成订单”是生命周期首单还是 2026 年内首单。本示例采用生命周期首次已完成订单，再选择首单发生在 2026 年的队列。

Adapt the examples to the target SQL dialect before execution执行前先把示例适配到目标 SQL 方言

“SQL” is a language family implemented through product-specific dialects. A query can be logically sound and still fail because the target system uses different date functions, casts, identifier quoting, parameters, top-N syntax, or window filtering. Name the engine and version in the prompt whenever the output will be executed.

“SQL”是一组由不同产品方言实现的语言家族。查询逻辑可能正确，却因目标系统采用不同日期函数、类型转换、标识符引号、参数、Top-N 或窗口筛选语法而失败。只要输出将被执行，就应在提示中写明数据库引擎与版本。

Treat the official PostgreSQL SQL tutorial and MySQL 8.4 SELECT reference as the authority for their respective dialects. Confirm behavior against the deployed version, because compatibility modes and feature releases can change accepted syntax.

应分别以官方 PostgreSQL SQL 教程和 MySQL 8.4 SELECT 参考作为各自方言的权威来源。还应针对实际部署版本确认行为，因为兼容模式和功能发布可能改变可接受语法。

Turn a vague data request into a testable natural-language SQL prompt把模糊数据请求改写为可测试的自然语言 SQL 提示

“Show sales by customer” is not a complete specification. It leaves sales meaning, customer population, time, status, currency, refund treatment, grouping grain, zero-activity behavior, sorting, and dialect unresolved. A generator must either guess or ask for clarification. A stronger prompt makes the decision boundary visible.

“按客户展示销售额”并不是完整规格。它没有解决销售额定义、客户总体、时间、状态、币种、退款处理、分组粒度、零活动行为、排序和方言。生成器只能猜测或追问。更强的提示会把决策边界显式化。

Engine and version:
Available tables, keys, and relevant columns:

Return [metric] for [population]
during [half-open date interval],
grouped at [one-row grain].

Include:
- [dimensions and measures]
- [status, null, refund, and zero-activity rules]
- [tie behavior and deterministic ordering]

Use bound parameters for [values].
Return read-only SQL only.
Do not invent tables, columns, relationships, or business definitions.
State unresolved assumptions before the SQL.

Validate generated SQL in layers before trusting the answer在信任答案前分层验证生成的 SQL

SQL correctness has several layers. Parsing proves only that the target engine recognizes the syntax. Object resolution proves that tables and columns exist. Neither proves that joins, definitions, time boundaries, aggregation grain, or output meaning match the decision. Use a review sequence that can reject a plausible query early.

SQL 正确性包含多个层次。成功解析只证明目标引擎识别语法；对象解析只证明表和列存在；两者都不能证明连接、定义、时间边界、聚合粒度和输出含义符合决策。应使用能够尽早否定“看似合理查询”的审查顺序。

1. Freeze the question and expected grain.冻结问题与预期粒度。 Write a one-sentence contract such as “one row per customer-month, completed orders only, UTC calendar months.” 写出一句话契约，例如“每个客户月份一行，仅含已完成订单，采用 UTC 自然月”。
2. Parse in the target dialect.在目标方言中解析。 Use the actual engine or its official parser; do not infer portability from visual similarity. 使用真实引擎或官方解析器，不要根据视觉相似度推断可移植性。
3. Resolve every object.解析每个对象。 Confirm schema qualification, table names, columns, aliases, data types, keys, and relationship direction. 确认 Schema 限定、表名、列、别名、数据类型、键和关系方向。
4. Audit population and time.审查总体与时间。 Check status rules, exclusions, null behavior, timezone, inclusivity, and late-arriving or future records. 检查状态规则、排除条件、NULL 行为、时区、边界包含方式，以及迟到或未来记录。
5. Audit join cardinality.审查连接基数。 Compare row counts before and after each join, measure key uniqueness, and pre-aggregate one-to-many branches. 比较每次连接前后的行数，测量键唯一性，并预聚合一对多分支。
6. Reconcile measures independently.独立核对指标。 Compare counts and totals with a simpler trusted query, ledger, approved dashboard, or hand-checked sample. 把计数与总额同更简单的可信查询、台账、批准报表或手工样本核对。
7. Test adversarial edge cases.测试对抗性边界案例。 Include nulls, duplicates, ties, zero denominators, empty periods, month-end timestamps, refunds, and multiple child rows. 包含 NULL、重复、并列、零分母、空期间、月末时间戳、退款和多个子行。
8. Inspect the execution plan safely.安全检查执行计划。 Start with non-executing plan inspection where available; remember that EXPLAIN ANALYZE actually runs the statement. 在可用时先使用不执行的计划检查；记住 EXPLAIN ANALYZE 会真正执行语句。
9. Run with constrained access.使用受限权限运行。 Use read-only, least-privilege credentials, bounded resources, and approved data environments. 使用只读、最小权限凭据、受限资源和批准的数据环境。
10. Record evidence and ownership.记录证据与责任人。 Preserve the prompt, SQL, schema version, parameters, reviewer, test results, and known limitations. 保存提示、SQL、Schema 版本、参数、审查者、测试结果和已知限制。

PostgreSQL's official Using EXPLAIN documentation explains that a query plan contains scan, join, aggregate, and sort nodes, and warns that EXPLAIN ANALYZE actually executes the statement. For data-changing SQL, use an approved safe method rather than assuming the word “explain” prevents side effects.

PostgreSQL 官方 Using EXPLAIN 文档说明，查询计划由扫描、连接、聚合和排序等节点构成，并明确警告 EXPLAIN ANALYZE 会真正执行语句。对于会修改数据的 SQL，应采用批准的安全方法，不能因为命令中有“explain”就假设没有副作用。

Keep natural-language SQL generation separate from safe execution把自然语言 SQL 生成与安全执行分开

A generated statement is code. It must pass the same security and governance gates as hand-written SQL. Values such as customer IDs and date limits should be bound through prepared or parameterized interfaces. Table names, column names, sort directions, and other structural elements generally cannot be bound as values; when dynamic structure is necessary, select from a strict allow-list controlled by the application.

生成的语句仍然是代码，必须通过与手写 SQL 相同的安全和治理门槛。客户 ID、日期边界等值应通过预编译或参数化接口绑定。表名、列名、排序方向等结构元素通常不能作为值绑定；如果确实需要动态结构，应由应用从严格白名单中选择。

The OWASP SQL Injection Prevention Cheat Sheet recommends prepared statements with parameterized queries as a primary defense and discourages dynamic query construction through string concatenation. It also emphasizes least privilege as an additional control. Parameterization does not prove analytical correctness, but it prevents data values from rewriting intended SQL structure when implemented properly.

OWASP SQL 注入防护速查表把预编译语句与参数化查询列为主要防御，并反对通过字符串拼接构造动态查询；同时强调最小权限作为额外控制。参数化不能证明分析逻辑正确，但正确实现时可以防止数据值改写预期 SQL 结构。

Use the NL2SQL Query Tester to inspect a generated SQL pattern使用 NL2SQL Query Tester 检查生成的 SQL 模式

The InfiniSynapse NL2SQL Query Tester accepts a plain-English question and displays generated SQL over built-in synthetic examples. Its current page demonstrates table selection, join paths, filters, aggregates, ordering, and limits entirely in the browser. It is useful for inspecting how a question may be translated into SQL structure.

InfiniSynapse NL2SQL Query Tester 接受英文自然语言问题，并基于内置合成示例展示生成的 SQL。当前工具页在浏览器中演示表选择、连接路径、筛选、聚合、排序和限制，适合检查一个问题可能如何被翻译为 SQL 结构。

Use the example library with deeper SQL pattern guides结合更深入的 SQL 模式指南使用示例库

These examples show complete questions, but each SQL feature has additional semantics and failure modes. Review SQL joins for relationship and cardinality control, GROUP BY SQL for grain and aggregation, CTE SQL for staged logic, and SQL window functions for ranking and time-series patterns.

这些示例展示完整问题，但每种 SQL 特性还有更多语义与失败模式。可继续阅读 SQL 连接了解关系与基数控制，阅读 GROUP BY SQL理解粒度与聚合，阅读 CTE SQL理解分阶段逻辑，并通过 SQL 窗口函数深入排名与时间序列模式。

For review after generation, use the SQL checker guide to distinguish syntax, structural complexity, and execution-plan checks, then follow the SQL query optimization workflow only after correctness and grain are established. A faster wrong query is still wrong.

生成后审查可使用 SQL Checker 指南区分语法、结构复杂度和执行计划检查；只有确认正确性和粒度后，才进入 SQL 查询优化流程。更快的错误查询仍然是错误查询。

Follow a review-ready workflow from question to approved result执行从问题到批准结果的可审查工作流

1. Name the decision.说明决策。 Identify the owner, action, deadline, and consequence the analysis will support. 识别分析要支持的责任人、行动、截止时间与后果。
2. Define the metric contract.定义指标契约。 Specify numerator, denominator, statuses, units, currency, exclusions, and recognition rules. 说明分子、分母、状态、单位、币种、排除条件和确认规则。
3. Describe result grain.描述结果粒度。 State exactly what one output row represents and which keys should be unique. 明确一条输出行代表什么，以及哪些键应唯一。
4. Provide governed schema context.提供治理后的 Schema 上下文。 Supply approved tables, columns, data types, keys, relationships, descriptions, and dialect. 提供批准的表、列、数据类型、键、关系、说明和方言。
5. Write a precise prompt.编写精确提示。 Include population, dates, grouping, zero behavior, ties, output columns, and unresolved assumptions. 包含总体、日期、分组、零值行为、并列、输出列和未解决假设。
6. Generate read-only SQL.生成只读 SQL。 Keep generation separate from execution and reject invented objects. 把生成与执行分开，并拒绝虚构对象。
7. Review structure and semantics.审查结构与语义。 Inspect selected fields, joins, filters, aggregation, windows, nulls, and ordering against the contract. 对照契约检查字段、连接、筛选、聚合、窗口、NULL 与排序。
8. Validate with controls.使用控制验证。 Run counts, uniqueness, totals, samples, boundaries, and adversarial cases. 运行计数、唯一性、总额、样本、边界和对抗案例检查。
9. Inspect cost and plan.检查成本与计划。 Use the target engine's planning tools, production-scale statistics, and representative parameters. 使用目标引擎计划工具、生产规模统计和代表性参数。
10. Approve, observe, and version.批准、观测与版本化。 Record reviewer evidence, query and schema versions, runtime safeguards, ownership, and change triggers. 记录审查证据、查询与 Schema 版本、运行保护、责任人和变更触发条件。

Avoid twelve mistakes that make SQL examples misleading避免让 SQL 示例产生误导的十二个错误

Frequently asked questions about SQL query examplesSQL 查询实例常见问题

Primary references for SQL syntax, windows, plans, and safetySQL 语法、窗口、执行计划与安全的权威参考

• PostgreSQL: The SQL Language — official tutorial covering querying, joins, and aggregate functions.——官方教程，涵盖查询、连接和聚合函数。
• PostgreSQL: Window Functions — official definitions for ROW_NUMBER, RANK, DENSE_RANK, LAG, frames, and peer behavior.——ROW_NUMBER、RANK、DENSE_RANK、LAG、窗口框架和并列行为的官方定义。
• PostgreSQL: Using EXPLAIN — official plan-reading guidance and the execution warning for EXPLAIN ANALYZE.——官方执行计划阅读指南，以及 EXPLAIN ANALYZE 会执行语句的警告。
• MySQL 8.4: SELECT Statement — official MySQL syntax for SELECT, WITH, joins, grouping, windows, ordering, and limits.——MySQL 关于 SELECT、WITH、连接、分组、窗口、排序和限制的官方语法。
• OWASP: SQL Injection Prevention Cheat Sheet — parameterization, allow-list validation, and least-privilege controls.——参数化、白名单验证与最小权限控制。