Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с алгоритмом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
SQL Server 2005. Каждый день создаётся новая таблица, куда в реальном времени записываются результаты измерения параметров от телеметрических датчиков:
create table #T_Measurement_2015_01_29
(
	[DateTime] datetime not null,
	[Parameter] varchar(50) not null,
	[ValueA] int not null,
	[ValueB] int not null
)


Таблица очень большая (до миллиарда записей в сутки) и на достаточную ретроспективу просто не хватает места. Чаще всего, тройка значений Parameter-ValueA-ValueB сохраняет своё значение во всей таблице, но иногда меняется несколько раз.
Идея заключается в преобразовании исходной таблицы в совокупность значений троек Parameter-ValueA-ValueB и временных интервалов BegTime и EndTime, в течение которых сохранялось значение тройки.

Например, для значений Sensor#1:
DateTime                Parameter  ValueA   ValueB
----------------------- ---------- -------- --------
2015-01-29 00:00:31.000 Sensor#1   13       87
2015-01-29 00:01:18.000 Sensor#1   13       87
2015-01-29 00:03:06.000 Sensor#1   13       87
2015-01-29 00:12:58.000 Sensor#1   12       90
2015-01-29 00:19:43.000 Sensor#1   12       90
2015-01-29 00:21:15.000 Sensor#1   13       87
2015-01-29 00:30:32.000 Sensor#1   13       87
2015-01-29 00:31:48.000 Sensor#1   12       90
2015-01-29 00:40:34.000 Sensor#1   13       87

справедливым бы было такое преобразование:
Parameter  ValueA   ValueB   BegTime                 EndTime
---------- -------- -------- ----------------------- -----------------------
Sensor#1   13       87       2015-01-29 00:00:31.000 2015-01-29 00:03:06.000
Sensor#1   12       90       2015-01-29 00:12:58.000 2015-01-29 00:19:43.000
Sensor#1   13       87       2015-01-29 00:21:15.000 2015-01-29 00:30:32.000
Sensor#1   12       90       2015-01-29 00:31:48.000 2015-01-29 00:31:48.000
Sensor#1   13       87       2015-01-29 00:40:34.000 2015-01-29 00:40:34.000


Помогите, пожалуйста с алгоритмом/скриптом.
29 янв 15, 23:35    [17192550]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
o-o
Guest
=Сергей=,
что-то такое, проверяйте, я уже спать.
в 2005 это все есть, только вроде инсерты надо по одному делать,
но ведь не суть, как данные заполнять

declare @t table 
(
	dt datetime not null,
	[Parameter] varchar(50) not null,
	[ValueA] int not null,
	[ValueB] int not null
);

insert into @t values 
('2015-01-29 00:00:31.000' ,'Sensor#1',   13,       87),
('2015-01-29 00:01:18.000' ,'Sensor#1',   13,       87),
('2015-01-29 00:03:06.000' ,'Sensor#1',   13,       87),
('2015-01-29 00:12:58.000' ,'Sensor#1',   12,       90),
('2015-01-29 00:19:43.000' ,'Sensor#1',   12,       90),
('2015-01-29 00:21:15.000' ,'Sensor#1',   13,       87),
('2015-01-29 00:30:32.000' ,'Sensor#1',   13,       87),
('2015-01-29 00:31:48.000' ,'Sensor#1',   12,       90),
('2015-01-29 00:40:34.000' ,'Sensor#1',   13,       87);

;with cte as
(
select dt, Parameter, ValueA, ValueB,
       row_number() over(order by dt) - row_number() over(partition by Parameter, ValueA, ValueB order by dt) as grp
from @t) 

select Parameter, ValueA, ValueB, min(dt) as BegTime, max(dt) as EndTime 
from cte
group by grp, Parameter, ValueA, ValueB
order by BegTime;
30 янв 15, 00:14    [17192796]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
o-o, большое спасибо за помощь! По производительности устраивает на 100%, но работает неверно:
declare @t table 
(
	dt datetime not null,
	[Parameter] varchar(50) not null,
	[ValueA] int not null,
	[ValueB] int not null
);

