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

Откуда:
Сообщений: 294
Доброго времени суток!

Подскажите пожалуйста, можно ли в таблице БД задать столбец, в котором бы автоматически рассчитывалось бы значение для новой записи исходя из предыдущих записей.
Например есть таблица, в которой есть люди, их положение (distance_km), дата и время данного положения. Нужно что бы при добавлении новой записи, касающейся данного человека рассчитывалась его средняя скорость ((distance_km2-distance_km1)/((Date2-Date1)*24)+(Time2-Time1))) и записывалась в поле "Speed_piople_km_h".

CREATE TABLE [dbo].[SPEED]
(
	[Id_SPEED] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Id_piople] INT NOT NULL, 
    CONSTRAINT [FK_Flood_ToTable] FOREIGN KEY ([Id_post]) REFERENCES [Post]([Id_post]),
	[Date] DATE NOT NULL,
	[Time] TIME NOT NULL,
	[distance_km] INT NOT NULL,
	[Speed_piople_km_h]
)


Если да, то подскажите пожалуйста как?
26 янв 18, 21:58    [21142873]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
Vlad__i__mir
Подскажите пожалуйста, можно ли в таблице БД задать столбец, в котором бы автоматически рассчитывалось бы значение для новой записи исходя из предыдущих записей.
конкретно на этот вопрос (без обсуждения влияния на производительность) ответ - вычисляемый столбец таблицы может быть основан на функции
create table dbo.Table   
(  
    id int,    
    CalcColumn as dbo.fn_Function(id)  
); 
26 янв 18, 22:25    [21142935]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
В триггере можно хоть квадратные уравнения решать.
26 янв 18, 22:26    [21142942]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Дедушка,

это он новую таблицу создаст, а мне нужно чтобы он в этой же таблице в столбец записал
26 янв 18, 23:31    [21143082]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Гавриленко Сергей Алексеевич,

что за тригер?
26 янв 18, 23:32    [21143085]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Vlad__i__mir
Гавриленко Сергей Алексеевич,

что за тригер?
Про триггеры (create trigger) и вычисляемые поля (computed columns) на основе функций вам придется прочитать в документации.
26 янв 18, 23:47    [21143102]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Дедушка, dbo.fn_Function(id) - это обычная прописываемая нами функция? Где её нужно прописывать?
Или это триггер?
Можно ли будет такую же функцию также вызвать в столбце таблицы, в которою добавляется запись?
31 янв 18, 12:44    [21153946]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
drop table if exists dbo.T
drop function if exists dbo.fn_T
go

create function dbo.fn_T (@id int)
  returns int
as
  begin 
      return(@id + 1)
  end
go

create table dbo.T (id int, c as dbo.fn_T (id))
go

--==============================================

insert dbo.T (id) values (1), (2)

select * from dbo.T
31 янв 18, 13:26    [21154108]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
iiyama
Member

Откуда:
Сообщений: 642
Vlad__i__mir,
немного не по вопросу, но может лучше сделать представление?
типа такого
CREATE VIEW dbo.V_SPEED 
AS
select 
	S0.[Id_SPEED]
	, S0.Id_piople
	, S0.Date
	, S0.Time
	, S0.distance_km
	, 1000.0*dm / DATEDIFF(ss, cast(0 as datetime),dt) AS [speep_mps]
	, 3600.0*dm / DATEDIFF(ss, cast(0 as datetime),dt) AS [speep_kmph]

from [dbo].[SPEED] S0
	OUTER APPLY 
	(
	select 	TOP 1
		  CAST(S0.date as datetime)+CAST(S0.time as datetime) - CAST(s1.date as datetime)-CAST(s1.time as datetime) as dt
		, S0.distance_km-S1.distance_km  as dm

	from [dbo].[SPEED] S1
	where S1.Id_piople=S0.Id_piople and CAST(S1.date as datetime)+CAST(S1.time as datetime)<CAST(S0.date as datetime)+CAST(S0.time as datetime)
	order by S1.Date desc, S1.Time desc
	)pval
31 янв 18, 14:26    [21154341]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Дедушка
drop table if exists dbo.T
drop function if exists dbo.fn_T
go

create function dbo.fn_T (@id int)
  returns int
as
  begin 
      return(@id + 1)
  end
go

