主页 / dba / 问题 / 341036
Accepted
Rishav Ghosh
Asked: 2024-07-19 03:49:53 +0800 CST

我该如何合并相关映射？

  • 772

我有一张表，其中包含来自不同来源（每个来源一行）的映射，针对三种不同的标识符类型（idtype1 到 idtype3），其中 idtype1 具有最高优先级，idtype3 具有最低优先级。我需要合并和整合所有相关映射，以便更好地了解它们之间的关系。

初始映射表如下所示：

id类型1 idtype2 idtype3
无效的 b 丙
无效的 无效的 丙
无效的 b d
一个 无效的 d
无效的 b 无效的
十 b 无效的

现在，由于所有 id 类型都以某种方式相互关联，即c关联到b ，而b 又关联到d，而 d 又关联到a，现在，由于x与b相关，因此所有类型本质上都相互关联。我应该能够将信息整合到以下结果中：

id类型1 idtype2 idtype3
一个 b 丙
一个 b d
十 b 丙
十 b d

示例 2：

id类型1 idtype2 idtype3
无效的 无效的 丙
无效的 b 丙
无效的 无效的 d
一个 无效的 d
无效的 b d
无效的 无效的 埃
无效的 b 埃
无效的 b 无效的

我应该将其归结为：

id类型1 idtype2 idtype3
一个 b 丙
一个 b d
一个 b 埃

有人能帮忙想出一个有效的逻辑吗？

编辑：

该表可以有多个这样的组合，即两个示例可以同时以不同的值出现。

CREATE TABLE mappings (idtype1 VARCHAR(10),idtype2 VARCHAR(10),idtype3 VARCHAR(10))
INSERT mappings
VALUES
(null,null,'c'),
(null,'b','c'),
(null,null,'d'),
('a',null,'d'),
(null,'b','d'),
(null,null,'e'),
(null,'b','e'),
(null,'b',null),
(null,'q','r'),
(null,null,'r'),
(null,'q','s'),
('p',null,'s'),
(null,'q',null),
('t','q',null)

输出应该是：

id类型1 idtype2 idtype3
一个 b 丙
一个 b d
十 b 丙
十 b d
页 问 r
页 问 s
页 问 吨
sql-server

2 个回答

  1. Best Answer

    假设..

    • 你只有 3 个级别
    • 每个字母只能存在于 1 个级别
    • 隐含关系是传递性的
      • 例如a->b->c∪ p->b->e⇒ { a->b->e, p->b->c}

    ...那么以下内容就足够了：

    有点混乱，但这里有一个使用“编辑”数据集示例的完整 dbfiddle 。

    首先，仅隔离那些传达关系的记录（任何具有 2 或 3 个非NULL值的行），如下所示。单例行可能会传达原本孤立的实体的存在，但不会提供任何相关的映射信息。

    (
  select 
    '1->2',
    idtype1,
    idtype2
  from mappings
  where idtype1 is not null
    and idtype2 is not null
)
union all
(
  select 
    '2->3',
    idtype2,
    idtype3
  from mappings
  where idtype2 is not null
    and idtype3 is not null
)
union all
(
  select 
    '1->3',
    idtype1,
    idtype3
  from mappings
  where idtype1 is not null
    and idtype3 is not null
)

    仅隔离这些行即可。

    id类型1 idtype2 idtype3
    b 丙
    一个 d
    b d
    b 埃
    问 r
    问 s
    页 s
    吨 问

    从那里推断缺失的关系。因为我们假设只存在 3 个级别，所以唯一可能的越级关系是带有标签的关系1->3。例如

      select * 
  from relationships
  where ancestor_level = 1
    and descendant_level = 3

    一旦您规范了关系并填补了任何空白，您就可以自行加入1->2&2->3关系以获得完整的集合，如下所示。

    select 
  r1.ancestor_name as level_1,
  r2.ancestor_name as level_2,
  r2.descendant_name as level_3
from relationships as r1
join relationships as r2 on r2.ancestor_name = r1.descendant_name
order by 1, 2, 3

    采用这种方法，我们假设以下解决方案：

    解决方案图

    红线是推断出来的。

    id类型1 idtype2 idtype3
    一个 b 丙
    一个 b d
    一个 b 埃
    页 问 r
    页 问 s
    吨 问 r
    吨 问 s

    该解决方案还有一些未解决的问题和注意事项。

    1. 如果p->a引入了关系，那么传递性是否适用，以至于a->q现在有效？
    2. 此解决方案仅适用于 3 个级别。第 4 个级别需要额外的关系发现。随着级别的增加，它变成了一个更标准的分层递归问题 - 答案取决于p->a⇒a->q

    可以在Find Ancestry From JSON中进一步阅读有关使用自连接与递归推断层次结构的示例

    • 2

  2. 如果您想要一个能够产生预期结果的单个查询，那么您可能需要执行类似的操作（本质上是构建一组每个不同且非空的“类型”值，然后输出每个值的交集）。

    ;WITH 
t1 AS (SELECT DISTINCT idType1 FROM mapTable WHERE idType1 Is Not Null),
t2 AS (SELECT DISTINCT idType2 FROM mapTable WHERE idType2 Is Not Null),
t3 AS (SELECT DISTINCT idType3 FROM mapTable WHERE idType3 Is Not Null)
SELECT
*
FROM t1
CROSS APPLY t2
CROSS APPLY t3;

    您谈到不同的值彼此之间存在“链接”，我承认，对我来说，这不完全合理 - 但根据您的“预期输出”，我假设它是您想要的每个 type1、type2、type3 值的可能组合

    • 0

相关问题