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

Откуда:
Сообщений: 66
+
/****** Object:  Table [dbo].[ProcessValues]    Script Date: 02.10.2018 16:34:56 ******/
DROP TABLE [dbo].[ProcessValues]
GO

/****** Object:  Table [dbo].[ProcessValues]    Script Date: 02.10.2018 16:34:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProcessValues](
	[Timekey] [datetime] NOT NULL,
	[ParamID] [int] NOT NULL,
	[Value] [float] NOT NULL,
 CONSTRAINT [PK_ProcessValues] PRIMARY KEY CLUSTERED 
(
	[Timekey] ASC,
	[ParamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [example]
GO

/****** Object:  Table [dbo].[Parameters]    Script Date: 02.10.2018 16:35:58 ******/
DROP TABLE [dbo].[Parameters]
GO

/****** Object:  Table [dbo].[Parameters]    Script Date: 02.10.2018 16:35:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Parameters](
	[ParamId] [int] NOT NULL,
	[ParamName] [varchar](150) NOT NULL,
 CONSTRAINT [PK_Parameters] PRIMARY KEY CLUSTERED 
(
	[ParamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

insert into ProcessValues values ('20181001 00:01:20', 1, 10)
insert into ProcessValues values ('20181001 00:01:25', 2, 100)
insert into ProcessValues values ('20181001 00:02:00', 3, 5)
insert into ProcessValues values ('20181001 00:01:21', 4, 3)
insert into ProcessValues values ('20181001 00:01:20', 5, 13)
insert into ProcessValues values ('20181001 00:02:30', 1, 11)
insert into ProcessValues values ('20181001 00:02:11', 2, 111)
insert into ProcessValues values ('20181001 00:03:45', 3, 7)
insert into ProcessValues values ('20181001 00:04:01', 4, 4)
insert into ProcessValues values ('20181001 00:03:11', 5, 3.5)
insert into ProcessValues values ('20181001 00:03:21', 1, 9)
insert into ProcessValues values ('20181001 00:04:21', 2, 102)
insert into ProcessValues values ('20181001 00:04:10', 3, 4)
insert into ProcessValues values ('20181001 00:04:15', 4, 3)
insert into ProcessValues values ('20181001 00:03:59', 5, 14)

insert into Parameters values (1, 'Давление, МПа')
insert into Parameters values (2, 'Плотность, кг/м3')
insert into Parameters values (3, 'Объем, м3')
insert into Parameters values (4, 'Масса, т')
insert into Parameters values (5, 'Лабораторная плотность, кг/м3')


Суть задачи такова. Есть некая таблица ProcessValue. В нее сохраняются значения некоторого процесса во времени: значения технологических параметров на некую точку времени. Нужно рассчитать некоторые значения по формулам на некоторые моменты времени. В расчете не должно быть пустых значений, т.е. нужно взять последнее значение параметра для расчета.

Что-то типа такого
declare @tp datetime
declare @v1 float
declare @v2 float
declare @v4 float

SET @tp = '20181001 00:02:00' -- расчетная  точка
/*
 некая формула для расчета на точку времени. Формула в общем случае абсолютно произвольная
  <p1>*<p2>/<p4>
 */

 select @v1 = Value from ProcessValues where Timekey = 
 (select MAX(timekey) FROM ProcessValues where ParamID = 1 AND timekey <= @tp)

 select @v2 = Value from ProcessValues where Timekey = 
 (select MAX(timekey) FROM ProcessValues where ParamID = 2 AND timekey <= @tp)

  select @v4 = Value from ProcessValues where Timekey = 
 (select MAX(timekey) FROM ProcessValues where ParamID = 4 AND timekey <= @tp)


 select case when @v4 > 0 then @v1*@v2/@v4 else NULL end ResultValue


Но проблема в том что формула может быть любой, а значений можно быть очень много. Т.е. каждый раз выбирать максимум - довольно затратно по времени.

Сообщение было отредактировано: 2 окт 18, 14:45
2 окт 18, 14:45    [21692725]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
sanitar,
можно как-то так,
можно и формулу через динмику считать,
можно и MAX через self join

declare @tp datetime = '20181001 00:02:00' -- расчетная  точка

SELECT [ResultValue] = [1]*[2]/NULLIF([3],0) FROM 
(
	SELECT	TOP 1 WITH TIES		 
		ParamId,	
		Value	
	FROM ProcessValues 
	WHERE 
		TimeKey <= @tp
	ORDER BY 
		ROW_NUMBER() OVER (PARTITION BY ParamId ORDER BY TimeKey DESC)
) t PIVOT (MAX(Value) FOR ParamId IN ([1],[2],[3],[4],[5],[6])) y
2 окт 18, 15:08    [21692775]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
sanitar
Member

Откуда:
Сообщений: 66
На выходе мне нужны примерно такие наборы данных:

20181001 00:01:00, Formulа1(p1,p2,p4)
20181001 00:02:00, Formulа1(p1,p2,p4)
20181001 00:03:00, Formulа1(p1,p2,p4)

