Является ли это идеальным SQL-запросом для извлечения случайных элементов

#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. еще один потрясающий упрощенный запрос