Объединение нескольких запросов SELECT в один VIEW

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

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

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, чтобы задать правильный вопрос.

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

Я пробовал несколько комбинаций, в которых я использую UNION или UNION ALL для объединения запросов.

В сочетании с тем, что я сделал, каждый SELECT включает все остальные SELECT, но с нулевыми значениями. Как это

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

и аналогично для остальных


postgresql sql sql-view
person hrdwdmrbl    schedule 05.12.2020    source источник
comment
Примеры данных и желаемые результаты были бы полезны.   -  person Gordon Linoff    schedule 05.12.2020
comment
@GordonLinoff У меня есть раздел «Желаемый конечный результат». Есть ли что-то еще, что вы хотели бы? Я мог бы добавить образцы данных, которых у меня нет.   -  person hrdwdmrbl    schedule 05.12.2020

Ответы (3)


arrow_upward
1
arrow_downward

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

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
person user14717238    schedule 05.12.2020

arrow_upward
1
arrow_downward

Вы можете использовать предложение 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

ДЕМО

person Akhilesh Mishra    schedule 05.12.2020

arrow_upward
0
arrow_downward

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

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 не будут непреднамеренно умножать строки, отбрасывая значения агрегирования, особенно для продаж.

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

person Gordon Linoff    schedule 05.12.2020