#sql #postgresql #join
#sql #postgresql #Присоединиться
Вопрос:
У меня есть приведенный ниже запрос для извлечения книг случайного автора и упорядочения по дате последнего изменения книг. Поскольку я новичок в SQL, что-то подсказывает мне, что этот запрос можно улучшить и упростить. Я был бы признателен за помощь экспертов по SQL, если это уже упрощено, тогда я буду считать себя экспертом по SQL :).
SELECT b.id, bc.author FROM book_authors bc
INNER JOIN books b ON bc.book = b.id
WHERE bc.author = (SELECT author FROM book_authors ORDER BY random() limit 1) AND b.status = 'GOOD'
GROUP BY b.id, bc.author
ORDER BY MAX(b.modified_date) DESC
LIMIT 10 OFFSET 0 -- for pagination purposes
Структуры таблиц
book_authors
book author
1 1
2 3
3 1
books
id status modified_date
1 GOOD 01-01-2010
2 GOOD 02-01-2010
3 GOOD 03-01-2010
authors
id
1
2
3
ищете вывод, подобный
authorId bookId
1 (a random author) 3
1 (same random author) 1
Комментарии:
1. каков ваш ожидаемый результат?
2. обновлен вопрос, это нормально?
3. Вам нужны только авторы, у которых есть «ХОРОШИЕ» книги? Вас волнует производительность?
4. @GordonLinoff да, да, я делаю
Ответ №1:
Ваш запрос не использует случайную выборку авторов. Он берет случайную выборку book_authors
, которая отличается. В частности, с большей вероятностью будут выбраны авторы с большим количеством книг. Рассмотрим:
author_id book_id
1 1
1 2
1 3
2 4
«Случайная» выборка из этой таблицы будет выбираться 1
в три раза чаще, чем 2
. Такие предвзятые результаты — это не то, как я интерпретирую «выборку книг случайного автора».
Итак, вы должны выполнять выборку из authors
таблицы. Структура вашего запроса является разумной (см. Ниже), хотя агрегирование не требуется:
SELECT b.id, bc.author
FROM book_authors bc INNER JOIN
books b
ON bc.book = b.id
WHERE bc.author = (SELECT a.id FROM authors id ORDER BY random() LIMIT 1) AND
b.status = 'GOOD'
ORDER BY b.modified_date DESC;
Теперь интересно, действительно ли это делает то, что вы хотите. Как написано, у Postgres есть два способа выполнить это:
- Запуск подзапроса один раз для запроса. Это получение одного случайного автора и использование его повсюду.
- Выполнение подзапроса один раз для сравнения в
WHERE
.
Обычно при каждом запуске подзапрос возвращает разные результаты (технически подзапрос является «недетерминированным»).). Итак, второй метод будет иметь другого случайного автора для каждого сравнения — и это не те результаты, которые вы хотите. На практике оптимизатор Postgres (я думаю) игнорирует неопределенный характер подзапроса и выполняет его только один раз.
Чтобы устранить эту потенциальную проблему, лучше перенести логику в FROM
предложение; тогда оно оценивается только один раз:
SELECT b.id, bc.author
FROM (SELECT a.id
FROM authors id
ORDER BY random()
LIMIT 1
) a INNER JOIN
book_authors bc
ON bc.author = a.id INNER JOIN
books b
ON bc.book = b.id
WHERE b.status = 'GOOD'
ORDER BY b.modified_date DESC;
Примечание: использование ORDER BY random() LIMIT 1
для извлечения одной случайной строки работает. Однако для чего угодно, кроме небольших таблиц, это довольно дорого с точки зрения производительности. У меня бы не вошло в привычку использовать это.
Комментарии:
1. спасибо, что нашли время, чтобы написать объяснение для странного человека, которого вы не знаете. Спасибо. Запрос отлично работает. что касается
random
любого предложения, которое у вас есть?2. @Eric . , , я бы предложил задать новый вопрос (или провести некоторое исследование). Как я уже сказал, для небольших таблиц это нормально. И если вы изучаете SQL, таблицы, вероятно, маленькие.
Ответ №2:
Вы можете попробовать следующее — вам не нужно group by clause
и MAX(b.modified_date)
в предложении order by
SELECT b.id, bc.author
FROM book_authors bc INNER JOIN books b ON bc.book = b.id
where b.status = 'GOOD'
and bc.author = (SELECT author FROM book_authors ORDER BY random() limit 1)
ORDER BY b.modified_date DESC
LIMIT 10
Ответ №3:
Нет необходимости в фильтрации. Я бы сделал случайный выбор в подзапросе, а затем присоединил его к книгам. Я также не вижу смысла в агрегировании.
Итак:
select b.id, bc.author
from (select * from book_authors order by random() limit 1) bc -- just one random author
inner join books b on bc.book = b.id
where b.status = 'GOOD'
order by b.modified_date desc
limit 10 offset 0
Комментарии:
1. еще один потрясающий упрощенный запрос