SQL：在动态键上加入行

Question

我有一个具有可以通过三个键Refi，refj和refk的复合键识别的产品的表。 refi不能为null，refj和refk可能为null。 钥匙有可信赖的命令。因此，REFI具有最低的推力功率，REFJ培养基和REFK最高的可信赖性能力。

产品P可以随着时间的推移升级其可信赖性关键，因此可以通过Refi和另一个时间点通过Refi和Refk来识别它。这段时间将表示为扫描产品的时间 - 列 t 。

规则1 ：目标是通过将至少一个键中的一个钥匙相对应的时间来识别独特的产品 - 这是具有最高推力可达功率的非空键。

规则2 ：另一个规则是，在某种意义上，如果产品随着时间的推移降低了其可信赖性关键，则该顺序不应在这种情况下进行。

规则3 ：最后一个和最终的规则 - 当2种产品P1和P2被确定为与第三产品P3的潜在通信时，它是具有具有最高可信度能力的通用密钥的产品与P3相对应。

景象

示例

2

1	景象	1	说明
规则	是	。	以下
一些	：	编号	和
​	​	​	​

​将这三行视为1个独特产品，因为它们都具有相同的非零reci1。这也是规则数3的一个典范，其中2种产品识别为与第三个的潜在通信也对应于彼此。

示例2 ：

t	Refi	Refj	Refj
T1	Refi1 refj1	refj1	null
T2	Refi2 Refi2	Refj1	null

在这个景象中，我希望能够将这两个行算作1行，因为它们具有共同的RefJ，并且RefJ具有较高的值得信赖的能力比重新提供的能力。因此，即使Refi键不同，REFJ也很重要。

示例3 ：

t	Refi	Refj	Refj
T1	Refi1 Refj1	refj1	refk1
T2	Refi2 Refi2	Refj1	null

在此典范中，我想说明规则编号2。在此CAS中，我想将这2行视为两个单独的产品不可能具有较低信任力的钥匙，因此在这种情况下，我们认为这是另一种产品。

示例4 ：

t	Refi	Refj	Refk
T1	refi1	null	null
T2	Refi2	refj1	null
T3	Refi1 Refi1	Refj1 Refk1	Refk1

在这个景象中，我想说明规则编号3。 在这种情况下，第一行是3D行中产品的潜在对应。 基于Refi1和第二行产品的第一组基于REFJ1。 由于RefJ具有比Refi更高的可信赖能力，因此在这种情况下，第一行被确定为1个产品，最后2行是同一产品。

目标：基于这些规则，我需要找到以一种或另一种方式处理这些情况的SQL查询 - 它可以直接在单独的列中计算独特的产品或将独特的键关联。

任何帮助，算法的想法或如何治疗不同案例的建议将得到赞赏！

Answer 1

您可以通过在相同的“ ref ”值上应用自我加入，在每个“ ref ”上分开工作，然后将Union应用于结果，尽管根据帖子中指向的一个（k＆gt; j＆gt; i）固定优先级。

WITH 
    cte_refk AS (
        SELECT t1.t AS t1_t, t2.t AS t2_t
        FROM       tab t1
        INNER JOIN tab t2 ON t1.refk = t2.refk
                         AND t1.t    > t2.t    ),
    cte_refj AS (
        SELECT t1.t AS t1_t, t2.t AS t2_t
        FROM       tab t1
        INNER JOIN tab t2 ON t1.refj = t2.refj
                         AND t1.t    > t2.t    ),
    cte_refi AS (
        SELECT t1.t AS t1_t, t2.t AS t2_t
        FROM       tab t1
        INNER JOIN tab t2 ON t1.refi = t2.refi
                         AND t1.t    > t2.t    )
SELECT * 
FROM cte_refk

UNION ALL

SELECT * 
FROM cte_refj 
WHERE t1_t NOT IN (SELECT t1_t FROM cte_refk)

UNION ALL

SELECT * 
FROM cte_refi
WHERE t1_t NOT IN (SELECT t1_t FROM cte_refk)
  AND t1_t NOT IN (SELECT t1_t FROM cte_refj)

此查询将为您提供匹配的行。

在此提小提琴。