insert into @t values
('2013-12-23 00:00:01', 'Sensor#1', 17, 87),
('2013-12-23 00:00:01', 'Sensor#2', 38, 98),
('2013-12-23 00:00:02', 'Sensor#1', 17, 87),
('2013-12-23 00:03:27', 'Sensor#1', 17, 87),
('2013-12-23 00:03:27', 'Sensor#1', 17, 87),
('2013-12-23 00:35:09', 'Sensor#1', 17, 87),
('2013-12-23 00:36:11', 'Sensor#1', 17, 87),
('2013-12-23 00:36:11', 'Sensor#1', 17, 87),
('2013-12-23 00:54:30', 'Sensor#1', 17, 87),
('2013-12-23 00:56:48', 'Sensor#1', 17, 87),
('2013-12-23 00:56:57', 'Sensor#1', 17, 87),
('2013-12-23 00:57:13', 'Sensor#1', 17, 87),
('2013-12-23 06:00:04', 'Sensor#2', 38, 98),
('2013-12-23 06:00:40', 'Sensor#2', 38, 98),
('2013-12-23 07:00:47', 'Sensor#1', 17, 87),
('2013-12-23 10:32:28', 'Sensor#2', 38, 98),
('2013-12-23 11:35:45', 'Sensor#2', 38, 98),
('2013-12-23 12:14:05', 'Sensor#2', 38, 98),
('2013-12-23 12:32:03', 'Sensor#2', 38, 98),
('2013-12-23 12:41:27', 'Sensor#2', 38, 98),
('2013-12-23 13:00:51', 'Sensor#1', 17, 87),
('2013-12-23 13:34:50', 'Sensor#1', 17, 87),
('2013-12-23 14:57:21', 'Sensor#2', 38, 98),
('2013-12-23 14:57:43', 'Sensor#2', 38, 98),
('2013-12-23 15:38:51', 'Sensor#2', 38, 98),
('2013-12-23 15:41:32', 'Sensor#2', 38, 98),
('2013-12-23 15:42:04', 'Sensor#2', 38, 98),
('2013-12-23 15:58:37', 'Sensor#1', 17, 87),
('2013-12-23 16:43:23', 'Sensor#1', 17, 87),
('2013-12-23 16:56:01', 'Sensor#2', 38, 98),
('2013-12-23 17:15:22', 'Sensor#2', 38, 98),
('2013-12-23 17:16:41', 'Sensor#2', 38, 98),
('2013-12-23 17:33:50', 'Sensor#2', 38, 98),
('2013-12-23 17:34:57', 'Sensor#2', 38, 98),
('2013-12-23 17:47:18', 'Sensor#1', 17, 87),
('2013-12-23 17:48:53', 'Sensor#1', 17, 87),
('2013-12-23 17:53:28', 'Sensor#2', 38, 98),
('2013-12-23 18:17:08', 'Sensor#2', 38, 98),
('2013-12-23 18:34:58', 'Sensor#1', 17, 87),
('2013-12-23 18:35:11', 'Sensor#1', 17, 87),
('2013-12-23 19:38:43', 'Sensor#1', 17, 87),
('2013-12-23 20:04:43', 'Sensor#1', 17, 87),
('2013-12-23 20:42:52', 'Sensor#1', 17, 87),
('2013-12-23 21:10:12', 'Sensor#1', 17, 87),
('2013-12-23 21:31:07', 'Sensor#2', 38, 98),
('2013-12-23 21:32:21', 'Sensor#2', 38, 98),
('2013-12-23 21:33:16', 'Sensor#1', 17, 87),
('2013-12-23 21:34:00', 'Sensor#1', 17, 87),
('2013-12-23 21:47:29', 'Sensor#2', 38, 98),
('2013-12-23 21:56:48', 'Sensor#1', 17, 87),
('2013-12-23 21:56:55', 'Sensor#1', 17, 87),
('2013-12-23 21:57:39', 'Sensor#1', 17, 87),
('2013-12-23 22:05:08', 'Sensor#1', 17, 87),
('2013-12-23 22:05:17', 'Sensor#1', 17, 87),
('2013-12-23 22:06:06', 'Sensor#1', 17, 87),
('2013-12-23 22:06:15', 'Sensor#1', 17, 87),
('2013-12-23 22:14:01', 'Sensor#1', 17, 87),
('2013-12-23 22:14:16', 'Sensor#1', 17, 87),
('2013-12-23 22:15:36', 'Sensor#1', 17, 87),
('2013-12-23 22:17:13', 'Sensor#1', 17, 87),
('2013-12-23 22:18:09', 'Sensor#1', 17, 87),
('2013-12-23 22:19:46', 'Sensor#1', 17, 87),
('2013-12-23 23:07:53', 'Sensor#1', 17, 87),
('2013-12-23 23:08:18', 'Sensor#1', 17, 87);

;with cte as
(
select dt, Parameter, ValueA, ValueB,
       row_number() over(order by dt) - row_number() over(partition by Parameter, ValueA, ValueB order by dt) as grp
from @t) 

select Parameter, ValueA, ValueB, min(dt) as BegTime, max(dt) as EndTime 
from cte
group by grp, Parameter, ValueA, ValueB
order by BegTime;