create table dbo.T (id int, c as dbo.fn_T (id))
go

--==============================================

insert dbo.T (id) values (1), (2)

select * from dbo.T


До этого сталкивался и применял функции только на основе SELECT и арифм операции, поэтому почти ничего не понятно.

Можете сказать где прочитать про данные конструкции?
Где они прописываются??? В колонке таблицы при её создании после "AS" или в триггерах? Или ...?
31 янв 18, 18:43    [21155454]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
https://msdn.microsoft.com/ru-ru/library/ms186755(v=sql.120).aspx
31 янв 18, 18:44    [21155455]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Гавриленко Сергей Алексеевич
https://msdn.microsoft.com/ru-ru/library/ms186755(v=sql.120).aspx


Спасибо, немного прояснилось. Но в какой части БД необходимо прописывать код функции не понял?
31 янв 18, 20:16    [21155632]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Дедушка
drop table if exists dbo.T
drop function if exists dbo.fn_T
go

create function dbo.fn_T (@id int)
  returns int
as
  begin 
      return(@id + 1)
  end
go

create table dbo.T (id int, c as dbo.fn_T (id))
go

--==============================================

insert dbo.T (id) values (1), (2)

select * from dbo.T


1. Зачем мы удаляем здесь функцию?
drop function if exists dbo.fn_T
go

2. Почему у нас здесь 2 "return"? Оба return-a относятся к функции dbo.fn_T?
3. Что должен вернуть 1-ый return, а что 2-ой?
create function dbo.fn_T (@id int)
  returns int
as
  begin 
      return(@id + 1)
  end
go
31 янв 18, 20:38    [21155661]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Vlad__i__mir,

Вопрос 2 снимается, узнал что это мы определяем тип возвращаемого функцией значения. Просто в программировании БД я почти нуль ))
31 янв 18, 22:00    [21155811]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Дедушка, если я объединю столбцы DATE and TIME будет ли работать следующее решение:
CREATE TABLE [dbo].[SPEED]
(
    [Id_SPEED] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Id_piople] INT NOT NULL, 
    CONSTRAINT [FK_Flood_ToTable] FOREIGN KEY ([Id_post]) REFERENCES [Post]([Id_post]),
    [DATE_TIME] DATE NOT NULL,
    [distance_km] INT NOT NULL,
    [Speed_piople_km_h] AS MidleSpeed (Id_piople, DATE_TIME, distance_km)
 
)

а в хранимых функцию пропишу вот такую функцию:

 CREATE FUNCTION MidleSpeed (@Id_piople int, @DATE_TIME DATETIME, @distance_km int)
 RETURNS int
 AS
 BEGIN
 CREATE TABLE [dbo].[SPEED_2]
(
    [Id_SPEED] INT,
    [Id_piople] INT, 
    [DATE_TIME] DATETIME,
    [distance_km] INT
)
 INSERT INTO dbo.SPEED
 SELECT [Id_SPEED], [Id_piople], [DATE_TIME], [distance_km] FROM SPEED WHERE Id_piople=@Id_piople
 @maxTime DATETIME,
 @maxTime=SELECT MAX(DATE_TIME) FROM SPEED.DATE_TIME
 @lastDistance_km int
 @lastDistance_km=SELECT [distance_km] FROM SPEED_2 WHERE DATE_TIME=@maxTime
 
 drop [dbo].[SPEED_2]
 
 RETURN ((@distance_km-@lastDistance_km)/(DATEDIFF(HOUR, @maxTime, @DATE_TIME))
 END
31 янв 18, 23:45    [21156044]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
Vlad__i__mir,
INSERT INTO dbo.SPEED_2
SELECT [Id_SPEED], [Id_piople], [DATE_TIME], [distance_km] FROM SPEED WHERE Id_piople=@Id_piople
31 янв 18, 23:53    [21156068]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
iiyama
Member

Откуда:
Сообщений: 642
Vlad__i__mir,

A.
Прочитайте еще раз ссылку которую Вам дали выше, особенно после слов:
1)В функциях допустимы следующие инструкции.2)Ограничения

B.
Как вы думаете что вернет функция DATEDIFF(HOUR, @maxTime, @DATE_TIME)
для значений @maxTime='20180101 00:00:00', @DATE_TIME='20180101 00:00:10'
и какую ошибку вы получите вызвав ее в знаменателе
1 фев 18, 17:16    [21158415]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
iiyama
Vlad__i__mir,

