RUN / #0014完成

运行 #14

模型：Sol。比较模式：single_model；差异字段：无。创建于 2026年8月29日 19:53。

原始报告 JSON ↗完整事件 JSONL 返回运行索引

gpt-5.6-sol

Sol

codex_cli · text综合得分 / 100 · 越高越好

98.12

资源效率

分别列出准确率、每正确等价题 Token、估算费用和生成耗时。费用采用运行时冻结的价格快照。

指标	Sol
准确率 `越高越好`	98.12
Token / 正确等价题 `越低越好`	19,753
费用 / 正确等价题 `USD · 估算`	不可估算
模型生成耗时 P95 `越低越好`	—
累计 Token `已记录题数`	232,58612/12 题

能力维度

加权得分

Sol

分类得分

0—100

能力维度	Sol
基础查询	100.00
复杂管道	96.25
子查询	100.00
窗口函数	96.25
聚合分析	100.00
连接语义	100.00

逐题结果（12）

展开案例可查看模型摘要、SQL、错误和原始案例 JSON。

案例	Sol
基础筛选与排序 `basic_filter_sort`	100.00
多表连接品类收入 `multi_join_category_revenue`	100.00
保留零订单客户 `left_join_zero_orders`	100.00
相关子查询分群均值 `correlated_segment_average`	100.00
从未完成订单客户 `not_exists_customers`	100.00
每品类收入前三商品 `top3_products_per_category`	100.00
月收入与累计收入 `monthly_running_revenue`	100.00
月收入环比增长 `monthly_mom_growth`	100.00
高于平均累计消费客户 `above_average_customer_spend`	100.00
渠道支付状态金额 `payment_status_by_channel`	100.00
品类退货率 `category_return_rate`	92.50
每单最新成功支付 `latest_successful_payment`	85.00

基础筛选与排序basic_filter_sort

Sol

查询 2026-01-01 起的已完成订单及客户名称，按订单总额降序、订单 ID 升序排列。

SELECT
  o.order_id,
  c.customer_name,
  o.order_date,
  o.total_amount
FROM fact_orders AS o
JOIN dim_customers AS c
  ON o.customer_id = c.customer_id
WHERE
  o.status = 'completed' AND o.order_date >= CAST('2026-01-01' AS DATE)
ORDER BY
  o.total_amount DESC,
  o.order_id ASC

STATUScompletedGEN—msEXEC307.0771249986137ms

上下文、请求与日志原始 JSON ↗

多表连接品类收入multi_join_category_revenue

Sol

按商品品类汇总已完成订单的去重订单数与订单行净销售额，并按净销售额降序、品类升序排列。

SELECT
  p.category,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
FROM fact_orders AS o
JOIN fact_order_items AS oi
  ON oi.order_id = o.order_id
JOIN dim_products AS p
  ON p.product_id = oi.product_id
WHERE
  o.status = 'completed'
GROUP BY
  p.category
ORDER BY
  net_revenue DESC,
  p.category ASC

STATUScompletedGEN—msEXEC222.81295799984946ms

上下文、请求与日志原始 JSON ↗

保留零订单客户left_join_zero_orders

Sol

列出所有客户及其已完成订单数，通过 LEFT JOIN 保留零订单客户，并按完成订单数降序、客户 ID 升序排列。

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(o.order_id) AS completed_order_count
FROM dim_customers AS c
LEFT JOIN fact_orders AS o
  ON c.customer_id = o.customer_id AND o.status = 'completed'
GROUP BY
  c.customer_id,
  c.customer_name
ORDER BY
  completed_order_count DESC,
  c.customer_id ASC

STATUScompletedGEN—msEXEC232.62758299824782ms

上下文、请求与日志原始 JSON ↗

相关子查询分群均值correlated_segment_average

Sol

返回金额高于其客户分群已完成订单平均金额的已完成订单，并按订单金额降序、订单 ID 升序排列。

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 c2.customer_id = o2.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 c.customer_id = o.customer_id
WHERE
  o.status = 'completed'
  AND o.total_amount > (
    SELECT
      AVG(o3.total_amount)
    FROM fact_orders AS o3
    JOIN dim_customers AS c3
      ON c3.customer_id = o3.customer_id
    WHERE
      o3.status = 'completed' AND c3.segment = c.segment
  )
