Как SQL запросом узнать историю изменения данных?

Question

[udi]

Здравствуйте.

Есть две таблицы
CREATE TABLE links ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [link_id] CHAR);
и
CREATE TABLE history_yap ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [link_id] INTEGER, [time] INTEGER, [value] INTEGER);

В первой таблице хранятся данные, во второй - история изменения неких параметров, относящихся в этим данным.

Как можно одним запросом определить, было ли изменение данных?

Для примера:
в таблице 1 данные
id=1
link_Id=123

в таблице 2 данные:
1 123 1390444262 0
2 123 1390444263 1

В этом случае запрос должен вывести 1, т.к. изменение было (в момент времени 1390444262 было 0, а в момент времени 1390444263 стало 1).

Актуально для SqLite.
Спасибо.

Answer 1

[Сардар]

SELECT t.id, COUNT(*), SUM(t.ct)
FROM (SELECT l.id, y.value, COUNT(*) as ct
    FROM links l
    INNER JOIN history_yap y ON l.link_id = y.link_id
    GROUP BY l.id, y.value) t
GROUP BY t.id
UNION ALL
SELECT l.id, -1, -1
FROM links l
LEFT JOIN history_yap y ON l.link_id = y.link_id
WHERE y.id IS NULL;

Логика почти та же, но во внутреннем запросе мы также считаем сколько всего было записей в links X history_yap. Также добавлен второй запрос на ссылки без каких либо записей в history_yap. В результате имеем таблицу с тремя колонками:
* link.id - искомое
* количество уникальных значений. Если -1, то в history_yap для этого links.id не было ни одной записи. Если больше 1, то есть минимум два history_yap для этой ссылки с разными значениями. Если 1, то смотрим на третью колонку.
* количество строк в links X history_yap для каждого links.id. Если во второй колонке 1 и в третьей колонке 1, то для этого links.id есть всего одна запись history_yap. Все остальные значения игнорируем.

P.S. ответил новым постом, т.к. в комментариях код не добавить.

Comments

[udi]

Просто супер. Благодарю.

Answer 2

[Сардар]

SELECT t.id, COUNT(*)
FROM (SELECT l.id, y.value
    FROM links l
    INNER JOIN history_yap y ON l.link_id = y.link_id
    GROUP BY l.id, y.value) t
GROUP BY t.id
HAVING COUNT(*) > 1;

Требуются индексы на link_id для быстрой работы. Логика:
* выбрать все ссылки и их историю изменений, нас интересуют уникальные значения. Этот внутренний select можно дополнить where, выбирающий строки только с определенного времени.
* полученную таблицу снова группируем по id, получаем ссылку и количество уникальных значений.
* дополняем условием "если уникальных значений больше 1", а значит было изменение.

Запрос видит только изменения с одного значения на другое. Моменты когда ссылка была добавлена в links, а также первую запись истории в history_yap запрос не видит.

Comments

[udi]

Спасибо. Как можно модифицировать запрос, чтобы он возвращал, скажем, -1, если в history_yap по конкретному link_id нет записей или всего одна запись?