sql - 如何根据相似的交易地址重新分类 user_id

Question

简而言之，我正在尝试查看不同的transaction_address值，并且任何时候有 2 个或更多相同的值，我想检查是否满足以下任何条件：

1. 如果 OUTPUT_IP 相同和/或 OUTPUT_AMOUNT 相同，则创建一个reclassified_address列出 OUTPUT_ID 的新列（例如 ）；

2. 如果 INPUT_IP 相同和/或 INPUT_AMOUNT 相同，则reclassified_address使用列出的 INPUT_ID 创建一个新列（例如）

此处的目标是使用副本transaction_address作为调查用户是否控制多个 IP 地址的一种方式。

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

TRANSACTION_ADDRESS | INPUT_IP | OUTPUT_IP | INPUT_AMOUNT | OUTPUT_AMOUNT
--------------------+----------+-----------+--------------+-------------------
 15c7853                xyz         abc          -0.01          0.70
 15c7853                def         abc          -0.50          0.70
 19vc842                abc         xyz3         -5.03          0.413
 19vc842                abcd        xyz3         -0.06          0.201
 188fdx8                abc         xyz4         -0.10          0.09
 154gf34                xyz1        abc          -0.07          0.18
 45f4ti5                ggg         abc          -0.10          0.24
 33cv5c5                jjj         abc          -0.08          1.13 

这是满足条件 1 的示例和快速概述（类似的逻辑将用于条件 2）：

TRANSACTION_ADDRESS | INPUT_IP | OUTPUT_IP | INPUT_AMOUNT | OUTPUT_AMOUNT
--------------------+----------+-----------+--------------+---------------
 15c7853                xyz         abc          -0.01          0.70
 15c7853                def         abc          -0.50          0.70

从这个例子中，我们看到TRANSACTION_ADDRESS 15c7853出现 >=2 次，所以现在我们检查OUTPUT_IP和/或OUTPUT_AMOUNT值是否相同。在这种情况下，它们都是，所以我们用abc重新分类INPUT_IP xyz和def。这会让我知道使用xyz的人也很可能也在使用def和abc。所需的输出如下所示：

TRANSACTION_ADDRESS | INPUT_IP | OUTPUT_IP | INPUT_AMOUNT | OUTPUT_AMOUNT | reclassified_address
--------------------+----------+-----------+--------------+---------------+---------------------
 15c7853                xyz         abc          -0.01          0.70             abc
 15c7853                def         abc          -0.50          0.70             abc

我尝试使用 HAVING 子句返回重复项：

SELECT TRANSACTION_ADDRESS, COUNT(*)
FROM sample_table
GROUP BY TRANSACTION_ADDRESS
HAVING COUNT(*) >= 2

但是，我不确定这是否是正确的方法，并且我不确定在满足条件 1 或 2 时如何返回新列。

标签： sqlazure-databricks