请求 #1
SEQ 29{
"status": "running"
}案例记录 case-run-00112
相关子查询分群均值
找出金额高于同客户分群已完成订单平均金额的已完成订单，输出 order_id、customer_id、segment、total_amount、segment_avg_amount，并按 total_amount 降序、order_id 升序；必须使用外层引用的相关子查询。
运行信息
以下字段来自本次运行的冻结记录。
run
#16case run
#112attempt
1status
completedtoken total
2,767estimated cost
不可估算model generation
未记录SQL execution
251msadapter
openai_compatibleresponse mode
json_schemarequested model
gpt-session-bridgeresolved model
gpt-session-bridgeprovider request id
未提供started
2026年8月29日 22:06finished
2026年8月29日 22:06suite hash
5b5d98876ea35114f18ce6dfa48cc9800d88b6baba80d311b2f52552a38b31af模型输入
显示本次实际保存的完整 Prompt。
你是 Text-to-SQL 数据分析与数据开发生成器。先形成简洁、可审计的结构化查询方案，再生成完成问题所需的 SQL；不要输出隐藏推理。
方言与安全规则：
Use DuckDB SQL. Return exactly one read-only query. Do not access files, URLs, extensions, or schemas outside the supplied tables.
数据库结构：
{"semantic_relationships":[{"cardinality":"many_to_one","from_entity":"order","sql_on":"fact_orders.customer_id = dim_customers.customer_id","to_entity":"customer"},{"cardinality":"many_to_one","from_entity":"order","sql_on":"fact_orders.channel_id = dim_channels.channel_id","to_entity":"channel"},{"cardinality":"many_to_one","from_entity":"order_item","sql_on":"fact_order_items.order_id = fact_orders.order_id","to_entity":"order"},{"cardinality":"many_to_one","from_entity":"order_item","sql_on":"fact_order_items.product_id = dim_products.product_id","to_entity":"product"},{"cardinality":"many_to_one","from_entity":"payment","sql_on":"fact_payments.order_id = fact_orders.order_id","to_entity":"order"},{"cardinality":"many_to_one","from_entity":"return","sql_on":"fact_returns.order_id = fact_order_items.order_id AND fact_returns.line_no = fact_order_items.line_no","to_entity":"order_item"}],"tables":[{"columns":[{"data_type":"BIGINT","name":"channel_id","nullable":false},{"data_type":"VARCHAR","name":"channel_name","nullable":false},{"data_type":"VARCHAR","name":"channel_type","nullable":false}],"foreign_keys":[],"name":"dim_channels","primary_key":["channel_id"]},{"columns":[{"data_type":"BIGINT","name":"customer_id","nullable":false},{"data_type":"VARCHAR","name":"customer_name","nullable":false},{"data_type":"VARCHAR","name":"city","nullable":true},{"data_type":"DATE","name":"signup_date","nullable":false},{"data_type":"VARCHAR","name":"segment","nullable":false}],"foreign_keys":[],"name":"dim_customers","primary_key":["customer_id"]},{"columns":[{"data_type":"BIGINT","name":"product_id","nullable":false},{"data_type":"VARCHAR","name":"product_name","nullable":false},{"data_type":"VARCHAR","name":"category","nullable":false},{"data_type":"VARCHAR","name":"brand","nullable":false},{"data_type":"DECIMAL(14,2)","name":"list_price","nullable":false}],"foreign_keys":[],"name":"dim_products","primary_key":["product_id"]},{"columns":[{"data_type":"BIGINT","name":"order_id","nullable":false},{"data_type":"BIGINT","name":"line_no","nullable":false},{"data_type":"BIGINT","name":"product_id","nullable":false},{"data_type":"BIGINT","name":"quantity","nullable":false},{"data_type":"DECIMAL(14,2)","name":"unit_price","nullable":false},{"data_type":"DECIMAL(14,2)","name":"discount_amount","nullable":false}],"foreign_keys":[{"columns":["order_id"],"referenced_columns":["order_id"],"referenced_table":"fact_orders"},{"columns":["product_id"],"referenced_columns":["product_id"],"referenced_table":"dim_products"}],"name":"fact_order_items","primary_key":["order_id","line_no"]},{"columns":[{"data_type":"BIGINT","name":"order_id","nullable":false},{"data_type":"BIGINT","name":"customer_id","nullable":false},{"data_type":"BIGINT","name":"channel_id","nullable":false},{"data_type":"DATE","name":"order_date","nullable":false},{"data_type":"VARCHAR","name":"status","nullable":false},{"data_type":"DECIMAL(14,2)","name":"total_amount","nullable":false}],"foreign_keys":[{"columns":["customer_id"],"referenced_columns":["customer_id"],"referenced_table":"dim_customers"},{"columns":["channel_id"],"referenced_columns":["channel_id"],"referenced_table":"dim_channels"}],"name":"fact_orders","primary_key":["order_id"]},{"columns":[{"data_type":"BIGINT","name":"payment_id","nullable":false},{"data_type":"BIGINT","name":"order_id","nullable":false},{"data_type":"TIMESTAMP","name":"paid_at","nullable":false},{"data_type":"VARCHAR","name":"payment_method","nullable":false},{"data_type":"DECIMAL(14,2)","name":"amount","nullable":false},{"data_type":"VARCHAR","name":"status","nullable":false}],"foreign_keys":[{"columns":["order_id"],"referenced_columns":["order_id"],"referenced_table":"fact_orders"}],"name":"fact_payments","primary_key":["payment_id"]},{"columns":[{"data_type":"BIGINT","name":"return_id","nullable":false},{"data_type":"BIGINT","name":"order_id","nullable":false},{"data_type":"BIGINT","name":"line_no","nullable":false},{"data_type":"TIMESTAMP","name":"returned_at","nullable":false},{"data_type":"BIGINT","name":"return_qty","nullable":false},{"data_type":"DECIMAL(14,2)","name":"refund_amount","nullable":false},{"data_type":"VARCHAR","name":"reason","nullable":true}],"foreign_keys":[{"columns":["order_id","line_no"],"referenced_columns":["order_id","line_no"],"referenced_table":"fact_order_items"}],"name":"fact_returns","primary_key":["return_id"]}]}
语义层与业务口径：
{"business_rules":["完成订单仅指 fact_orders.status = 'completed'。","净销售额为 quantity * unit_price - discount_amount。","paid、refunded、failed 金额只按 fact_payments.status 分类。","退货率为完成订单的 returned_qty / sold_qty，售出数量仅含完成订单。","月份按 UTC Gregorian calendar 计算。","订单头 total_amount 应等于订单行净额汇总，差异视为数据质量异常。"],"dimensions":[{"data_type":"VARCHAR","description":"客户分群","expression":"dim_customers.segment","name":"customer_segment"},{"data_type":"VARCHAR","description":"商品品类","expression":"dim_products.category","name":"product_category"},{"data_type":"VARCHAR","description":"渠道类型","expression":"dim_channels.channel_type","name":"channel_type"},{"data_type":"VARCHAR","description":"UTC Gregorian 月份","expression":"strftime(fact_orders.order_date, '%Y-%m')","name":"order_month"}],"entities":[{"description":"客户主数据","grain":"每行一个客户","name":"customer","primary_key":["customer_id"],"table":"dim_customers"},{"description":"商品主数据","grain":"每行一个商品","name":"product","primary_key":["product_id"],"table":"dim_products"},{"description":"渠道主数据","grain":"每行一个渠道","name":"channel","primary_key":["channel_id"],"table":"dim_channels"},{"description":"订单头","grain":"每行一个订单","name":"order","primary_key":["order_id"],"table":"fact_orders"},{"description":"订单行","grain":"每行一个订单商品行","name":"order_item","primary_key":["order_id","line_no"],"table":"fact_order_items"},{"description":"支付尝试","grain":"每行一笔支付","name":"payment","primary_key":["payment_id"],"table":"fact_payments"},{"description":"退货记录","grain":"每行一条订单行退货","name":"return","primary_key":["return_id"],"table":"fact_returns"}],"metrics":[{"description":"已完成订单数","expression":"COUNT(DISTINCT CASE WHEN fact_orders.status = 'completed' THEN fact_orders.order_id END)","filters":["fact_orders.status = 'completed'"],"grain":"聚合","name":"completed_order_count"},{"description":"完成订单商品行净销售额","expression":"SUM(fact_order_items.quantity * fact_order_items.unit_price - fact_order_items.discount_amount)","filters":["fact_orders.status = 'completed'"],"grain":"聚合","name":"net_revenue"},{"description":"成功支付金额","expression":"SUM(CASE WHEN fact_payments.status = 'paid' THEN fact_payments.amount ELSE 0 END)","filters":[],"grain":"聚合","name":"paid_amount"},{"description":"退款状态支付金额","expression":"SUM(CASE WHEN fact_payments.status = 'refunded' THEN fact_payments.amount ELSE 0 END)","filters":[],"grain":"聚合","name":"refunded_amount"},{"description":"失败支付金额","expression":"SUM(CASE WHEN fact_payments.status = 'failed' THEN fact_payments.amount ELSE 0 END)","filters":[],"grain":"聚合","name":"failed_amount"},{"description":"完成订单退货数量除以售出数量","expression":"SUM(fact_returns.return_qty) / SUM(fact_order_items.quantity)","filters":["fact_orders.status = 'completed'"],"grain":"聚合","name":"return_rate"}],"relationships":[{"cardinality":"many_to_one","from_entity":"order","sql_on":"fact_orders.customer_id = dim_customers.customer_id","to_entity":"customer"},{"cardinality":"many_to_one","from_entity":"order","sql_on":"fact_orders.channel_id = dim_channels.channel_id","to_entity":"channel"},{"cardinality":"many_to_one","from_entity":"order_item","sql_on":"fact_order_items.order_id = fact_orders.order_id","to_entity":"order"},{"cardinality":"many_to_one","from_entity":"order_item","sql_on":"fact_order_items.product_id = dim_products.product_id","to_entity":"product"},{"cardinality":"many_to_one","from_entity":"payment","sql_on":"fact_payments.order_id = fact_orders.order_id","to_entity":"order"},{"cardinality":"many_to_one","from_entity":"return","sql_on":"fact_returns.order_id = fact_order_items.order_id AND fact_returns.line_no = fact_order_items.line_no","to_entity":"order_item"}]}
问题：
找出金额高于同客户分群已完成订单平均金额的已完成订单，输出 order_id、customer_id、segment、total_amount、segment_avg_amount，并按 total_amount 降序、order_id 升序；必须使用外层引用的相关子查询。
规划要求：
plan 只记录输出粒度、数据源、连接、过滤、指标、执行步骤和风险检查；每项保持简短。
SQL 必须严格实现该 plan，并遵循语义层业务口径。
summary 只概括最终做法，assumptions 只列无法由上下文确定的必要假设。
输出必须严格匹配此 JSON Schema：
{"$defs":{"QueryPlan":{"additionalProperties":false,"properties":{"filters":{"items":{"type":"string"},"title":"Filters","type":"array"},"grain":{"title":"Grain","type":"string"},"joins":{"items":{"type":"string"},"title":"Joins","type":"array"},"metrics":{"items":{"type":"string"},"title":"Metrics","type":"array"},"risks":{"items":{"type":"string"},"title":"Risks","type":"array"},"sources":{"items":{"type":"string"},"title":"Sources","type":"array"},"steps":{"items":{"type":"string"},"title":"Steps","type":"array"}},"required":["grain","sources","joins","filters","metrics","steps","risks"],"title":"QueryPlan","type":"object"}},"additionalProperties":false,"properties":{"assumptions":{"items":{"type":"string"},"title":"Assumptions","type":"array"},"plan":{"$ref":"#/$defs/QueryPlan"},"sql":{"title":"Sql","type":"string"},"summary":{"title":"Summary","type":"string"}},"required":["plan","sql","summary","assumptions"],"title":"GenerationOutput","type":"object"}调用请求（1）
按事件序号排列；密钥和 Authorization 已移除。
历史证据边界：完整 Prompt 已保存，但这次 openai_compatible 记录没有保存底层 HTTP 报文或 CLI 参数。本站不会把推测内容伪装成原始请求；未来运行会在 provider.requested 中记录脱敏后的完整调用信封。
模型输出
分别显示原始响应、解析结果和 SQL。
原始响应 raw_output
{"plan":{"grain":"每行一个高于所属客户分群均值的已完成订单。","sources":["fact_orders：订单状态、金额与客户键。","dim_customers：客户分群。"],"joins":["fact_orders.customer_id = dim_customers.customer_id。","相关子查询内以客户键连接订单与客户，并用内层 segment = 外层 segment 关联。"],"filters":["外层订单 status = 'completed'。","相关子查询均值仅包含 status = 'completed' 的订单。","total_amount > segment_avg_amount。"],"metrics":["segment_avg_amount = 同客户分群已完成订单的 AVG(total_amount)。"],"steps":["连接已完成订单与客户，取得 segment。","用引用外层 segment 的相关标量子查询计算分群平均订单金额。","筛选订单金额严格高于分群均值的记录。","按 total_amount 降序、order_id 升序排序。"],"risks":["按 segment 标签分组；同名分群会被视为同一分群。","严格大于均值，等于均值的订单不输出。"]},"sql":"SELECT\n ranked_orders.order_id,\n ranked_orders.customer_id,\n ranked_orders.segment,\n ranked_orders.total_amount,\n ranked_orders.segment_avg_amount\nFROM (\n SELECT\n o.order_id,\n o.customer_id,\n c.segment,\n o.total_amount,\n (\n SELECT AVG(o2.total_amount)\n FROM fact_orders AS o2\n JOIN dim_customers AS c2\n ON o2.customer_id = c2.customer_id\n WHERE o2.status = 'completed'\n AND c2.segment = c.segment\n ) AS segment_avg_amount\n FROM fact_orders AS o\n JOIN dim_customers AS c\n ON o.customer_id = c.customer_id\n WHERE o.status = 'completed'\n) AS ranked_orders\nWHERE ranked_orders.total_amount > ranked_orders.segment_avg_amount\nORDER BY ranked_orders.total_amount DESC, ranked_orders.order_id ASC","summary":"连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算该分群已完成订单平均金额，再筛选高于均值的订单并按要求排序。","assumptions":[]}结构化查询方案 plan
{
"filters": [
"外层订单 status = 'completed'。",
"相关子查询均值仅包含 status = 'completed' 的订单。",
"total_amount > segment_avg_amount。"
],
"grain": "每行一个高于所属客户分群均值的已完成订单。",
"joins": [
"fact_orders.customer_id = dim_customers.customer_id。",
"相关子查询内以客户键连接订单与客户，并用内层 segment = 外层 segment 关联。"
],
"metrics": [
"segment_avg_amount = 同客户分群已完成订单的 AVG(total_amount)。"
],
"risks": [
"按 segment 标签分组；同名分群会被视为同一分群。",
"严格大于均值，等于均值的订单不输出。"
],
"sources": [
"fact_orders：订单状态、金额与客户键。",
"dim_customers：客户分群。"
],
"steps": [
"连接已完成订单与客户，取得 segment。",
"用引用外层 segment 的相关标量子查询计算分群平均订单金额。",
"筛选订单金额严格高于分群均值的记录。",
"按 total_amount 降序、order_id 升序排序。"
]
}模型生成 SQL
SELECT
ranked_orders.order_id,
ranked_orders.customer_id,
ranked_orders.segment,
ranked_orders.total_amount,
ranked_orders.segment_avg_amount
FROM (
SELECT
o.order_id,
o.customer_id,
c.segment,
o.total_amount,
(
SELECT AVG(o2.total_amount)
FROM fact_orders AS o2
JOIN dim_customers AS c2
ON o2.customer_id = c2.customer_id
WHERE o2.status = 'completed'
AND c2.segment = c.segment
) AS segment_avg_amount
FROM fact_orders AS o
JOIN dim_customers AS c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
) AS ranked_orders
WHERE ranked_orders.total_amount > ranked_orders.segment_avg_amount
ORDER BY ranked_orders.total_amount DESC, ranked_orders.order_id ASC规范化 SQL
SELECT
ranked_orders.order_id,
ranked_orders.customer_id,
ranked_orders.segment,
ranked_orders.total_amount,
ranked_orders.segment_avg_amount
FROM (
SELECT
o.order_id,
o.customer_id,
c.segment,
o.total_amount,
(
SELECT
AVG(o2.total_amount)
FROM fact_orders AS o2
JOIN dim_customers AS c2
ON o2.customer_id = c2.customer_id
WHERE
o2.status = 'completed' AND c2.segment = c.segment
) AS segment_avg_amount
FROM fact_orders AS o
JOIN dim_customers AS c
ON o.customer_id = c.customer_id
WHERE
o.status = 'completed'
) AS ranked_orders
WHERE
ranked_orders.total_amount > ranked_orders.segment_avg_amount
ORDER BY
ranked_orders.total_amount DESC,
ranked_orders.order_id ASCToken 与耗时
{
"token_usage": {
"completion_tokens": 387,
"prompt_tokens": 2380,
"total_tokens": 2767
},
"generation_ms": null,
"execution_ms": 251.31962500017835
}评分结果
包含评分明细、参考 SQL、结构要求、比较规则和结果差异。
评分明细 score
{
"ast_rules": [
{
"details": {
"actual": 1,
"required": 1
},
"id": "correlated",
"kind": "correlated_subquery",
"passed": true
}
],
"column_count": 5,
"column_names": 5,
"execution": 10,
"ordering": 10,
"protocol": 5,
"read_only_ast": 5,
"row_f1": 45,
"sql_capability": 15,
"total": 100
}参考 SQL
SELECT o.order_id, o.customer_id, c.segment, o.total_amount, a.segment_avg_amount FROM fact_orders o JOIN dim_customers c ON c.customer_id = o.customer_id JOIN LATERAL (SELECT ROUND(AVG(o2.total_amount), 2) AS segment_avg_amount FROM fact_orders o2 JOIN dim_customers c2 ON c2.customer_id = o2.customer_id WHERE o2.status = 'completed' AND c2.segment = c.segment) a ON TRUE WHERE o.status = 'completed' AND o.total_amount > a.segment_avg_amount ORDER BY o.total_amount DESC, o.order_id ASCAST 结构要求
[
{
"id": "correlated",
"kind": "correlated_subquery",
"min": 1
}
]结果比较规则
{
"abs_tolerance": "0.005",
"decimal_scale": 2,
"duplicate_policy": "multiset",
"max_rows": 10000,
"rel_tolerance": "0",
"row_order_significant": true
}期望结果预览
{
"columns": [
{
"name": "order_id",
"type": "BIGINT"
},
{
"name": "customer_id",
"type": "BIGINT"
},
{
"name": "segment",
"type": "VARCHAR"
},
{
"name": "total_amount",
"type": "DECIMAL(14,2)"
},
{
"name": "segment_avg_amount",
"type": "DOUBLE"
}
],
"digest": "3b49d666cb9c60cf09b2cec279ed1983a7d5f65ef6192104a800615634292f03",
"row_count": 232,
"rows": [
[
162,
70,
"core",
"3834.75",
"1213.07"
],
[
413,
46,
"core",
"3468.75",
"1213.07"
],
[
598,
39,
"vip",
"3425.75",
"1254.16"
],
[
234,
109,
"growth",
"3290.25",
"1110.42"
],
[
599,
107,
"vip",
"3282.50",
"1254.16"
],
[
562,
59,
"vip",
"3278.25",
"1254.16"
],
[
157,
2,
"core",
"3065.50",
"1213.07"
],
[
497,
78,
"core",
"3039.75",
"1213.07"
],
[
529,
95,
"vip",
"3037.25",
"1254.16"
],
[
94,
80,
"new",
"3035.50",
"1078.87"
],
[
315,
65,
"growth",
"3006.75",
"1110.42"
],
[
423,
20,
"new",
"2991.50",
"1078.87"
],
[
135,
29,
"growth",
"2926.25",
"1110.42"
],
[
532,
85,
"growth",
"2868.25",
"1110.42"
],
[
287,
80,
"new",
"2831.00",
"1078.87"
],
[
133,
89,
"growth",
"2799.00",
"1110.42"
],
[
93,
44,
"new",
"2775.50",
"1078.87"
],
[
36,
65,
"growth",
"2756.25",
"1110.42"
],
[
242,
5,
"growth",
"2751.75",
"1110.42"
],
[
44,
59,
"vip",
"2743.00",
"1254.16"
],
[
396,
74,
"core",
"2737.25",
"1213.07"
],
[
365,
107,
"vip",
"2710.00",
"1254.16"
],
[
238,
14,
"core",
"2697.00",
"1213.07"
],
[
198,
10,
"core",
"2665.75",
"1213.07"
],
[
117,
92,
"new",
"2587.75",
"1078.87"
],
[
403,
43,
"vip",
"2573.00",
"1254.16"
],
[
468,
34,
"core",
"2459.50",
"1213.07"
],
[
194,
104,
"new",
"2419.00",
"1078.87"
],
[
255,
17,
"growth",
"2417.25",
"1110.42"
],
[
327,
43,
"vip",
"2405.75",
"1254.16"
],
[
206,
63,
"vip",
"2372.00",
"1254.16"
],
[
228,
56,
"new",
"2371.75",
"1078.87"
],
[
503,
44,
"new",
"2363.25",
"1078.87"
],
[
587,
85,
"growth",
"2334.50",
"1110.42"
],
[
556,
98,
"core",
"2318.00",
"1213.07"
],
[
38,
95,
"vip",
"2307.75",
"1254.16"
],
[
545,
2,
"core",
"2307.25",
"1213.07"
],
[
144,
38,
"core",
"2272.25",
"1213.07"
],
[
526,
27,
"vip",
"2269.75",
"1254.16"
],
[
368,
45,
"growth",
"2257.25",
"1110.42"
],
[
105,
19,
"vip",
"2244.75",
"1254.16"
],
[
356,
44,
"new",
"2234.00",
"1078.87"
],
[
395,
1,
"growth",
"2230.25",
"1110.42"
],
[
374,
61,
"growth",
"2220.75",
"1110.42"
],
[
183,
82,
"core",
"2219.25",
"1213.07"
],
[
512,
72,
"new",
"2211.75",
"1078.87"
],
[
65,
61,
"growth",
"2208.75",
"1110.42"
],
[
444,
69,
"growth",
"2185.50",
"1110.42"
],
[
202,
109,
"growth",
"2177.25",
"1110.42"
],
[
27,
85,
"growth",
"2174.50",
"1110.42"
],
[
597,
94,
"core",
"2167.75",
"1213.07"
],
[
434,
91,
"vip",
"2164.50",
"1254.16"
],
[
145,
31,
"vip",
"2161.50",
"1254.16"
],
[
134,
52,
"new",
"2158.25",
"1078.87"
],
[
506,
21,
"growth",
"2151.50",
"1110.42"
],
[
386,
39,
"vip",
"2137.25",
"1254.16"
],
[
272,
32,
"new",
"2114.75",
"1078.87"
],
[
258,
82,
"core",
"2108.00",
"1213.07"
],
[
62,
50,
"core",
"2106.25",
"1213.07"
],
[
227,
50,
"core",
"2095.50",
"1213.07"
],
[
324,
21,
"growth",
"2077.75",
"1110.42"
],
[
159,
34,
"core",
"2060.50",
"1213.07"
],
[
447,
38,
"core",
"2050.25",
"1213.07"
],
[
377,
106,
"core",
"2049.75",
"1213.07"
],
[
279,
40,
"new",
"2018.50",
"1078.87"
],
[
132,
17,
"growth",
"2007.50",
"1110.42"
],
[
98,
21,
"growth",
"1982.25",
"1110.42"
],
[
68,
13,
"growth",
"1975.75",
"1110.42"
],
[
266,
58,
"core",
"1969.00",
"1213.07"
],
[
452,
42,
"core",
"1968.75",
"1213.07"
],
[
172,
62,
"core",
"1968.00",
"1213.07"
],
[
298,
94,
"core",
"1951.25",
"1213.07"
],
[
469,
97,
"growth",
"1936.50",
"1110.42"
],
[
406,
51,
"vip",
"1933.50",
"1254.16"
],
[
257,
31,
"vip",
"1930.25",
"1254.16"
],
[
107,
66,
"core",
"1924.50",
"1213.07"
],
[
392,
96,
"new",
"1922.25",
"1078.87"
],
[
143,
60,
"new",
"1922.00",
"1078.87"
],
[
63,
94,
"core",
"1911.25",
"1213.07"
],
[
47,
24,
"new",
"1908.50",
"1078.87"
],
[
274,
89,
"growth",
"1891.50",
"1110.42"
],
[
523,
60,
"new",
"1890.50",
"1078.87"
],
[
504,
107,
"vip",
"1890.00",
"1254.16"
],
[
57,
105,
"growth",
"1888.25",
"1110.42"
],
[
252,
13,
"growth",
"1875.75",
"1110.42"
],
[
73,
46,
"core",
"1856.50",
"1213.07"
],
[
375,
49,
"growth",
"1846.75",
"1110.42"
],
[
472,
71,
"vip",
"1846.25",
"1254.16"
],
[
127,
51,
"vip",
"1817.50",
"1254.16"
],
[
383,
32,
"new",
"1816.75",
"1078.87"
],
[
393,
6,
"core",
"1809.25",
"1213.07"
],
[
566,
104,
"new",
"1799.25",
"1078.87"
],
[
334,
14,
"core",
"1798.50",
"1213.07"
],
[
129,
93,
"growth",
"1798.00",
"1110.42"
],
[
192,
76,
"new",
"1793.75",
"1078.87"
],
[
578,
104,
"new",
"1791.00",
"1078.87"
],
[
388,
15,
"vip",
"1789.75",
"1254.16"
],
[
549,
10,
"core",
"1789.75",
"1213.07"
],
[
49,
14,
"core",
"1789.00",
"1213.07"
],
[
397,
67,
"vip",
"1785.00",
"1254.16"
],
[
213,
28,
"new",
"1780.75",
"1078.87"
],
[
323,
85,
"growth",
"1780.50",
"1110.42"
],
[
405,
79,
"vip",
"1774.50",
"1254.16"
],
[
95,
15,
"vip",
"1770.50",
"1254.16"
],
[
338,
35,
"vip",
"1760.75",
"1254.16"
],
[
422,
79,
"vip",
"1758.00",
"1254.16"
],
[
459,
106,
"core",
"1756.75",
"1213.07"
],
[
112,
17,
"growth",
"1742.50",
"1110.42"
],
[
3,
48,
"new",
"1735.00",
"1078.87"
],
[
177,
93,
"growth",
"1732.50",
"1110.42"
],
[
335,
22,
"core",
"1727.00",
"1213.07"
],
[
55,
69,
"growth",
"1714.25",
"1110.42"
],
[
6,
31,
"vip",
"1713.50",
"1254.16"
],
[
229,
12,
"new",
"1696.75",
"1078.87"
],
[
138,
64,
"new",
"1695.25",
"1078.87"
],
[
296,
48,
"new",
"1692.00",
"1078.87"
],
[
336,
61,
"growth",
"1691.75",
"1110.42"
],
[
179,
110,
"core",
"1690.25",
"1213.07"
],
[
193,
67,
"vip",
"1683.75",
"1254.16"
],
[
89,
108,
"new",
"1676.75",
"1078.87"
],
[
4,
32,
"new",
"1676.25",
"1078.87"
],
[
424,
56,
"new",
"1670.75",
"1078.87"
],
[
369,
35,
"vip",
"1667.50",
"1254.16"
],
[
248,
26,
"core",
"1663.75",
"1213.07"
],
[
22,
88,
"new",
"1656.25",
"1078.87"
],
[
486,
20,
"new",
"1655.50",
"1078.87"
],
[
54,
38,
"core",
"1655.25",
"1213.07"
],
[
427,
2,
"core",
"1655.00",
"1213.07"
],
[
147,
29,
"growth",
"1648.00",
"1110.42"
],
[
349,
13,
"growth",
"1646.50",
"1110.42"
],
[
223,
67,
"vip",
"1640.00",
"1254.16"
],
[
419,
21,
"growth",
"1638.50",
"1110.42"
],
[
216,
57,
"growth",
"1628.00",
"1110.42"
],
[
42,
12,
"new",
"1624.25",
"1078.87"
],
[
305,
53,
"growth",
"1621.75",
"1110.42"
],
[
586,
33,
"growth",
"1616.00",
"1110.42"
],
[
292,
65,
"growth",
"1609.75",
"1110.42"
],
[
328,
83,
"vip",
"1607.00",
"1254.16"
],
[
5,
55,
"vip",
"1589.50",
"1254.16"
],
[
495,
74,
"core",
"1587.00",
"1213.07"
],
[
589,
24,
"new",
"1584.50",
"1078.87"
],
[
508,
12,
"new",
"1584.25",
"1078.87"
],
[
154,
80,
"new",
"1581.00",
"1078.87"
],
[
39,
44,
"new",
"1565.00",
"1078.87"
],
[
123,
56,
"new",
"1561.75",
"1078.87"
],
[
146,
50,
"core",
"1535.75",
"1213.07"
],
[
246,
82,
"core",
"1522.00",
"1213.07"
],
[
254,
17,
"growth",
"1520.25",
"1110.42"
],
[
9,
104,
"new",
"1516.00",
"1078.87"
],
[
554,
86,
"core",
"1515.25",
"1213.07"
],
[
404,
58,
"core",
"1512.25",
"1213.07"
],
[
502,
66,
"core",
"1511.00",
"1213.07"
],
[
372,
67,
"vip",
"1507.50",
"1254.16"
],
[
409,
47,
"vip",
"1506.25",
"1254.16"
],
[
167,
1,
"growth",
"1501.50",
"1110.42"
],
[
346,
71,
"vip",
"1498.50",
"1254.16"
],
[
148,
95,
"vip",
"1496.75",
"1254.16"
],
[
399,
109,
"growth",
"1493.00",
"1110.42"
],
[
509,
21,
"growth",
"1492.75",
"1110.42"
],
[
564,
46,
"core",
"1491.25",
"1213.07"
],
[
267,
17,
"growth",
"1489.00",
"1110.42"
],
[
283,
53,
"growth",
"1487.50",
"1110.42"
],
[
276,
13,
"growth",
"1485.00",
"1110.42"
],
[
585,
100,
"new",
"1484.00",
"1078.87"
],
[
82,
80,
"new",
"1471.50",
"1078.87"
],
[
414,
13,
"growth",
"1471.00",
"1110.42"
],
[
387,
12,
"new",
"1468.75",
"1078.87"
],
[
103,
95,
"vip",
"1464.25",
"1254.16"
],
[
343,
84,
"new",
"1462.50",
"1078.87"
],
[
77,
41,
"growth",
"1458.25",
"1110.42"
],
[
174,
35,
"vip",
"1458.00",
"1254.16"
],
[
518,
43,
"vip",
"1450.50",
"1254.16"
],
[
199,
92,
"new",
"1449.50",
"1078.87"
],
[
106,
78,
"core",
"1442.25",
"1213.07"
],
[
306,
104,
"new",
"1438.25",
"1078.87"
],
[
402,
110,
"core",
"1421.25",
"1213.07"
],
[
546,
83,
"vip",
"1419.50",
"1254.16"
],
[
478,
96,
"new",
"1415.00",
"1078.87"
],
[
215,
71,
"vip",
"1411.50",
"1254.16"
],
[
567,
48,
"new",
"1409.50",
"1078.87"
],
[
514,
9,
"growth",
"1397.00",
"1110.42"
],
[
575,
55,
"vip",
"1392.50",
"1254.16"
],
[
52,
90,
"core",
"1376.75",
"1213.07"
],
[
438,
34,
"core",
"1373.00",
"1213.07"
],
[
542,
31,
"vip",
"1372.50",
"1254.16"
],
[
243,
92,
"new",
"1371.50",
"1078.87"
],
[
294,
10,
"core",
"1370.00",
"1213.07"
],
[
339,
81,
"growth",
"1368.75",
"1110.42"
],
[
537,
93,
"growth",
"1363.25",
"1110.42"
],
[
293,
59,
"vip",
"1361.25",
"1254.16"
],
[
453,
108,
"new",
"1359.50",
"1078.87"
],
[
449,
97,
"growth",
"1357.00",
"1110.42"
],
[
477,
24,
"new",
"1355.25",
"1078.87"
],
[
573,
67,
"vip",
"1351.00",
"1254.16"
],
[
14,
69,
"growth",
"1350.00",
"1110.42"
],
[
364,
53,
"growth",
"1349.25",
"1110.42"
],
[
594,
41,
"growth",
"1345.50",
"1110.42"
],
[
568,
84,
"new",
"1343.00",
"1078.87"
],
[
463,
107,
"vip",
"1342.25",
"1254.16"
],
[
299,
29,
"growth",
"1341.50",
"1110.42"
]
]
}实际结果预览
{
"columns": [
{
"name": "order_id",
"type": "BIGINT"
},
{
"name": "customer_id",
"type": "BIGINT"
},
{
"name": "segment",
"type": "VARCHAR"
},
{
"name": "total_amount",
"type": "DECIMAL(14,2)"
},
{
"name": "segment_avg_amount",
"type": "DOUBLE"
}
],
"extra": [],
"missing": [],
"row_count": 232,
"rows": [
[
162,
70,
"core",
"3834.75",
1213.0695652173913
],
[
413,
46,
"core",
"3468.75",
1213.0695652173913
],
[
598,
39,
"vip",
"3425.75",
1254.1552631578948
],
[
234,
109,
"growth",
"3290.25",
1110.4161073825503
],
[
599,
107,
"vip",
"3282.50",
1254.1552631578948
],
[
562,
59,
"vip",
"3278.25",
1254.1552631578948
],
[
157,
2,
"core",
"3065.50",
1213.0695652173913
],
[
497,
78,
"core",
"3039.75",
1213.0695652173913
],
[
529,
95,
"vip",
"3037.25",
1254.1552631578948
],
[
94,
80,
"new",
"3035.50",
1078.868
],
[
315,
65,
"growth",
"3006.75",
1110.4161073825503
],
[
423,
20,
"new",
"2991.50",
1078.868
],
[
135,
29,
"growth",
"2926.25",
1110.4161073825503
],
[
532,
85,
"growth",
"2868.25",
1110.4161073825503
],
[
287,
80,
"new",
"2831.00",
1078.868
],
[
133,
89,
"growth",
"2799.00",
1110.4161073825503
],
[
93,
44,
"new",
"2775.50",
1078.868
],
[
36,
65,
"growth",
"2756.25",
1110.4161073825503
],
[
242,
5,
"growth",
"2751.75",
1110.4161073825503
],
[
44,
59,
"vip",
"2743.00",
1254.1552631578948
],
[
396,
74,
"core",
"2737.25",
1213.0695652173913
],
[
365,
107,
"vip",
"2710.00",
1254.1552631578948
],
[
238,
14,
"core",
"2697.00",
1213.0695652173913
],
[
198,
10,
"core",
"2665.75",
1213.0695652173913
],
[
117,
92,
"new",
"2587.75",
1078.868
],
[
403,
43,
"vip",
"2573.00",
1254.1552631578948
],
[
468,
34,
"core",
"2459.50",
1213.0695652173913
],
[
194,
104,
"new",
"2419.00",
1078.868
],
[
255,
17,
"growth",
"2417.25",
1110.4161073825503
],
[
327,
43,
"vip",
"2405.75",
1254.1552631578948
],
[
206,
63,
"vip",
"2372.00",
1254.1552631578948
],
[
228,
56,
"new",
"2371.75",
1078.868
],
[
503,
44,
"new",
"2363.25",
1078.868
],
[
587,
85,
"growth",
"2334.50",
1110.4161073825503
],
[
556,
98,
"core",
"2318.00",
1213.0695652173913
],
[
38,
95,
"vip",
"2307.75",
1254.1552631578948
],
[
545,
2,
"core",
"2307.25",
1213.0695652173913
],
[
144,
38,
"core",
"2272.25",
1213.0695652173913
],
[
526,
27,
"vip",
"2269.75",
1254.1552631578948
],
[
368,
45,
"growth",
"2257.25",
1110.4161073825503
],
[
105,
19,
"vip",
"2244.75",
1254.1552631578948
],
[
356,
44,
"new",
"2234.00",
1078.868
],
[
395,
1,
"growth",
"2230.25",
1110.4161073825503
],
[
374,
61,
"growth",
"2220.75",
1110.4161073825503
],
[
183,
82,
"core",
"2219.25",
1213.0695652173913
],
[
512,
72,
"new",
"2211.75",
1078.868
],
[
65,
61,
"growth",
"2208.75",
1110.4161073825503
],
[
444,
69,
"growth",
"2185.50",
1110.4161073825503
],
[
202,
109,
"growth",
"2177.25",
1110.4161073825503
],
[
27,
85,
"growth",
"2174.50",
1110.4161073825503
],
[
597,
94,
"core",
"2167.75",
1213.0695652173913
],
[
434,
91,
"vip",
"2164.50",
1254.1552631578948
],
[
145,
31,
"vip",
"2161.50",
1254.1552631578948
],
[
134,
52,
"new",
"2158.25",
1078.868
],
[
506,
21,
"growth",
"2151.50",
1110.4161073825503
],
[
386,
39,
"vip",
"2137.25",
1254.1552631578948
],
[
272,
32,
"new",
"2114.75",
1078.868
],
[
258,
82,
"core",
"2108.00",
1213.0695652173913
],
[
62,
50,
"core",
"2106.25",
1213.0695652173913
],
[
227,
50,
"core",
"2095.50",
1213.0695652173913
],
[
324,
21,
"growth",
"2077.75",
1110.4161073825503
],
[
159,
34,
"core",
"2060.50",
1213.0695652173913
],
[
447,
38,
"core",
"2050.25",
1213.0695652173913
],
[
377,
106,
"core",
"2049.75",
1213.0695652173913
],
[
279,
40,
"new",
"2018.50",
1078.868
],
[
132,
17,
"growth",
"2007.50",
1110.4161073825503
],
[
98,
21,
"growth",
"1982.25",
1110.4161073825503
],
[
68,
13,
"growth",
"1975.75",
1110.4161073825503
],
[
266,
58,
"core",
"1969.00",
1213.0695652173913
],
[
452,
42,
"core",
"1968.75",
1213.0695652173913
],
[
172,
62,
"core",
"1968.00",
1213.0695652173913
],
[
298,
94,
"core",
"1951.25",
1213.0695652173913
],
[
469,
97,
"growth",
"1936.50",
1110.4161073825503
],
[
406,
51,
"vip",
"1933.50",
1254.1552631578948
],
[
257,
31,
"vip",
"1930.25",
1254.1552631578948
],
[
107,
66,
"core",
"1924.50",
1213.0695652173913
],
[
392,
96,
"new",
"1922.25",
1078.868
],
[
143,
60,
"new",
"1922.00",
1078.868
],
[
63,
94,
"core",
"1911.25",
1213.0695652173913
],
[
47,
24,
"new",
"1908.50",
1078.868
],
[
274,
89,
"growth",
"1891.50",
1110.4161073825503
],
[
523,
60,
"new",
"1890.50",
1078.868
],
[
504,
107,
"vip",
"1890.00",
1254.1552631578948
],
[
57,
105,
"growth",
"1888.25",
1110.4161073825503
],
[
252,
13,
"growth",
"1875.75",
1110.4161073825503
],
[
73,
46,
"core",
"1856.50",
1213.0695652173913
],
[
375,
49,
"growth",
"1846.75",
1110.4161073825503
],
[
472,
71,
"vip",
"1846.25",
1254.1552631578948
],
[
127,
51,
"vip",
"1817.50",
1254.1552631578948
],
[
383,
32,
"new",
"1816.75",
1078.868
],
[
393,
6,
"core",
"1809.25",
1213.0695652173913
],
[
566,
104,
"new",
"1799.25",
1078.868
],
[
334,
14,
"core",
"1798.50",
1213.0695652173913
],
[
129,
93,
"growth",
"1798.00",
1110.4161073825503
],
[
192,
76,
"new",
"1793.75",
1078.868
],
[
578,
104,
"new",
"1791.00",
1078.868
],
[
388,
15,
"vip",
"1789.75",
1254.1552631578948
],
[
549,
10,
"core",
"1789.75",
1213.0695652173913
],
[
49,
14,
"core",
"1789.00",
1213.0695652173913
],
[
397,
67,
"vip",
"1785.00",
1254.1552631578948
],
[
213,
28,
"new",
"1780.75",
1078.868
],
[
323,
85,
"growth",
"1780.50",
1110.4161073825503
],
[
405,
79,
"vip",
"1774.50",
1254.1552631578948
],
[
95,
15,
"vip",
"1770.50",
1254.1552631578948
],
[
338,
35,
"vip",
"1760.75",
1254.1552631578948
],
[
422,
79,
"vip",
"1758.00",
1254.1552631578948
],
[
459,
106,
"core",
"1756.75",
1213.0695652173913
],
[
112,
17,
"growth",
"1742.50",
1110.4161073825503
],
[
3,
48,
"new",
"1735.00",
1078.868
],
[
177,
93,
"growth",
"1732.50",
1110.4161073825503
],
[
335,
22,
"core",
"1727.00",
1213.0695652173913
],
[
55,
69,
"growth",
"1714.25",
1110.4161073825503
],
[
6,
31,
"vip",
"1713.50",
1254.1552631578948
],
[
229,
12,
"new",
"1696.75",
1078.868
],
[
138,
64,
"new",
"1695.25",
1078.868
],
[
296,
48,
"new",
"1692.00",
1078.868
],
[
336,
61,
"growth",
"1691.75",
1110.4161073825503
],
[
179,
110,
"core",
"1690.25",
1213.0695652173913
],
[
193,
67,
"vip",
"1683.75",
1254.1552631578948
],
[
89,
108,
"new",
"1676.75",
1078.868
],
[
4,
32,
"new",
"1676.25",
1078.868
],
[
424,
56,
"new",
"1670.75",
1078.868
],
[
369,
35,
"vip",
"1667.50",
1254.1552631578948
],
[
248,
26,
"core",
"1663.75",
1213.0695652173913
],
[
22,
88,
"new",
"1656.25",
1078.868
],
[
486,
20,
"new",
"1655.50",
1078.868
],
[
54,
38,
"core",
"1655.25",
1213.0695652173913
],
[
427,
2,
"core",
"1655.00",
1213.0695652173913
],
[
147,
29,
"growth",
"1648.00",
1110.4161073825503
],
[
349,
13,
"growth",
"1646.50",
1110.4161073825503
],
[
223,
67,
"vip",
"1640.00",
1254.1552631578948
],
[
419,
21,
"growth",
"1638.50",
1110.4161073825503
],
[
216,
57,
"growth",
"1628.00",
1110.4161073825503
],
[
42,
12,
"new",
"1624.25",
1078.868
],
[
305,
53,
"growth",
"1621.75",
1110.4161073825503
],
[
586,
33,
"growth",
"1616.00",
1110.4161073825503
],
[
292,
65,
"growth",
"1609.75",
1110.4161073825503
],
[
328,
83,
"vip",
"1607.00",
1254.1552631578948
],
[
5,
55,
"vip",
"1589.50",
1254.1552631578948
],
[
495,
74,
"core",
"1587.00",
1213.0695652173913
],
[
589,
24,
"new",
"1584.50",
1078.868
],
[
508,
12,
"new",
"1584.25",
1078.868
],
[
154,
80,
"new",
"1581.00",
1078.868
],
[
39,
44,
"new",
"1565.00",
1078.868
],
[
123,
56,
"new",
"1561.75",
1078.868
],
[
146,
50,
"core",
"1535.75",
1213.0695652173913
],
[
246,
82,
"core",
"1522.00",
1213.0695652173913
],
[
254,
17,
"growth",
"1520.25",
1110.4161073825503
],
[
9,
104,
"new",
"1516.00",
1078.868
],
[
554,
86,
"core",
"1515.25",
1213.0695652173913
],
[
404,
58,
"core",
"1512.25",
1213.0695652173913
],
[
502,
66,
"core",
"1511.00",
1213.0695652173913
],
[
372,
67,
"vip",
"1507.50",
1254.1552631578948
],
[
409,
47,
"vip",
"1506.25",
1254.1552631578948
],
[
167,
1,
"growth",
"1501.50",
1110.4161073825503
],
[
346,
71,
"vip",
"1498.50",
1254.1552631578948
],
[
148,
95,
"vip",
"1496.75",
1254.1552631578948
],
[
399,
109,
"growth",
"1493.00",
1110.4161073825503
],
[
509,
21,
"growth",
"1492.75",
1110.4161073825503
],
[
564,
46,
"core",
"1491.25",
1213.0695652173913
],
[
267,
17,
"growth",
"1489.00",
1110.4161073825503
],
[
283,
53,
"growth",
"1487.50",
1110.4161073825503
],
[
276,
13,
"growth",
"1485.00",
1110.4161073825503
],
[
585,
100,
"new",
"1484.00",
1078.868
],
[
82,
80,
"new",
"1471.50",
1078.868
],
[
414,
13,
"growth",
"1471.00",
1110.4161073825503
],
[
387,
12,
"new",
"1468.75",
1078.868
],
[
103,
95,
"vip",
"1464.25",
1254.1552631578948
],
[
343,
84,
"new",
"1462.50",
1078.868
],
[
77,
41,
"growth",
"1458.25",
1110.4161073825503
],
[
174,
35,
"vip",
"1458.00",
1254.1552631578948
],
[
518,
43,
"vip",
"1450.50",
1254.1552631578948
],
[
199,
92,
"new",
"1449.50",
1078.868
],
[
106,
78,
"core",
"1442.25",
1213.0695652173913
],
[
306,
104,
"new",
"1438.25",
1078.868
],
[
402,
110,
"core",
"1421.25",
1213.0695652173913
],
[
546,
83,
"vip",
"1419.50",
1254.1552631578948
],
[
478,
96,
"new",
"1415.00",
1078.868
],
[
215,
71,
"vip",
"1411.50",
1254.1552631578948
],
[
567,
48,
"new",
"1409.50",
1078.868
],
[
514,
9,
"growth",
"1397.00",
1110.4161073825503
],
[
575,
55,
"vip",
"1392.50",
1254.1552631578948
],
[
52,
90,
"core",
"1376.75",
1213.0695652173913
],
[
438,
34,
"core",
"1373.00",
1213.0695652173913
],
[
542,
31,
"vip",
"1372.50",
1254.1552631578948
],
[
243,
92,
"new",
"1371.50",
1078.868
],
[
294,
10,
"core",
"1370.00",
1213.0695652173913
],
[
339,
81,
"growth",
"1368.75",
1110.4161073825503
],
[
537,
93,
"growth",
"1363.25",
1110.4161073825503
],
[
293,
59,
"vip",
"1361.25",
1254.1552631578948
],
[
453,
108,
"new",
"1359.50",
1078.868
],
[
449,
97,
"growth",
"1357.00",
1110.4161073825503
],
[
477,
24,
"new",
"1355.25",
1078.868
],
[
573,
67,
"vip",
"1351.00",
1254.1552631578948
],
[
14,
69,
"growth",
"1350.00",
1110.4161073825503
],
[
364,
53,
"growth",
"1349.25",
1110.4161073825503
],
[
594,
41,
"growth",
"1345.50",
1110.4161073825503
],
[
568,
84,
"new",
"1343.00",
1078.868
],
[
463,
107,
"vip",
"1342.25",
1254.1552631578948
],
[
299,
29,
"growth",
"1341.50",
1110.4161073825503
]
]
}摘要、错误与结果哈希
{
"visible_summary": "连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算该分群已完成订单平均金额，再筛选高于均值的订单并按要求排序。",
"error_code": null,
"error_message": null,
"expected_digest": "3b49d666cb9c60cf09b2cec279ed1983a7d5f65ef6192104a800615634292f03",
"actual_digest": "bacd5fd221acef2ee692bec24902cddf66cc79f1e06addc5cd98bd0a6649db36",
"assumptions": []
}事件记录（8）
按全局事件序号排列，保留时间、级别、类型、消息和 payload。
SEQ 27case.startedinfo{
"status": "generating"
}SEQ 28prompt.builtinfo{
"status": "completed"
}SEQ 29provider.requestedinfo{
"status": "running"
}SEQ 30provider.deltainfo{
"text": "{\"plan\":{\"grain\":\"每行一个高于所属客户分群均值的已完成订单。\",\"sources\":[\"fact_orders：订单状态、金额与客户键。\",\"dim_customers：客户分群。\"],\"joins\":[\"fact_orders.customer_id = dim_customers.customer_id。\",\"相关子查询内以客户键连接订单与客户，并用内层 segment = 外层 segment 关联。\"],\"filters\":[\"外层订单 status = 'completed'。\",\"相关子查询均值仅包含 status = 'completed' 的订单。\",\"total_amount > segment_avg_amount。\"],\"metrics\":[\"segment_avg_amount = 同客户分群已完成订单的 AVG(total_amount)。\"],\"steps\":[\"连接已完成订单与客户，取得 segment。\",\"用引用外层 segment 的相关标量子查询计算分群平均订单金额。\",\"筛选订单金额严格高于分群均值的记录。\",\"按 total_amount 降序、order_id 升序排序。\"],\"risks\":[\"按 segment 标签分组；同名分群会被视为同一分群。\",\"严格大于均值，等于均值的订单不输出。\"]},\"sql\":\"SELECT\\n ranked_orders.order_id,\\n ranked_orders.customer_id,\\n ranked_orders.segment,\\n ranked_orders.total_amount,\\n ranked_orders.segment_avg_amount\\nFROM (\\n SELECT\\n o.order_id,\\n o.customer_id,\\n c.segment,\\n o.total_amount,\\n (\\n SELECT AVG(o2.total_amount)\\n FROM fact_orders AS o2\\n JOIN dim_customers AS c2\\n ON o2.customer_id = c2.customer_id\\n WHERE o2.status = 'completed'\\n AND c2.segment = c.segment\\n ) AS segment_avg_amount\\n FROM fact_orders AS o\\n JOIN dim_customers AS c\\n ON o.customer_id = c.customer_id\\n WHERE o.status = 'completed'\\n) AS ranked_orders\\nWHERE ranked_orders.total_amount > ranked_orders.segment_avg_amount\\nORDER BY ranked_orders.total_amount DESC, ranked_orders.order_id ASC\",\"summary\":\"连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算该分群已完成订单平均金额，再筛选高于均值的订单并按要求排序。\",\"assumptions\":[]}"
}SEQ 31provider.completedinfo{
"elapsed_ms": 16225.30283400556,
"status": "completed",
"token_usage": {
"completion_tokens": 387,
"prompt_tokens": 2380,
"total_tokens": 2767
}
}SEQ 32plan.completedinfo{
"grain": "每行一个高于所属客户分群均值的已完成订单。",
"status": "completed",
"steps": 4
}SEQ 33sql.parsedinfo{
"status": "completed"
}SEQ 34score.completedinfo{
"score": 100,
"status": "completed"
}原始案例数据
完整 JSON 字段，不经过页面裁剪。
展开全部原始字段
{
"actual_digest": "bacd5fd221acef2ee692bec24902cddf66cc79f1e06addc5cd98bd0a6649db36",
"assumptions": [],
"attempt": 1,
"category": "subquery",
"comparison": {
"abs_tolerance": "0.005",
"decimal_scale": 2,
"duplicate_policy": "multiset",
"max_rows": 10000,
"rel_tolerance": "0",
"row_order_significant": true
},
"difficulty": "hard",
"error_code": null,
"error_message": null,
"execution_ms": 251.31962500017835,
"expected_digest": "3b49d666cb9c60cf09b2cec279ed1983a7d5f65ef6192104a800615634292f03",
"expected_result_preview": {
"columns": [
{
"name": "order_id",
"type": "BIGINT"
},
{
"name": "customer_id",
"type": "BIGINT"
},
{
"name": "segment",
"type": "VARCHAR"
},
{
"name": "total_amount",
"type": "DECIMAL(14,2)"
},
{
"name": "segment_avg_amount",
"type": "DOUBLE"
}
],
"digest": "3b49d666cb9c60cf09b2cec279ed1983a7d5f65ef6192104a800615634292f03",
"row_count": 232,
"rows": [
[
162,
70,
"core",
"3834.75",
"1213.07"
],
[
413,
46,
"core",
"3468.75",
"1213.07"
],
[
598,
39,
"vip",
"3425.75",
"1254.16"
],
[
234,
109,
"growth",
"3290.25",
"1110.42"
],
[
599,
107,
"vip",
"3282.50",
"1254.16"
],
[
562,
59,
"vip",
"3278.25",
"1254.16"
],
[
157,
2,
"core",
"3065.50",
"1213.07"
],
[
497,
78,
"core",
"3039.75",
"1213.07"
],
[
529,
95,
"vip",
"3037.25",
"1254.16"
],
[
94,
80,
"new",
"3035.50",
"1078.87"
],
[
315,
65,
"growth",
"3006.75",
"1110.42"
],
[
423,
20,
"new",
"2991.50",
"1078.87"
],
[
135,
29,
"growth",
"2926.25",
"1110.42"
],
[
532,
85,
"growth",
"2868.25",
"1110.42"
],
[
287,
80,
"new",
"2831.00",
"1078.87"
],
[
133,
89,
"growth",
"2799.00",
"1110.42"
],
[
93,
44,
"new",
"2775.50",
"1078.87"
],
[
36,
65,
"growth",
"2756.25",
"1110.42"
],
[
242,
5,
"growth",
"2751.75",
"1110.42"
],
[
44,
59,
"vip",
"2743.00",
"1254.16"
],
[
396,
74,
"core",
"2737.25",
"1213.07"
],
[
365,
107,
"vip",
"2710.00",
"1254.16"
],
[
238,
14,
"core",
"2697.00",
"1213.07"
],
[
198,
10,
"core",
"2665.75",
"1213.07"
],
[
117,
92,
"new",
"2587.75",
"1078.87"
],
[
403,
43,
"vip",
"2573.00",
"1254.16"
],
[
468,
34,
"core",
"2459.50",
"1213.07"
],
[
194,
104,
"new",
"2419.00",
"1078.87"
],
[
255,
17,
"growth",
"2417.25",
"1110.42"
],
[
327,
43,
"vip",
"2405.75",
"1254.16"
],
[
206,
63,
"vip",
"2372.00",
"1254.16"
],
[
228,
56,
"new",
"2371.75",
"1078.87"
],
[
503,
44,
"new",
"2363.25",
"1078.87"
],
[
587,
85,
"growth",
"2334.50",
"1110.42"
],
[
556,
98,
"core",
"2318.00",
"1213.07"
],
[
38,
95,
"vip",
"2307.75",
"1254.16"
],
[
545,
2,
"core",
"2307.25",
"1213.07"
],
[
144,
38,
"core",
"2272.25",
"1213.07"
],
[
526,
27,
"vip",
"2269.75",
"1254.16"
],
[
368,
45,
"growth",
"2257.25",
"1110.42"
],
[
105,
19,
"vip",
"2244.75",
"1254.16"
],
[
356,
44,
"new",
"2234.00",
"1078.87"
],
[
395,
1,
"growth",
"2230.25",
"1110.42"
],
[
374,
61,
"growth",
"2220.75",
"1110.42"
],
[
183,
82,
"core",
"2219.25",
"1213.07"
],
[
512,
72,
"new",
"2211.75",
"1078.87"
],
[
65,
61,
"growth",
"2208.75",
"1110.42"
],
[
444,
69,
"growth",
"2185.50",
"1110.42"
],
[
202,
109,
"growth",
"2177.25",
"1110.42"
],
[
27,
85,
"growth",
"2174.50",
"1110.42"
],
[
597,
94,
"core",
"2167.75",
"1213.07"
],
[
434,
91,
"vip",
"2164.50",
"1254.16"
],
[
145,
31,
"vip",
"2161.50",
"1254.16"
],
[
134,
52,
"new",
"2158.25",
"1078.87"
],
[
506,
21,
"growth",
"2151.50",
"1110.42"
],
[
386,
39,
"vip",
"2137.25",
"1254.16"
],
[
272,
32,
"new",
"2114.75",
"1078.87"
],
[
258,
82,
"core",
"2108.00",
"1213.07"
],
[
62,
50,
"core",
"2106.25",
"1213.07"
],
[
227,
50,
"core",
"2095.50",
"1213.07"
],
[
324,
21,
"growth",
"2077.75",
"1110.42"
],
[
159,
34,
"core",
"2060.50",
"1213.07"
],
[
447,
38,
"core",
"2050.25",
"1213.07"
],
[
377,
106,
"core",
"2049.75",
"1213.07"
],
[
279,
40,
"new",
"2018.50",
"1078.87"
],
[
132,
17,
"growth",
"2007.50",
"1110.42"
],
[
98,
21,
"growth",
"1982.25",
"1110.42"
],
[
68,
13,
"growth",
"1975.75",
"1110.42"
],
[
266,
58,
"core",
"1969.00",
"1213.07"
],
[
452,
42,
"core",
"1968.75",
"1213.07"
],
[
172,
62,
"core",
"1968.00",
"1213.07"
],
[
298,
94,
"core",
"1951.25",
"1213.07"
],
[
469,
97,
"growth",
"1936.50",
"1110.42"
],
[
406,
51,
"vip",
"1933.50",
"1254.16"
],
[
257,
31,
"vip",
"1930.25",
"1254.16"
],
[
107,
66,
"core",
"1924.50",
"1213.07"
],
[
392,
96,
"new",
"1922.25",
"1078.87"
],
[
143,
60,
"new",
"1922.00",
"1078.87"
],
[
63,
94,
"core",
"1911.25",
"1213.07"
],
[
47,
24,
"new",
"1908.50",
"1078.87"
],
[
274,
89,
"growth",
"1891.50",
"1110.42"
],
[
523,
60,
"new",
"1890.50",
"1078.87"
],
[
504,
107,
"vip",
"1890.00",
"1254.16"
],
[
57,
105,
"growth",
"1888.25",
"1110.42"
],
[
252,
13,
"growth",
"1875.75",
"1110.42"
],
[
73,
46,
"core",
"1856.50",
"1213.07"
],
[
375,
49,
"growth",
"1846.75",
"1110.42"
],
[
472,
71,
"vip",
"1846.25",
"1254.16"
],
[
127,
51,
"vip",
"1817.50",
"1254.16"
],
[
383,
32,
"new",
"1816.75",
"1078.87"
],
[
393,
6,
"core",
"1809.25",
"1213.07"
],
[
566,
104,
"new",
"1799.25",
"1078.87"
],
[
334,
14,
"core",
"1798.50",
"1213.07"
],
[
129,
93,
"growth",
"1798.00",
"1110.42"
],
[
192,
76,
"new",
"1793.75",
"1078.87"
],
[
578,
104,
"new",
"1791.00",
"1078.87"
],
[
388,
15,
"vip",
"1789.75",
"1254.16"
],
[
549,
10,
"core",
"1789.75",
"1213.07"
],
[
49,
14,
"core",
"1789.00",
"1213.07"
],
[
397,
67,
"vip",
"1785.00",
"1254.16"
],
[
213,
28,
"new",
"1780.75",
"1078.87"
],
[
323,
85,
"growth",
"1780.50",
"1110.42"
],
[
405,
79,
"vip",
"1774.50",
"1254.16"
],
[
95,
15,
"vip",
"1770.50",
"1254.16"
],
[
338,
35,
"vip",
"1760.75",
"1254.16"
],
[
422,
79,
"vip",
"1758.00",
"1254.16"
],
[
459,
106,
"core",
"1756.75",
"1213.07"
],
[
112,
17,
"growth",
"1742.50",
"1110.42"
],
[
3,
48,
"new",
"1735.00",
"1078.87"
],
[
177,
93,
"growth",
"1732.50",
"1110.42"
],
[
335,
22,
"core",
"1727.00",
"1213.07"
],
[
55,
69,
"growth",
"1714.25",
"1110.42"
],
[
6,
31,
"vip",
"1713.50",
"1254.16"
],
[
229,
12,
"new",
"1696.75",
"1078.87"
],
[
138,
64,
"new",
"1695.25",
"1078.87"
],
[
296,
48,
"new",
"1692.00",
"1078.87"
],
[
336,
61,
"growth",
"1691.75",
"1110.42"
],
[
179,
110,
"core",
"1690.25",
"1213.07"
],
[
193,
67,
"vip",
"1683.75",
"1254.16"
],
[
89,
108,
"new",
"1676.75",
"1078.87"
],
[
4,
32,
"new",
"1676.25",
"1078.87"
],
[
424,
56,
"new",
"1670.75",
"1078.87"
],
[
369,
35,
"vip",
"1667.50",
"1254.16"
],
[
248,
26,
"core",
"1663.75",
"1213.07"
],
[
22,
88,
"new",
"1656.25",
"1078.87"
],
[
486,
20,
"new",
"1655.50",
"1078.87"
],
[
54,
38,
"core",
"1655.25",
"1213.07"
],
[
427,
2,
"core",
"1655.00",
"1213.07"
],
[
147,
29,
"growth",
"1648.00",
"1110.42"
],
[
349,
13,
"growth",
"1646.50",
"1110.42"
],
[
223,
67,
"vip",
"1640.00",
"1254.16"
],
[
419,
21,
"growth",
"1638.50",
"1110.42"
],
[
216,
57,
"growth",
"1628.00",
"1110.42"
],
[
42,
12,
"new",
"1624.25",
"1078.87"
],
[
305,
53,
"growth",
"1621.75",
"1110.42"
],
[
586,
33,
"growth",
"1616.00",
"1110.42"
],
[
292,
65,
"growth",
"1609.75",
"1110.42"
],
[
328,
83,
"vip",
"1607.00",
"1254.16"
],
[
5,
55,
"vip",
"1589.50",
"1254.16"
],
[
495,
74,
"core",
"1587.00",
"1213.07"
],
[
589,
24,
"new",
"1584.50",
"1078.87"
],
[
508,
12,
"new",
"1584.25",
"1078.87"
],
[
154,
80,
"new",
"1581.00",
"1078.87"
],
[
39,
44,
"new",
"1565.00",
"1078.87"
],
[
123,
56,
"new",
"1561.75",
"1078.87"
],
[
146,
50,
"core",
"1535.75",
"1213.07"
],
[
246,
82,
"core",
"1522.00",
"1213.07"
],
[
254,
17,
"growth",
"1520.25",
"1110.42"
],
[
9,
104,
"new",
"1516.00",
"1078.87"
],
[
554,
86,
"core",
"1515.25",
"1213.07"
],
[
404,
58,
"core",
"1512.25",
"1213.07"
],
[
502,
66,
"core",
"1511.00",
"1213.07"
],
[
372,
67,
"vip",
"1507.50",
"1254.16"
],
[
409,
47,
"vip",
"1506.25",
"1254.16"
],
[
167,
1,
"growth",
"1501.50",
"1110.42"
],
[
346,
71,
"vip",
"1498.50",
"1254.16"
],
[
148,
95,
"vip",
"1496.75",
"1254.16"
],
[
399,
109,
"growth",
"1493.00",
"1110.42"
],
[
509,
21,
"growth",
"1492.75",
"1110.42"
],
[
564,
46,
"core",
"1491.25",
"1213.07"
],
[
267,
17,
"growth",
"1489.00",
"1110.42"
],
[
283,
53,
"growth",
"1487.50",
"1110.42"
],
[
276,
13,
"growth",
"1485.00",
"1110.42"
],
[
585,
100,
"new",
"1484.00",
"1078.87"
],
[
82,
80,
"new",
"1471.50",
"1078.87"
],
[
414,
13,
"growth",
"1471.00",
"1110.42"
],
[
387,
12,
"new",
"1468.75",
"1078.87"
],
[
103,
95,
"vip",
"1464.25",
"1254.16"
],
[
343,
84,
"new",
"1462.50",
"1078.87"
],
[
77,
41,
"growth",
"1458.25",
"1110.42"
],
[
174,
35,
"vip",
"1458.00",
"1254.16"
],
[
518,
43,
"vip",
"1450.50",
"1254.16"
],
[
199,
92,
"new",
"1449.50",
"1078.87"
],
[
106,
78,
"core",
"1442.25",
"1213.07"
],
[
306,
104,
"new",
"1438.25",
"1078.87"
],
[
402,
110,
"core",
"1421.25",
"1213.07"
],
[
546,
83,
"vip",
"1419.50",
"1254.16"
],
[
478,
96,
"new",
"1415.00",
"1078.87"
],
[
215,
71,
"vip",
"1411.50",
"1254.16"
],
[
567,
48,
"new",
"1409.50",
"1078.87"
],
[
514,
9,
"growth",
"1397.00",
"1110.42"
],
[
575,
55,
"vip",
"1392.50",
"1254.16"
],
[
52,
90,
"core",
"1376.75",
"1213.07"
],
[
438,
34,
"core",
"1373.00",
"1213.07"
],
[
542,
31,
"vip",
"1372.50",
"1254.16"
],
[
243,
92,
"new",
"1371.50",
"1078.87"
],
[
294,
10,
"core",
"1370.00",
"1213.07"
],
[
339,
81,
"growth",
"1368.75",
"1110.42"
],
[
537,
93,
"growth",
"1363.25",
"1110.42"
],
[
293,
59,
"vip",
"1361.25",
"1254.16"
],
[
453,
108,
"new",
"1359.50",
"1078.87"
],
[
449,
97,
"growth",
"1357.00",
"1110.42"
],
[
477,
24,
"new",
"1355.25",
"1078.87"
],
[
573,
67,
"vip",
"1351.00",
"1254.16"
],
[
14,
69,
"growth",
"1350.00",
"1110.42"
],
[
364,
53,
"growth",
"1349.25",
"1110.42"
],
[
594,
41,
"growth",
"1345.50",
"1110.42"
],
[
568,
84,
"new",
"1343.00",
"1078.87"
],
[
463,
107,
"vip",
"1342.25",
"1254.16"
],
[
299,
29,
"growth",
"1341.50",
"1110.42"
]
]
},
"finished_at": "2026-08-29T22:06:34.740299",
"formatted_sql": "SELECT\n ranked_orders.order_id,\n ranked_orders.customer_id,\n ranked_orders.segment,\n ranked_orders.total_amount,\n ranked_orders.segment_avg_amount\nFROM (\n SELECT\n o.order_id,\n o.customer_id,\n c.segment,\n o.total_amount,\n (\n SELECT\n AVG(o2.total_amount)\n FROM fact_orders AS o2\n JOIN dim_customers AS c2\n ON o2.customer_id = c2.customer_id\n WHERE\n o2.status = 'completed' AND c2.segment = c.segment\n ) AS segment_avg_amount\n FROM fact_orders AS o\n JOIN dim_customers AS c\n ON o.customer_id = c.customer_id\n WHERE\n o.status = 'completed'\n) AS ranked_orders\nWHERE\n ranked_orders.total_amount > ranked_orders.segment_avg_amount\nORDER BY\n ranked_orders.total_amount DESC,\n ranked_orders.order_id ASC",
"generated_sql": "SELECT\n ranked_orders.order_id,\n ranked_orders.customer_id,\n ranked_orders.segment,\n ranked_orders.total_amount,\n ranked_orders.segment_avg_amount\nFROM (\n SELECT\n o.order_id,\n o.customer_id,\n c.segment,\n o.total_amount,\n (\n SELECT AVG(o2.total_amount)\n FROM fact_orders AS o2\n JOIN dim_customers AS c2\n ON o2.customer_id = c2.customer_id\n WHERE o2.status = 'completed'\n AND c2.segment = c.segment\n ) AS segment_avg_amount\n FROM fact_orders AS o\n JOIN dim_customers AS c\n ON o.customer_id = c.customer_id\n WHERE o.status = 'completed'\n) AS ranked_orders\nWHERE ranked_orders.total_amount > ranked_orders.segment_avg_amount\nORDER BY ranked_orders.total_amount DESC, ranked_orders.order_id ASC",
"generation_ms": null,
"id": 112,
"model_name": "GPT 当前会话桥接（流程验收）",
"model_run_id": 19,
"plan": {
"filters": [
"外层订单 status = 'completed'。",
"相关子查询均值仅包含 status = 'completed' 的订单。",
"total_amount > segment_avg_amount。"
],
"grain": "每行一个高于所属客户分群均值的已完成订单。",
"joins": [
"fact_orders.customer_id = dim_customers.customer_id。",
"相关子查询内以客户键连接订单与客户，并用内层 segment = 外层 segment 关联。"
],
"metrics": [
"segment_avg_amount = 同客户分群已完成订单的 AVG(total_amount)。"
],
"risks": [
"按 segment 标签分组；同名分群会被视为同一分群。",
"严格大于均值，等于均值的订单不输出。"
],
"sources": [
"fact_orders：订单状态、金额与客户键。",
"dim_customers：客户分群。"
],
"steps": [
"连接已完成订单与客户，取得 segment。",
"用引用外层 segment 的相关标量子查询计算分群平均订单金额。",
"筛选订单金额严格高于分群均值的记录。",
"按 total_amount 降序、order_id 升序排序。"
]
},
"prompt": "你是 Text-to-SQL 数据分析与数据开发生成器。先形成简洁、可审计的结构化查询方案，再生成完成问题所需的 SQL；不要输出隐藏推理。\n\n方言与安全规则：\nUse DuckDB SQL. Return exactly one read-only query. Do not access files, URLs, extensions, or schemas outside the supplied tables.\n\n数据库结构：\n{\"semantic_relationships\":[{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.customer_id = dim_customers.customer_id\",\"to_entity\":\"customer\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.channel_id = dim_channels.channel_id\",\"to_entity\":\"channel\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.product_id = dim_products.product_id\",\"to_entity\":\"product\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"payment\",\"sql_on\":\"fact_payments.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"return\",\"sql_on\":\"fact_returns.order_id = fact_order_items.order_id AND fact_returns.line_no = fact_order_items.line_no\",\"to_entity\":\"order_item\"}],\"tables\":[{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"channel_id\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"channel_name\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"channel_type\",\"nullable\":false}],\"foreign_keys\":[],\"name\":\"dim_channels\",\"primary_key\":[\"channel_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"customer_id\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"customer_name\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"city\",\"nullable\":true},{\"data_type\":\"DATE\",\"name\":\"signup_date\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"segment\",\"nullable\":false}],\"foreign_keys\":[],\"name\":\"dim_customers\",\"primary_key\":[\"customer_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"product_id\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"product_name\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"category\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"brand\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"list_price\",\"nullable\":false}],\"foreign_keys\":[],\"name\":\"dim_products\",\"primary_key\":[\"product_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"line_no\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"product_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"quantity\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"unit_price\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"discount_amount\",\"nullable\":false}],\"foreign_keys\":[{\"columns\":[\"order_id\"],\"referenced_columns\":[\"order_id\"],\"referenced_table\":\"fact_orders\"},{\"columns\":[\"product_id\"],\"referenced_columns\":[\"product_id\"],\"referenced_table\":\"dim_products\"}],\"name\":\"fact_order_items\",\"primary_key\":[\"order_id\",\"line_no\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"customer_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"channel_id\",\"nullable\":false},{\"data_type\":\"DATE\",\"name\":\"order_date\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"status\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"total_amount\",\"nullable\":false}],\"foreign_keys\":[{\"columns\":[\"customer_id\"],\"referenced_columns\":[\"customer_id\"],\"referenced_table\":\"dim_customers\"},{\"columns\":[\"channel_id\"],\"referenced_columns\":[\"channel_id\"],\"referenced_table\":\"dim_channels\"}],\"name\":\"fact_orders\",\"primary_key\":[\"order_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"payment_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"TIMESTAMP\",\"name\":\"paid_at\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"payment_method\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"amount\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"status\",\"nullable\":false}],\"foreign_keys\":[{\"columns\":[\"order_id\"],\"referenced_columns\":[\"order_id\"],\"referenced_table\":\"fact_orders\"}],\"name\":\"fact_payments\",\"primary_key\":[\"payment_id\"]},{\"columns\":[{\"data_type\":\"BIGINT\",\"name\":\"return_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"order_id\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"line_no\",\"nullable\":false},{\"data_type\":\"TIMESTAMP\",\"name\":\"returned_at\",\"nullable\":false},{\"data_type\":\"BIGINT\",\"name\":\"return_qty\",\"nullable\":false},{\"data_type\":\"DECIMAL(14,2)\",\"name\":\"refund_amount\",\"nullable\":false},{\"data_type\":\"VARCHAR\",\"name\":\"reason\",\"nullable\":true}],\"foreign_keys\":[{\"columns\":[\"order_id\",\"line_no\"],\"referenced_columns\":[\"order_id\",\"line_no\"],\"referenced_table\":\"fact_order_items\"}],\"name\":\"fact_returns\",\"primary_key\":[\"return_id\"]}]}\n\n语义层与业务口径：\n{\"business_rules\":[\"完成订单仅指 fact_orders.status = 'completed'。\",\"净销售额为 quantity * unit_price - discount_amount。\",\"paid、refunded、failed 金额只按 fact_payments.status 分类。\",\"退货率为完成订单的 returned_qty / sold_qty，售出数量仅含完成订单。\",\"月份按 UTC Gregorian calendar 计算。\",\"订单头 total_amount 应等于订单行净额汇总，差异视为数据质量异常。\"],\"dimensions\":[{\"data_type\":\"VARCHAR\",\"description\":\"客户分群\",\"expression\":\"dim_customers.segment\",\"name\":\"customer_segment\"},{\"data_type\":\"VARCHAR\",\"description\":\"商品品类\",\"expression\":\"dim_products.category\",\"name\":\"product_category\"},{\"data_type\":\"VARCHAR\",\"description\":\"渠道类型\",\"expression\":\"dim_channels.channel_type\",\"name\":\"channel_type\"},{\"data_type\":\"VARCHAR\",\"description\":\"UTC Gregorian 月份\",\"expression\":\"strftime(fact_orders.order_date, '%Y-%m')\",\"name\":\"order_month\"}],\"entities\":[{\"description\":\"客户主数据\",\"grain\":\"每行一个客户\",\"name\":\"customer\",\"primary_key\":[\"customer_id\"],\"table\":\"dim_customers\"},{\"description\":\"商品主数据\",\"grain\":\"每行一个商品\",\"name\":\"product\",\"primary_key\":[\"product_id\"],\"table\":\"dim_products\"},{\"description\":\"渠道主数据\",\"grain\":\"每行一个渠道\",\"name\":\"channel\",\"primary_key\":[\"channel_id\"],\"table\":\"dim_channels\"},{\"description\":\"订单头\",\"grain\":\"每行一个订单\",\"name\":\"order\",\"primary_key\":[\"order_id\"],\"table\":\"fact_orders\"},{\"description\":\"订单行\",\"grain\":\"每行一个订单商品行\",\"name\":\"order_item\",\"primary_key\":[\"order_id\",\"line_no\"],\"table\":\"fact_order_items\"},{\"description\":\"支付尝试\",\"grain\":\"每行一笔支付\",\"name\":\"payment\",\"primary_key\":[\"payment_id\"],\"table\":\"fact_payments\"},{\"description\":\"退货记录\",\"grain\":\"每行一条订单行退货\",\"name\":\"return\",\"primary_key\":[\"return_id\"],\"table\":\"fact_returns\"}],\"metrics\":[{\"description\":\"已完成订单数\",\"expression\":\"COUNT(DISTINCT CASE WHEN fact_orders.status = 'completed' THEN fact_orders.order_id END)\",\"filters\":[\"fact_orders.status = 'completed'\"],\"grain\":\"聚合\",\"name\":\"completed_order_count\"},{\"description\":\"完成订单商品行净销售额\",\"expression\":\"SUM(fact_order_items.quantity * fact_order_items.unit_price - fact_order_items.discount_amount)\",\"filters\":[\"fact_orders.status = 'completed'\"],\"grain\":\"聚合\",\"name\":\"net_revenue\"},{\"description\":\"成功支付金额\",\"expression\":\"SUM(CASE WHEN fact_payments.status = 'paid' THEN fact_payments.amount ELSE 0 END)\",\"filters\":[],\"grain\":\"聚合\",\"name\":\"paid_amount\"},{\"description\":\"退款状态支付金额\",\"expression\":\"SUM(CASE WHEN fact_payments.status = 'refunded' THEN fact_payments.amount ELSE 0 END)\",\"filters\":[],\"grain\":\"聚合\",\"name\":\"refunded_amount\"},{\"description\":\"失败支付金额\",\"expression\":\"SUM(CASE WHEN fact_payments.status = 'failed' THEN fact_payments.amount ELSE 0 END)\",\"filters\":[],\"grain\":\"聚合\",\"name\":\"failed_amount\"},{\"description\":\"完成订单退货数量除以售出数量\",\"expression\":\"SUM(fact_returns.return_qty) / SUM(fact_order_items.quantity)\",\"filters\":[\"fact_orders.status = 'completed'\"],\"grain\":\"聚合\",\"name\":\"return_rate\"}],\"relationships\":[{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.customer_id = dim_customers.customer_id\",\"to_entity\":\"customer\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order\",\"sql_on\":\"fact_orders.channel_id = dim_channels.channel_id\",\"to_entity\":\"channel\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"order_item\",\"sql_on\":\"fact_order_items.product_id = dim_products.product_id\",\"to_entity\":\"product\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"payment\",\"sql_on\":\"fact_payments.order_id = fact_orders.order_id\",\"to_entity\":\"order\"},{\"cardinality\":\"many_to_one\",\"from_entity\":\"return\",\"sql_on\":\"fact_returns.order_id = fact_order_items.order_id AND fact_returns.line_no = fact_order_items.line_no\",\"to_entity\":\"order_item\"}]}\n\n问题：\n找出金额高于同客户分群已完成订单平均金额的已完成订单，输出 order_id、customer_id、segment、total_amount、segment_avg_amount，并按 total_amount 降序、order_id 升序；必须使用外层引用的相关子查询。\n\n规划要求：\nplan 只记录输出粒度、数据源、连接、过滤、指标、执行步骤和风险检查；每项保持简短。\nSQL 必须严格实现该 plan，并遵循语义层业务口径。\nsummary 只概括最终做法，assumptions 只列无法由上下文确定的必要假设。\n输出必须严格匹配此 JSON Schema：\n{\"$defs\":{\"QueryPlan\":{\"additionalProperties\":false,\"properties\":{\"filters\":{\"items\":{\"type\":\"string\"},\"title\":\"Filters\",\"type\":\"array\"},\"grain\":{\"title\":\"Grain\",\"type\":\"string\"},\"joins\":{\"items\":{\"type\":\"string\"},\"title\":\"Joins\",\"type\":\"array\"},\"metrics\":{\"items\":{\"type\":\"string\"},\"title\":\"Metrics\",\"type\":\"array\"},\"risks\":{\"items\":{\"type\":\"string\"},\"title\":\"Risks\",\"type\":\"array\"},\"sources\":{\"items\":{\"type\":\"string\"},\"title\":\"Sources\",\"type\":\"array\"},\"steps\":{\"items\":{\"type\":\"string\"},\"title\":\"Steps\",\"type\":\"array\"}},\"required\":[\"grain\",\"sources\",\"joins\",\"filters\",\"metrics\",\"steps\",\"risks\"],\"title\":\"QueryPlan\",\"type\":\"object\"}},\"additionalProperties\":false,\"properties\":{\"assumptions\":{\"items\":{\"type\":\"string\"},\"title\":\"Assumptions\",\"type\":\"array\"},\"plan\":{\"$ref\":\"#/$defs/QueryPlan\"},\"sql\":{\"title\":\"Sql\",\"type\":\"string\"},\"summary\":{\"title\":\"Summary\",\"type\":\"string\"}},\"required\":[\"plan\",\"sql\",\"summary\",\"assumptions\"],\"title\":\"GenerationOutput\",\"type\":\"object\"}",
"provider_request_id": null,
"question": "找出金额高于同客户分群已完成订单平均金额的已完成订单，输出 order_id、customer_id、segment、total_amount、segment_avg_amount，并按 total_amount 降序、order_id 升序；必须使用外层引用的相关子查询。",
"radar_dimension": "复杂查询",
"raw_output": "{\"plan\":{\"grain\":\"每行一个高于所属客户分群均值的已完成订单。\",\"sources\":[\"fact_orders：订单状态、金额与客户键。\",\"dim_customers：客户分群。\"],\"joins\":[\"fact_orders.customer_id = dim_customers.customer_id。\",\"相关子查询内以客户键连接订单与客户，并用内层 segment = 外层 segment 关联。\"],\"filters\":[\"外层订单 status = 'completed'。\",\"相关子查询均值仅包含 status = 'completed' 的订单。\",\"total_amount > segment_avg_amount。\"],\"metrics\":[\"segment_avg_amount = 同客户分群已完成订单的 AVG(total_amount)。\"],\"steps\":[\"连接已完成订单与客户，取得 segment。\",\"用引用外层 segment 的相关标量子查询计算分群平均订单金额。\",\"筛选订单金额严格高于分群均值的记录。\",\"按 total_amount 降序、order_id 升序排序。\"],\"risks\":[\"按 segment 标签分组；同名分群会被视为同一分群。\",\"严格大于均值，等于均值的订单不输出。\"]},\"sql\":\"SELECT\\n ranked_orders.order_id,\\n ranked_orders.customer_id,\\n ranked_orders.segment,\\n ranked_orders.total_amount,\\n ranked_orders.segment_avg_amount\\nFROM (\\n SELECT\\n o.order_id,\\n o.customer_id,\\n c.segment,\\n o.total_amount,\\n (\\n SELECT AVG(o2.total_amount)\\n FROM fact_orders AS o2\\n JOIN dim_customers AS c2\\n ON o2.customer_id = c2.customer_id\\n WHERE o2.status = 'completed'\\n AND c2.segment = c.segment\\n ) AS segment_avg_amount\\n FROM fact_orders AS o\\n JOIN dim_customers AS c\\n ON o.customer_id = c.customer_id\\n WHERE o.status = 'completed'\\n) AS ranked_orders\\nWHERE ranked_orders.total_amount > ranked_orders.segment_avg_amount\\nORDER BY ranked_orders.total_amount DESC, ranked_orders.order_id ASC\",\"summary\":\"连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算该分群已完成订单平均金额，再筛选高于均值的订单并按要求排序。\",\"assumptions\":[]}",
"reference_sql": "SELECT o.order_id, o.customer_id, c.segment, o.total_amount, a.segment_avg_amount FROM fact_orders o JOIN dim_customers c ON c.customer_id = o.customer_id JOIN LATERAL (SELECT ROUND(AVG(o2.total_amount), 2) AS segment_avg_amount FROM fact_orders o2 JOIN dim_customers c2 ON c2.customer_id = o2.customer_id WHERE o2.status = 'completed' AND c2.segment = c.segment) a ON TRUE WHERE o.status = 'completed' AND o.total_amount > a.segment_avg_amount ORDER BY o.total_amount DESC, o.order_id ASC",
"requested_model_id": "gpt-session-bridge",
"required_ast": [
{
"id": "correlated",
"kind": "correlated_subquery",
"min": 1
}
],
"resolved_model_id": "gpt-session-bridge",
"result_preview": {
"columns": [
{
"name": "order_id",
"type": "BIGINT"
},
{
"name": "customer_id",
"type": "BIGINT"
},
{
"name": "segment",
"type": "VARCHAR"
},
{
"name": "total_amount",
"type": "DECIMAL(14,2)"
},
{
"name": "segment_avg_amount",
"type": "DOUBLE"
}
],
"extra": [],
"missing": [],
"row_count": 232,
"rows": [
[
162,
70,
"core",
"3834.75",
1213.0695652173913
],
[
413,
46,
"core",
"3468.75",
1213.0695652173913
],
[
598,
39,
"vip",
"3425.75",
1254.1552631578948
],
[
234,
109,
"growth",
"3290.25",
1110.4161073825503
],
[
599,
107,
"vip",
"3282.50",
1254.1552631578948
],
[
562,
59,
"vip",
"3278.25",
1254.1552631578948
],
[
157,
2,
"core",
"3065.50",
1213.0695652173913
],
[
497,
78,
"core",
"3039.75",
1213.0695652173913
],
[
529,
95,
"vip",
"3037.25",
1254.1552631578948
],
[
94,
80,
"new",
"3035.50",
1078.868
],
[
315,
65,
"growth",
"3006.75",
1110.4161073825503
],
[
423,
20,
"new",
"2991.50",
1078.868
],
[
135,
29,
"growth",
"2926.25",
1110.4161073825503
],
[
532,
85,
"growth",
"2868.25",
1110.4161073825503
],
[
287,
80,
"new",
"2831.00",
1078.868
],
[
133,
89,
"growth",
"2799.00",
1110.4161073825503
],
[
93,
44,
"new",
"2775.50",
1078.868
],
[
36,
65,
"growth",
"2756.25",
1110.4161073825503
],
[
242,
5,
"growth",
"2751.75",
1110.4161073825503
],
[
44,
59,
"vip",
"2743.00",
1254.1552631578948
],
[
396,
74,
"core",
"2737.25",
1213.0695652173913
],
[
365,
107,
"vip",
"2710.00",
1254.1552631578948
],
[
238,
14,
"core",
"2697.00",
1213.0695652173913
],
[
198,
10,
"core",
"2665.75",
1213.0695652173913
],
[
117,
92,
"new",
"2587.75",
1078.868
],
[
403,
43,
"vip",
"2573.00",
1254.1552631578948
],
[
468,
34,
"core",
"2459.50",
1213.0695652173913
],
[
194,
104,
"new",
"2419.00",
1078.868
],
[
255,
17,
"growth",
"2417.25",
1110.4161073825503
],
[
327,
43,
"vip",
"2405.75",
1254.1552631578948
],
[
206,
63,
"vip",
"2372.00",
1254.1552631578948
],
[
228,
56,
"new",
"2371.75",
1078.868
],
[
503,
44,
"new",
"2363.25",
1078.868
],
[
587,
85,
"growth",
"2334.50",
1110.4161073825503
],
[
556,
98,
"core",
"2318.00",
1213.0695652173913
],
[
38,
95,
"vip",
"2307.75",
1254.1552631578948
],
[
545,
2,
"core",
"2307.25",
1213.0695652173913
],
[
144,
38,
"core",
"2272.25",
1213.0695652173913
],
[
526,
27,
"vip",
"2269.75",
1254.1552631578948
],
[
368,
45,
"growth",
"2257.25",
1110.4161073825503
],
[
105,
19,
"vip",
"2244.75",
1254.1552631578948
],
[
356,
44,
"new",
"2234.00",
1078.868
],
[
395,
1,
"growth",
"2230.25",
1110.4161073825503
],
[
374,
61,
"growth",
"2220.75",
1110.4161073825503
],
[
183,
82,
"core",
"2219.25",
1213.0695652173913
],
[
512,
72,
"new",
"2211.75",
1078.868
],
[
65,
61,
"growth",
"2208.75",
1110.4161073825503
],
[
444,
69,
"growth",
"2185.50",
1110.4161073825503
],
[
202,
109,
"growth",
"2177.25",
1110.4161073825503
],
[
27,
85,
"growth",
"2174.50",
1110.4161073825503
],
[
597,
94,
"core",
"2167.75",
1213.0695652173913
],
[
434,
91,
"vip",
"2164.50",
1254.1552631578948
],
[
145,
31,
"vip",
"2161.50",
1254.1552631578948
],
[
134,
52,
"new",
"2158.25",
1078.868
],
[
506,
21,
"growth",
"2151.50",
1110.4161073825503
],
[
386,
39,
"vip",
"2137.25",
1254.1552631578948
],
[
272,
32,
"new",
"2114.75",
1078.868
],
[
258,
82,
"core",
"2108.00",
1213.0695652173913
],
[
62,
50,
"core",
"2106.25",
1213.0695652173913
],
[
227,
50,
"core",
"2095.50",
1213.0695652173913
],
[
324,
21,
"growth",
"2077.75",
1110.4161073825503
],
[
159,
34,
"core",
"2060.50",
1213.0695652173913
],
[
447,
38,
"core",
"2050.25",
1213.0695652173913
],
[
377,
106,
"core",
"2049.75",
1213.0695652173913
],
[
279,
40,
"new",
"2018.50",
1078.868
],
[
132,
17,
"growth",
"2007.50",
1110.4161073825503
],
[
98,
21,
"growth",
"1982.25",
1110.4161073825503
],
[
68,
13,
"growth",
"1975.75",
1110.4161073825503
],
[
266,
58,
"core",
"1969.00",
1213.0695652173913
],
[
452,
42,
"core",
"1968.75",
1213.0695652173913
],
[
172,
62,
"core",
"1968.00",
1213.0695652173913
],
[
298,
94,
"core",
"1951.25",
1213.0695652173913
],
[
469,
97,
"growth",
"1936.50",
1110.4161073825503
],
[
406,
51,
"vip",
"1933.50",
1254.1552631578948
],
[
257,
31,
"vip",
"1930.25",
1254.1552631578948
],
[
107,
66,
"core",
"1924.50",
1213.0695652173913
],
[
392,
96,
"new",
"1922.25",
1078.868
],
[
143,
60,
"new",
"1922.00",
1078.868
],
[
63,
94,
"core",
"1911.25",
1213.0695652173913
],
[
47,
24,
"new",
"1908.50",
1078.868
],
[
274,
89,
"growth",
"1891.50",
1110.4161073825503
],
[
523,
60,
"new",
"1890.50",
1078.868
],
[
504,
107,
"vip",
"1890.00",
1254.1552631578948
],
[
57,
105,
"growth",
"1888.25",
1110.4161073825503
],
[
252,
13,
"growth",
"1875.75",
1110.4161073825503
],
[
73,
46,
"core",
"1856.50",
1213.0695652173913
],
[
375,
49,
"growth",
"1846.75",
1110.4161073825503
],
[
472,
71,
"vip",
"1846.25",
1254.1552631578948
],
[
127,
51,
"vip",
"1817.50",
1254.1552631578948
],
[
383,
32,
"new",
"1816.75",
1078.868
],
[
393,
6,
"core",
"1809.25",
1213.0695652173913
],
[
566,
104,
"new",
"1799.25",
1078.868
],
[
334,
14,
"core",
"1798.50",
1213.0695652173913
],
[
129,
93,
"growth",
"1798.00",
1110.4161073825503
],
[
192,
76,
"new",
"1793.75",
1078.868
],
[
578,
104,
"new",
"1791.00",
1078.868
],
[
388,
15,
"vip",
"1789.75",
1254.1552631578948
],
[
549,
10,
"core",
"1789.75",
1213.0695652173913
],
[
49,
14,
"core",
"1789.00",
1213.0695652173913
],
[
397,
67,
"vip",
"1785.00",
1254.1552631578948
],
[
213,
28,
"new",
"1780.75",
1078.868
],
[
323,
85,
"growth",
"1780.50",
1110.4161073825503
],
[
405,
79,
"vip",
"1774.50",
1254.1552631578948
],
[
95,
15,
"vip",
"1770.50",
1254.1552631578948
],
[
338,
35,
"vip",
"1760.75",
1254.1552631578948
],
[
422,
79,
"vip",
"1758.00",
1254.1552631578948
],
[
459,
106,
"core",
"1756.75",
1213.0695652173913
],
[
112,
17,
"growth",
"1742.50",
1110.4161073825503
],
[
3,
48,
"new",
"1735.00",
1078.868
],
[
177,
93,
"growth",
"1732.50",
1110.4161073825503
],
[
335,
22,
"core",
"1727.00",
1213.0695652173913
],
[
55,
69,
"growth",
"1714.25",
1110.4161073825503
],
[
6,
31,
"vip",
"1713.50",
1254.1552631578948
],
[
229,
12,
"new",
"1696.75",
1078.868
],
[
138,
64,
"new",
"1695.25",
1078.868
],
[
296,
48,
"new",
"1692.00",
1078.868
],
[
336,
61,
"growth",
"1691.75",
1110.4161073825503
],
[
179,
110,
"core",
"1690.25",
1213.0695652173913
],
[
193,
67,
"vip",
"1683.75",
1254.1552631578948
],
[
89,
108,
"new",
"1676.75",
1078.868
],
[
4,
32,
"new",
"1676.25",
1078.868
],
[
424,
56,
"new",
"1670.75",
1078.868
],
[
369,
35,
"vip",
"1667.50",
1254.1552631578948
],
[
248,
26,
"core",
"1663.75",
1213.0695652173913
],
[
22,
88,
"new",
"1656.25",
1078.868
],
[
486,
20,
"new",
"1655.50",
1078.868
],
[
54,
38,
"core",
"1655.25",
1213.0695652173913
],
[
427,
2,
"core",
"1655.00",
1213.0695652173913
],
[
147,
29,
"growth",
"1648.00",
1110.4161073825503
],
[
349,
13,
"growth",
"1646.50",
1110.4161073825503
],
[
223,
67,
"vip",
"1640.00",
1254.1552631578948
],
[
419,
21,
"growth",
"1638.50",
1110.4161073825503
],
[
216,
57,
"growth",
"1628.00",
1110.4161073825503
],
[
42,
12,
"new",
"1624.25",
1078.868
],
[
305,
53,
"growth",
"1621.75",
1110.4161073825503
],
[
586,
33,
"growth",
"1616.00",
1110.4161073825503
],
[
292,
65,
"growth",
"1609.75",
1110.4161073825503
],
[
328,
83,
"vip",
"1607.00",
1254.1552631578948
],
[
5,
55,
"vip",
"1589.50",
1254.1552631578948
],
[
495,
74,
"core",
"1587.00",
1213.0695652173913
],
[
589,
24,
"new",
"1584.50",
1078.868
],
[
508,
12,
"new",
"1584.25",
1078.868
],
[
154,
80,
"new",
"1581.00",
1078.868
],
[
39,
44,
"new",
"1565.00",
1078.868
],
[
123,
56,
"new",
"1561.75",
1078.868
],
[
146,
50,
"core",
"1535.75",
1213.0695652173913
],
[
246,
82,
"core",
"1522.00",
1213.0695652173913
],
[
254,
17,
"growth",
"1520.25",
1110.4161073825503
],
[
9,
104,
"new",
"1516.00",
1078.868
],
[
554,
86,
"core",
"1515.25",
1213.0695652173913
],
[
404,
58,
"core",
"1512.25",
1213.0695652173913
],
[
502,
66,
"core",
"1511.00",
1213.0695652173913
],
[
372,
67,
"vip",
"1507.50",
1254.1552631578948
],
[
409,
47,
"vip",
"1506.25",
1254.1552631578948
],
[
167,
1,
"growth",
"1501.50",
1110.4161073825503
],
[
346,
71,
"vip",
"1498.50",
1254.1552631578948
],
[
148,
95,
"vip",
"1496.75",
1254.1552631578948
],
[
399,
109,
"growth",
"1493.00",
1110.4161073825503
],
[
509,
21,
"growth",
"1492.75",
1110.4161073825503
],
[
564,
46,
"core",
"1491.25",
1213.0695652173913
],
[
267,
17,
"growth",
"1489.00",
1110.4161073825503
],
[
283,
53,
"growth",
"1487.50",
1110.4161073825503
],
[
276,
13,
"growth",
"1485.00",
1110.4161073825503
],
[
585,
100,
"new",
"1484.00",
1078.868
],
[
82,
80,
"new",
"1471.50",
1078.868
],
[
414,
13,
"growth",
"1471.00",
1110.4161073825503
],
[
387,
12,
"new",
"1468.75",
1078.868
],
[
103,
95,
"vip",
"1464.25",
1254.1552631578948
],
[
343,
84,
"new",
"1462.50",
1078.868
],
[
77,
41,
"growth",
"1458.25",
1110.4161073825503
],
[
174,
35,
"vip",
"1458.00",
1254.1552631578948
],
[
518,
43,
"vip",
"1450.50",
1254.1552631578948
],
[
199,
92,
"new",
"1449.50",
1078.868
],
[
106,
78,
"core",
"1442.25",
1213.0695652173913
],
[
306,
104,
"new",
"1438.25",
1078.868
],
[
402,
110,
"core",
"1421.25",
1213.0695652173913
],
[
546,
83,
"vip",
"1419.50",
1254.1552631578948
],
[
478,
96,
"new",
"1415.00",
1078.868
],
[
215,
71,
"vip",
"1411.50",
1254.1552631578948
],
[
567,
48,
"new",
"1409.50",
1078.868
],
[
514,
9,
"growth",
"1397.00",
1110.4161073825503
],
[
575,
55,
"vip",
"1392.50",
1254.1552631578948
],
[
52,
90,
"core",
"1376.75",
1213.0695652173913
],
[
438,
34,
"core",
"1373.00",
1213.0695652173913
],
[
542,
31,
"vip",
"1372.50",
1254.1552631578948
],
[
243,
92,
"new",
"1371.50",
1078.868
],
[
294,
10,
"core",
"1370.00",
1213.0695652173913
],
[
339,
81,
"growth",
"1368.75",
1110.4161073825503
],
[
537,
93,
"growth",
"1363.25",
1110.4161073825503
],
[
293,
59,
"vip",
"1361.25",
1254.1552631578948
],
[
453,
108,
"new",
"1359.50",
1078.868
],
[
449,
97,
"growth",
"1357.00",
1110.4161073825503
],
[
477,
24,
"new",
"1355.25",
1078.868
],
[
573,
67,
"vip",
"1351.00",
1254.1552631578948
],
[
14,
69,
"growth",
"1350.00",
1110.4161073825503
],
[
364,
53,
"growth",
"1349.25",
1110.4161073825503
],
[
594,
41,
"growth",
"1345.50",
1110.4161073825503
],
[
568,
84,
"new",
"1343.00",
1078.868
],
[
463,
107,
"vip",
"1342.25",
1254.1552631578948
],
[
299,
29,
"growth",
"1341.50",
1110.4161073825503
]
]
},
"run_id": 16,
"score": {
"ast_rules": [
{
"details": {
"actual": 1,
"required": 1
},
"id": "correlated",
"kind": "correlated_subquery",
"passed": true
}
],
"column_count": 5,
"column_names": 5,
"execution": 10,
"ordering": 10,
"protocol": 5,
"read_only_ast": 5,
"row_f1": 45,
"sql_capability": 15,
"total": 100
},
"stable_key": "correlated_segment_average",
"started_at": "2026-08-29T22:06:18.221207",
"status": "completed",
"suite_content_hash": "5b5d98876ea35114f18ce6dfa48cc9800d88b6baba80d311b2f52552a38b31af",
"title": "相关子查询分群均值",
"token_usage": {
"completion_tokens": 387,
"prompt_tokens": 2380,
"total_tokens": 2767
},
"visible_summary": "连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算该分群已完成订单平均金额，再筛选高于均值的订单并按要求排序。"
}