@vgray
Раз в 3-4 дня приходят данные о показаниях в виде огромного csv файла, которы выглядит вот так
sensor_id,timestamp,value
Нужно хранить историю изменения показаний.
Если хранить в лоб, то будет много дубликатов, которые отличаются только датой. хочется хранить только те строки, которые отличаются от предыдущей
Те если приходили такие данные
sensor_N1,2021-02-01,100
sensor_N1,2021-02-10,100
sensor_N1,2021-02-21,115
sensor_N1,2021-02-25,115
sensor_N1,2021-03-01,115
sensor_N1,2021-03-11,100
то хотелось бы оставить только строки
sensor_N1,2021-02-01,100
sensor_N1,2021-02-21,115
sensor_N1,2021-03-11,100
В какую сторону смотреть, чтобы такое реализовать? .
Пока придумались такие варианты
1) Если при инсерте делать select на предыдущую строку — то это ужасно медленно (500 миллионов датчиков)
2) Перед импортом данных, делать экспорт последней записи для всех датчиков, скриптами отпределять данные которые изменились и заливать только измененные данные
3) Что-то еще?
Смотрим любую базу которая позволит решить эту задачу.
Решения вопроса 0
Ответы на вопрос 4
@LaRN
Т. е. грузим текущие данные и если по датчику значение не поменялось, то просто изменяем дату окончания интервала на дату текущей загрузки, если значение датчика поменялось(отличается от сохраненного в оперативной таблице) , то текущий интервал выгружаем в архивную таблицу, а в оперативной добавляем новую(изменяем существующую) запись для датчика у которой дата начала и дата окончания будет равна дате текущей загрузки, а значение текущему загружаемому значению датчика.
Т. е. в оперативной таблице всегда количество записей равно количеству датчиков, а в исторической весь скоп предыдущих значений.
Это должно защитить от того, что с течением времени скорость работы с оперативной таблицей будет деградировать, от того что там будет расти число записей.
Если же нужно какой-то отчёт строить или выгрузку за период или за прошлые даты, то тут уже нужно будет работать с исторической таблицей и это будет уже не очень быстро, но такие операции обычно не требуется часто выполнять.
Так как данные грузятся раз в 3-4 дня, наверное не очень критично, что загрузка будет выполнять не мгновенно. Тут наверное проще в несколько этапов сделать: на первом проходе построить список идентификатор датчиков по которым значение не поменялось, затем по этому списку проапдейтить дату окончания интервала на дату текущей загрузки, затем по датчикам которые не попали в список перелить строки в архивную таблицу и наконец поменять для изменённый датчиков в оперативной таблице значение счётчика и даты начала и окончания интервала.
Всё шаги можно делать массово.
@ky0
Засовывать кучу лишней информации в базу, и только потом вычищать — контрпродуктивно. С этим справится простейший скрипт, пробегающий по всем строчкам.
@ComodoHacker
1) Если при инсерте делать select на предыдущую строку — то это ужасно медленно (500 миллионов датчиков)
А вы пробовали? Если делать умеючи, то нормально.
Заливаете порцию данных, скажем, в 100 тыс. строк в промежуточную таблицу. Делаете INSERT … SELECT … JOIN. Ну и индексы правльно настроить.
3) Что-то еще?
Например. Вместо INSERT делаете UPDATE. На UPDATE вешаете триггер, который делает копию текущего значения, если оно отличается.
@MaLuTkA_UA
WITH t AS (SELECT *, row_number() over (PARTITION BY id ORDER BY created_at) as row FROM temporary_data),
d AS (SELECT DISTINCT ON (id) * FROM current_data ORDER BY id, created_at DESC)
SELECT t.*
FROM t
LEFT JOIN t t2 ON t.id = t2.id AND t.row = t2.row+1
LEFT JOIN d ON t.row = 1 AND t.id = d.id
WHERE t.value <> coalesce(t2.value, d.value);