Получить верхнее состояние истории статусов

@uaggster · Регистрация: 14.10.2022

Студворк — интернет-сервис помощи студентам

Коллеги, помогите советом!

Есть некая система, которая отслеживает состояние неких приборов (типа электросчетчика, но это точно).
Приборы опрашиваются периодически, существенно асинхронно, каждый по собственному расписанию.
В табличку складывается история опроса приборов:
ИД прибора, Дата/время опроса, Статус прибора (... еще что-то до кучи, но это не существенно).
Задача:
1. Максимально быстро получить верхнюю, по времени, запись, относящуюся к прибору / группе приборов / ко всем приборам.
2. Максимально быстро регистрировать такие события.
3. Максимально быстро получить верхнюю, по времени, запись, относящуюся к прибору / группе приборов / ко всем приборам на определенный момент времени, ниже текущего (это наименее приоритетная задача). Если первые 2 работают быстро, то 3 - может потерпеть.

Нюансы: приборов 10-50 млн.
Количество опросов каждого прибора: ~ 1-100 раз в сутки.

В таблице/базе не обязательно хранить данные "за всегда". Примерно за месяц они теряют актуальность и хоронятся в хранилище.
Но на период актуальности они хранятся в одном месте, и нужно обеспечить пп. 1-3.

Собственно, вопрос: "Куды бечь?"
Такое впечатление, что это задача для кликхаус, но из клихаус у нас только MSSQLSERVER и костыли.
Пожалуйста, поделитесь идеями, даже тривиальными!

@Ludwig Valentin · 12.11.2024, 18:48

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

@uaggster · 12.11.2024, 20:24 **[ТС]**

Проблема в том, что количество записей в сырой рабочей таблице будет порядка 5 млрд. в устоявшемся состоянии.
Такая таблица сама по себе довольно тяжелая.
Хотелось бы сжать ее чем-то вроде колумнстора.

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

Была мысль разрезать таблицу на 2 секции - первую с установленным признаком "самая свежая запись", вторую - со сброшенным признаком.
И, соответственно при заливке пакета данных по опросам приборов (записи, разумеется, льются не по одной, а пакетами 1000-100000 записей, буферизация, короче говоря), мерждить порцию с установкой признака "самая свежая" вновь заливаемым данным, и сбрасыванием этого признака со всех остальных записей, соответствующих id приборов из загружаемого пакета.
И таким образом получить кусок таблицы в которой заведомо самые свежие записи лежат.
Но я что-то слабо представляю себе рост затрат на загрузку. ИМХО это будет чудовищно.

Или сразу лить данные в две таблицы: "самые свежие" и "все".
Но с заливкой во "все" вопросов нет. Берем и просто валим туда записи. Кластерный индекс делаем по (дате опроса счетчика DESC, ид счетчика), и используем для запросов типа 3.
А максимально быструю таблицу на 50 млн. - "только актуальные состояния" уже для типа 1.

Но встает вопрос поддержания такой таблицы. При заливке в ней опять придется искать и мерджить данные из пакета загрузки, с перезаписью всех данных, см. выше.
Будет ли стоить овчинка выделки?

@Аватар · 12.11.2024, 20:49

с двумя таблицами возможно еще "чудовищней" затраты будут, все таки вместо update будет insert и delete. а эксперимент с реальным объемом данных провести на одной таблице неподъемно?

@invm · 12.11.2024, 21:02

В качестве бредовой идеи - темпоральная таблица

@PaulWist · 13.11.2024, 08:29

Сообщение от uaggster

Приборы опрашиваются периодически, существенно асинхронно, каждый по собственному расписанию

Вопросы:

1. Кем/как опрашиваются приборы, предполагаю, что это SCADA-система или всё сразу валится в MSSQL прям с датчиков??

2. Если есть прослойка в виде SCADA, то какими порциями и по сколько датчиков, как часто она выдаёт на сервер БД ??

@uaggster · 13.11.2024, 09:36 **[ТС]**

Сообщение от PaulWist

1. Кем/как опрашиваются приборы, предполагаю, что это SCADA-система или всё сразу валится в MSSQL прям с датчиков??

Ну, отдельный слой конечно. Точнее - десятке два различных приложения, которые опрашивают датчики, буферизируют ответ и формируют пакет для "захоронения" на MSSQL. По собственному расписанию.
Это не реалтайм система, там мгновенная доступность данных не нужна.
Не совсем SCADA, но близко.

@PaulWist · 13.11.2024, 09:45

Сообщение от uaggster

десятке два различных приложения

Ага.

Значит если для каждого приложения своя таблица, то из 5млрд получаем 250 млн записей, уже легче

Нууу и вторая таблица/view нужна, куда триггером складывать "последние" значения.

PS а как сейчас организован сбор и хранение - всё в одну таблицу??

@uaggster · 13.11.2024, 14:15 **[ТС]**

Сообщение от PaulWist

Значит если для каждого приложения своя таблица, то из 5млрд получаем 250 млн записей, уже легче

