拾拾穗数据工作室SQL 模型评测台

完成但有错误 · 2026年8月30日 06:05

运行 #16 评测报告

GPT 当前会话桥接（流程验收），18 道题。综合得分为规则加权分，不是正确率。

比较条件存在差异：single_model。阅读模型间差距时必须保留这一前提。

失败记录：2 个失败案例已按固定规则计分，详情见逐题结果。

逐题结果原始报告 JSON ↗事件 JSONL ↗
gpt-session-bridge

GPT 当前会话桥接（流程验收）

综合得分 / 100
87.31
18题目数量
1每题尝试次数
2失败案例

样本边界：结论仅适用于此题库哈希、模型版本、适配器和参数快照。跨版本、跨运行稳定性需要独立复测。

得分与资源消耗

新 efficiency-v2 按业务结果正确题数归一；旧 efficiency-v1 保留综合得分 ÷ 100 的折算口径，两种指标不能混排。记录不完整时不估算单位正确题消耗。

指标GPT 当前会话桥接（流程验收）
综合得分
固定规则加权 / 100		87.31
Token / 归一单位2,953每得分折算题 · 17/18 题有记录
费用 / 归一单位
基于冻结价格；缺失不估		不可估算每得分折算题 · unavailable
生成耗时中位数
模型生成阶段		—P95 —

分项得分

分项为加权得分，不是业务结果正确率。

题目维度GPT 当前会话桥接（流程验收）
基础查询100.00
复杂查询96.38
数据开发92.50
时间与窗口68.33
聚合与指标100.00
连接与粒度66.67

逐题结果

得分为全部计划尝试的均分，失败计入分母，未完成显示未知。展开可查看每次作答。

基础筛选与排序basic_filter_sort100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

筛选 2026-01-01 起的已完成订单，连接客户维表获取客户名称，输出订单字段并按金额降序、订单号升序排列。

结果正确（页面派生）

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
查看 A1 完整证据
多表连接品类收入multi_join_category_revenue100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

连接订单、订单行和商品表，筛选已完成订单后按品类统计 distinct 订单数与订单行净销售额，并按指定顺序排序。

结果正确（页面派生）

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
查看 A1 完整证据
保留零订单客户left_join_zero_orders100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

以客户表为主表 LEFT JOIN 订单表，通过条件去重计数统计已完成订单，保留零订单客户并按指定顺序排序。

结果正确（页面派生）

SELECT
  c.customer_id,
  c.customer_name,
  COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.order_id END) AS completed_order_count
FROM dim_customers AS c
LEFT JOIN fact_orders AS o
  ON o.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.customer_name
ORDER BY
  completed_order_count DESC,
  c.customer_id ASC
查看 A1 完整证据
相关子查询分群均值correlated_segment_average100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

连接已完成订单与客户，并通过引用外层客户分群的相关子查询计算该分群已完成订单平均金额，再筛选高于均值的订单并按要求排序。

结果正确（页面派生）

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
查看 A1 完整证据
从未完成订单客户not_exists_customers100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

从客户主表出发，使用相关 NOT EXISTS 子查询排除任何存在已完成订单的客户，并按 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
查看 A1 完整证据
每品类收入前三商品top3_products_per_category100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

先汇总完成订单中各品类商品的净销售额，再用 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
  INNER JOIN fact_orders AS o
    ON oi.order_id = o.order_id
  INNER 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
查看 A1 完整证据
月收入与累计收入monthly_running_revenue100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

按 2025 年月份汇总已完成订单商品行净销售额，并使用按 month 排序的 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
  INNER 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
    STRFTIME(o.order_date, '%Y-%m')
)
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
查看 A1 完整证据
月收入环比增长monthly_mom_growth5.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果未完全正确（页面派生）

A1 · 失败 · 5.00

按完成订单的订单行净额汇总 2025 年月收入，补齐 12 个日历月，并用 LAG 计算相邻月份的收入环比。

结果未完全正确（页面派生）

查看 A1 完整证据
高于平均累计消费客户above_average_customer_spend89.15

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果未完全正确（页面派生）

A1 · 完成 · 89.15

先汇总每位有已完成订单客户的订单行净销售额，再通过外层派生表计算客户平均累计消费，筛出高于平均值者并按指定顺序输出。

结果未完全正确（页面派生）

SELECT
  customer_id,
  customer_name,
  total_spend
FROM (
  SELECT
    customer_spend.customer_id,
    customer_spend.customer_name,
    customer_spend.total_spend,
    AVG(customer_spend.total_spend) OVER () AS avg_total_spend
  FROM (
    SELECT
      c.customer_id,
      c.customer_name,
      SUM(
        COALESCE(oi.quantity * oi.unit_price - oi.discount_amount, CAST(0 AS DECIMAL(14, 2)))
      ) AS total_spend
    FROM fact_orders AS o
    INNER JOIN dim_customers AS c
      ON o.customer_id = c.customer_id
    LEFT JOIN fact_order_items AS oi
      ON o.order_id = oi.order_id
    WHERE
      o.status = 'completed'
    GROUP BY
      c.customer_id,
      c.customer_name
  ) AS customer_spend
) AS customer_spend_with_average
WHERE
  total_spend > avg_total_spend
ORDER BY
  total_spend DESC,
  customer_id ASC
查看 A1 完整证据
渠道支付状态金额payment_status_by_channel100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

将支付记录经订单关联到渠道，按渠道类型分组，并使用三组 SUM(CASE WHEN ...) 分别汇总 paid、refunded 和 failed 状态金额。

结果正确（页面派生）

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
查看 A1 完整证据
品类退货率category_return_rate92.50

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 92.50

分别在品类粒度聚合已完成订单的售出数量与退货数量，再连接聚合结果计算四舍五入至 4 位的退货率，避免明细连接导致 fan-out。

结果正确（页面派生）

WITH sold_by_category AS (
  SELECT
    p.category,
    SUM(oi.quantity) AS sold_qty
  FROM fact_orders AS o
  INNER JOIN fact_order_items AS oi
    ON o.order_id = oi.order_id
  INNER JOIN dim_products AS p
    ON oi.product_id = p.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
  INNER JOIN fact_order_items AS oi
    ON r.order_id = oi.order_id AND r.line_no = oi.line_no
  INNER JOIN fact_orders AS o
    ON oi.order_id = o.order_id
  INNER JOIN dim_products AS p
    ON oi.product_id = p.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(38, 10)) / NULLIF(s.sold_qty, 0), 4) AS return_rate
FROM sold_by_category AS s
LEFT JOIN returned_by_category AS r
  ON s.category = r.category
ORDER BY
  return_rate DESC,
  s.category ASC
查看 A1 完整证据
每单最新成功支付latest_successful_payment85.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 85.00

从成功支付记录中使用 ROW_NUMBER 为每个订单按支付时间及 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
查看 A1 完整证据
缺失城市标准化null_city_label100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

从客户维表筛选 city 为 NULL 的客户，以“未知”作为城市标签，并按 customer_id 升序输出。

结果正确（页面派生）

SELECT
  customer_id,
  customer_name,
  COALESCE(city, '未知') AS city_label
FROM dim_customers
WHERE
  city IS NULL
ORDER BY
  customer_id ASC
查看 A1 完整证据
已完成订单金额分层completed_order_amount_band100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

筛选 2026 年已完成订单，使用 CASE 按订单头 total_amount 分层，并按金额降序、订单号升序输出。

结果正确（页面派生）

SELECT
  order_id,
  total_amount,
  CASE
    WHEN total_amount >= 2000
    THEN 'high'
    WHEN total_amount >= 1000
    THEN 'medium'
    ELSE 'low'
  END AS amount_band
FROM fact_orders
WHERE
  status = 'completed'
  AND order_date >= CAST('2026-01-01' AS DATE)
  AND order_date < CAST('2027-01-01' AS DATE)
ORDER BY
  total_amount DESC,
  order_id ASC
查看 A1 完整证据
全渠道经营表现all_channel_performance0.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确性未知

A1 · 失败 · 0.00

模型输出不是严格 JSON，且不符合单层 json fence 恢复规则

结果正确性未知

查看 A1 完整证据
渠道核心指标semantic_channel_kpis100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

按 channel_type 分别预聚合完成订单、完成订单行净销售额和支付状态金额，再连接各聚合结果，避免订单行与支付记录直接连接导致重复计算。

结果正确（页面派生）

WITH channel_types AS (
  SELECT DISTINCT
    channel_type
  FROM dim_channels
), completed_orders AS (
  SELECT
    c.channel_type,
    COUNT(DISTINCT o.order_id) AS completed_order_count
  FROM fact_orders AS o
  INNER JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  WHERE
    o.status = 'completed'
  GROUP BY
    c.channel_type
), net_revenue AS (
  SELECT
    c.channel_type,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_order_items AS oi
  INNER JOIN fact_orders AS o
    ON oi.order_id = o.order_id
  INNER JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  WHERE
    o.status = 'completed'
  GROUP BY
    c.channel_type
), payment_amounts AS (
  SELECT
    c.channel_type,
    SUM(CASE WHEN p.status = 'paid' THEN p.amount ELSE CAST(0 AS DECIMAL(14, 2)) END) AS paid_amount,
    SUM(CASE WHEN p.status = 'refunded' THEN p.amount ELSE CAST(0 AS DECIMAL(14, 2)) END) AS refunded_amount,
    SUM(CASE WHEN p.status = 'failed' THEN p.amount ELSE CAST(0 AS DECIMAL(14, 2)) END) AS failed_amount
  FROM fact_payments AS p
  INNER JOIN fact_orders AS o
    ON p.order_id = o.order_id
  INNER JOIN dim_channels AS c
    ON o.channel_id = c.channel_id
  GROUP BY
    c.channel_type
)
SELECT
  ct.channel_type,
  COALESCE(co.completed_order_count, 0) AS completed_order_count,
  COALESCE(nr.net_revenue, CAST(0 AS DECIMAL(14, 2))) AS net_revenue,
  COALESCE(pa.paid_amount, CAST(0 AS DECIMAL(14, 2))) AS paid_amount,
  COALESCE(pa.refunded_amount, CAST(0 AS DECIMAL(14, 2))) AS refunded_amount,
  COALESCE(pa.failed_amount, CAST(0 AS DECIMAL(14, 2))) AS failed_amount
FROM channel_types AS ct
LEFT JOIN completed_orders AS co
  ON ct.channel_type = co.channel_type
LEFT JOIN net_revenue AS nr
  ON ct.channel_type = nr.channel_type
LEFT JOIN payment_amounts AS pa
  ON ct.channel_type = pa.channel_type
ORDER BY
  ct.channel_type ASC
查看 A1 完整证据
品类收入贡献占比category_revenue_share100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

按商品品类汇总完成订单行的净销售额，再以全部品类净销售额为分母计算百分比占比并四舍五入至 2 位，最后按指定顺序排序。

结果正确（页面派生）

WITH category_revenue AS (
  SELECT
    p.category,
    SUM(oi.quantity * oi.unit_price - oi.discount_amount) AS net_revenue
  FROM fact_orders AS o
  INNER JOIN fact_order_items AS oi
    ON oi.order_id = o.order_id
  INNER JOIN dim_products AS p
    ON p.product_id = oi.product_id
  WHERE
    o.status = 'completed'
  GROUP BY
    p.category
), category_metrics AS (
  SELECT
    category,
    net_revenue,
    ROUND(100.0 * net_revenue / NULLIF(SUM(net_revenue) OVER (), 0), 2) AS revenue_share_pct
  FROM category_revenue
)
SELECT
  category,
  net_revenue,
  revenue_share_pct
FROM category_metrics
ORDER BY
  revenue_share_pct DESC,
  category ASC
查看 A1 完整证据
订单头明细金额对账order_total_reconciliation100.00

GPT 当前会话桥接（流程验收）

全部 1 次计划作答 · 结果正确（页面派生）

A1 · 完成 · 100.00

汇总每个订单的订单行净额，与订单头 total_amount 在 2 位小数精度下对账，仅输出有差异的订单并按指定顺序排列。

结果正确（页面派生）

WITH reconciliation AS (
  SELECT
    o.order_id,
    ROUND(o.total_amount, 2) AS stored_total,
    ROUND(COALESCE(SUM(oi.quantity * oi.unit_price - oi.discount_amount), 0), 2) AS calculated_total
  FROM fact_orders AS o
  LEFT JOIN fact_order_items AS oi
    ON o.order_id = oi.order_id
  GROUP BY
    o.order_id,
    o.total_amount
), differences AS (
  SELECT
    order_id,
    stored_total,
    calculated_total,
    ROUND(stored_total - calculated_total, 2) AS difference
  FROM reconciliation
  WHERE
    stored_total <> calculated_total
)
SELECT
  order_id,
  stored_total,
  calculated_total,
  difference
FROM differences
ORDER BY
  ABS(difference) DESC,
  order_id ASC
查看 A1 完整证据

配置与校验摘要

复测需使用对应的冻结配置和题库哈希。同一模型名不一定对应同一模型版本。

app_version0.1.0
attempts1
case_count18
duckdb_version1.5.5
output_contractquery-plan-v1
scorer_version1.0.0
sqlglot_version30.17.0
suite hash5b5d98876ea35114f18ce6dfa48cc9800d88b6baba80d311b2f52552a38b31af
bundle sha256d9058da24c959b3562efdcef6263fa9e5ce1deee644f2ae14ad6853ad3b4d724
report schemarun-report-v1
报告 JSON ↗题库版本评分方法