Как сделать группировку по двум срезам?

Question

[pqgg7nwkd4]

Предположим, есть таблица фигур:

CREATE TABLE fig(
  id BIGINT PRIMARY KEY,
  title VARCHAR
)

Таблица вершин:

CREATE TABLE vertex(
  id BIGINT PRIMARY KEY,
  fig_id BIGINT,
  vertex_cost INTEGER -- стоимость - некое свойство вершины
)

Таблица сторон:

CREATE TABLE edge (
  id BIGINT PRIMARY KEY,
  fig_id BIGINT,
  edge_cost INTEGER -- стоимость - некое свойство грани
)

Вопрос 1:

Можно ли без вложенных запросов получить список фигур и для каждой фигуры сумму стоимостей ее вершин и сумму стоимостей граней?

С помощью вложенных запросов задачу можно решить так:

-- пример правильного, но не удовлетворяющего основному условию задачи, решения:
SELECT 
  fig.*,
  (SELECT SUM(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_sum,
  (SELECT SUM(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_sum
FROM fig

Вопрос 2 (со звездочкой):

Можно ли без вложенных запросов получить список фигур и для каждой фигуры сумму стоимостей ее вершин и сумму стоимостей граней, а также минимальные и максимальные стоимости?

С помощью вложенных запросов задачу можно решить так:

-- пример правильного, но не удовлетворяющего основному условию задачи, решения:
SELECT 
  fig.*,
  (SELECT SUM(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_sum,
  (SELECT SUM(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_sum,
  (SELECT MIN(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_min,
  (SELECT MIN(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_min,
  (SELECT MAX(vertex_cost) FROM vertex WHERE vertex.fig_id = fig.id) vertex_max,
  (SELECT MAX(edge_cost) FROM edge WHERE edge.fig_id = fig.id) edge_max
FROM fig

Answer 1

[Дмитрий Ковальский]

SELECT f.* ,SUM(ISNULL(edge_cost,0)) as edge, SUM(ISNULL(vertex_cost,0)) as vortex 
FROM fig as f
LEFT JOIN vertex as v ON f.id = v.fig_id
LEFT JOIN edge as e ON f.id = e.fig_id
GROUP BY f.id

Можно примерно так. Аналогично мудрить со второй задачей.

Comments

[pqgg7nwkd4]

К сожалению, это не будет работать как ожидается.
Запрос до группировки выберет записи из таблиц vertex и edge по нескольку раз, поэтому суммы после группировки будут в разы больше.

Answer 2

[Melkij]

with vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max аналогично*/ from vertex group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge group by fid_id
)
select id, name, vertex_cost, edge_cost
    from fig
    left join vertex_cost vc on fig.id=vc.fig_id
    left join edge_cost ec on fig.id=ec.fig_id

Если у фигур косты есть всегда - то заменить left join на join. Сейчас фигура будет возвращена всегда, но с NULL.

Если надо предварительно пофильтровать сами фигуры:

with figures as (
select id, name from figures where name like 'foo%'
),
vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max*/ from vertex where fig_id in (select id from figures) group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge where fig_id in (select id from figures) group by fid_id
)
select id, name, vertex_cost, edge_cost
    from figures
    left join vertex_cost vc on figures.id=vc.fig_id
    left join edge_cost ec on figures.id=ec.fig_id

Answer 3

[Urvin]

SELECT
	fig.id,
	SUM(vertex_cost) / COUNT(DISTINCT edge.id) vertex_sum,
	SUM(edge_cost) / COUNT(DISTINCT vertex.id) ege_sum,
	MIN(vertex_cost) vertex_min,
	MIN(edge_cost) edge_min,	
	MAX(vertex_cost) vertex_max,
	MAX(edge_cost) edge_max		
FROM 
	fig
	JOIN vertex
		ON fig.id = vertex.fig_id
	JOIN edge 
		ON fig.id = edge.fig_id
GROUP BY
	fig.id

Comments

[pqgg7nwkd4]

Конечно, этот трюк здесь сработает. К сожалению, не сработает с более сложными случаями, например, при array_agg.

[Urvin]

pqgg7nwkd4: SQL-запрос отражает решение Вашей задачи с обозначенным конечным набором условий.

[pqgg7nwkd4]

Urvin: никто же не спорит.