主页 / dba / 问题 / 168460
Accepted
eComEvo
Asked: 2017-03-29 17:50:50 +0800 CST

Postgres 加入外表有所有记录的地方

  • 772

我有这个people和tags桌子，像这样，

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

如果我想使用连接在表中查找同时包含 和 标签的所有内容people，我将如何在 Postgres 9.6 中高效地执行此操作？npywtags

people在这种情况下，我应该只从表中获取 Joe 的记录。

postgresql postgresql-9.6

5 个回答

  1. 以下是一些不涉及使用array_agg.

    针对每个标签返回INTERSECT的集合使用运算符：people_id

    WITH both_tags AS (
    SELECT people_id FROM tags WHERE tag = 'np'
    INTERSECT 
    SELECT people_id FROM tags WHERE tag = 'yw')
SELECT *
  FROM people 
  WHERE id IN (SELECT people_id FROM both_tags);

    或者您可以使用 aCOUNT(DISTINCT tag) = 2来查找具有这两个标签的人。（请注意，DISTINCT添加 是为了处理一个人可能有两次相同标签的情况。如果不可能，删除是安全的。）

    WITH both_tags AS (
    SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2)
SELECT *
  FROM people 
  WHERE id IN (SELECT people_id FROM both_tags);

    第二种方法更容易扩展以接受任意数量的标签，尽管第一种方法并非不可能。

    • 3

  2. 还有两种方法 - 使用连接或相关子查询 - 没有GROUP BY：

    第一个使用EXISTS子查询：

    select p.id, p.name
from people as p 
where exists (select from tags as t where t.people_id = p.id and t.tag = 'np')
  and exists (select from tags as t where t.people_id = p.id and t.tag = 'yw')
;

    第二个假设对 的UNIQUE约束(tag, people_id)：

    select p.id, p.name
from people as p 
  join tags as t1 on t1.people_id = p.id and t1.tag = 'np'
  join tags as t2 on t1.people_id = p.id and t2.tag = 'yw'
;

    在dbfiddle.uk进行测试。

    还要在 SO 上检查这个问题，有 10 多种方法可以解决此类问题 - 以及性能分析： How to filter SQL results in a has-many-through relation。

    他们甚至有一个标签：

    这可能令人惊讶，但大多数情况下，many join 方法、many exists 和类似的方法（如使用的方法INTERSECT）比方法更有效group by / count。但当然有许多细节对性能很重要。查询参数、表大小、索引、数据分布等等都会影响各种方法的性能。

    • 3

  3. 在这里，我们选择所有人并对标签进行数组聚合。我们一次性完成。然后我们将其包装在一个子选择中，并找到同时具有np, 和的所有匹配项yw。

    SELECT people_id, name, array_agg(tag) AS tags
FROM people
JOIN tags ON (people_id = people.id)
GROUP BY people_id, name
HAVING array_agg(tag) @> ARRAY['np', 'yw'];

 id | name |  tags   
----+------+---------
  1 | Joe  | {np,yw}
(1 row)

    您有时可以通过降低条件来加快速度

    SELECT people_id, name, array_agg(tag) AS tags
FROM people
JOIN tags ON (people_id = people.id)

-- push down
WHERE tag IN ('np', 'yw')

GROUP BY people_id, name
HAVING array_agg(tag) @> ARRAY['np', 'yw'];

    您也可以直接将标签数组放在上面people。然后查询它变得非常简单。

    • 2
  4. Best Answer

    mendosi's answer 的一个小变体，它避免了WITH：

    SELECT *
FROM people 
WHERE id IN 
(    
      SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2
);

    编号 | 姓名
 -: | :---
  1 | 乔

    这种方法与他/她的方法有一些小的区别：

    • 如果您使用的数据库不处理WITH语句（很久以前就不是 PostgreSQL 的情况）
    • 你觉得不舒服WITH
    • 您想避免这样一个事实，即在 PostgreSQL 中，WITH优化栅栏；并且（从今天开始）最终阻止数据库执行一些优化。
    • 这应该非常接近完全 SQL 标准，并且适用于 DBFiddle 上所有可用的数据库（截至今天）。

    dbfiddle在这里

    如果您正在寻找最快的解决方案，我会在实际条件下检查不同的方法，并根据您实际获得的时间来决定。我提出的查询非常标准，应该不会比带有 a 的查询慢WITH，但它是否比其他方法慢或快，我事先并不知道。

    • 2

  5. 另一种简单的等值连接方式：

    select p.id, name
from people p join tags on tags.people_id=p.id
where tag in ('np','yw')
group by p.id, name
having count(distinct tag)=2;

    编号 | 姓名
-: | :---
 1 | 乔

    dbfiddle在这里

    • 2

相关问题