sql - 已应用 GROUP BY 后多列的不同值

Question

基本上，我有以下查询(实际上更复杂，但我认为这种简化是可以的):

SELECT a, b, x
FROM table

output:

 a | b | x
-----------
 1 | 2 | 34
 1 | 3 | 35
 1 | 3 | 36
 1 | 4 | 37
 2 | 3 | 38
 2 | 3 | 39
 2 | 4 | 40
 3 | 4 | 41
 3 | 5 | 42

为了计算每个“a 和 b 对”出现的次数，我使用 GROUP BY:

SELECT a, b, COUNT(x) AS count
FROM table
GROUP BY a, b
ORDER BY count

output:

 a | b | count
--------------
 1 | 2 | 1
 1 | 4 | 1
 2 | 4 | 1
 3 | 4 | 1
 3 | 5 | 1
 1 | 3 | 2
 2 | 3 | 2

令我困扰的是 a 和 b 的多次出现。我想保持“计数”不变，但如果 a 或 b 已经在前一行中，则删除接下来的每一行。如果“a”的值作为“b”出现在前一行中，反之亦然，如果它也删除一行，那将是一件好事。

首选预期输出:

 a | b | count
--------------
 1 | 2 | 1
 1 | 4 | 1    <- should not be in output since we had a=1
 2 | 4 | 1    <- should not be in output since we had b=
 3 | 4 | 1    
 3 | 5 | 1    <- should not be in output since we had a=3
 1 | 3 | 2    <- should not be in output since we had a=1 / a=3
 2 | 3 | 2    <- should not be in output since we had b=2 / a=3

因此，这:

 a | b | count
--------------
 1 | 2 | 1
 3 | 4 | 1    

替代预期输出，如果以上内容太复杂:

 a | b | count
--------------
 1 | 2 | 1
 1 | 4 | 1    <- should not be in output since we had a=1
 2 | 4 | 1    
 3 | 4 | 1    <- should not be in output since we had b=4
 3 | 5 | 1    
 1 | 3 | 2    <- should not be in output since we had a=1
 2 | 3 | 2    <- should not be in output since we had a=2

因此，这:

 a | b | count
--------------
 1 | 2 | 1
 2 | 4 | 1    
 3 | 5 | 1    

Answer 1

这是一个相当困惑的问题，但这里有一些事情需要考虑:

SELECT a, b, count
FROM (
    SELECT a, b, count,
          rank() over (partition by b order by count, a) as b_rank
    FROM (
        SELECT a, b, count,
          rank() over (partition by a order by count, b) as a_rank
        FROM (
            SELECT a, b, COUNT(*) AS count
            FROM t
            GROUP BY a, b
            ORDER BY count
          ) pc
      ) pc2
    WHERE a_rank < 3
  ) pc3
WHERE b_rank = 1

每个a value 在结果中最多出现两次，但是 b值将是唯一的。一些b出现在低计数对中的值可能不会反射(reflect)在结果中。 a 的可能重复之间存在权衡和 b 的数量可能完全遗漏的值:允许 a 的更多重复项(通过更改为，例如 WHERE a_rank < 4 )减少了 b 的数量可能会遗漏的值。