A.
Прочитайте еще раз ссылку которую Вам дали выше, особенно после слов:
1)В функциях допустимы следующие инструкции.2)Ограничения

B.
Как вы думаете что вернет функция DATEDIFF(HOUR, @maxTime, @DATE_TIME)
для значений @maxTime='20180101 00:00:00', @DATE_TIME='20180101 00:00:10'
и какую ошибку вы получите вызвав ее в знаменателе


А. Прочитал. Понял, что внутри функции нельзя создавать таблицу. Попытался обойти это ограничение с помощью второй функции: 1-ая возвращает таблицу, которая содержит только записи с нужным ID, а 2-я уже производит все остальные вычисления с таблицей, которую вернула 1-ая функция. Но проблема в том, что приходится вызывать данную функцию дважды: первый раз для того чтобы определить максимальное время, а второй раз для того чтобы выбрать расстояние соответствующее данному времени. И вот тут вопрос - получается одни и те же записи будут добавлены дважды?
Не знаю как правильней будет здесь поступить.

В.
Устранил эту ошибку путем добавления проверки на равенство нулю.

CREATE FUNCTION TableSpeed (@Id_piople int)
 RETURNS @SPEED_2 TABLE
 (
    [Id_SPEED] INT,
    [Id_piople] INT, 
    [DATE_TIME] DATETIME,
    [distance_km] INT
 )
 AS
 BEGIN
 INSERT INTO dbo.SPEED_2
 SELECT [Id_SPEED], [Id_piople], [DATE_TIME], [distance_km] FROM SPEED WHERE Id_piople=@Id_piople;
 
 RETURN (@SPEED-2);
 
=======================================================================================
 CREATE FUNCTION MidleSpeed (@Id_piople int, @DATE_TIME DATETIME, @distance_km int)
 RETURNS int
 AS
 BEGIN
 DECLARE @maxTime DATETIME;
 DECLARE @lastDistance_km int;
 
 SET @maxTime=SELECT MAX(DATE_TIME) FROM TableSpeed(@Id_piople).DATE_TIME;
 
 SET @lastDistance_km=SELECT [distance_km] FROM TableSpeed(@Id_piople) WHERE DATE_TIME=@maxTime;
 
 DECLARE @Time int;
 DECLARE @Km_h int;
 SET @Km_h=0;
 
 SET @Time=DATEDIFF(HOUR, @maxTime, @DATE_TIME);
  
 IF (@Time!=0) 
 SET @Km_h=(@distance_km-@lastDistance_km)/@Time;
 
 RETURN (@Km_h);
 END


Помогите пожалуйста с пунктом А. Мне кажется у меня не верно, но как по другому обойти ограничение не знаю.
1 фев 18, 20:22    [21158890]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
iiyama
Member

Откуда:
Сообщений: 642
Vlad__i__mir,

Прошу прощения, может я тупой или сегодня пятница, а может и то и другое, НО ИМХО

зачем хранить среднюю скорость в таблице? Ведь это явно расчетная величина, зависящая от текущего и предыдущего положения в пространстве. Ведь если у вас добавляются данные в середину интервала, который у Вас уже рассчитан и сохранен, то Вы должны рассчитать новую среднюю скорость и для добавляемой точки и перерасчитать скорость для уже сохраненной:
A(хранение)___C(добавление)____B(хранение).

Далее ИМХО во второй степени

1. Напишите запрос который по положениям в пространстве-времени(ваша таблица), рассчитывает изменения в пространстве. Т.е. Ваша логика определения предыдущего положения.
2. Вычислите характеристику
3. Только далее думайте как материализовать эти вычисления: в виде запроса, в виде представления, в виде табличной функции, в виде новой сущности, в виде нового поля или еще чего

Пи.Эс. Я бы реализовал через представление, где вместе со скоростью, была бы доступна информация и по 2м положениям в пространстве, на основе чего она была и рассчитана
2 фев 18, 10:43    [21159857]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
iiyama, это я сделал. Проблема с реализацией. В этом прошу помощи.

