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

Откуда:
Сообщений: 53
Ребят, привет.
У меня есть приложение, клиент написан на WPF, аналитические выкладки в MS SQL (хранимые процедуры)
Помогите разобраться с задачей, совсем голову уже сломал.
Есть исходные данные: в БД существует таблица, которая содержит:
Счет - дата операции - Остаток
Задача: Нужно проверить, было ли по счету за последний год в любые 30 последовательных дней приращение остатка в 2 раза?
т.е. к примеру: у нас есть расчетный период с 10.06.2016 по 09.06.2017, берем первые 30 дней, с 10.06 по 09.07 и проверяем последовательно:
остаток за 09.06 и остаток за 10.06, затем остаток за 09.06 и остаток за 11.06, таким образом нужно проверить первый день периода с каждым последующим днем.
Затем, если проверили первые 30 дней, нужно перейти к 2-ым 30 дням, это получается период с 11.06 по 10.07, внутри этого периода
опять проверяем первый день периода (11.06) с последующими днями и так пройти весь год.
Можно ли такую логику реализовать на tsql ?
Думал в сторону курсоров,к примеру ну создам курсор, ну буду буду я бежать по этой таблицы, но как брать корректно периоды и сравнивать остатки по датам?а дальше что? и по времени тоже будет очень затратно...в общем не знаю, ничего не приходит на ум.
9 июн 17, 09:32    [20551995]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
aleksrov
Member

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

Курсоры в TSQL от лукавого. Их чаще всего используют для административных задач и никогда для таких как у вас. SQL построен на теории множеств, которая никак не вяжется с курсорами. Это надо делать на клиенте скорее.
9 июн 17, 09:44    [20552021]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
_den89, это хотите?

select	[т].[Счет], т.[дата операции], [т].[Остаток], [т2].[дата операции], [т2].[Остаток]
from	[dbo].[Таблица] as [т]
		inner join master..spt_values as sv on sv.Type = 'p' and sv.number between 1 and 30
		inner join dbo.Таблица as [т2] on [т2].[Счет] = [т].[Счет] and [т2].[дата операции] = dateadd(day, sv.number, т.[дата операции])
where	[т].[дата операции] between '20160610' and '20170609'
	and [т2].[Остаток] >= ([т].[Остаток] * 2)
9 июн 17, 09:53    [20552048]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
Владислав Колосов
Member

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

на 2102+ можете использовать оконные предложения over() для подсчета сумм для каждых 30 строк:

select sum(number) over (ORDER BY number ROWS BETWEEN 0 FOLLOWING AND 2 FOLLOWING) ss
from master.dbo.spt_values 
where type = 'P'


Несложно модифицировать запрос для своих целей, имея календарь. Тем более, просто, если у вас есть ежедневные остатки.
9 июн 17, 11:15    [20552351]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
_den89
Member

Откуда:
Сообщений: 53
Minamoto, спасибо Вам огроменное! То что нужно
14 июн 17, 16:19    [20564343]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
_den89
Member

Откуда:
Сообщений: 53
Владислав Колосов, спасибо, попробую
14 июн 17, 16:19    [20564346]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3681
aleksrov
_den89,

Курсоры в TSQL от лукавого. Их чаще всего используют для административных задач и никогда для таких как у вас. SQL построен на теории множеств, которая никак не вяжется с курсорами. Это надо делать на клиенте скорее.


жжоте
14 июн 17, 16:29    [20564390]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
AlexTank
Member

Откуда: SPB.RU
Сообщений: 93
Вопрос в тему:
Есть функция

ALTER FUNCTION [dbo].[getAreaID] ( @StaffID int)
RETURNS varchar(512)
AS
BEGIN

 DECLARE @result VARCHAR(512) = ''
 
 SELECT @result = @result + convert(varchar,s.ID) + ','
 from 
 [staff_area] ss with (nolock)
 left join dimArea s with (nolock) on ss.AreaID = s.ID
 where ss.StaffID = @StaffID
 order by s.Name

 if (LEN(@result) > 0) set @result = left(@result,len(@result)-1)
 else set @result = null
 RETURN @result
END

GO


Это теперь так курсоры принято писать?
Что будет работать быстрее, эта вариация или конструкция
SELECT FieldA , STUFF(( SELECT  ','+ FieldB FROM TableName a
WHERE b.FieldA = a.FieldA FOR XML PATH('')),1 ,1, '')  Members
FROM TableName b
GROUP BY FieldA;
14 июн 17, 17:08    [20564507]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9300
AlexTank
Что будет работать быстрее, эта вариация или конструкция
А сравнить никак?
Ну и еще учесть, что "конструкция" недокументирована и при определенных условиях может вернуть неверный результат.
14 июн 17, 17:19    [20564556]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
AlexTank
Member

Откуда: SPB.RU
Сообщений: 93
invm
AlexTank
Что будет работать быстрее, эта вариация или конструкция
А сравнить никак?
Ну и еще учесть, что "конструкция" недокументирована и при определенных условиях может вернуть неверный результат.

Сравнить можно, но для того, чтобы на реальных данных сравнить, нужно сделать несколько трудоемких процедур, а на тестовых они примерно одинаково работают.
Только напрягает вызов функции во вьюшке (на каждую строку), которая иногда в себя уходит, поэтому и полез ковырять.
14 июн 17, 17:26    [20564588]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
invm
Member

Откуда: Москва
Сообщений: 9300
AlexTank
а на тестовых они примерно одинаково работают.
Видимо сравнивали неправильно.
Хотите увеличить производительность - избавляйтесь от скалярных функций.
+ Сравнение
use tempdb;
go

create table dbo.t (id int identity primary key, g int, s varchar(100), o int);

insert into dbo.t
 (g, s, o)
 select top (1000000)
  rand(checksum(newid())) * 1000, cast(newid() as varchar(100)), rand(checksum(newid())) * 1000
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

create index IX_t__g on dbo.t(g) include (s, o);
go

create function dbo.fnStringConcatScalar
(
 @g int
)
returns varchar(max)
as
begin
 declare @result varchar(max) = '';

 select
  @result += ', ' + s
 from
  dbo.t
 where
  g = @g
 order by
  o;

 return @result;
end;
go

create function dbo.fnStringConcatInline
(
 @g int
)
returns table
as
return (
 select stuff((
   select ', ' + s from dbo.t where g = @g order by o for xml path(''), type
  ).value('.', 'varchar(max)'), 1, 2, '') as result
);
go

declare @s varchar(max);

set statistics time on;

select
 @s = dbo.fnStringConcatScalar(a.g)
from
 (select distinct g from dbo.t) a
option
 (maxdop 1);

select
 @s = b.result
from
 (select distinct g from dbo.t) a cross apply
 dbo.fnStringConcatInline(a.g) b
option
 (maxdop 1);

set statistics time off;
go

drop function dbo.fnStringConcatScalar, dbo.fnStringConcatInline;
drop table dbo.t;
go
14 июн 17, 18:13    [20564751]     Ответить | Цитировать Сообщить модератору
 Re: Перебор значений в таблице  [new]
AlexTank
Member

Откуда: SPB.RU
Сообщений: 93
invm
AlexTank
а на тестовых они примерно одинаково работают.
Видимо сравнивали неправильно.
Хотите увеличить производительность - избавляйтесь от скалярных функций.
+ Сравнение


Так то показательно
Спасибо

Время работы SQL Server:
Время ЦП = 5062 мс, затраченное время = 5196 мс.

Время работы SQL Server:
Время ЦП = 2438 мс, затраченное время = 2467 мс.
15 июн 17, 09:57    [20565774]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить