当userid或transactionid可能发生变化时，将所有行按共同userid或transactionid分组

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

Answer 2

我们首先查找分配给每个transactionid (tid)的userid (uid)。有许多选择此分配的方法，但将使用最小(最小)userid:

SELECT tid, min(uid) xid
FROM log
GROUP BY tid

这会导致以下赋值:

 tid | xid 
-----+-----
   1 | A   
   2 | A   
   3 | A   
   4 | C   
   5 | D   
   6 | D   
   7 | E   
(7 rows)

现在我们有了赋值，我们可以将它们连接到原始表:

SELECT a.uid, a.tid, b.xid
FROM log a
JOIN (
  SELECT tid, min(uid) xid
  FROM log
  GROUP BY tid
) b
ON (a.tid = b.tid)
ORDER BY 1, 2;

输出:

 uid | tid | xid 
-----+-----+-----
 A   |   1 | A   
 A   |   2 | A   
 A   |   3 | A   
 B   |   2 | A   
 B   |   2 | A   
 C   |   4 | C   
 D   |   5 | D   
 D   |   5 | D   
 D   |   6 | D   
 E   |   6 | D   
 E   |   7 | E   
 F   |   4 | C   
(12 rows)