sql - PostgreSQL 不同的行与一列中的不同值的计数连接

Question

我使用的是 PostgreSQL 9.4，我有一个 1300 万行的表，数据大致如下:

  a  | b | u  | t 
-----+---+----+----
 foo | 1 |  1 | 10
 foo | 1 |  2 | 11
 foo | 1 |  2 | 11
 foo | 2 |  4 | 1
 foo | 3 |  5 | 2
 bar | 1 |  6 | 2
 bar | 2 |  7 | 2
 bar | 2 |  8 | 3
 bar | 3 |  9 | 4
 bar | 4 | 10 | 5
 bar | 5 | 11 | 6
 baz | 1 | 12 | 1
 baz | 1 | 13 | 2
 baz | 1 | 13 | 2
 baz | 1 | 13 | 3

md5(a)、b 和(md5(a), b) 上都有索引。 (实际上，a 可能包含超过 4k 个字符的值。)还有一个我在上面省略的 SERIAL 类型的主键列。

我正在尝试构建一个将返回以下结果的查询:

  a  | b | u  | t  | z 
-----+---+----+----+---
 foo | 1 |  1 | 10 | 3
 foo | 1 |  2 | 11 | 3
 foo | 2 |  4 | 1  | 3
 foo | 3 |  5 | 2  | 3
 bar | 1 |  6 | 2  | 5
 bar | 2 |  7 | 2  | 5
 bar | 2 |  8 | 3  | 5
 bar | 3 |  9 | 4  | 5
 bar | 4 | 10 | 5  | 5
 bar | 5 | 11 | 6  | 5

在这些结果中，所有行都被删除了重复项，就像应用了 GROUP BY a, b, u, t 一样，z 是 的不同值的计数b 用于 a 上的每个分区，并且仅包含 z 值大于 2 的行。

我可以让 z 过滤器按如下方式工作:

SELECT a, COUNT(b) AS z from (SELECT DISTINCT a, b FROM t) AS foo GROUP BY a
  HAVING COUNT(b) > 2;

但是，我很难将其与表中的其余数据结合起来。

执行此操作最有效的方法是什么？

Answer 1

您的第一步已经可以更简单了:

SELECT md5(a) AS md5_a, count(DISTINCT b) AS z
FROM   t
GROUP  BY 1
HAVING count(DISTINCT b) > 2;

使用 md5(a) 代替 a，因为 a 显然可以非常长，并且您已经有了关于 md5(a) 等的索引。

由于您的表很大，您需要一个高效的查询。这应该是最快的解决方案之一 - 具有足够的索引支持。您在 (md5(a), b) 上的索引是有用的，但是 - 假设 b、u 和 t是小列 - (md5(a), b, u, t) 上的索引对于查询的第二步(横向连接)会更好。

您想要的最终结果:

SELECT DISTINCT ON (md5(t.a), b, u, t)
       t.a, t.b, t.u, t.t, a.z
FROM  (
   SELECT md5(a) AS md5_a, count(DISTINCT b) AS z
   FROM   t
   GROUP  BY 1
   HAVING count(DISTINCT b) > 2
   ) a
JOIN   t ON md5(t.a) = md5_a
ORDER  BY 1, 2, 3, 4;  -- optional

或者可能更快，但是:

SELECT a, b, u, t, z
FROM  (
   SELECT DISTINCT ON (1, 2, 3, 4)
          md5(t.a) AS md5_a, t.b, t.u, t.t, t.a
   FROM   t
   ) t
JOIN  (
   SELECT md5(a) AS md5_a, count(DISTINCT b) AS z
   FROM   t
   GROUP  BY 1
   HAVING count(DISTINCT b) > 2
   ) z USING (md5_a)
ORDER  BY 1, 2, 3, 4;  -- optional

DISTINCT ON的详细解释:

• Select first row in each GROUP BY group?