sql - 当两者都可能更改时，按公共(public)用户 ID 或事务 ID 对所有行进行分组

Question

上下文是我们有一个 userid 和一个 transactionid，但由于客户端设置，有时 userid 会改变但 transactionid 保持不变(当两者应该保持不变时)，有时 transactionid 会改变但 userid 不会(所以我们不能只使用 transactionid)。

所以我们想得到一些我们可以用作实际 ID 的东西。新列(即实际 ID)仅用于对行进行分组，因此它可以是任意(唯一)值。顺序根本不重要。

例子:

| userid | transactionid |
|--------|---------------|
| A      |             1 |
| A      |             2 |
| A      |             3 |
| B      |             2 |
| B      |             2 |
| C      |             4 |
| D      |             5 |
| D      |             5 |
| D      |             6 |
| E      |             6 |
| E      |             7 |
| F      |             4 |

可能变成:

| userid | transactionid | actualid |
|--------|---------------|----------|
| A      | 1             | A        |
| A      | 2             | A        |
| A      | 3             | A        |
| B      | 2             | A        |
| B      | 2             | A        |
| C      | 4             | C        |
| D      | 5             | E        |
| D      | 5             | E        |
| D      | 6             | E        |
| E      | 6             | E        |
| E      | 7             | E        |
| F      | 4             | C        |

我们使用的是 Presto，所以有一些限制(没有临时表，聚合查询中没有 DISTINCT 等)

此处显示了在 Python 中使用 for 循环的解决方案:http://nbviewer.ipython.org/urls/gist.githubusercontent.com/jamesmcm/2554d5d4498b5d46d42d/raw/587ff552c34027e85ee199d5d8e5cb192e5550d1/gistfile1.txt

但我们需要 Presto 的解决方案。

这最初写在 Gist 上:https://gist.github.com/jamesmcm/9b9a559eb9f69405c04a

如果您想要表格的正确格式。

Answer 1

这可以使用 Windows 函数而不是连接来完成，并且需要比@David 建议的额外步骤。

使用下面的例子

uid | tid
 B  |  2
 B  |  3
 B  |  4
 A  |  3
 E  |  1

这是可以做到的

SELECT 
    *,
    MIN(minuid) OVER (PARTITION BY mintid order by 1) as final_id
FROM(
    SELECT 
        tid,
        uid,
        MIN(uid) OVER (PARTITION BY tid order by 1) as minuid,
        MIN(tid) OVER (PARTITION BY uid order by 1) as mintid
    FROM
        log
) 

这应该会返回正确的 ID

uid | tid | minuid | mintid | final_id
 B  |  2  |   B    |   2    |   A
 B  |  3  |   A    |   2    |   A
 B  |  4  |   B    |   2    |   A
 A  |  3  |   A    |   3    |   A
 E  |  1  |   E    |   1    |   E