Чтобы в дальнейшем все это дело можно было группировать по меткам времени, ну и разные вычисления производить. Данных очень много, ну это могут быть десятки миллионов. Я думаю для начала следует возможно как-то хранить этот мегамассив только по изменению, т.е. для начала избавиться от дублирующих записей, если значение не менялось - то и вообще его не записывать. А вот дальше как быть?
2 окт 18, 15:14    [21692790]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
court
Member

Откуда:
Сообщений: 1956
sanitar
А вот дальше как быть?
Что "дальше" ?
Вопрос твой в чём ?

"Дальше" пишешь 100500 функций, в каждой из которых реализуешь отдельную формулу, которую рассчитываешь, как выше показали.

... нуу или одну ХП
create proc spFormulaResult
	@tp datetime
	,@formula nvarchar(1000)
as 
declare @sql nvarchar(4000)
declare @FormulaResult float

 SELECT [1] as v1, [2] as v2, [3] as v3, [4] as v4, [5] as v5, [6] as v6
 into #t
 from
(
	SELECT	TOP 1 WITH TIES		 
		ParamId,	
		Value	
	FROM ProcessValues 
	WHERE 
		TimeKey <= @tp
	ORDER BY 
		ROW_NUMBER() OVER (PARTITION BY ParamId ORDER BY TimeKey DESC)
) t PIVOT (MAX(Value) FOR ParamId IN ([1],[2],[3],[4],[5],[6])) y

set @sql=N'select @FormulaResult='+@formula+' from #t'
exec sp_executesql @sql, N'@FormulaResult float output', @FormulaResult=@FormulaResult output

select @FormulaResult as FormulaResult

drop table #t

go

exec spFormulaResult '20181001 00:02:00', 'case when v4 > 0 then v1*v2/v4 else NULL end'
go

exec spFormulaResult '20181001 00:03:00', 'case when v4 > 0 then v3/v2/v4 else NULL end'
go
2 окт 18, 16:36    [21692957]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
sanitar
Но проблема в том что формула может быть любой, а значений можно быть очень много. Т.е. каждый раз выбирать максимум - довольно затратно по времени.


Вам надо формализовать задачу. Что значит "формула может быть любой"?
Пока понятна только первая часть - вам нужны последнее значения из временного диапазона по каждому параметру.

Если данных много может проще иметь табличку по параметрам, куда вставлять значения/дату при вставке в основную таблицу по какому то фильтру даты? Но это не подойдёт если у вас метки времени, на которые надо делать выборки, случайные, а не например конец/начало месяца/дня/часа.
2 окт 18, 19:54    [21693241]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
sanitar
Данных очень много, ну это могут быть десятки миллионов. Я думаю для начала следует возможно как-то хранить этот мегамассив только по изменению, т.е. для начала избавиться от дублирующих записей, если значение не менялось - то и вообще его не записывать. А вот дальше как быть?


Вы пробовали оценить количество дублей?
Логика никак не зависит от факта записи значения, даже неизменившегося?
2 окт 18, 20:03    [21693256]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
sanitar
Member

Откуда:
Сообщений: 66
PizzaPizza
sanitar
Данных очень много, ну это могут быть десятки миллионов. Я думаю для начала следует возможно как-то хранить этот мегамассив только по изменению, т.е. для начала избавиться от дублирующих записей, если значение не менялось - то и вообще его не записывать. А вот дальше как быть?


Вы пробовали оценить количество дублей?
Логика никак не зависит от факта записи значения, даже неизменившегося?


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

Кроме того, есть значения которые вводятся вручную по регламенту: ну скажем один раз в смену, сутки или месяц. Эти значения вообще нет смысла дублировать. При расчетах всегда берем последние - именно они считаются актуальными.
3 окт 18, 09:06    [21693666]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
sanitar,

1. Необходимо изменить порядок столбцов в ПК ProcessValues на (ParmID, TimeKey)

2. Создаете функцию
create function dbo.fnBuildExpressionByFormula
(
 @formula varchar(max),
 @tp datetime
)
returns table
as
return (
 with r(x) as
 (
  select
   replace(a.p, cast(b.ParamID as varchar(10)) + '}', isnull(cast(c.Value as varchar(30)), 'null')) 
  from
   (select value, row_number() over (order by (select 1)) from string_split(@formula, '{') where value > '') a(p, rn) cross apply
   (select cast(left(a.p, charindex('}', a.p) - 1) as int)) b(ParamID) outer apply
   (select top (1) Value from ProcessValues where ParamID = b.ParamID and TimeKey <= @tp order by timeKey desc) c
  order by
   a.rn
  for xml path(''), type
 )
 select x.value('.', 'varchar(max)') as Expression from r
);
go
Пример использования
select Expression from dbo.fnBuildExpressionByFormula('{1}*{2}/{4}', '20181002');

3. Ищите или пишите самостоятельно функцию/процедуру для вычисления выражения. Или вычисляете на клиенте.
Если совсем не волнует производительность и безопасность, то вот готовая - 21685462

Если сервер не поддерживает string_split, то на форуме есть полно примеров функций разбиения строки по разделителю. Возьмите любой понравившийся.
3 окт 18, 10:44    [21693767]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
invm,

автор
Необходимо изменить порядок столбцов в ПК ProcessValues на (ParmID, TimeKey)

я кончено понимаю зачем, но с точки зрения заполнения это будет огромная тошниловка
3 окт 18, 10:46    [21693772]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Это задача для клиентского приложения.
3 окт 18, 10:50    [21693778]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
invm
Member

Откуда: Москва
Сообщений: 9122
TaPaK
с точки зрения заполнения это будет огромная тошниловка
Зависит от нагрузки.

Если будет совсем плохо, то можно секционировать по ParamID или удвоить объем хранения, сделав дополнительный индекс для нужд читателей.
3 окт 18, 11:08    [21693799]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
TSQL настолько хорош, что всегда возникает соблазн перенести решения прикладного уровня на плечи ХП :)
3 окт 18, 11:26    [21693827]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
sanitar
PizzaPizza
пропущено...


Вы пробовали оценить количество дублей?
Логика никак не зависит от факта записи значения, даже неизменившегося?


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

Кроме того, есть значения которые вводятся вручную по регламенту: ну скажем один раз в смену, сутки или месяц. Эти значения вообще нет смысла дублировать. При расчетах всегда берем последние - именно они считаются актуальными.


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

Действительно, сделайте более селективный индекс по ParamId сначала.

Дальше, как у же предложено, пивотом (если определенное количество ParamId) выбирается сет параметров и уже там не очень понятно в селекте ли у вас формула или же вам её передавать в запрос надо...
3 окт 18, 18:25    [21694441]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
sanitar
Member

Откуда:
Сообщений: 66
TaPaK, спасибо за идею с TIES. Очень помогло.

В общем случае с вашей помощью я придумал примерно такое решение. Сейчас тестирую на реальном потоке данных.


1. Пишу в таблицу ProcessValues только изменившиеся значения, если значение не поменялось, то новая метка времени ему не присваивается. Количество записей при этом снизилось больше чем на порядок.

2. Поскольку каждый расчет у меня привязан к коммерческим суткам, то на начало суток я делаю проверку чтобы все данные имелись на начало с помощью метода, предложенного TaPaK. Если каких-то данных не хватает я их записываю принудительно.
declare @tp datetime = '20181001 00:02:00' -- расчетная  точка

SELECT @v1 = [1], @v2 = [2], @v3=[3] FROM 
(
	SELECT	TOP 1 WITH TIES		 
		ParamId,	
		Value	
	FROM ProcessValues 
	WHERE 
		TimeKey <= @tp
	ORDER BY 
		ROW_NUMBER() OVER (PARTITION BY ParamId ORDER BY TimeKey DESC)
) t PIVOT (MAX(Value) FOR ParamId IN ([1],[2],[3],[4],[5],[6])) y


3. Подготовка к расчету. Если на какие-то метки данных идут пустые значения, я их "смазывю" с предудущих точек. Таким образом. Здесь пример для одного параметра. С помощью UNION в динамике можно склеить несколько, но обязательно для каждого задать интервал только на расчетные сутки, на начало суток мы гарантированно имеем значения см. п2.

WITH C AS
(
  SELECT Timekey, v, relevantid,

      MAX( CASE WHEN v IS NOT NULL THEN timekey END )
  OVER( ORDER BY timekey
        ROWS UNBOUNDED PRECEDING ) AS grp
  FROM 
  (
SELECT
Timekey,
[1] 
FROM
(
select 
Timekey,
Tagname, 
Value 
from ProcessValue
) a
  PIVOT (MAX(Value) FOR Tagname IN
      (
      [1]
      )
	  )  b ) d
      CROSS APPLY ( VALUES( CASE WHEN v IS NOT NULL THEN timekey END ) )
  AS A(relevantid)
)
SELECT timekey, 
  MAX(v) OVER(PARTITION BY grp
          ORDER BY timekey
          ROWS UNBOUNDED PRECEDING ) AS v
		  
FROM C;


4. Собственно сам расчет, тут обычные формулы. Ничего сложного. Вся проблема чтобы на каждую точку времени иметь все значения без "дыр".

Не знаю насколько красивое решение. Сначала все кажется тривиальным, но когда начинаешь реализовывать, то понимаешь насколько все непросто, ну во всяком случае для меня.
12 окт 18, 08:45    [21702159]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите красивое решение  [new]
sanitar
Member

Откуда:
Сообщений: 66
Идею со "смазыванием" значений взял у [url="https://www.itprotoday.com/software-development/last-non-null-puzzle"]Здесь[/url] Еще нашел очень компактное интересное решение с Quirky UPDATE. Но там же пишут что куирки апдейт лучше не использовать.
12 окт 18, 08:59    [21702163]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить