咨询从concepts表生成含siblings-ids的extended_concept表的SQL方案

• 现有concepts表存储本体图数据，表结构：concepts(id, definition, parent-id, path-to-root)，包含1000万条记录
• 示例记录：

  ('A28681773', "definition2", 'A32452653', 'A24672666.A24681708.A24674976.A27414154.A32452653')
  ('A33193765', "definition1", 'A24670862', 'A24672666.A24681708.A24674976.A27414154.A27414079.A24679016.A24670862')
  

• 需求：生成extended_concept表（或直接导出CSV），结构为extended_concept(concept-id, definition, parent-id, siblings-ids)，要求：
  • siblings-ids为逗号分隔的兄弟节点ID字符串
  • 仅保留兄弟节点数量≥3的概念

以下针对主流数据库提供实现方案，核心逻辑是先筛选出符合条件的父节点，再聚合兄弟ID并关联原表数据：

1. MySQL 实现 #

-- 创建extended_concept表（可选，直接导出CSV可跳过）
CREATE TABLE extended_concept AS
SELECT
    c.id AS `concept-id`,
    c.definition,
    c.`parent-id`,
    GROUP_CONCAT(s.id SEPARATOR ',') AS `siblings-ids`
FROM concepts c
-- 关联同父节点的所有兄弟节点
JOIN concepts s ON c.`parent-id` = s.`parent-id`
-- 筛选出子节点数≥3的父节点
WHERE c.`parent-id` IN (
    SELECT `parent-id`
    FROM concepts
    GROUP BY `parent-id`
    HAVING COUNT(id) >= 3
)
GROUP BY c.id, c.definition, c.`parent-id`;

-- 导出为CSV文件
SELECT * FROM extended_concept
INTO OUTFILE '/path/to/extended_concept.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

2. PostgreSQL 实现 #

-- 创建extended_concept表（可选）
CREATE TABLE extended_concept AS
SELECT
    c.id AS "concept-id",
    c.definition,
    c."parent-id",
    STRING_AGG(s.id, ',' ORDER BY s.id) AS "siblings-ids"
FROM concepts c
JOIN concepts s ON c."parent-id" = s."parent-id"
WHERE c."parent-id" IN (
    SELECT "parent-id"
    FROM concepts
    GROUP BY "parent-id"
    HAVING COUNT(id) >= 3
)
GROUP BY c.id, c.definition, c."parent-id";

-- 导出为CSV文件
COPY extended_concept TO '/path/to/extended_concept.csv' WITH (FORMAT csv, HEADER, DELIMITER ',', QUOTE '"');

3. SQL Server 实现 #

-- 创建extended_concept表（可选）
SELECT
    c.id AS [concept-id],
    c.definition,
    c.[parent-id],
    STRING_AGG(s.id, ',') AS [siblings-ids]
INTO extended_concept
FROM concepts c
JOIN concepts s ON c.[parent-id] = s.[parent-id]
WHERE c.[parent-id] IN (
    SELECT [parent-id]
    FROM concepts
    GROUP BY [parent-id]
    HAVING COUNT(id) >= 3
)
GROUP BY c.id, c.definition, c.[parent-id];

-- 导出为CSV：可通过SSMS"导出数据"向导，或使用bcp命令行工具
-- bcp命令示例：
-- bcp "SELECT * FROM YourDB.dbo.extended_concept" queryout "C:\path\extended_concept.csv" -S YourServer -d YourDB -U username -P password -c -t, -r\n

方案说明 #

• 子查询先过滤出拥有≥3个子节点的父ID，减少后续关联计算量；针对1000万条数据的场景，建议给parent-id字段添加索引提升性能
• 若需要排除当前节点自身，可在JOIN条件中添加AND s.id != c.id
• 导出CSV时需确保数据库服务对目标路径有读写权限

内容的提问来源于stack exchange，提问作者Muhammad Sulaiman