sql - 如何基于特定的、相同的列值进行聚类

Question

我正在查看不同的区块链交易，并希望根据一些规则进行集群INPUT_ADDRESS和OUTPUT_ADDRESS值，以帮助确定地址是否被同一所有者使用。简而言之，这是我要为集群创建的规则：

首先，我想检查是否有 >=2 的相同TRANS_HASH值。如果存在重复值：

(A) 如果OUTPUT_ADDRESS相同和/或OUTPUT_AMOUNT相同，则聚类INPUT_ADDRESS；(B) 如果INPUT_ADDRESS相同和/或INPUT_AMOUNT相同，则聚类OUTPUT_ADDRESS

这是我正在使用的表格示例：

BLOCK_DATE | BLOCK_HEIGHT | TRANS_HASH | INPUT_ADDRESS | OUTPUT_ADDRESS | INPUT_AMOUNT | OUTPUT_AMOUNT
01/11/2020    190            15c7853       xyz             abc              -0.01          0.70
01/11/2020    190            15c7853       def             abc              -0.50          0.70
01/11/2020    191            19vc842       abc             xyz3             -5.03          0.413
01/11/2020    191            19vc842       abcd            xyz3             -0.06          0.201
01/12/2020    191            188fdx8       abc             xyz4             -0.10          0.09
01/12/2020    192            154gf34       xyz1            abc              -0.07          0.18
01/12/2020    192            45f4ti5       ggg             abc              -0.10          0.24
01/12/2020    192            33cv5c5       jjj             abc              -0.08          1.13 

从这个例子中，我们看到TRANS_HASH 15c7853出现 >=2 次，所以现在我们检查OUTPUT_ADDRESS和/或 OUTPUT_AMOUNT 值是否相同。由于它们都是，我们将INPUT_ADDRESS xyz和def与abc聚类

我想返回这样的东西：

TRANS_HASH |  OUTPUT_ADDRESS | OUTPUT_AMOUNT | CLUSTERED_INPUT_ADDRESS | CLUSTERED_INPUT_AMOUNT
-----------+-----------------+---------------+-------------------------+-----------------------
15c7853         abc              0.70             xyz                       -0.01
15c7853         abc              0.70             def                       -0.50

我尝试使用 HAVING 子句返回重复项，但我不确定这是否是正确的方法，也不确定如何返回集群地址/金额：

SELECT TRANS_HASH, COUNT(*)
FROM blockchain_table
GROUP BY TRANS_HASH
HAVING COUNT(*) >= 2

我需要在这里做一个子查询吗？就像是：

SELECT TRANS_HASH, OUTPUT_ADDRESS, OUTPUT_AMOUNT, INPUT_ADDRESS AS CLUSTERED_INPUT_ADDRESS, INPUT_AMOUNT AS CLUSTERED_INPUT_AMOUNT
FROM
(
    SELECT TRANS_HASH, COUNT(*)
    FROM blockchain_table
    GROUP BY TRANS_HASH
    HAVING COUNT(*) >= 2
)

此外，创建两个单独的查询来解释 (A) 和 (B) 会更有效吗？还是我应该在一个查询中全部写完？

标签： sqlazureazure-databricks