GLM-5.1 在「PostgreSQL JSONB字段解析与聚合统计」的评测结果

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

基本信息

  • 模型名称：GLM-5.1
  • 用例名称：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 操作符的用途有简要说明

评测结果

综合得分

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

基础难度评测结果

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

模型生成产物

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

你好！作为一名 PostgreSQL 数据库工程师，我为你编写了以下 SQL 查询语句。 为了满足不同场景下的性能需求，我提供了两种写法：**标准提取法**（简单直观）和 **包含查询法**（适合大数据量且建立了 GIN 索引的场景）。 ### 推荐方案一：标准 JSONB 提取（最直观） ```sql SELECT id, created_at, payload ->> 'error_code' AS error_code -- 使用 ->> 操作符提取键值为文本 FROM app_logs WHERE payload ->> 'level' = 'error'; -- 筛选 level 键的文本值为 'error' 的记录 ``` ### 推荐方案二：JSONB 包含查询（性能更优） 如果你的表数据量很大，并且 `payload` 字段上建立了 `GIN` 索引（如 `CREATE INDEX idx_app_logs_payload ON app_logs USING GIN (payload);`），推荐使用 `@>` 操作符，这会让查询走索引，速度极快。 ```sql SELECT id, created_at, payload ->> 'error_code' AS error_code -- 提取 error_code FROM app_logs WHERE payload @> '{"level": "error"}'::jsonb; -- 使用包含操作符查询包含指定键值对的记录 ``` --- ### 💡 JSONB 关键操作符说明： * **`->` (Get JSON object field)**：提取 JSONB 中的指定键，**返回的仍然是 JSONB 类型**。例如 `payload -> 'level'` 返回的是 `"error"`（带双引号的 JSON 字符串）。 * **`->>` (Get JSON object field as text)**：提取 JSONB 中的指定键，**返回的是 TEXT 纯文本类型**。例如 `payload ->> 'level'` 返回的是 `error`（不带外层引号的纯文本）。在上面的查询中，由于我们需要提取具体的错误码字符串，所以使用 `->>` 最为合适。 * **`@>` (Contains)**：判断左侧的 JSONB 对象是否包含右侧的顶层键/值对。这是 PostgreSQL 处理 JSONB 最为强大的操作符之一，因为它能完美配合 GIN 索引实现高速检索。

AI 评审点评

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

【CLAUDE】候选输出质量优秀，不仅完整满足了题目的所有要求，还额外提供了基于 GIN 索引的性能优化方案，体现了资深工程师的实践经验。SQL 语法正确，注释详尽，格式规范，对 JSONB 操作符的解释清晰准确，整体表现超出参考答案水平。 【GEMINI】该回答表现极其出色。不仅完美完成了题目要求的提取和过滤任务，还充分体现了‘资深 PostgreSQL 工程师’的身份，主动提供了针对大数据量场景的性能优化方案（使用 @> 操作符配合 GIN 索引）。解释部分深入浅出，对操作符的细微差别（如是否带引号）讲解得非常到位。 【KIMI】整体而言，该SQL语句在功能实现和代码规范方面都表现良好，基本满足了题目要求。但在解释说明方面还有提升空间，需要对`@>`操作符的适用场景和使用方法进行更清晰的说明。

进阶难度评测结果

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

模型生成产物

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

以下是满足需求的完整 SQL 查询及详细说明： ### 完整 SQL 语句 ```sql SELECT -- 步骤1：时间截断。使用 date_trunc 将 created_at 截断到小时，获取整点时间戳 date_trunc('hour', created_at) AS hour, -- 步骤2：JSONB 提取。使用 ->> 操作符以文本格式提取 payload 中的 error_code payload ->> 'error_code' AS error_code, -- 步骤3：分组聚合。统计每个分组内的记录数 COUNT(*) AS error_count FROM app_logs WHERE -- 过滤条件：提取 payload 中的 level 字段，并筛选值为 'error' 的记录 payload ->> 'level' = 'error' GROUP BY -- 按整点时间和错误码进行分组 date_trunc('hour', created_at), payload ->> 'error_code' ORDER BY -- 排序规则1：按时间升序排列 hour ASC, -- 排序规则2：按出现次数降序排列 error_count DESC; ``` --- ### 关键步骤与函数说明 #### 1. JSONB 提取 (`->>` 操作符) * **用法**：`payload ->> 'level'` 和 `payload ->> 'error_code'` * **作用**：`->>` 是 PostgreSQL 的 JSONB 操作符，用于**按键名提取 JSON 对象中的值，并将其作为 `text` 类型返回**。 * **补充**：如果使用单箭头 `->`（如 `payload -> 'level'`），则返回的依然是 `jsonb` 类型。在 WHERE 条件中比较时，使用 `->>` 返回文本与 `'error'` 比较性能更好且更直观；而在需要继续进行 JSON 操作时才使用 `->`。 #### 2. 时间截断 (`date_trunc` 函数) * **用法**：`date_trunc('hour', created_at)` * **作用**：将时间戳截断到指定的精度。在这里，它会将 `created_at` 的分钟、秒和微秒全部置为 0，从而得到该时间所属的**整点时间戳**（例如 `2023-10-25 14:35:20` 会变为 `2023-10-25 14:00:00`）。这是按时间维度分组统计的标准做法。 #### 3. 分组聚合 (`GROUP BY` 与 `COUNT`) * **用法**：`GROUP BY date_trunc(...), payload ->> 'error_code'` 配合 `COUNT(*)` * **作用**：将相同小时、相同错误码的记录归为同一组，`COUNT(*)` 计算该组内的行数，即该错误码在该小时内出现的总次数。 #### 💡 性能优化建议（进阶） 如果该表数据量巨大，且频繁按 JSON 内部的 `level` 字段过滤，建议创建一个 GIN 索引来加速 WHERE 过滤： ```sql CREATE INDEX idx_app_logs_payload_level ON app_logs USING gin (payload jsonb_path_ops); ``` 此时，WHERE 条件可以改写为包含操作符的形式，以命中 GIN 索引： ```sql WHERE payload @> '{"level": "error"}'::jsonb ``` `@>` 操作符的作用是检查左侧 JSONB 是否包含右侧的键值对，这在数据量大时查询效率远高于 `->>` 的文本提取比较。

AI 评审点评

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

【CLAUDE】候选答案质量优秀。SQL 语句在功能实现上完全正确，所有核心要求（JSONB 提取、时间截断、条件过滤、分组聚合、复合排序）均准确无误且可直接执行。解释说明详尽透彻，不仅对每个步骤进行了注释，还专门设立章节深入讲解关键函数和操作符的用法与区别，甚至提供了 GIN 索引等性能优化建议，展现了较强的工程实践能力。代码格式规范整洁，注释清晰，可读性强。整体表现达到了专业 PostgreSQL 工程师的水准，是一份高质量的技术答案。 【GEMINI】这是一份完美的回答。模型不仅准确地完成了所有技术要求，还提供了深入的函数原理解释和进阶的性能优化建议，完全符合“资深 PostgreSQL 数据库工程师”的人设定位。 【KIMI】该候选输出是一份高质量的 PostgreSQL SQL 解答，完全满足题目所有功能要求，JSONB 操作符和时间函数使用准确，分组聚合逻辑无误，排序条件正确。解释说明部分不仅覆盖了基础知识点，还提供了进阶的性能优化建议，体现了深厚的专业功底。代码格式规范，注释清晰，整体可读性强。 minor 改进空间在于 GROUP BY 可更简洁地引用列位置，以及优化建议部分可更明确标注为可选方案而非推荐替代。

困难难度评测结果

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

模型生成产物

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