выдаёт результат:
Parameter  ValueA  ValueB  BegTime             EndTime
---------- ------- ------- ------------------- -------------------
Sensor#1   17      87      2013-12-23 00:00:01 2013-12-23 00:00:01
Sensor#2   38      98      2013-12-23 00:00:01 2013-12-23 00:00:01
Sensor#1   17      87      2013-12-23 00:00:02 2013-12-23 00:57:13
Sensor#2   38      98      2013-12-23 06:00:04 2013-12-23 06:00:40
Sensor#1   17      87      2013-12-23 07:00:47 2013-12-23 07:00:47
Sensor#2   38      98      2013-12-23 10:32:28 2013-12-23 12:41:27
Sensor#1   17      87      2013-12-23 13:00:51 2013-12-23 13:34:50
Sensor#2   38      98      2013-12-23 14:57:21 2013-12-23 15:42:04
Sensor#1   17      87      2013-12-23 15:58:37 2013-12-23 16:43:23
Sensor#2   38      98      2013-12-23 16:56:01 2013-12-23 17:34:57
Sensor#1   17      87      2013-12-23 17:47:18 2013-12-23 17:48:53
Sensor#2   38      98      2013-12-23 17:53:28 2013-12-23 18:17:08
Sensor#1   17      87      2013-12-23 18:34:58 2013-12-23 21:10:12
Sensor#2   38      98      2013-12-23 21:31:07 2013-12-23 21:32:21
Sensor#1   17      87      2013-12-23 21:33:16 2013-12-23 21:34:00
Sensor#2   38      98      2013-12-23 21:47:29 2013-12-23 21:47:29
Sensor#1   17      87      2013-12-23 21:56:48 2013-12-23 23:08:18

а результат должен быть таким:
Parameter  ValueA  ValueB  BegTime             EndTime
---------- ------- ------- ------------------- -------------------
Sensor#1   17      87      2013-12-23 00:00:01 2013-12-23 23:08:18
Sensor#2   38      98      2013-12-23 00:00:01 2013-12-23 21:47:29


Помогите пожалуйста.
30 янв 15, 09:48    [17193607]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
Добрый Э - Эх
Guest
=Сергей=,

просто добавь воды partition by в первый row_number:
with cte as
(
select dt, Parameter, ValueA, ValueB,
       row_number() over(partition by Parameter order by dt) - row_number() over(partition by Parameter, ValueA, ValueB order by dt) as grp
from @t) 

select Parameter, ValueA, ValueB, min(dt) as BegTime, max(dt) as EndTime 
from cte
group by grp, Parameter, ValueA, ValueB
order by BegTime;
30 янв 15, 10:01    [17193701]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
aleks2
Guest
Бред.
Сначала записывать - потом преобразовывать.

Система не должна записывать показание пока оно не изменилось.
30 янв 15, 11:46    [17194319]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
человек_ниоткуда
Guest
Когда я обнаружил такую проблему в аналогичной системе, я сделал так. Размер таблицы сократился в разы, время записи (с учётом поиска id параметра), увеличилось на 3.1% . Переделка системы, с юнитами, заняла 3 часа.
create table #T_Measurement_2015_01_29
(
	[DateTime] datetime not null,
	[Id_Parameter] smallint not null,
	[ValueA] int not null,
	[ValueB] int not null
)
;

CREATE TABLE #T_Parameter_2015_01_29 
(	[Id_Parameter] smallint not null
,	[Name] varchar(50) NOT NULL PRIMARY KEY);


PS: и я сделал базюльку и там создавал таблицы. "drop table" никто не отменял, и, если коллектор падал, то инфа не терялась.
30 янв 15, 12:18    [17194536]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
aleks2
Бред.
Сначала записывать - потом преобразовывать.
Система не должна записывать показание пока оно не изменилось.

Систему проектировал не я - она логирует кучу параметров от 100000+ датчиков и хранит не более 5 суток.
Моя задача всё это грамотно и компактно сохранить.
Всем большое спасибо!!!
1 фев 15, 15:13    [17202689]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с алгоритмом  [new]
aleks2
Guest
=Сергей=
aleks2
Бред.
Сначала записывать - потом преобразовывать.
Система не должна записывать показание пока оно не изменилось.

Систему проектировал не я - она логирует кучу параметров от 100000+ датчиков и хранит не более 5 суток.
Моя задача всё это грамотно и компактно сохранить.
Всем большое спасибо!!!


Был бы грамотный - просто стирал бы лишние записи. А так, ты ничем не лучше проектантов системы, такой же халтурщик.
Но если нет - то не добавишь.
1 фев 15, 16:05    [17202763]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить