在PostgreSQL中从联接表中过滤数据

Question

在我的数据库中，我有以下模式：

CREATE TABLE survey_results (
    id integer NOT NULL
);

CREATE TABLE slide_results (
    id integer NOT NULL,
    survey_result_id integer,
    tags character varying[] DEFAULT '{}'::character varying[],
    content character varying,
    created_at timestamp with time zone NOT NULL
);

INSERT INTO survey_results (id)
  VALUES (1);

INSERT INTO slide_results (id, survey_result_id, tags, content, created_at)
  VALUES (1, 1, '{food}', 'Food slide', now());

INSERT INTO slide_results (id, survey_result_id, tags, content, created_at)
  VALUES (2, 1, '{motivation}', 'Motivation slide', now());

现在，我希望有一个SQL查询，它将返回带有指定标记的幻灯片结果的调查结果id和内容。我写了这样的东西：

select distinct on(sr.id)
  sr.id,
  slr.content AS food,
  slr2.content AS motivation
  from survey_results sr

  LEFT JOIN slide_results slr ON slr.survey_result_id = sr.id AND slr.id IN (
    SELECT id as id
    FROM slide_results
    WHERE 'food' = ANY(tags)
    ORDER BY created_at desc
  )

  LEFT JOIN slide_results slr2 ON slr2.survey_result_id = sr.id AND slr2.id IN (
    SELECT id as id
    FROM slide_results
    WHERE 'motivation' = ANY(tags)
    ORDER BY created_at desc
  )
  group by slr.content, slr2.content, sr.id

返回：

| id  | food       | motivation       |
| --- | ---------- | ---------------- |
| 1   | Food slide | Motivation slide |

这个查询工作正常，但是我想知道是否有更好的方法来完成这个任务？

编辑：

我忘了添加链接，做数据库：

https://www.db-fiddle.com/f/gP761psywgmovfdTT7DjP4/0

Answer 1

我会这样写这个查询：

SELECT DISTINCT ON (sr.id)
       sr.id,
       slr.content AS food,
       slr2.content AS motivation
FROM survey_results AS sr
   LEFT JOIN (SELECT survey_result_id, content, created_at
              FROM slide_results
              WHERE '{food}' <@ tags) AS slr
      ON slr.survey_result_id = sr.id
   LEFT JOIN (SELECT survey_result_id, content, created_at
              FROM slide_results
              WHERE '{motivation}' <@ tags) AS slr2
      ON slr2.survey_result_id = sr.id
ORDER BY sr.id, slr.created_at DESC, slr2.created_at DESC;

ORDER BY必须位于外部查询中才能有效。

使用<@而不是=ANY可以在slide_results.tags上使用GIN索引。

使用FROM列表中的子选择可以避免不必要的联接和效率低下的IN子查询。

Answer 2

我不能保证这比你所拥有的更好，但它似乎更具有可伸缩性。如果没有看到完整的数据集和所需的结果，就很难知道这是否会在任何方面适得其反：

select
  sl.survey_result_id,
  array_to_string (array_agg (distinct sl.content) filter
      (where 'food' = any (sl.tags)), ',') as food,
  array_to_string (array_agg (distinct sl.content) filter
      (where 'motivation' = any (sl.tags)), ',') as motivation
from
  survey_results s
  join slide_results sl on s.id = sl.survey_result_id
group by survey_result_id