ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт значений в БД
22 сообщений из 22, страница 1 из 1
Расчёт значений в БД
    #39591962
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Доброго времени суток!

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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]
)



Если да, то подскажите пожалуйста как?
...
Рейтинг: 0 / 0
26.01.2018, 21:58
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39591969
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
Vlad__i__mirПодскажите пожалуйста, можно ли в таблице БД задать столбец, в котором бы автоматически рассчитывалось бы значение для новой записи исходя из предыдущих записей.конкретно на этот вопрос (без обсуждения влияния на производительность) ответ - вычисляемый столбец таблицы может быть основан на функции
Код: sql
1.
2.
3.
4.
5.
create table dbo.Table   
(  
    id int,    
    CalcColumn as dbo.fn_Function(id)  
);
...
Рейтинг: 0 / 0
26.01.2018, 22:25
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39591971
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
В триггере можно хоть квадратные уравнения решать.
...
Рейтинг: 0 / 0
26.01.2018, 22:26
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39592003
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Дедушка,

это он новую таблицу создаст, а мне нужно чтобы он в этой же таблице в столбец записал
...
Рейтинг: 0 / 0
26.01.2018, 23:31
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39592004
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Гавриленко Сергей Алексеевич,

что за тригер?
...
Рейтинг: 0 / 0
26.01.2018, 23:32
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39592008
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Vlad__i__mirГавриленко Сергей Алексеевич,

что за тригер?Про триггеры (create trigger) и вычисляемые поля (computed columns) на основе функций вам придется прочитать в документации.
...
Рейтинг: 0 / 0
26.01.2018, 23:47
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39593870
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Дедушка, dbo.fn_Function(id) - это обычная прописываемая нами функция? Где её нужно прописывать?
Или это триггер?
Можно ли будет такую же функцию также вызвать в столбце таблицы, в которою добавляется запись?
...
Рейтинг: 0 / 0
31.01.2018, 12:44
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39593907
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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
...
Рейтинг: 0 / 0
31.01.2018, 13:26
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39593995
iiyama
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iiyama
Участник
Vlad__i__mir,
немного не по вопросу, но может лучше сделать представление?
типа такого
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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
...
Рейтинг: 0 / 0
31.01.2018, 14:26
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594301
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Дедушка
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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" или в триггерах? Или ...?
...
Рейтинг: 0 / 0
31.01.2018, 18:43
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594302
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
https://msdn.microsoft.com/ru-ru/library/ms186755(v=sql.120).aspx
...
Рейтинг: 0 / 0
31.01.2018, 18:44
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594391
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Гавриленко Сергей Алексеевич https://msdn.microsoft.com/ru-ru/library/ms186755(v=sql.120).aspx

Спасибо, немного прояснилось. Но в какой части БД необходимо прописывать код функции не понял?
...
Рейтинг: 0 / 0
31.01.2018, 20:16
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594399
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Дедушка
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
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. Зачем мы удаляем здесь функцию?
Код: sql
1.
2.
drop function if exists dbo.fn_T
go


2. Почему у нас здесь 2 "return"? Оба return-a относятся к функции dbo.fn_T?
3. Что должен вернуть 1-ый return, а что 2-ой?
Код: sql
1.
2.
3.
4.
5.
6.
7.
create function dbo.fn_T (@id int)
  returns int
as
  begin 
      return(@id + 1)
  end
go
...
Рейтинг: 0 / 0
31.01.2018, 20:38
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594459
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Vlad__i__mir,

Вопрос 2 снимается, узнал что это мы определяем тип возвращаемого функцией значения. Просто в программировании БД я почти нуль ))
...
Рейтинг: 0 / 0
31.01.2018, 22:00
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594494
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Дедушка, если я объединю столбцы DATE and TIME будет ли работать следующее решение:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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)
 
)


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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
 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
...
Рейтинг: 0 / 0
31.01.2018, 23:45
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39594498
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
Vlad__i__mir,
INSERT INTO dbo.SPEED_2
SELECT [Id_SPEED], [Id_piople], [DATE_TIME], [distance_km] FROM SPEED WHERE Id_piople=@Id_piople
...
Рейтинг: 0 / 0
31.01.2018, 23:53
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39595103
iiyama
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iiyama
Участник
Vlad__i__mir,

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

B.
Как вы думаете что вернет функция DATEDIFF(HOUR, @maxTime, @DATE_TIME)
для значений @maxTime='20180101 00:00:00', @DATE_TIME='20180101 00:00:10'
и какую ошибку вы получите вызвав ее в знаменателе
...
Рейтинг: 0 / 0
01.02.2018, 17:16
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39595228
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
iiyamaVlad__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-ая функция. Но проблема в том, что приходится вызывать данную функцию дважды: первый раз для того чтобы определить максимальное время, а второй раз для того чтобы выбрать расстояние соответствующее данному времени. И вот тут вопрос - получается одни и те же записи будут добавлены дважды?
Не знаю как правильней будет здесь поступить.

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

Код: 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.
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



Помогите пожалуйста с пунктом А. Мне кажется у меня не верно, но как по другому обойти ограничение не знаю.
...
Рейтинг: 0 / 0
01.02.2018, 20:22
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39595452
iiyama
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iiyama
Участник
Vlad__i__mir,

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

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

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

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

Пи.Эс. Я бы реализовал через представление, где вместе со скоростью, была бы доступна информация и по 2м положениям в пространстве, на основе чего она была и рассчитана
...
Рейтинг: 0 / 0
02.02.2018, 10:43
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39595490
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
iiyama, это я сделал. Проблема с реализацией. В этом прошу помощи.

1. Мы находим записи с нужным нам ID точки (Id_piople), далее находим среди них запись с максимальной датой и временем и берём расстояние соответствующее этой дате времени. Тут возникает один вопрос попадёт или нет в эту выборку добавляемая запись, для которой мы делаем расчёт?
Код: sql
1.
2.
3.
4.
 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. Вычитаем из переданного в аргументы функции текущего расстояние найденное, и из времени текущего времени переданного в аргументы найденное. Производим деление полученных в результате вычитаний значений, получаем среднею скорость:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
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;
...
Рейтинг: 0 / 0
02.02.2018, 11:39
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39595530
iiyama
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iiyama
Участник
Vlad__i__mir,
 


Код: 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.
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


...
Рейтинг: 0 / 0
02.02.2018, 12:34
| Ответить | Цитировать | Написать  
Расчёт значений в БД
    #39595974
Vlad__i__mir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vlad__i__mir
Гость
iiyama, спасибо!

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

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

2. Вот здесь можно сделать сортировку по времени, а то вдруг он в обратном направлении пойдёт?
Код: sql
1.
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 указывать не имя таблицы, а функцию, которая должна вернуть таблицу?
...
Рейтинг: 0 / 0
03.02.2018, 01:58
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт значений в БД
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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