Qwen: Qwen3.5-9B 在「PostgreSQL JSONB字段解析与聚合统计」的评测结果

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

基本信息

  • 模型名称：Qwen: Qwen3.5-9B
  • 用例名称：PostgreSQL JSONB字段解析与聚合统计
  • 测试类型：文本生成
  • 评测维度：SQL 数据库能力

系统提示词（System Prompt）

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

你是一名资深的 PostgreSQL 数据库工程师。 回答要求： 1. 给出完整、可直接执行的 SQL 语句 2. 对关键语法和操作符进行简要注释 3. 说明 JSONB 操作符的含义 4. 最终 SQL 要格式整洁、易于阅读

用户提示词（User Prompt）

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

有一张 PostgreSQL 表，结构如下： ```sql CREATE TABLE app_logs ( id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ NOT NULL, payload JSONB NOT NULL ); ``` 其中 `payload` 字段的示例数据如下： ```json { "level": "error", "error_code": "E1001", "message": "Connection timeout" } ``` 请写一条 SQL 查询，从 `payload` 字段中提取 `error_code` 的值，并只返回 `level` 为 `"error"` 的记录。 要求返回列：`id`、`created_at`、`error_code`（从 JSONB 中提取的文本值）。

任务要求

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

  1. 正确使用 JSONB 操作符（如 `->>` 或 `->>`）提取 `error_code` 字段为文本类型
  2. 正确使用 WHERE 条件过滤 `payload->>'level' = 'error'` 的记录
  3. 返回列包含 `id`、`created_at` 以及提取后的 `error_code`
  4. SQL 语法正确，可在标准 PostgreSQL 环境下执行
  5. 对 JSONB 操作符的用途有简要说明

评测结果

综合得分

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

基础难度评测结果

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

模型生成产物

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

以下是满足您需求的 PostgreSQL 查询语句，包括必要的注释和 JSONB 操作符说明。 ```sql -- 查询 level 为 'error' 的日志记录，提取 id、created_at 和 error_code SELECT id, -- 主键 ID created_at, -- 创建时间 payload->>'error_code' AS error_code -- 从 JSONB 中提取 error_code 字段（返回文本） FROM app_logs WHERE payload->>'level' = 'error'; -- 过滤 level 为 error 的记录 ``` ### 关键 JSONB 操作符说明： | 操作符 | 名称 | 返回值类型 | 说明 | |--------|------|------------|------| | `->` | 获取 JSON 字段值 | JSONB | 返回原始 JSONB 类型，不直接转换为文本 | | `->>` | 获取 JSON 字段值 | TEXT | **最常用**，返回文本，可直接参与字符串比较 | | `#>` | 获取嵌套字段值 | JSONB | 用于数组或嵌套对象路径提取 | | `#>>` | 获取嵌套字段值 | TEXT | 嵌套字段提取，返回文本 | | `@>` | 包含判断 | BOOLEAN | 左侧 JSONB 包含右侧 | | `>'error_code' AS error_code FROM app_logs WHERE payload->>'level' = 'error'; ``` ### 注意事项： 1. **空值处理**：若 `level` 或 `error_code` 字段不存在，`->>` 操作符会返回 `NULL`，不会报错。 2. **类型安全**：使用 `->>` 可直接比较文本，无需类型转换；若使用 `->`，需配合 `::text` 转换。 3. **索引优化**：GIN 索引适用于 JSONB 查询，建议在生产环境中添加。 4. **路径安全**：若需获取嵌套字段，可使用 `payload->'level'->>'message'` 形式。 该查询语句可直接执行，符合 PostgreSQL 规范，适用于生产环境日志分析场景。

AI 评审点评

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

【CLAUDE】候选输出完全满足题目的功能要求，SQL 语句正确可执行，JSONB 操作符使用准确，过滤逻辑和字段提取均无误。解释说明部分超出预期，提供了完整的操作符对照表和性能优化建议，具有较高的实用参考价值。代码格式规范整洁，整体质量优秀，是一份高质量的回答。 【GEMINI】模型表现优秀，不仅完美解决了核心查询需求，还主动提供了性能优化方案和全面的 JSONB 操作符知识库，展现了资深数据库工程师的专业素养。 【KIMI】该候选输出是一份高质量的 PostgreSQL JSONB 查询解答，核心 SQL 完全正确且可直接执行。候选者展现了扎实的数据库知识，不仅满足题目基本要求，还主动提供了丰富的扩展内容（完整操作符参考表、性能优化建议、注意事项等），体现了资深工程师的专业素养。 minor 不足在于操作符与操作数间的空格使用不够统一，以及扩展内容对于 basic 难度题目略显冗余，但整体上是一份优秀的答案。