你好！处理包含复杂 JSONB 的日志数据、结合窗口函数进行多维聚合与环比分析，正是 PostgreSQL 的强项。 针对你的需求，我设计了一条包含多个 CTE（公共表表达式）的查询语句。这种模块化的写法不仅逻辑清晰，而且方便后续维护和扩展。 ### 完整的 SQL 查询 ```sql WITH filtered_logs AS ( -- 步骤 1: 基础过滤与数据提取 -- 提前过滤掉不符合条件的数据，减少后续计算量 SELECT -- date_trunc 将时间截断到小时，返回该小时的起始时间戳 date_trunc('hour', created_at) AS hour, -- 提取嵌套的 error code payload -> 'error' ->> 'code' AS error_code FROM app_logs WHERE payload ->> 'level' = 'error' -- 确保 error.code 存在且不为 NULL AND payload -> 'error' ->> 'code' IS NOT NULL -- @> 是 JSONB 的包含操作符，检查 tags 数组中是否包含 "critical" 元素 AND payload -> 'tags' @> '["critical"]'::jsonb ), hourly_aggregation AS ( -- 步骤 2: 按小时和 error_code 聚合 SELECT hour, error_code, COUNT(*) AS error_count FROM filtered_logs GROUP BY hour, error_code ), hourly_ranking AS ( -- 步骤 3: 窗口排名 -- 在每个小时窗口内，按错误数量降序排名 SELECT hour, error_code, error_count, -- RANK() 函数：如果两个 error_count 相同，会并列同名，且跳过后续名次 (如: 1, 2, 2, 4) RANK() OVER (PARTITION BY hour ORDER BY error_count DESC) AS rank_in_hour FROM hourly_aggregation ), top_n_errors AS ( -- 截取每小时排名前 3 的错误类型 SELECT hour, error_code, error_count, rank_in_hour FROM hourly_ranking WHERE rank_in_hour `, `->>`, `@>`)**: - `payload -> 'error' ->> 'code'`：`->` 提取 JSON 对象/数组（保留类型），`->>` 提取为纯文本。这里通过链式调用深入嵌套结构。 - `payload -> 'tags' @> '["critical"]'::jsonb`：`@>` 是极其高效的 JSONB 包含运算符。右侧必须也是 JSONB 类型（所以加了 `::jsonb` 强转）。它完美契合了“数组中是否包含某个元素”的场景，且如果在 `payload` 字段上建立了 GIN 索引，这个条件会**走索引**，极大地提升查询速度。 2. **时间截断 (`date_trunc`)**: - `date_trunc('hour', created_at)`：将如 `2023-10-25 14:35:20` 的时间统一截断为 `2023-10-25 14:00:00`，从而将同一个小时内的数据归并到同一个分组键下。 3. **窗口排名 (`RANK() OVER(...))`)**: - `PARTITION BY hour`：将数据按小时划分成一个个独立的“窗口”。 - `ORDER BY error_count DESC`：在上述窗口内部，按统计次数从高到低排。 4. **跨行引用 (`LAG`)**: - `LAG(error_count) OVER (PARTITION BY error_code ORDER BY hour)`：这是计算“环比”的核心。它把同一个 `error_code` 按时间排序，然后让当前行去获取它上一行的 `error_count` 值。 --- ### 边界情况讨论 1. **JSONB 字段缺失或结构不符**： - 如果某条记录没有 `error` 或 `tags` 字段，`payload -> 'tags' @> '["critical"]'` 的计算结果是 `false`，会被安全地过滤掉，**不会引发报错**。 - `payload ->...

AI 评审点评

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

【CLAUDE】候选答案质量很高，完整实现了所有功能需求，SQL 结构清晰，注释详尽，边界情况讨论较为全面，并额外提供了 GIN 索引等工程实践建议。与参考答案相比，在时区处理和操作符返回类型的系统性说明上略有欠缺，但整体表现接近参考答案水平，是一份优秀的回答。 【GEMINI】这是一份教科书级别的回答。模型不仅准确完成了所有复杂的 SQL 逻辑（JSONB 嵌套提取、过滤、聚合、排名及环比计算），而且在代码组织、技术解释和边界情况讨论上表现出极高的专业素养。提供的 GIN 索引优化建议和关于 RANK 行为的讨论体现了其实战经验，完全满足并超出了题目要求。 【KIMI】候选人的整体表现良好，SQL 查询正确性高，代码结构清晰，解释说明较为详细。但在边界情况的解释和处理上还有提升空间，特别是对 `tags` 数组为空或不存在时的处理逻辑需要更详细的讨论。

相关链接

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

加载中...
100%