Поиск разницы в строках в запросе с помощью SQLite
У меня есть таблица SQLite, в которой указаны цены на различные товары. Это таблица моментальных снимков, поэтому она содержит цены с 5-минутными интервалами. Я хотел бы написать запрос, который возвращал бы разницу в цене от одной строки к другой по каждому элементу.
Столбцы: id (auto inc), record_id (id продукта), price (цена в этот момент времени), time (всего в секундах с начала эпохи).
Я пытаюсь вернуть столбец «разница», содержащий значение разницы между интервалами.
Given the following id record_id price time 1 apple001 36.00 ... 67 apple001 37.87 ... 765 apple001 45.82 ... 892 apple001 26.76 ... I'd like it to return id record_id price time difference 1 apple001 36.00 ... 0 67 apple001 37.87 ... 1.87 765 apple001 45.82 ... 7.95 892 apple001 26.76 ... -19.06
Возможно ли это с SQLite?
Во-вторых, если это возможно - есть ли способ ограничить его последними 5 или около того записями?
Буду признателен за любую помощь, спасибо.
Просто хотел добавить несколько вещей. Я нашел способы сделать это в других базах данных, но я использую XULRunner, то есть SQLite. Вот почему я работаю с этим.
Второстепенный вопрос, возможно, потребует уточнения, я хочу упорядочить по времени, взять и проанализировать последние 5 записей. При необходимости я могу решить эту проблему отдельно.
Вот MySQL решение, вроде. Я иду к этому подходу, но главное - «Если таблица содержит столбец последовательности, но есть пробелы, измените его нумерацию. Если в таблице нет такого столбца, добавьте его». По замыслу, в этом сценарии есть пробелы, так как одновременно обновляется много записей, и это не по порядку.
Ответы 2
Я не знаю, есть ли в SQLite какие-то ограничения, но вы можете попробовать следующие операторы, которые работают в Sql Server.
Если разница во времени постоянная (вы указываете, что это 5 минут), вы можете написать:
SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A
LEFT OUTER JOIN Table1 B ON A.record_id = B.record_id AND A.time - B.time = 5
иначе
SELECT A.id, A.record_id, A.price, A.time, ISNULL(A.price - B.price, 0) AS difference
FROM Table1 as A
LEFT OUTER JOIN Table1 B ON B.record_id = A.record_id
AND B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)
Утверждение без объединений выглядит следующим образом
SELECT id, record_id, price, time,
(SELECT A.price - B.price
FROM Table1 as B
WHERE B.record_id = A.record_id AND
B.time = (SELECT MAX(time) FROM Table1 C WHERE C.time < A.time AND C.record_id = A.record_id)) AS difference
FROM Table1 as A
Надеюсь, что один из них вам поможет.
Да, это немного поздно, но для полноты картины. В SQLite Release 3.25.0 в 2018 добавлена поддержка оконных функций.. И указанная выше задача теперь может быть выполнена с помощью функций LAG () и LEAD ().
Взято из: https://www.sqlitetutorial.net/sqlite-window-functions/
LAGПредоставляет доступ к строке с заданным физическим смещением, которое предшествует текущей строке.
Таким образом, используя команду sqlite3 в Linux, следующее должно соответствовать выходным данным, указанным в вашем вопросе. Первые 2 команды предназначены только для отображения правильного формата вывода.
sqlite> .mode columns
sqlite> .headers on
sqlite> CREATE TABLE data(id INT, record_id TEXT, price REAL);
sqlite> INSERT INTO data VALUES(1,"apple001",36.00);
sqlite> INSERT INTO data VALUES(67,"apple001",37.87);
sqlite> INSERT INTO data VALUES(765,"apple001",45.82);
sqlite> INSERT INTO data VALUES(892,"apple001",26.76);
sqlite> SELECT id, record_id, price, (price - LAG(price, 1) OVER (ORDER BY id)) AS difference FROM data;
id record_id price difference
---------- ---------- ---------- ----------
1 apple001 36.0
67 apple001 37.87 1.87
765 apple001 45.82 7.95
892 apple001 26.76 -19.06
Я надеюсь, что это сэкономит время новым пользователям.
Немного поздно, но все же мне помогли !, Спасибо.
Что лучше - иметь соединения или не иметь соединений и почему?