@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
  • Вопрос задан
  • 269 просмотров
Пригласить эксперта
Ответы на вопрос 3
@dmitryKovalskiy
программист средней руки
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

Можно примерно так. Аналогично мудрить со второй задачей.
Ответ написан
Melkij
@Melkij
PostgreSQL DBA
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
Ответ написан
Комментировать
Urvin
@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
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы