ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Подскажите красивое решение
15 сообщений из 15, страница 1 из 1
Подскажите красивое решение
    #39711714
sanitar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanitar
Участник
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
/****** 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. В нее сохраняются значения некоторого процесса во времени: значения технологических параметров на некую точку времени. Нужно рассчитать некоторые значения по формулам на некоторые моменты времени. В расчете не должно быть пустых значений, т.е. нужно взять последнее значение параметра для расчета.

Что-то типа такого
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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



Но проблема в том что формула может быть любой, а значений можно быть очень много. Т.е. каждый раз выбирать максимум - довольно затратно по времени.
...
Рейтинг: 0 / 0
02.10.2018, 14:45
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39711739
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
sanitar,
можно как-то так,
можно и формулу через динмику считать,
можно и MAX через self join

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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
...
Рейтинг: 0 / 0
02.10.2018, 15:08
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39711745
sanitar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanitar
Участник
На выходе мне нужны примерно такие наборы данных:

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)

Чтобы в дальнейшем все это дело можно было группировать по меткам времени, ну и разные вычисления производить. Данных очень много, ну это могут быть десятки миллионов. Я думаю для начала следует возможно как-то хранить этот мегамассив только по изменению, т.е. для начала избавиться от дублирующих записей, если значение не менялось - то и вообще его не записывать. А вот дальше как быть?
...
Рейтинг: 0 / 0
02.10.2018, 15:14
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39711822
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
sanitarА вот дальше как быть?Что "дальше" ?
Вопрос твой в чём ?

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

... нуу или одну ХП
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
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
...
Рейтинг: 0 / 0
02.10.2018, 16:36
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712020
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
sanitarНо проблема в том что формула может быть любой, а значений можно быть очень много. Т.е. каждый раз выбирать максимум - довольно затратно по времени.

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

Если данных много может проще иметь табличку по параметрам, куда вставлять значения/дату при вставке в основную таблицу по какому то фильтру даты? Но это не подойдёт если у вас метки времени, на которые надо делать выборки, случайные, а не например конец/начало месяца/дня/часа.
...
Рейтинг: 0 / 0
02.10.2018, 19:54
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712029
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
sanitar Данных очень много, ну это могут быть десятки миллионов. Я думаю для начала следует возможно как-то хранить этот мегамассив только по изменению, т.е. для начала избавиться от дублирующих записей, если значение не менялось - то и вообще его не записывать. А вот дальше как быть?

Вы пробовали оценить количество дублей?
Логика никак не зависит от факта записи значения, даже неизменившегося?
...
Рейтинг: 0 / 0
02.10.2018, 20:03
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712224
sanitar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanitar
Участник
PizzaPizzasanitar Данных очень много, ну это могут быть десятки миллионов. Я думаю для начала следует возможно как-то хранить этот мегамассив только по изменению, т.е. для начала избавиться от дублирующих записей, если значение не менялось - то и вообще его не записывать. А вот дальше как быть?

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

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

Кроме того, есть значения которые вводятся вручную по регламенту: ну скажем один раз в смену, сутки или месяц. Эти значения вообще нет смысла дублировать. При расчетах всегда берем последние - именно они считаются актуальными.
...
Рейтинг: 0 / 0
03.10.2018, 09:06
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712282
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
sanitar,

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

2. Создаете функцию
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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

Пример использования
Код: sql
1.
select Expression from dbo.fnBuildExpressionByFormula('{1}*{2}/{4}', '20181002');


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

Если сервер не поддерживает string_split, то на форуме есть полно примеров функций разбиения строки по разделителю. Возьмите любой понравившийся.
...
Рейтинг: 0 / 0
03.10.2018, 10:44
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712285
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
invm,

авторНеобходимо изменить порядок столбцов в ПК ProcessValues на (ParmID, TimeKey)
я кончено понимаю зачем, но с точки зрения заполнения это будет огромная тошниловка
...
Рейтинг: 0 / 0
03.10.2018, 10:46
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712291
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Это задача для клиентского приложения.
...
Рейтинг: 0 / 0
03.10.2018, 10:50
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712301
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
TaPaKс точки зрения заполнения это будет огромная тошниловкаЗависит от нагрузки.

Если будет совсем плохо, то можно секционировать по ParamID или удвоить объем хранения, сделав дополнительный индекс для нужд читателей.
...
Рейтинг: 0 / 0
03.10.2018, 11:08
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712310
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
TSQL настолько хорош, что всегда возникает соблазн перенести решения прикладного уровня на плечи ХП :)
...
Рейтинг: 0 / 0
03.10.2018, 11:26
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39712612
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
sanitarPizzaPizzaпропущено...


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

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

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

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

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

Дальше, как у же предложено, пивотом (если определенное количество ParamId) выбирается сет параметров и уже там не очень понятно в селекте ли у вас формула или же вам её передавать в запрос надо...
...
Рейтинг: 0 / 0
03.10.2018, 18:25
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39716484
sanitar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanitar
Участник
TaPaK, спасибо за идею с TIES. Очень помогло.

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


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

2. Поскольку каждый расчет у меня привязан к коммерческим суткам, то на начало суток я делаю проверку чтобы все данные имелись на начало с помощью метода, предложенного TaPaK. Если каких-то данных не хватает я их записываю принудительно.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
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. Собственно сам расчет, тут обычные формулы. Ничего сложного. Вся проблема чтобы на каждую точку времени иметь все значения без "дыр".

Не знаю насколько красивое решение. Сначала все кажется тривиальным, но когда начинаешь реализовывать, то понимаешь насколько все непросто, ну во всяком случае для меня.
...
Рейтинг: 0 / 0
12.10.2018, 08:45
| Ответить | Цитировать | Написать  
Подскажите красивое решение
    #39716487
sanitar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sanitar
Участник
Идею со "смазыванием" значений взял у " https://www.itprotoday.com/software-development/last-non-null-puzzle%22%5D%D0%97%D0%B4%D0%B5%D1%81%D1%8C]https://www.itprotoday.com/software-development/last-non-null-puzzle"]Здесь Еще нашел очень компактное интересное решение с Quirky UPDATE. Но там же пишут что куирки апдейт лучше не использовать.
...
Рейтинг: 0 / 0
12.10.2018, 08:59
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Подскажите красивое решение
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]