Объедините эти запросы

Я хочу представление, которое объединяет несколько запросов. Что-то вроде

 SELECT
  products.id AS id
FROM products
 

 SELECT
  publications.visible AS online_visible
FROM products
INNER JOIN publications ON publications.product_id = products.id
WHERE publication.name = 'online'
 

 SELECT
  publications.visible AS retail_visible
FROM products
INNER JOIN publications ON publications.product_id = products.id
WHERE publication.name = 'retail'
 

 SELECT
  SUM(sales.quantity) AS year_sales
FROM products
LEFT OUTER JOIN sales ON sales.product_id = products.id
WHERE sales.date > current_date - interval '365' day
GROUP BY products.id
 

 SELECT
  SUM(sales.quantity) AS month_sales
FROM products
LEFT OUTER JOIN sales ON sales.product_id = products.id
WHERE sales.date > current_date - interval '30' day
GROUP BY products.id
 

Желаемый конечный результат

В итоге я хочу получить таблицу, подобную

 | ID |online_visible|retail_visible|month_sales|year_sales|
|----|--------------|--------------|-----------|----------|
|  1 |   false      |    true      |     35    |   420    |
 

Данные

• Я тестировал каждый из запросов по отдельности самостоятельно, поэтому я знаю, что каждый из них работает.
• Вероятно, вы можете предположить, что любая опечатка здесь вызвана тем, что я пытаюсь упростить свой вопрос, и не является причиной моей проблемы.
• Вероятно, мой вопрос задавался раньше, но я недостаточно знаю SQL, чтобы задать правильный вопрос

Что я пробовал

Я пробовал несколько комбинаций, в которых я использую ОБЪЕДИНЕНИЕ или ОБЪЕДИНЕНИЕ ВСЕХ для объединения запросов.

В сочетании с тем, что я сделал, каждый ВЫБОР включает в себя все остальные выборки, но с нулевыми значениями. Например, это

 SELECT
  products.id AS id,
  null::boolean AS online_visible,
  null::boolean AS retail_visible,
  null:int AS month_sales,
  null:int AS year_sales
FROM products
 

и аналогично для других

Вы можете получить желаемый результат с помощью этого запроса:

 WITH products_publications AS (
  SELECT
    products.id AS id,
    MAX(CASE WHEN publication.name = 'online' THEN publications.visible END) 
      AS online_visible,
    MAX(CASE WHEN publication.name = 'retail' THEN publications.visible END) 
      AS retail_visible
  FROM products
  INNER JOIN publications ON publications.product_id = products.id
  WHERE publication.name IN ('online', 'retail')
  GROUP BY products.id
), products_sales AS (
  SELECT
    products.id AS id,
    SUM(CASE WHEN sales.date > current_date - interval '30' day 
        THEN sales.quantity END) AS month_sales,
    SUM(CASE WHEN sales.date > current_date - interval '365' day 
        THEN sales.quantity END) AS year_sales
  FROM products
  LEFT OUTER JOIN sales ON sales.product_id = products.id
  GROUP BY products.id
)
SELECT
  p.id,
  pp.online_visible,
  pp.retail_visible,
  ps.month_sales,
  ps.year_sales
FROM products p
LEFT JOIN products_publications pp ON pp.id = p.id
LEFT JOIN products_sales ps ON ps.id = p.id
 

Вы можете использовать filter предложение, чтобы получить его эффективно, как показано ниже:

 select 
    pr.id "id",
    coalesce(bool_or(pu.visible) filter (where pu.name='online'),false) "online_visible",
    coalesce(bool_or(pu.visible) filter (where pu.name='retail'),false) "retail_visible",
    coalesce(sum(sa.quantity) filter  (where sa.date > current_date - interval '365' day),0) "year_sales",
    coalesce(sum(sa.quantity) filter  (where sa.date > current_date - interval '30' day),0) "month_sales"
from products pr
left join publications pu on pr.id=pu.product_id
left join sales sa on sa.product_id=pr.id
group by 1
 

ДЕМОНСТРАЦИЯ

Хм. Я думаю, что я мог бы подойти к этому путем агрегирования по отдельным измерениям, а затем JOIN редактирования:

 SELECT p.id, (pu.num_online > 0) as online_visible,
      (pu.num_retail > 0) as retail_visible,
       s.monthly_sales, s.yearly_sales
FROM products p LEFT JOIN
     (SELECT pu.product_id,
             COUNT(*) OVER (FILTER BY pu.name = 'online') as num_online,
             COUNT(*) OVER (FILTER BY pu.name = 'retail') as num_retail             
      FROM publications pu
      GROUP BY pu.product_id
     ) pu
     ON pu.product_id = p.id LEFT JOIN
     (SELECT s.products_id, SUM(s.quantity) AS year_sales,
             SUM(s.quantity) FILTER (WHERE s.date > current_date - interval '30' day) as month_sales
      FROM sales s
      WHERE s.date > current_date - interval '365' day
      GROUP BY s.id
     ) s
     ON s.product_id = p.id;
 

Это гарантирует, что JOIN s не будут непреднамеренно умножать строки, отбрасывая значения агрегирования — особенно для продаж.

Обратите внимание, что вам не нужна products таблица в подзапросах.