SAP HANA SQL查询：从含逗号列提取唯一值并调整办公地点计数

现有doc_details表存储医生的部门分配信息，示例数据如下：

department_id         department_name           count_offices           office_names
101,201,204,401         b,b,c,b                       4                HQ,TCM,TCM,SCM

需要将各字段的重复值去重，得到如下目标输出：

department_id         department_name           count_offices          office_names
101,201,204,401           b,c                         3                 HQ,TCM,SCM

实现SQL #

WITH split_data AS (
    -- 拆分各逗号分隔字段，用序号保证拆分后各字段的对应关系
    SELECT
        d.doc_id, -- 替换为表中唯一标识医生的字段，若无则用其他分组依据
        s1.VALUE AS dept_id,
        s1.ORDINAL AS ord1,
        s2.VALUE AS dept_name,
        s2.ORDINAL AS ord2,
        s3.VALUE AS office_name,
        s3.ORDINAL AS ord3
    FROM doc_details d
    -- 拆分department_id字段
    LEFT JOIN STRING_SPLIT(d.department_id, ',') s1 ON 1=1
    -- 拆分department_name字段，用序号关联保证对应关系
    LEFT JOIN STRING_SPLIT(d.department_name, ',') s2 ON s1.ORDINAL = s2.ORDINAL
    -- 拆分office_names字段，用序号关联保证对应关系
    LEFT JOIN STRING_SPLIT(d.office_names, ',') s3 ON s1.ORDINAL = s3.ORDINAL
),
unique_data AS (
    -- 对拆分后的数据去重，保留唯一的部门ID、名称和办公地点
    SELECT DISTINCT
        doc_id,
        dept_id,
        dept_name,
        office_name
    FROM split_data
)
-- 重新聚合为逗号分隔字符串，同时统计唯一办公地点数量
SELECT
    STRING_AGG(DISTINCT dept_id, ',' ORDER BY dept_id) AS department_id,
    STRING_AGG(DISTINCT dept_name, ',' ORDER BY dept_name) AS department_name,
    COUNT(DISTINCT office_name) AS count_offices,
    STRING_AGG(DISTINCT office_name, ',' ORDER BY office_name) AS office_names
FROM unique_data
GROUP BY doc_id;

代码说明 #

1. 字段拆分：用STRING_SPLIT把每个逗号分隔的字段拆成多行，通过ORDINAL序号保证拆分后各字段的对应关系（比如原表第1个部门ID对应第1个部门名称和办公地点）。
2. 去重处理：通过DISTINCT过滤拆分后的重复记录，确保部门ID、名称、办公地点只保留唯一值。
3. 聚合重组：用STRING_AGG把去重后的字段重新拼接成逗号分隔字符串，用COUNT(DISTINCT)统计唯一办公地点的数量，最后按医生标识分组得到结果。

注意：如果表中没有doc_id这类唯一标识，要根据实际表结构调整分组字段，确保每个医生的信息单独聚合。

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