devspec
@devspec
Помогло? Отметь решением

Как составить сложный SQL запрос?

Приветствую, коллеги!

Столкнулся сегодня со сложной задачкой, на которую не хватило скиллов. Обращаюсь к сообществу за помощью.
Итак, есть таблица history с примерно такой структурой:
id | task_id | phrase_id | date | position
В эту таблицу попадают некие исторические данные - один или несколько раз в сутки выполняются некоторые задания, которые просто пишут в эту таблицу новую позицию (int) по фразе (int) и текущее время (timestamp).
Стоит задача одним запросом извлечь суммарную информацию: сколько позиций фраз выросло, сколько позиций просело, сколько позиций осталось неизменными при последней проверке по сравнению с предыдущей проверкой для каждой задачи по всем фразам, принадлежащим этой задаче.
СУБД - postgresql.
Помогите, плиз, мозг ломается.
  • Вопрос задан
  • 601 просмотр
Решения вопроса 1
Melkij
@Melkij
PostgreSQL DBA
Каждая проверка - целостный срез данных? То есть может быть такое, что для фразы 5 последняя проверка была в 2020-07-05, а для фразы 6 надо отматывать в 2020-07-03?

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

select count(*) filter(where r1.position < r2.position) as pos_down,
count(*) filter(where r1.position = r2.position) as pos_same,
count(*) filter(where r1.position > r2.position) as pos_up from ...


Укус второй: надо откуда-то получить r1 и r2 соответственно строки таблицы за сравниваемые срезы. Это
tablename r1 full join tablename r2 on r1.phrase_id = r2.phrase_id where r1.... and r2....


Доедаем слона:
нужно дописать условие фильтрации и достать для r1 данные одного среза, для r2 - другого. Тут у вас не вполне конкретизировано что есть "предыдущая проверка". Например, достанем из таблицы tasks предыдущий task_id:
where r2.task_id = 123 and r1.task_id in (select id from tasks where id < 123 order by id desc limit 1)
Ответ написан
Пригласить эксперта
Ответы на вопрос 2
dimonchik2013
@dimonchik2013
non progredi est regredi
profesor08
@profesor08
Проверки тоже должны быть в бд. Достаточно знать дату проверки и id последнего проверенной записи в истории. Потом берешь самую свежую запись в истории, берешь самую свежую запись проверки, по ней берешь последнюю проверенную запись из истории, и сравниваешь. Потом записываешь данные проверки в бд. Если хочется, то можешь воткнуть в один запрос, либо транзакцией, либо еще как-то.
Ответ написан
Комментировать
Ваш ответ на вопрос

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

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