Создать представление базы данных, объединяющее несколько таблиц

Question

Я хотел бы получить помощь в создании представления базы данных. Моя схема БД выглядит следующим образом:

products            (id, ignored_comments_ids (array))
activities          (id)
comments            (id)
activities_comments (activity_id comment_id)
products_comments   (product_id, comment_id)
offers              (product_id, activity_id)

Теперь мне нужно создать представление комментариев всех продуктов с пользовательским столбцом с именем source:

• source = 'ПРЕДЛОЖЕНИЕ': комментарии поступают отproducts.offers.activities.comments ассоциация

• source = 'ПРЯМО': комментарии поступают от products.comments ассоциация

  Кроме того, представление должно исключать комментарии из products.ignored_comments_ids

Как я могу это сделать? Вид должен иметь product_id, source и все столбцы из comments Таблица.

Я пришел к следующему мнению, как я могу улучшить его?

CREATE OR REPLACE VIEW all_comments AS
  WITH the_comments AS (
    SELECT
      comments.*,
      'OFFER'     AS source,
      products.id AS product_id
    FROM comments
    JOIN activities_comments ON activities_comments.comment_id = comments.id
    JOIN activities          ON activities.id = activities_comments.activity_id
    JOIN offers              ON offers.activity_id = activities.id
    JOIN products            ON products.id = offers.product_id
  UNION
    SELECT
      comments.*,
      'DIRECT'    AS source,
      products.id AS product_id
    FROM comments
    JOIN products_comments ON products_comments.comment_id = comments.id
    JOIN products          ON products.id = products_comments.product_id
  )
  SELECT DISTINCT ON (the_comments.id)
    the_comments.id,
    the_comments.name,
    the_comments.source,
    the_comments.product_id
  FROM the_comments
  JOIN products ON products.id = the_comments.product_id
  WHERE NOT to_json(products.ignored_comment_ids)::jsonb @> the_comments.id::jsonb
  ORDER BY the_comments.id;

Answer 1

UNION может использоваться для объединения двух наборов данных И, одновременно удаляя дублирующиеся строки. UNION ALL может использоваться для объединения 2 наборов данных (а затем останавливается). Так UNION ALL позволяет избежать затрат на поиск и удаление дублированных строк, что ускоряет процесс.

В пределах вашего исходного общего табличного выражения (cte) the_comments вы заставляете каждую сторону объединения использовать разные константы, например

select *
from (
    select 1 as id, 'OFFER' AS source
    union
    select 1 as id, 'DIRECT' AS source
    ) d
;
result:
  id   source  
 ---- -------- 
   1   DIRECT  
   1   OFFER 

Несмотря на то, что id 1 находится в обеих сторонах этого объединения, из-за различных констант 2 строки возвращаются этим примером запроса. Поэтому, пожалуйста, используйте UNION ALL вместо.

Несмотря на удобство select * он не должен использоваться в представлениях (хотя существуют аргументы в обоих направлениях, например, здесь). Возможно, это было сделано для упрощения вопроса, но я надеюсь, что он не используется буквально, как видно. Если целью представления является возвращение только 4 столбцов, укажите только эти столбцы.

Хотя вам нужен product_id в выводе, это может быть получено из offers.product_id или же products_comments.product_id так что вам на самом деле не нужно присоединяться к таблице продуктов. Нет необходимости присоединяться к таблице продуктов после cte.

Потому что мы сейчас используем UNION ALL Я не вижу никакой пользы SELECT DISTINCT ON(...)Я подозреваю, что это просто накладные расходы, которые могут быть удалены. Очевидно, я не могу это проверить, и это может зависеть исключительно от ваших функциональных требований. Также обратите внимание, что SELECT DISTINCT ON(...) удалит source который вы так тщательно представили, например,

select distinct on (id) id, source
from (
    select 1 as id, 'OFFER' AS source
    union
    select 1 as id, 'DIRECT'AS source
    ) d
;
result:
  id   source  
 ---- -------- 
   1   DIRECT   

Не включайте order by предложение в любом представлении, только заказать "окончательный запрос". Другими словами; если вы создаете представление, вы, вероятно, будете использовать его в нескольких других запросах. Каждый из этих запросов, скорее всего, будет иметь свое собственное предложение where и будет нуждаться в разных порядках результата. Если вы заказываете представление, вы просто потребляете циклы процессора, а потом отказываетесь от этих усилий позже. Поэтому, пожалуйста, удалите порядок по пункту.

Я бы очень хотел предложить другой подход к заключительному предложению where, но, поскольку я не очень разбираюсь с JSON, у меня нет достаточного опыта, чтобы предложить альтернативу. Однако использование функций для данных в предложении where почти всегда является причиной низкой производительности, особенно в том смысле, что это обычно исключает доступ к индексам столбцов, участвующих в этих функциях. Поиск более эффективного способа исключения исключений из комментариев, вероятно, значительно улучшит производительность ваших запросов.

Итак, мои предложения приведут к этому:

WITH the_comments
AS (
    SELECT
        comments.id
      , comments.name
      , 'OFFER' AS source
      , offers.product_id AS product_id
    FROM comments
    JOIN activities_comments ON activities_comments.comment_id = comments.id
    JOIN activities ON activities.id = activities_comments.activity_id
    JOIN offers ON offers.activity_id = activities.id
    UNION ALL
    SELECT
        comments.id
      , comments.name
      , 'DIRECT' AS source
      , products_comments.product_id AS product_id
    FROM comments
    JOIN products_comments ON products_comments.comment_id = comments.id
    )
SELECT
    the_comments.id
  , the_comments.name
  , the_comments.source
  , the_comments.product_id
FROM the_comments
/* perhaps raise a separate question on this bit */
WHERE NOT to_json(products.ignored_comment_ids)::jsonb @> the_comments.id::jsonb