ORDER BY
  o.total_amount DESC,
  o.order_id ASC

STATUScompletedGEN—msEXEC218.44962500108522ms

上下文、请求与日志原始 JSON ↗

从未完成订单客户not_exists_customers

Sol

返回从未有已完成订单的客户，并按 customer_id 升序排列。

SELECT
  c.customer_id,
  c.customer_name
FROM dim_customers AS c
WHERE
  NOT EXISTS(
    SELECT
      1
    FROM fact_orders AS o
    WHERE
      o.customer_id = c.customer_id AND o.status = 'completed'
  )
ORDER BY
  c.customer_id ASC

STATUScompletedGEN—msEXEC220.48662499946658ms

上下文、请求与日志原始 JSON ↗

每品类收入前三商品top3_products_per_category

Sol

汇总完成订单中各商品的净销售额，使用 ROW_NUMBER 按品类排名，并返回每个品类前三名商品。

WITH product_revenue AS (
  SELECT
    p.category,
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_order_items AS oi
  JOIN fact_orders AS o
    ON oi.order_id = o.order_id
  JOIN dim_products AS p
    ON oi.product_id = p.product_id
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category,
    p.product_id,
    p.product_name
), ranked_products AS (
  SELECT
    category,
    product_id,
    product_name,
    net_revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY net_revenue DESC, product_id ASC) AS rank_no
  FROM product_revenue
)
SELECT
  category,
  product_id,
  product_name,
  net_revenue,
  rank_no
FROM ranked_products
WHERE
  rank_no <= 3
ORDER BY
  category ASC,
  rank_no ASC

STATUScompletedGEN—msEXEC208.59083300092607ms

上下文、请求与日志原始 JSON ↗

月收入与累计收入monthly_running_revenue

Sol

按月汇总 2025 年已完成订单的净销售额，并使用按月份排序的 SUM 窗口计算累计收入。

WITH monthly AS (
  SELECT
    STRFTIME(o.order_date, '%Y-%m') AS month,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS monthly_revenue
  FROM fact_orders AS o
  JOIN fact_order_items AS oi
    ON oi.order_id = o.order_id
  WHERE
    o.status = 'completed'
    AND o.order_date >= CAST('2025-01-01' AS DATE)
    AND o.order_date < CAST('2026-01-01' AS DATE)
  GROUP BY
    1
)
SELECT
  month,
  monthly_revenue,
  SUM(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM monthly
ORDER BY
  month ASC

STATUScompletedGEN—msEXEC245.44245899960515ms

上下文、请求与日志原始 JSON ↗

月收入环比增长monthly_mom_growth

Sol

按月汇总 2025 年完成订单的净销售额，并使用 LAG 计算环比百分比；首个有收入月份为 NULL。

WITH monthly AS (
  SELECT
    STRFTIME(o.order_date, '%Y-%m') AS month,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS monthly_revenue
  FROM fact_orders AS o
  JOIN fact_order_items AS oi
    ON oi.order_id = o.order_id
  WHERE
    o.status = 'completed'
    AND o.order_date >= CAST('2025-01-01' AS DATE)
    AND o.order_date < CAST('2026-01-01' AS DATE)
  GROUP BY
    1
), with_previous AS (
  SELECT
    month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY month) AS previous_revenue
  FROM monthly
)
SELECT
  month,
  monthly_revenue,
  ROUND(100.0 * (
    monthly_revenue - previous_revenue
  ) / NULLIF(previous_revenue, 0), 2) AS mom_pct
FROM with_previous
ORDER BY
  month ASC

STATUScompletedGEN—msEXEC228.02033300104085ms

上下文、请求与日志原始 JSON ↗

高于平均累计消费客户above_average_customer_spend

Sol

返回累计已完成订单净消费高于有已完成订单客户平均累计净消费的客户，并按消费降序、客户 ID 升序排列。

SELECT
  customer_id,
  customer_name,
  total_spend
