镜像数据筛选算法优化：保留单条镜像记录而非全部删除

原始人际关系数据 #

line#        person-pin   reciprocal-person-pin  relationship   reciprocal-relationship
1             2942         3911                   son            mother                     
2             3911         2560                   client         financial advisor
3             3911         1195                   employee       employer
4             3911         1190                   church member  church
5             3911         2942                   mother         son
6             3911         3910                   mother         daughter
7             3911         3912                   mother         daughter
8             3911         5062                   wife           husband

其中行1和行5是镜像记录（person-pin与reciprocal-person-pin字段值互为颠倒），需要保留其中任意一条（优先保留行5），而非全部删除。

原算法问题 #

尝试的Progress 4GL代码会将成对的镜像记录全部跳过，无法保留单条：

FOR EACH ttRelationshipData:

    FIND FIRST ttRelationshipData2 WHERE ttRelationshipData.person-pin EQ ttRelationshipData2.reciprocal-person-pin
                                     AND ttRelationshipData.reciprocal-person-pin EQ ttRelationshipData2.person-pin NO-LOCK NO-ERROR.

    IF AVAILABLE ttRelationshipData2 THEN NEXT.
    ELSE DO:
        CREATE ttRelationship.
        ASSIGN
            ttRelationship.alpha-pin = ttRelationshipData.constituent-pin
            ttRelationship.alpha-role = ttRelationshipData.constituent-relationship-name
            ttRelationship.bravo-pin =  ttRelationshipData.related-constituent-pin
            ttRelationship.bravo-role = ttRelationshipData.relatedconstituent-rel-name.
    END.
END.

期望输出 #

line#        person-pin   reciprocal-person-pin  relationship   reciprocal-relationship
                  
1             3911         2560                   client         financial advisor
2             3911         1195                   employee       employer
3             3911         1190                   church member  church
4             3911         2942                   mother         son
5             3911         3910                   mother         daughter
6             3911         3912                   mother         daughter
7             3911         5062                   wife           husband

优化思路 #

• 引入排序规则避免重复处理：给镜像对设定唯一判断标准，比如只处理person-pin >= reciprocal-person-pin的记录，确保每对镜像仅被处理一次；如果需要优先保留特定关系类型（如行5的mother-son），可额外增加关系判断跳过反向记录。示例代码：

  FOR EACH ttRelationshipData:
      // 只处理person-pin大于等于对方的记录，避免重复
      IF ttRelationshipData.person-pin < ttRelationshipData.reciprocal-person-pin THEN NEXT.
      
      // 可选：跳过"son"这类反向关系，优先保留"mother"记录
      // IF ttRelationshipData.relationship = "son" THEN NEXT.
      
      CREATE ttRelationship.
      ASSIGN
          ttRelationship.alpha-pin = ttRelationshipData.constituent-pin
          ttRelationship.alpha-role = ttRelationshipData.constituent-relationship-name
          ttRelationship.bravo-pin =  ttRelationshipData.related-constituent-pin
          ttRelationship.bravo-role = ttRelationshipData.relatedconstituent-rel-name.
  END.
  

• 标记已处理记录：给临时表添加processed逻辑字段，遍历记录时若当前记录未被标记，则创建目标记录，同时找到其镜像记录并标记为已处理，确保不会重复处理。示例：

  DEF TEMP-TABLE ttRelationshipData LIKE original-table
      FIELD processed AS LOGICAL INITIAL NO.
  
  // 假设已将原始数据导入临时表
  FOR EACH ttRelationshipData WHERE NOT ttRelationshipData.processed:
      CREATE ttRelationship.
      ASSIGN
          ttRelationship.alpha-pin = ttRelationshipData.constituent-pin
          ttRelationship.alpha-role = ttRelationshipData.constituent-relationship-name
          ttRelationship.bravo-pin =  ttRelationshipData.related-constituent-pin
          ttRelationship.bravo-role = ttRelationshipData.relatedconstituent-rel-name.
      
      // 定位镜像记录并标记为已处理
      FIND FIRST ttRelationshipData2 WHERE ttRelationshipData2.person-pin = ttRelationshipData.reciprocal-person-pin
                                       AND ttRelationshipData2.reciprocal-person-pin = ttRelationshipData.person-pin
                                       AND NOT ttRelationshipData2.processed NO-LOCK NO-ERROR.
      IF AVAILABLE ttRelationshipData2 THEN ttRelationshipData2.processed = YES.
  END.
  

• 生成唯一分组键去重：将person-pin和reciprocal-person-pin按升序拼接成唯一字符串（如STRING(MIN(person-pin, reciprocal-person-pin)) + "-" + STRING(MAX(person-pin, reciprocal-person-pin))），通过分组仅保留每组的第一条记录，实现去重。

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