【SQL查询优化】关联与聚合操作战实——从多值标签中提取水果名称

需求概述

目标是查询特定call_id关联的label_cd中代码所对应的水果名称，期望输出格式为：call_id对应的结果为水果->苹果, 水果->香蕉。

pg数据库

原始数据结构

• items表：存储调用ID及包含多个代码的标签。

INSERT INTO "items" ("call_id", "label_cd") VALUES ('802403070839345017521', '90201,90202');

• fruits表：存储水果的ID、名称、父ID及代码。

INSERT INTO "fruits" ("id", "name", "parent_id", "code") VALUES (28, '水果->苹果', 28, '90201');
INSERT INTO "fruits" ("id", "name", "parent_id", "code") VALUES (29, '水果->香蕉', 29, '90202');

items.label_cd和 fruits.code

解决方案

• 步骤详解

1. 创建临时表：首先利用WITH子句创建一个名为split_labels的临时表，用于拆分items表中label_cd字段的多个代码值。
2. 关联查询：随后，创建第二个临时表matched_labels，通过将拆分后的代码与fruits表根据code字段进行关联，获取每个代码对应的水果名称。
3. 聚合结果：最后，对关联后的结果按call_id进行分组，使用STRING_AGG函数将同一call_id下的所有水果名称和代码分别用逗号连接起来，形成最终的查询结果。

• SQL查询代码优化

WITH 
  -- 拆分items表中的label_cd为独立的code值
  split_labels AS (
    SELECT call_id, TRIM(value) AS code
    FROM items, UNNEST(string_to_array(label_cd, ',')) AS value
  ),
  
  -- 关联split_labels与fruits表，获取匹配的水果名称
  matched_fruits AS (
    SELECT sl.call_id, f.name, f.code
    FROM split_labels sl
    JOIN fruits f ON sl.code = f.code
  )
  
-- 聚合结果，按call_id分组并列出所有关联的水果名称及代码
SELECT 
  mf.call_id,
  STRING_AGG(mf.name, ', ') AS fruit_names,
  STRING_AGG(mf.code, ', ') AS codes
FROM matched_fruits mf
GROUP BY mf.call_id;

查询结果

802403070839345017521	水果->苹果, 水果->香蕉	90201, 90202