1. Мы находим записи с нужным нам ID точки (Id_piople), далее находим среди них запись с максимальной датой и временем и берём расстояние соответствующее этой дате времени. Тут возникает один вопрос попадёт или нет в эту выборку добавляемая запись, для которой мы делаем расчёт?
 INSERT INTO dbo.SPEED_2
 SELECT [Id_SPEED], [Id_piople], [DATE_TIME], [distance_km] FROM SPEED WHERE Id_piople=@Id_piople;
 SET @maxTime=(SELECT MAX(DATE_TIME) FROM TableSpeed(@Id_piople).DATE_TIME);
 SET @lastDistance_km=(SELECT [distance_km] FROM TableSpeed(@Id_piople) WHERE DATE_TIME=@maxTime);

2. Вычитаем из переданного в аргументы функции текущего расстояние найденное, и из времени текущего времени переданного в аргументы найденное. Производим деление полученных в результате вычитаний значений, получаем среднею скорость:

DECLARE @Time int;
 DECLARE @Km_h int;
 SET @Km_h=0;
 
 SET @Time=DATEDIFF(HOUR, @maxTime, @DATE_TIME);
  
 IF (@Time!=0) 
 SET @Km_h=(@distance_km-@lastDistance_km)/@Time;
2 фев 18, 11:39    [21160050]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
iiyama
Member

Откуда:
Сообщений: 642
Vlad__i__mir,
+


CREATE TABLE [dbo].[POINTS]
(
	[Id_SPEED] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Id_piople] INT NOT NULL, 
 	[Date] DATE NOT NULL,
	[Time] TIME NOT NULL,
	[distance_km] INT NOT NULL,
	[DateAndTime] AS CAST([Date] as datetime)+CAST([Time] as datetime) PERSISTED
)

INSERT INTO [dbo].[POINTS] ([Id_piople], [Date], [Time], [distance_km])
values
 (1,'20180202', '12:00:00',0)
,(1,'20180202', '12:05:00',1)
,(1,'20180202', '12:11:00',2)
,(1,'20180202', '12:17:00',3)
,(1,'20180202', '12:23:30',4)
,(1,'20180202', '12:30:50',5)

,(2,'20180202', '12:00:00',0)
,(2,'20180202', '12:04:50',1)
,(2,'20180202', '12:10:10',2)
,(2,'20180202', '12:16:00',3)
,(2,'20180202', '12:22:00',4)
,(2,'20180202', '12:29:50',5)
*/	

--create VIEW dbo.AVG_SPEED_ON_STEPS AS
select 
	  [Id_SPEED]
	, [Id_piople]
	, [Date]
	, [Time]

	, [distance_km]
	, LAG(distance_km) OVER(partition by id_piople ORDER BY distance_km) AS pdistance_km

	, [DateAndTime]
	, LAG(DateAndTime) OVER(partition by id_piople ORDER BY distance_km) AS pDateAndTime

	, 3600.*(distance_km - LAG(distance_km) OVER(partition by id_piople ORDER BY distance_km))/NULLIF(DATEDIFF(ss, LAG(DateAndTime) OVER(partition by id_piople ORDER BY distance_km), DateAndTime), 0) AS [Speed_kmh]
from Points p1

2 фев 18, 12:34    [21160220]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт значений в БД  [new]
Vlad__i__mir
Member

Откуда:
Сообщений: 294
iiyama, спасибо!

Ответе ещё пожалуйста на пару не объемных вопросов.

1. Почему решили, что лучше объединить столбцы [Date] и [Time], а не взять один типа datetime?

2. Вот здесь можно сделать сортировку по времени, а то вдруг он в обратном направлении пойдёт?
LAG(distance_km) OVER(partition by id_piople ORDER BY distance_km) AS pdistance_km
LAG(DateAndTime) OVER(partition by id_piople ORDER BY distance_km) AS pDateAndTime



И ещё пару вопросов всё-таки по функциям (простоя не знаю получится ли у меня отразить представление в C# как класс сущность):
А. Может ли функция вызываемая для расчёта значения поля таблицы просматривать и анализировать строки этой же таблицы?
Б. Если А - да, то попадёт ли в её диапазон анализа запись, которая добавляется и для которой рассчитывается значение столбца?
В. Можно ли в качестве источника данных в запросе SELECT указывать не имя таблицы, а функцию, которая должна вернуть таблицу?
3 фев 18, 01:58    [21162333]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить