Elephant 在「多表关联与窗口函数综合查询」的评测结果

这是该 AI 模型在此测试用例上的详细评测结果。

基本信息

  • 模型名称：Elephant
  • 用例名称：多表关联与窗口函数综合查询
  • 测试类型：文本生成
  • 评测维度：SQL 数据库能力

系统提示词（System Prompt）

这是给 AI 模型的背景设定和角色指令：

你是一名资深数据库工程师，擅长编写清晰、高效的 SQL 查询。 回答要求： 1. 给出完整可执行的 SQL 语句 2. 对关键子句添加注释说明用途 3. 使用规范的 SQL 格式（关键字大写，适当缩进） 4. 最终查询结果字段要有明确的别名

用户提示词（User Prompt）

这是用户给 AI 模型的具体任务要求：

现有如下电商数据库表结构： ```sql -- 商品表 CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), category_id INT ); -- 订单明细表 CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2) ); ``` 请编写一条 SQL 查询，统计每个商品的总销售额（quantity × unit_price 之和），并按总销售额从高到低排序，返回以下字段： - product_id - product_name - category_id - total_sales（总销售额） 只需返回总销售额大于 0 的商品。

任务要求

AI 模型需要满足以下要求：

  1. 正确使用 JOIN 将 products 与 order_items 关联
  2. 正确计算总销售额：SUM(quantity * unit_price)
  3. 使用 GROUP BY 按商品分组
  4. 使用 HAVING 或 WHERE 过滤总销售额大于 0 的记录
  5. 结果按总销售额降序排列，字段别名清晰

评测结果

综合得分

该模型在此用例的得分为：94.7 分（满分 100 分），已通过

基础难度评测结果

  • 得分：94.7 分
  • 通过状态：已通过

模型生成产物

以下是该模型针对此任务的实际生成内容：

```sql SELECT p.product_id, -- 商品ID p.product_name, -- 商品名称 p.category_id, -- 分类ID SUM(oi.quantity * oi.unit_price) AS total_sales -- 计算总销售额（数量 × 单价） FROM products p -- 从商品表获取商品信息 INNER JOIN order_items oi -- 内连接订单明细表，确保只统计有订单的商品 ON p.product_id = oi.product_id -- 通过商品ID关联两张表 GROUP BY p.product_id, -- 按商品ID分组 p.product_name, -- 包含商品名称以满足GROUP BY要求 p.category_id -- 包含分类ID以满足GROUP BY要求 HAVING SUM(oi.quantity * oi.unit_price) > 0 -- 筛选总销售额大于0的商品 ORDER BY total_sales DESC; -- 按总销售额从高到低排序 ```

AI 评审点评

以下是 AI 评审对该模型输出的点评：

【GEMINI】该模型生成的 SQL 语句逻辑严谨、执行效率高，完全符合电商业务场景下的统计需求。代码书写美观，注释到位，是一个高质量的回答。唯一的小瑕疵是对于非计算字段没有按照提示要求显式地添加 AS 别名，但在实际数据库操作中不影响结果正确性。 【KIMI】整体来看，这是一个高质量的 SQL 查询。它正确地使用了 JOIN、GROUP BY 和 HAVING 子句，计算了总销售额并按要求过滤和排序。SQL 书写规范，注释详细，可读性高。唯一的小瑕疵是 GROUP BY 子句中包含了 product_name 和 category_id，虽然这并不影响查询结果的正确性，但从理论上讲，这两个字段并不需要出现在 GROUP BY 子句中，因为它们不是聚合函数的一部分。不过，考虑到实际执行时这两个字段作为分组依据并不会导致错误，且可以提高查询的健壮性，因此这个问题并不严重。总的来说，这是一个优秀的 SQL 查询。

进阶难度评测结果

  • 得分：94.0 分
  • 通过状态：已通过

模型生成产物

以下是该模型针对此任务的实际生成内容：

```sql WITH -- CTE 1: 计算每个商品的总销售额（quantity × unit_price 之和） product_sales AS ( SELECT p.product_id, p.product_name, p.category_id, SUM(oi.quantity * oi.unit_price) AS total_sales FROM products p JOIN order_items oi ON p.product_id = oi.product_id GROUP BY p.product_id, p.product_name, p.category_id ), -- CTE 2: 使用窗口函数计算每个商品在其所属类目内的销售额排名 ranked_products AS ( SELECT ps.product_id, ps.product_name, ps.category_id, ps.total_sales, -- 使用 RANK() 窗口函数，按类目分组并按总销售额降序排名 RANK() OVER (PARTITION BY ps.category_id ORDER BY ps.total_sales DESC) AS sales_rank FROM product_sales ps ) -- 最终查询：连接类目表，筛选每个类目前3名，并按要求排序 SELECT c.category_name, -- 类目名称 rp.product_name, -- 商品名称 rp.total_sales, -- 总销售额 rp.sales_rank -- 类目内销售额排名 FROM ranked_products rp JOIN categories c ON rp.category_id = c.category_id WHERE rp.sales_rank <= 3 -- 仅保留每个类目中排名前3的商品 ORDER BY c.category_name ASC, -- 按类目名称升序 rp.sales_rank ASC; -- 按销售额排名升序 ```

AI 评审点评

以下是 AI 评审对该模型输出的点评：

【GEMINI】这是一份教科书级别的 SQL 答案。模型不仅准确完成了复杂的分析查询逻辑，还在代码风格、注释规范和逻辑拆分上表现出色。采用在最后阶段关联类目表的做法在性能上也具有一定的优越性（减少了参与 Join 的数据量）。 【KIMI】整体而言，候选人提供的 SQL 语句很好地满足了题目要求，逻辑正确，结构清晰，完整性良好。只是在解释 RANK() 函数选择方面稍显不足，但这不影响 SQL 语句的正确性和执行结果。

困难难度评测结果

  • 得分：94.7 分
  • 通过状态：已通过

模型生成产物

以下是该模型针对此任务的实际生成内容：

```sql WITH -- CTE 1: 筛选有效订单（仅 completed 状态且在 2024 年） valid_orders AS ( SELECT order_id, user_id, order_date FROM orders WHERE status = 'completed' AND order_date >= '2024-01-01' AND order_date <= '2024-12-31' ), -- CTE 2: 计算每个商品的总销售额（连接商品、类目及有效订单明细） product_sales AS ( SELECT p.product_id, p.product_name, p.category_id, c.category_name, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_sales FROM products p INNER JOIN categories c ON p.category_id = c.category_id INNER JOIN order_items oi ON p.product_id = oi.product_id INNER JOIN valid_orders vo ON oi.order_id = vo.order_id GROUP BY p.product_id, p.product_name, p.category_id, c.category_name ), -- CTE 3: 计算类目中每个商品的销售额排名（使用 RANK 处理并列） ranked_products AS ( SELECT category_id, category_name, product_id, product_name, total_sales, RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS sales_rank FROM product_sales ), -- CTE 4: 计算每个类目的平均销售额（基于所有有销售记录的商品） category_avg AS ( SELECT category_id, category_name, ROUND(AVG(total_sales), 2) AS category_avg_sales FROM product_sales GROUP BY category_id, category_name ) -- 主查询：筛选排名前三的商品，计算与类目平均销售额的偏差百分比 SELECT rp.category_name, rp.product_name, rp.total_sales, rp.sales_rank, ca.category_avg_sales, ROUND((rp.total_sales - ca.category_avg_sales) / ca.category_avg_sales * 100, 2) AS deviation_pct FROM ranked_products rp INNER JOIN category_avg ca ON rp.category_id = ca.category_id WHERE rp.sales_rank <= 3 ORDER BY rp.category_name ASC, rp.sales_rank ASC; ``` **关于并列第 3 名的处理说明：** 若某类目内多个商品销售额并列第 3 名，`RANK()` 函数会赋予它们相同的排名（如 1, 2, 3, 3, 3），导致后续排名跳过（例如下一个为第 6 名），因此 `WHERE sales_rank <= 3` 可能返回超过 3 条记录。 这种处理是合理的，因为它真实反映了并列情况，避免了人为截断造成的数据偏差，符合业务中“公平展示”的需求。

AI 评审点评

以下是 AI 评审对该模型输出的点评：

【GEMINI】这是一份非常优秀的 SQL 编写示例。模型展现了深厚的 SQL 功底，不仅逻辑严密，而且代码结构优雅。通过多层 CTE 将复杂的逻辑拆解得非常清晰，注释详尽。对窗口函数 RANK() 的运用准确，计算类目均值时采用了独立的 CTE 关联，虽然也可以使用窗口聚合函数简化，但目前的写法逻辑同样严谨且易于理解。边界情况的说明准确且具备业务洞察力。 【KIMI】该SQL语句整体质量较高，逻辑正确，结构清晰，返回了所有要求字段，并对边界情况给出了合理说明。但在部分注释的详细程度上还有提升空间。

相关链接

您可以通过以下链接查看更多相关内容：

加载中...
100%