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

Question

[Артем]

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

Столкнулся сегодня со сложной задачкой, на которую не хватило скиллов. Обращаюсь к сообществу за помощью.
Итак, есть таблица history с примерно такой структурой:
id | task_id | phrase_id | date | position
В эту таблицу попадают некие исторические данные - один или несколько раз в сутки выполняются некоторые задания, которые просто пишут в эту таблицу новую позицию (int) по фразе (int) и текущее время (timestamp).
Стоит задача одним запросом извлечь суммарную информацию: сколько позиций фраз выросло, сколько позиций просело, сколько позиций осталось неизменными при последней проверке по сравнению с предыдущей проверкой для каждой задачи по всем фразам, принадлежащим этой задаче.
СУБД - postgresql.
Помогите, плиз, мозг ломается.

Answer 1

[Melkij]

Каждая проверка - целостный срез данных? То есть может быть такое, что для фразы 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)

Comments

[Артем]

Спасибо за ответ. Изучаем TimescaleDB, пока к результатам не пришли.
Каждая проверка - это целостный срез данных. По всем фразам проверки осуществляются регулярно и пакетно, но интервал между ними непредсказуем.

[Артем]

Предыдущая проверка - суть набор строк, у которых date одинаково, т.к. date записывается бекэндом, а не средствами БД.

[Артем]

И этот набор строк имеет всегда одно и то же количество строк.

[Артем]

Например,
текущая проверка: 100 строк за 06.07.2020 00:15:11
предыдущая проверка : 100 строк за 05.07.2020 00:18:43

[Melkij]

Тогда ответ актуален для этого случая. Вам нужен full join срезов по фразе и получите удобное сравнение хоть сразу "эта позиция увеличилась на 7 пунктов"

Answer 2

[Dimonchik]

скиллы тут
https://habr.com/ru/company/oleg-bunin/blog/464303/

ну и ++ по timescaledb

Comments

[Артем]

Спасибо, изучу.

Answer 3

[profesor08]

Проверки тоже должны быть в бд. Достаточно знать дату проверки и id последнего проверенной записи в истории. Потом берешь самую свежую запись в истории, берешь самую свежую запись проверки, по ней берешь последнюю проверенную запись из истории, и сравниваешь. Потом записываешь данные проверки в бд. Если хочется, то можешь воткнуть в один запрос, либо транзакцией, либо еще как-то.