FROM (
  SELECT
    customer_id,
    customer_name,
    total_spend,
    AVG(total_spend) OVER () AS avg_total_spend
  FROM (
    SELECT
      c.customer_id,
      c.customer_name,
      SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS total_spend
    FROM fact_orders AS o
    JOIN fact_order_items AS oi
      ON oi.order_id = o.order_id
    JOIN dim_customers AS c
      ON c.customer_id = o.customer_id
    WHERE
      o.status = 'completed'
    GROUP BY
      c.customer_id,
      c.customer_name
  ) AS customer_spend
) AS customer_spend_with_avg
WHERE
  total_spend > avg_total_spend
ORDER BY
  total_spend DESC,
  customer_id ASC

STATUScompletedGEN—msEXEC231.27925000153482ms

上下文、请求与日志原始 JSON ↗

渠道支付状态金额payment_status_by_channel

Sol

按渠道类型汇总支付成功、退款状态和支付失败的金额，并按渠道类型升序排列。

SELECT
  c.channel_type,
  SUM(CASE WHEN p.status = 'paid' THEN p.amount ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN p.status = 'refunded' THEN p.amount ELSE 0 END) AS refunded_amount,
  SUM(CASE WHEN p.status = 'failed' THEN p.amount ELSE 0 END) AS failed_amount
FROM fact_payments AS p
JOIN fact_orders AS o
  ON p.order_id = o.order_id
JOIN dim_channels AS c
  ON o.channel_id = c.channel_id
GROUP BY
  c.channel_type
ORDER BY
  c.channel_type ASC

STATUScompletedGEN—msEXEC200.85850000032224ms

上下文、请求与日志原始 JSON ↗

品类退货率category_return_rate

Sol

分别按品类聚合完成订单的售出数量和退货数量，再计算四舍五入至 4 位的退货率，避免明细表直接联结导致重复计数。

WITH sold_by_category AS (
  SELECT
    p.category,
    SUM(oi.quantity) AS sold_qty
  FROM fact_order_items AS oi
  JOIN fact_orders AS o
    ON o.order_id = oi.order_id
  JOIN dim_products AS p
    ON p.product_id = oi.product_id
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category
), returned_by_category AS (
  SELECT
    p.category,
    SUM(r.return_qty) AS returned_qty
  FROM fact_returns AS r
  JOIN fact_order_items AS oi
    ON oi.order_id = r.order_id AND oi.line_no = r.line_no
  JOIN fact_orders AS o
    ON o.order_id = oi.order_id
  JOIN dim_products AS p
    ON p.product_id = oi.product_id
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category
)
SELECT
  s.category,
  s.sold_qty,
  COALESCE(r.returned_qty, 0) AS returned_qty,
  ROUND(CAST(COALESCE(r.returned_qty, 0) AS DECIMAL(18, 3)) / NULLIF(s.sold_qty, 0), 4) AS return_rate
FROM sold_by_category AS s
LEFT JOIN returned_by_category AS r
  ON r.category = s.category
ORDER BY
  return_rate DESC,
  s.category ASC

STATUScompletedGEN—msEXEC220.4132499973639ms

上下文、请求与日志原始 JSON ↗

每单最新成功支付latest_successful_payment

Sol

为每个有成功支付记录的订单返回最新一笔成功支付；paid_at 相同时选择 payment_id 较大的记录。

SELECT
  order_id,
  payment_id,
  paid_at,
  amount
FROM (
  SELECT
    order_id,
    payment_id,
    paid_at,
    amount,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC, payment_id DESC) AS rn
  FROM fact_payments
  WHERE
    status = 'paid'
) AS ranked_payments
WHERE
  rn = 1
ORDER BY
  order_id ASC

STATUScompletedGEN—msEXEC218.830000001617ms

上下文、请求与日志原始 JSON ↗

运行配置

字段来自运行创建时冻结的快照。

app_version0.1.0

attempts1

case_count12

duckdb_version1.5.5

output_contractquery-plan-v1

scorer_version1.0.0

sqlglot_version30.17.0

suite hash0a4a18b4374f510f5eff18b06272c30c3375e1f082ae405adc8ead7dd9c81556

bundle sha256b8f72f4f616f4077a37a7a180dc1dab0c2e9522dde02b1b42f1738b53c0abb99

report schemarun-report-v1

attempts1