进阶难度评测结果

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

模型生成产物

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

```sql SELECT -- 1. 时间处理：将 created_at 截断到小时精度，并转换为 BIGINT (Unix 时间戳) DATE_TRUNC('hour', created_at)::BIGINT AS hour, -- 2. JSON 提取：从 payload 中获取 error_code 字段 payload->>'error_code' AS error_code, -- 3. 聚合函数：统计该小时内该错误码的记录数量 COUNT(*) AS error_count FROM app_logs WHERE -- 4. 过滤条件：仅筛选 level 为 'error' 的记录 -- 注意：->> 操作符将 JSON 中的值提取为 TEXT 以便进行字符串比较 payload->>'level' = 'error' GROUP BY -- 5. 分组依据：按截断后的小时时间戳 和 错误码 进行分组 DATE_TRUNC('hour', created_at), payload->>'error_code' ORDER BY -- 6. 排序规则：先按小时升序，再按出现次数降序 hour ASC, error_count DESC; ``` ### 关键步骤与函数说明 | 步骤 | 关键代码/函数 | 作用说明 | | :--- | :--- | :--- | | **JSON 提取** | `payload->>'level'``payload->>'error_code'` | `->>` 是 PostgreSQL 的 JSONB 提取操作符，返回**文本 (TEXT)**。对比字符串时使用 `->>` 而非 `->` (返回 JSON 对象)，能避免类型转换错误并提高可读性。 | | **时间截断** | `DATE_TRUNC('hour', ...)` | 将时间戳截断到指定的时间单位（此处为小时）。例如 `2023-10-01 01:30:00` 会被截断为 `2023-10-01 01:00:00`。 | | **类型转换** | `::BIGINT` | 将截断后的 `TIMESTAMPTZ` 转换为 **BIGINT**。这在语义上代表"Unix 时间戳"（秒级整数），便于数值排序和作为整点时间标识。 | | **分组聚合** | `GROUP BY ...``COUNT(*)` | PostgreSQL 要求非聚合列必须在 `GROUP BY` 中显式列出。`DATE_TRUNC` 后的结果在 `GROUP BY` 中使用表达式而非别名是最佳实践，以确保兼容性。 | | **排序** | `ORDER BY hour ASC, error_count DESC` | 符合需求：先按时间先后（升序），同一小时内按错误发生频次（降序）排列。 | ### 工程师建议 1. **索引优化**：如果数据量很大，建议在 `payload` 字段上建立 **GIN 索引**，以加速 JSONB 字段的查询过滤。 ```sql CREATE INDEX idx_logs_payload ON app_logs USING GIN (payload); -- 或者针对特定字段建立表达式索引 -- CREATE INDEX idx_logs_level ON app_logs ((payload->>'level')); ``` 2. **空值处理**：如果 `error_code` 可能缺失（即不存在该 key），`->>` 会返回 `NULL`，`GROUP BY` 会将它们归为 `NULL` 组。如果需要排除错误码缺失的记录，可在 `WHERE` 子句中添加 `payload->>'error_code' IS NOT NULL`。

AI 评审点评

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

【CLAUDE】候选输出整体结构清晰，涵盖了题目所有核心要求（JSONB 提取、时间截断、分组聚合、排序），SQL 可执行，注释说明详细。主要问题在于将 `DATE_TRUNC` 结果强转为 `::BIGINT`，这偏离了题目要求返回「整点时间戳」列的语义（应保持 TIMESTAMPTZ 类型），且 SELECT 与 GROUP BY 中的表达式不一致。此外，将 BIGINT 描述为「整点时间标识」的最佳实践存在误导性。整体而言是一份有一定质量的答案，但因类型处理偏差影响了正确性得分。 【GEMINI】模型表现优秀，代码结构清晰，注释专业。唯一的问题在于对 `DATE_TRUNC` 结果进行 `::BIGINT` 转换的尝试在 PostgreSQL 中是无效语法，这会影响 SQL 的直接可执行性。建议在处理时间戳时保留其日期时间类型，或使用 `EXTRACT(EPOCH FROM ...)` 进行转换。 【KIMI】该候选输出整体质量良好，核心功能实现正确，解释说明专业且完整。主要扣分点在于不必要的 `::BIGINT` 类型转换，导致返回列的数据类型从「整点时间戳」变为「Unix 时间戳整数」，偏离题目明确要求。建议去掉类型转换，直接使用 `date_trunc('hour', created_at)` 的结果作为 `hour` 列，以完全符合需求。此外，操作符周围的空格格式可进一步优化。