Неа.
Но, в принципе, можно сегментировать по признаку опросчика или региона.

Сообщение от PaulWist

PS а как сейчас организован сбор и хранение - всё в одну таблицу??

Да.
В результате отклик системы за гранью добра и зла.

@katamoto · 13.11.2024, 14:56

Сообщение от uaggster

В результате отклик системы за гранью добра и зла.

А скинь ~~мячик~~ планы )

@Jack Famous · 13.11.2024, 16:11

uaggster, я бы делил по какому-нибудь признаку в отдельные таблицы.
Меньше таблица = выше скорость работы с ней.

@uaggster · 15.11.2024, 20:01 **[ТС]**

Сообщение от invm

В качестве бредовой идеи - темпоральная таблица

Пробуем покатать на тестах.

Сообщение от katamoto

А скинь мячик планы )

В нынешних нет ничего интересного. Clustered columnstore scan + compute scalar + sort.

Запросы примерно такого вида:

T-SQL
1
2
3
4
5
6
Select * from
(
Select *, Row_Number() over (partition by a.ID order by a.actuality_date DESC) N
From dbo.tbl a
) t
Where t.N=1

@invm · 17.11.2024, 18:54

1. Добавить столбец-признак - последняя добавленная строка или нет
2. Секционировать по этому столбцу
3. Пари вставке потребуется снимать признак у текущей "последней"

@Ludwig Valentin · 18.11.2024, 12:15

Сообщение от invm

Добавить столбец-признак - последняя добавленная строка или нет

Добавить столбец с датой записи.
Секционировать по этому столбцу.

@uaggster · 18.11.2024, 20:08 **[ТС]**

Сообщение от Ludwig Valentin

Добавить столбец с датой записи.
Секционировать по этому столбцу.

Нет, так не пойдет. Верхняя запись может появиться 2 минуты назад, а может и полгода как уже.

@Ludwig Valentin · 18.11.2024, 20:20

Сообщение от uaggster

Нет, так не пойдет. Верхняя запись может появиться 2 минуты назад, а может и полгода как уже.

Говорилось о миллионах записей.
Даже в худшем случае по-вашему архив за полгода будет пустой.

@uaggster · 18.11.2024, 20:23 **[ТС]**

Сообщение от invm

1. Добавить столбец-признак - последняя добавленная строка или нет
2. Секционировать по этому столбцу
3. Пари вставке потребуется снимать признак у текущей "последней"

Да, тоже думал об этом.
Но:
1. Добавлять записи придется через merge, а не insert. Ну, или upsert какой-то городить.
2. Возникнут накладные расходы при перемещении записей между секциями.

Собственно, а не будет ли выгоднее просто нарисовать индекс по полю "верхняя строка" + ИД, может даже и фильтрованный?
Хотя с секциями "верхняя" - "исторические", на первый взгляд, поинтереснее. Если сделать кластерный индекс по ИД +признак верхняя строка, и секционировать по признаку верхней строки, то кей-лукапов, в отличие от случая фильтрованного некластерного индекса не будет.
Да и сжать секции можно по-разному, насколько я понимаю. Верхнюю секцию оставить rowstore, историческую сделать columnstore (так, вроде, можно).

Буду думать.

Добавлено через 52 секунды

Сообщение от Ludwig Valentin

Даже в худшем случае по-вашему архив за полгода будет пустой.

Ну не полгода, ну два дня назад. Что это меняет?

@Ludwig Valentin · 18.11.2024, 20:25

Сообщение от uaggster

1. Добавлять записи придется через merge, а не insert. Ну, или upsert какой-то городить.
2. Возникнут накладные расходы при перемещении записей между секциями.

вы определитесь с анализом.
вы архивируете все текущие значения (insert)
или вам нужны уникальные значения состояния системы (merge)?

@uaggster · 18.11.2024, 20:40 **[ТС]**

1. Я архивирую все текущие
2. Для 80% случаев применения, нужно именно текущее, верхнее состояние.
3. Однако для 20% случаев - нужны все состояния прибора, либо N последних, либо не позднее какого-то момента времени.

Поэтому и была мысль хранить всё в двух таблицах. В первой - только верхнее состояние, во второй - все состояния.
Проблема в том, что это тоже не спасает от upsert'a
В верхнюю таблицу нужно будет merge, в общую - можно insert.
Но тут еще всякие негативные моменты с целостностью вылезут.

Мысль по поводу секционирования - мне нравится больше. Не спасает от upsert'a, но хоть экземпляр данных один, и не придется согласовывать данные.
Таблица с системным управлением версиями (темпоральная), тоже кажется интересным решением, но опыта работы с ними нет.
И в пользу секционирования плюсом идет возможность по-разному сжать секции, чего хотелось бы.

Добавлено через 2 минуты

Сообщение от Ludwig Valentin

или вам нужны уникальные значения состояния системы (merge)?

