WedX - журнал о программировании и компьютерных науках

Найти все сообщения, где последние комментарии были оставлены гостем вчера

Доброе утро, ребята,

Я думаю, как написать это легко и эффективно

Итак, у меня есть таблица сообщений (id, user_id, created_at) и таблица комментариев (id, user_id, post_id, created_at). Мне нужно найти все сообщения, где их последний комментарий был оставлен гостевым пользователем (user_id равен NULL) и со вчерашнего дня. Таким образом, в этих постах не должно быть комментариев за сегодняшний день, но должен быть хотя бы один за вчерашний день. И ПОСЛЕДНИЙ комментарий со вчерашнего дня должен быть добавлен гостевым пользователем (никаких других комментариев после этого).

Я могу получить то, что мне нужно, но сложно написать умный запрос, который будет выполняться эффективно. Предполагаемое количество записей в результате — более 1к (у нас каждый день тысячи новых постов и десятки тысяч новых комментариев).

Имеет ли смысл добавлять индекс к полям created_at и, возможно, мне придется каким-то образом денормализовать свои данные, чтобы добиться хороших результатов (до 500 мс в худшем случае)

Любые мысли приветствуются.

Спасибо.

postgresql sql query-optimization where-clause greatest-n-per-group
30.08.2020

  • Можете ли вы поделиться существующим запросом, структурой таблицы и индексами? 30.08.2020
  • Структура индексов может быть гибкой. Поля таблицы довольно очевидны. POSTS(ID, user_id, created_at,...) --- COMMENTS(ID, user_id, post_id, created_at...) Спасибо. 30.08.2020
  • Кто-нибудь, пожалуйста?... 30.08.2020
  • Опубликуйте свой фактический запрос, как запросил @mohdatif, а не повторение описания вашей таблицы. Далее опубликуйте фактическую таблицу DDL, включая все индексы и план объяснения для запроса. 30.08.2020

Ответы:


1

Вы не показали свой текущий запрос. Одним из вариантов было бы написать это с боковым соединением, которое извлекает последний комментарий для post_id, а затем с фильтром в предложении where:

select *
from posts p
cross join lateral (
    select c.*
    from comments c
    where c.post_id = p.id
    order by c.created_at desc limit 1
) c
where c.user_id is null

Этот запрос должен использовать преимущества индекса comments(post_id, created_at desc, user_id) (или, возможно, comments(post_id, created_at desc, user_id)).

Мы также можем попробовать предварительно отфильтровать таблицу комментариев с помощью distinct on:

select *
from posts p
inner join (
    select distinct on (post_id) *
    from comments 
    order by post_id, created_at desc
) c on c.post_id = p.id
where c.user_id is null
30.08.2020

2

Вы изучали использование EXPLAIN ANALYZE? Это должно помочь вам выяснить, улучшит ли добавление индексов время выполнения запросов, хотя, если вы еще не использовали его раньше, вам, несомненно, потребуется потратить некоторое время на изучение того, как его использовать.

30.08.2020

3

Если вы ищете посты и для эффективности, я бы порекомендовал:

select c.post_id
from (select distinct on (post_id) c.*
      from comments c
      where c.created_on >= current_date - interval '1 day'
      order by post_id, created_on desc
     ) c
where c.created_on < current_date and
      c.user_id is null;

Для этого вам нужен индекс comments(created_on, post_id).

30.08.2020
Новые материалы
Как проанализировать работу вашего классификатора?
Не всегда просто знать, какие показатели использовать С развитием глубокого обучения все больше и больше людей учатся обучать свой первый классификатор. Но как только вы закончите..
Работа с цепями Маркова, часть 4 (Машинное обучение)
Нелинейные цепи Маркова с агрегатором и их приложения (arXiv) Автор : Бар Лайт Аннотация: Изучаются свойства подкласса случайных процессов, называемых дискретными нелинейными цепями Маркова..
Crazy Laravel Livewire упростил мне создание электронной коммерции (панель администратора и API) [Часть 3]
Как вы сегодня, ребята? В этой части мы создадим CRUD для данных о продукте. Думаю, в этой части я не буду слишком много делиться теорией, но чаще буду делиться своим кодом. Потому что..
Использование машинного обучения и Python для классификации 1000 сезонов новичков MLB Hitter
Чему может научиться машина, глядя на сезоны новичков 1000 игроков MLB? Это то, что исследует это приложение. В этом процессе мы будем использовать неконтролируемое обучение, чтобы..
Учебные заметки: создание моего первого пакета Node.js
Это мои обучающие заметки, когда я научился создавать свой самый первый пакет Node.js, распространяемый через npm. Оглавление Глоссарий I. Новый пакет 1.1 советы по инициализации..
Забудьте о Matplotlib: улучшите визуализацию данных с помощью умопомрачительных функций Seaborn!
Примечание. Эта запись в блоге предполагает базовое знакомство с Python и концепциями анализа данных. Привет, энтузиасты данных! Добро пожаловать в мой блог, где я расскажу о невероятных..
ИИ в аэрокосмической отрасли
Каждый полет – это шаг вперед к великой мечте. Чтобы это происходило в их собственном темпе, необходима команда астронавтов для погони за космосом и команда технического обслуживания..
Для любых предложений по сайту: [email protected]