SQL сравнение строк по датам?

Question

[Алексей Лихачев]

Имеется таблица log с логами следующего содержания:
id | link_id | date | value
где id порядковый номер, link_id связь с другой таблицей, date дата создания записи, value значение.

Требуется найти разность value у последней и предпоследней даты для каждого link_id, у самого получается что-то подобное:

select log1.link_id,log2.link_id,log1.value,log2.value,log1.value - log2.value as diff,log1.date,log2.date
from log as log1, log as log2
where log1.link_id = log2.link_id and 
log2.date = (select max(date) from log as log3 where log3.link_id = log2.link_id) and 
log1.date = (select max(date) from log as log4 where log4.link_id = log1.link_id and log4.date = (select max(date) from log as log5 where log5.link_id = log4.link_id))

По моему мнению логика такая: выводим разницу между value где совпадают link_id и где одна дата максимальная, другая перед максимальная

Comments

[SabMakc]

Какая используется СУБД?
Необходимо именно получить в виде запроса, или можно дообработать потом данные в коде приложения?

[Алексей Лихачев]

MySQL, хотелось бы конечно сразу запросом, плюс сразу бы получить возможность сортировки по этому новому полю.
В коде обрабатывать очень не хотелось бы, но в целом если не получится, то это и придется делать

[SabMakc]

Повторяются ли даты в date для одного link_id?
Можно ли утверждать, что и id идут по порядку возрастания date в рамках link_id?

[Алексей Лихачев]

даты повторяться могут, но в таком случае value у них одинаковое, плюс периодически удаляем такие повторы.
id идут однозначно по порядку возрастания дат в рамках link_id
забыл уточнить id это инкремент

Answer 1

[kirillzorin]

А чем вас ваш собственный вариант не устраивает? Там нужно только исправить "=" на "<" в условии "log4.date =".

Comments

[Алексей Лихачев]

Как-то не красиво, нет возможности сортировки по diff, под запрос в подзапросе, при этом первый подзапросе одинаковый. Думаю должно быть более элегантное решение

[kirillzorin]

Сортировать можно. Чуть-чуть упростить тоже можно:

select
log1.link_id,
log2.value - log1.value as diff,
log2.date,
log1.date
from log as log1 join log as log2 on log1.link_id = log2.link_id
join (select max(date) d, link_id
from log
group by link_id) as log3 on log1.link_id = log3.link_id
where
log2.date = log3.d and
log1.date = (select max(date)
from log as log4
where log4.link_id = log1.link_id and log4.date < log3.d)
order by diff;

Answer 2

[SabMakc]

Для MS SQL можно использовать такой запрос:

select link_id, sum(case NUM when 1 then [date] when 2 then [date]*(-1) else 0 end)
from (select ROW_NUMBER() over (PARTITION BY link_id order by [date]) as NUM, link_id, [date] from [log])t
group by link_id

Comments

[Алексей Лихачев]

но в MySQL не заведется?

Answer 3

[Александр]

@SabMakc, немного не верно. Нам нужна разница value, а не [date]. И что за [date]*(-1)???
Правильно будет так:

select link_id, sum(case NUM 
                      when 1 then value 
		      when 2 then value*(-1) 
		      else 0 
		    end)
from (select ROW_NUMBER() over (PARTITION BY link_id order by [date] desc) as NUM, link_id, value from log) t
group by link_id

Comments

[Алексей Лихачев]

но опять же решение не для MySQL

[SabMakc]

Согласен, нужен value. А [date]*(-1) - зависит от того, в каком виде представить дату. Для стандартных DATETIME и DATE не подойдет, но если привести их к INT - то вполне. Но в свете value это не актуально.

[Алексей Лихачев]

но опять же решение не для MySQL, получается кроме как делать два запроса, а затем обрабатывать данные нет вариантов? ну или мой почти рабочий, но не оптимальный запрос?

[SabMakc]

Да, 2 запроса - самый оптимальный вариант.
Запросом 1 выбираем максимальные значения.
Запросом 2 выбираем максимальные значения, при условии что id не попало в запрос 1.
И потом обрабатываем результат.

В принципе, используя UNION ALL можно добиться и 1 общего запроса (если использовать его вместо внутреннего подзапроса).

По оптимальности... Я очень сильно сомневаюсь, что предложенный вариант для MS SQL можно назвать оптимальным.

[Алексей Лихачев]

хм, очень хорошая идея на счет условия что id не попало в первый запрос

[SabMakc]

В данном случае учтите, что возможны дубли по value и
link_id, которые надо считать как единую запись.