Чтобы выделить верхнюю запись признаком "текущая", нужно для всех остальных записей этого прибора этот признак сбросить.
Это upsert. Можно merge, можно на коленке.

Новые блоги и статьи Все статьи Все блоги /
PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Access VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.	Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .	Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .

@uaggster 1302 / 356 / 97 Регистрация: 14.10.2022 Сообщений: 1,084

	Получить верхнее состояние истории статусов 12.11.2024, 16:31. Показов 915. Ответов 18 Метки нет (Все метки) Коллеги, помогите советом! Есть некая система, которая отслеживает состояние неких приборов (типа электросчетчика, но это точно). Приборы опрашиваются периодически, существенно асинхронно, каждый по собственному расписанию. В табличку складывается история опроса приборов: ИД прибора, Дата/время опроса, Статус прибора (... еще что-то до кучи, но это не существенно). Задача: 1. Максимально быстро получить верхнюю, по времени, запись, относящуюся к прибору / группе приборов / ко всем приборам. 2. Максимально быстро регистрировать такие события. 3. Максимально быстро получить верхнюю, по времени, запись, относящуюся к прибору / группе приборов / ко всем приборам на определенный момент времени, ниже текущего (это наименее приоритетная задача). Если первые 2 работают быстро, то 3 - может потерпеть. Нюансы: приборов 10-50 млн. Количество опросов каждого прибора: ~ 1-100 раз в сутки. В таблице/базе не обязательно хранить данные "за всегда". Примерно за месяц они теряют актуальность и хоронятся в хранилище. Но на период актуальности они хранятся в одном месте, и нужно обеспечить пп. 1-3. Собственно, вопрос: "Куды бечь?" Такое впечатление, что это задача для кликхаус, но из клихаус у нас только MSSQLSERVER и костыли. Пожалуйста, поделитесь идеями, даже тривиальными! 0

@Ludwig Valentin Нарушитель 170 / 286 / 26 Регистрация: 11.08.2022 Сообщений: 3,609
	12.11.2024, 18:48
	Можно проиндексировать дату записи. Можно каждый месяц архивировать данные из текущей таблицы в большую таблицу архива- а текущая рассчитана на данные последнего месяца. 0

@uaggster 1302 / 356 / 97 Регистрация: 14.10.2022 Сообщений: 1,084
	12.11.2024, 20:24 [ТС]
	Проблема в том, что количество записей в сырой рабочей таблице будет порядка 5 млрд. в устоявшемся состоянии. Такая таблица сама по себе довольно тяжелая. Хотелось бы сжать ее чем-то вроде колумнстора. И вообще, хотелось бы какого-то волшебства. Ну, типа того - валим данные в одну таблицу, и при этом автоматически устанавливается признак "самая свежая" для верхней записи. И записи с этим признаком где-то в отдельной секции живут, например. Была мысль разрезать таблицу на 2 секции - первую с установленным признаком "самая свежая запись", вторую - со сброшенным признаком. И, соответственно при заливке пакета данных по опросам приборов (записи, разумеется, льются не по одной, а пакетами 1000-100000 записей, буферизация, короче говоря), мерждить порцию с установкой признака "самая свежая" вновь заливаемым данным, и сбрасыванием этого признака со всех остальных записей, соответствующих id приборов из загружаемого пакета. И таким образом получить кусок таблицы в которой заведомо самые свежие записи лежат. Но я что-то слабо представляю себе рост затрат на загрузку. ИМХО это будет чудовищно. Или сразу лить данные в две таблицы: "самые свежие" и "все". Но с заливкой во "все" вопросов нет. Берем и просто валим туда записи. Кластерный индекс делаем по (дате опроса счетчика DESC, ид счетчика), и используем для запросов типа 3. А максимально быструю таблицу на 50 млн. - "только актуальные состояния" уже для типа 1. Но встает вопрос поддержания такой таблицы. При заливке в ней опять придется искать и мерджить данные из пакета загрузки, с перезаписью всех данных, см. выше. Будет ли стоить овчинка выделки? 0

@Аватар 5393 / 1465 / 513 Регистрация: 31.05.2012 Сообщений: 5,153
	12.11.2024, 20:49
	с двумя таблицами возможно еще "чудовищней" затраты будут, все таки вместо update будет insert и delete. а эксперимент с реальным объемом данных провести на одной таблице неподъемно? 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	12.11.2024, 21:02
	В качестве бредовой идеи - темпоральная таблица 1

@Jack Famous 932 / 365 / 43 Регистрация: 10.05.2021 Сообщений: 1,564 Записей в блоге: 10
	13.11.2024, 16:11
	uaggster, я бы делил по какому-нибудь признаку в отдельные таблицы. Меньше таблица = выше скорость работы с ней. 0

@invm 3614 / 2135 / 756 Регистрация: 02.06.2013 Сообщений: 5,169
	17.11.2024, 18:54
	1. Добавить столбец-признак - последняя добавленная строка или нет 2. Секционировать по этому столбцу 3. Пари вставке потребуется снимать признак у текущей "последней" 0