Агрегация с двумя объединениями (MySQL)

У меня есть один стол под названием галерея. Для каждой строки в галерее есть несколько строк в таблице. Одна картина принадлежит одной галерее. Тогда есть голосование стола. Там каждый ряд является положительным или отрицательным для определенной галереи. Вот (упрощенная) структура:

gallery ( gallery_id )
picture ( picture_id, picture_gallery_ref )
vote ( vote_id, vote_value, vote_gallery_ref )

Теперь я хочу, чтобы один запрос дал мне следующую информацию: все галереи с собственными полями данных и количеством изображений, которые подключены к галерее, и суммированным значением голосов.

Вот мой запрос, но из-за многократного объединения агрегированные значения не являются правильными. (По крайней мере, когда есть более одного ряда фотографий или голосов.)

SELECT 
  *, SUM( vote_value ) as score, COUNT( picture_id ) AS pictures
FROM 
  gallery
LEFT JOIN 
  vote
  ON gallery_id = vote_gallery_ref
LEFT JOIN 
  picture
  ON gallery_id = picture_gallery_ref
GROUP BY gallery_id

Потому что я заметил, что COUNT( DISTINCT picture_id ) дает мне правильное количество фотографий, которые я пробовал это:

( SUM( vote_value ) / GREATEST( COUNT( DISTINCT picture_id ), 1 ) ) AS score

В этом примере это работает, но что, если в одном запросе было больше объединений?

Просто хочу узнать, есть ли лучший или более "элегантный" способ решения этой проблемы. Также я хотел бы знать, является ли мое решение специфичным для MySQL или стандартным SQL?

Источник

4 ответа

Решение

Эта цитата из Уильяма Охама применима здесь:

Enita non sunt Multiplicanda

(Латынь для "сущностей не должно быть умножено сверх необходимости").

Вы должны пересмотреть, почему вам нужно сделать это в одном запросе? Это правда, что один запрос имеет меньше накладных расходов, чем несколько запросов, но если природа этого отдельного запроса становится слишком сложной, как для разработки, так и для выполнения СУБД, тогда выполняйте отдельные запросы.

Источник

Или просто используйте подзапросы...

Я не знаю, если это правильный синтаксис MySQL, но вы можете сделать что-то похожее на:

SELECT
  gallery.*, a.score, b.pictures
LEFT JOIN
(
  select vote_gallery_ref, sum(vote_value) as score
  from vote
  group by vote_gallery_ref
) a ON gallery_id = vote_gallery_ref
LEFT JOIN 
(
  select picture_gallery_ref, count(picture_id) as pictures
  from picture
  group by picture_gallery_ref
) b ON gallery_id = picture_gallery_ref
Источник

Как часто вы добавляете / меняете записи голосов?

Как часто вы добавляете / удаляете записи изображений?

Как часто вы запускаете этот запрос для этих итогов?

Может быть лучше создать полные поля в таблице галереи (total_pictures, total_votes, total_vote_values).

Когда вы добавляете или удаляете запись в таблице изображений, вы также обновляете сумму в таблице галереи. Это можно сделать с помощью триггеров на таблице изображений для автоматического обновления таблицы галереи. Это также можно сделать, используя транзакцию, объединяющую два оператора SQL для обновления таблицы изображений и таблицы галереи. При добавлении записи в таблицу изображений увеличивается total_pictures поле на столе галереи. Когда вы удаляете запись из таблицы изображений, уменьшается total_pictures поле.

Аналогично, когда запись о голосовании добавляется или удаляется или vote_value изменения вы обновляете total_votes а также total_vote_values поля. Добавление записи увеличивает total_votes поле и добавляет vote_values в total_vote_values, Удаление записи уменьшает total_votes поле и вычитает vote_values от total_vote_values, обновление vote_values в протоколе голосования также должны обновить total_vote_values с разницей (вычесть старое значение, добавить новое значение).

Ваш запрос теперь становится тривиальным - это простой запрос из таблицы галереи. Но это происходит за счет более сложных обновлений таблиц с фотографиями и голосованиями.

Источник

Как сказал Билл Карвин, делать все это в рамках одного запроса довольно некрасиво.

Но, если вам нужно сделать это, для объединения и выбора неагрегированных данных с агрегированными данными требуется объединение с подзапросами (в последние несколько лет я не так часто использовал SQL, поэтому я действительно забыл подходящий термин для этого).

Давайте предположим, что ваша таблица галереи имеет дополнительные поля name а также state:

select g.gallery_id, g.name, g.state, i.num_pictures, j.sum_vote_values
from gallery g
inner join (
  select g.gallery_id, count(p.picture_id) as 'num_pictures'
  from gallery g
  left join picture p on g.gallery_id = p.picture_gallery_ref
  group by g.gallery_id) as i on g.gallery_id = i.gallery_id
left join (
  select g.gallery_id, sum(v.vote_value) as 'sum_vote_values'
  from gallery g
  left join vote v on g.gallery_id = v.vote_gallery_ref
  group by g.gallery_id
) as j on g.gallery_id = j.gallery_id

Это даст набор результатов, который выглядит следующим образом:

gallery_id, name, state, num_pictures, sum_vote_values
1, 'Gallery A', 'NJ', 4, 19
2, 'Gallery B', 'NY', 3, 32
3, 'Empty gallery', 'CT', 0,
Источник
Другие вопросы по тегам