困难难度评测结果

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

模型生成产物

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

你好！作为一名资深 PostgreSQL 工程师，我为你设计了如下 SQL 查询方案。该方案利用 CTE（公用表表达式）将逻辑拆分为清晰的四个步骤，充分利用了 JSONB 操作符、窗口函数以及聚合分析。 ### SQL 查询代码 ```sql -- 启用 GIN 索引优化 JSONB 查询性能 (建议在实际环境中执行) -- CREATE INDEX IF NOT EXISTS idx_app_logs_payload ON app_logs USING GIN (payload); WITH -- CTE 1: 数据过滤与字段提取 -- 作用： -- 1. 筛选 level='error' 的记录。 -- 2. 确保 error_code 字段存在且不为空。 -- 3. 确保 tags 数组中包含 'critical' 标签。 -- 4. 提前规范化时间戳，提取小时。 filtered_logs AS ( SELECT created_at, payload->>'level' AS log_level, payload->'error'->>'code' AS error_code, payload->'tags' @> '["critical"]' AS is_critical, date_trunc('hour', created_at) AS hour FROM app_logs WHERE -- JSONB 路径提取与字符串比较 payload->>'level' = 'error' -- 确保 error 对象存在且 code 字段不为 NULL AND payload->'error'->>'code' IS NOT NULL -- 检查 tags 数组是否包含 'critical' 元素 -- @> 操作符用于 JSONB 数组包含性检查 AND payload->'tags' @> '["critical"]' ), -- CTE 2: 按小时和错误码聚合 -- 作用： -- 将过滤后的记录按 "hour" 和 "error_code" 分组，统计原始记录数量。 hourly_stats AS ( SELECT hour, error_code, COUNT(*) AS error_count -- 使用 COUNT(*) 避免 NULL 值计数 FROM filtered_logs GROUP BY 1, 2 -- 按 hour 和 error_code 分组 ), -- CTE 3: 窗口排名 -- 作用： -- 在每个小时内，按错误次数降序计算排名。 -- 只保留每小时内排名前 3 的错误码。 -- 注意：RANK() 允许并列（Tie），若需严格限制行数建议改用 ROW_NUMBER()。 ranked_stats AS ( SELECT hour, error_code, error_count, RANK() OVER ( PARTITION BY hour -- 按小时分区 ORDER BY error_count DESC -- 按错误次数降序排列 ) AS rank_in_hour FROM hourly_stats WHERE rank_in_hour >` 操作符将 JSONB 路径转换为文本以便比较。 * **关键操作**： * `payload->'tags' @> '["critical"]'`：利用 JSONB 的包含操作符高效判断数组中是否包含特定字符串，比展开数组后 `IN` 操作更高效且支持索引。 * `date_trunc('hour', ...)`：将时间戳截断到小时级别，作为分组的基准键。 2. **`hourly_stats` (CTE 2)** * **核心逻辑**：数据聚合。 * **关键操作**：`GROUP BY 1, 2` 对小时和错误码进行组合分组，计算 `error_count`。这是后续窗口函数计算的基础。 3. **`ranked_stats` (CTE 3)** * **核心逻辑**：窗口排名。 * **关键操作**：`RANK() OVER (PARTITION BY hour ...)`。 * **排名逻辑**：`PARTITION BY` 确保排名在每个小时内独立计算。`ORDER BY error_count DESC` 确保次数多的排在前面。 4. **`final_stats` (CTE 4)** * **核心逻辑**：时间序列分析（环比）。 * **关键操作**：`LAG(error_count) OVER (PARTITION BY error_code ORDER BY hour)`。 *...

AI 评审点评

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

【KIMI】整体而言，候选人提供的 SQL 查询方案在功能实现上较为完整，正确处理了 JSONB 字段的提取、过滤、聚合统计以及环比变化量的计算。代码结构清晰，注释有助于理解。但在边界情况的讨论上还有提升空间，特别是对可能出现的异常或特殊情况的考虑不够全面。

相关链接

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

加载中...
100%