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

Откуда:
Сообщений: 86
Версия сервера: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Имеем следующую таблицу:
DECLARE @t table (date datetime, SBS decimal(38,5),IIS decimal(38,5))


INSERT INTO @t (date,SBS,IIS)
SELECT '20150301 01:02:03', 10, 0
UNION ALL
SELECT '20150301 03:02:03', 10, 0
UNION ALL
SELECT '20150301 05:02:03', 10, 0
UNION ALL
SELECT '20150301 06:02:03', 10, 100
UNION ALL
SELECT '20150301 07:02:03', 100, 0
UNION ALL
SELECT '20150301 08:02:03', 100, 0
UNION ALL
SELECT '20150301 09:02:03', 100, 0
UNION ALL
SELECT '20150301 10:02:03', 100, 0
UNION ALL
SELECT '20150301 11:02:03', 100, 0
UNION ALL
SELECT '20150301 12:02:03', 100, 0
UNION ALL
SELECT '20150301 13:02:03', 100, 0
UNION ALL
SELECT '20150305 01:02:03', 100, 1000



Select * from @t


необходимо получить вот такую:
date SBS IIS itog
2015-03-01 01:02:03.000 10 0 0
2015-03-01 03:02:03.000 10 0 0
2015-03-01 05:02:03.000 10 0 0
2015-03-01 06:02:03.000 10 100 40
2015-03-01 07:02:03.000 100 0 0
2015-03-01 08:02:03.000 100 300 200
2015-03-02 09:02:03.000 100 0 0
2015-03-03 10:02:03.000 100 0 0
2015-03-03 11:02:03.000 100 0 0
2015-03-03 12:02:03.000 100 0 0
2015-03-03 13:02:03.000 100 0 0
2015-03-05 01:02:03.000 100 0 0
2015-03-05 02:02:03.000 100 0 0
2015-03-05 03:02:03.000 100 1000 800


Поле itog считается как наименьшее из значений IIS и суммы SBS на 5 дней назад. В сумму SBS входят только те значения которые не были учтены для другого значения IIS.

У меня есть вариант реализации с циклом внутри цикла, но возможно кто-то подскажет более элегантное решение, сейчас думаю в сторону рекурсивного CTE, но пока что-то осмысленное написать не удалось.
Буду признателен, за любые подсказки.
26 апр 16, 10:43    [19106590]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
deadok
В сумму SBS входят только те значения которые не были учтены для другого значения IIS.


Это что значит?
26 апр 16, 10:48    [19106625]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20974
iljy, вероятно, он имеет в виду, что суммируются только записи при сортировке по date между записями с ненулевым IIS... т.е. отсечение или по ненулевому IIS, или по разнице date более 5 суток.
26 апр 16, 10:50    [19106640]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
deadok
Member

Откуда:
Сообщений: 86
Akina почти прав.

пляшем от не нулевых значений IIS. смотрим упорядоченные по дате значения SBS на 5 дней назад, берем меньшее из значений: IIS или cумму SBS, но есть нюанс если значения из колонки SBS уже использовались для другой строки с IIS то они в сумму для текущей строки IIS попасть не должны.
26 апр 16, 10:58    [19106708]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

данные у вас странные.

;with cte as
(
	Select *, ROW_NUMBER() over(order by date) rn,
			  ROW_NUMBER() over (ORDER BY case IIS when 0 then 1 else 0 end,
										  case IIS when 0 then DATEDIFF(d, 0, date)
														  else  -DATEDIFF(d, 0, date)
										  end, date) gn
	from @t
)
select t.date, t.SBS, t.IIS, case IIS when 0 then 0 else case when s + SBS > IIS then IIS else s+SBS end end
from cte t outer apply(
	select SUM(SBS) s
	from cte cc
	where t.IIS != 0 and cc.IIS = 0 and dateadd(d, -5, t.date) <= cc.date and cc.date < t.date and cc.gn - cc.rn = t.gn
) o
order by t.date
26 апр 16, 11:18    [19106861]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
deadok
Member

Откуда:
Сообщений: 86
iljy, данные и правда странные должны быть вот такие исходные:
INSERT INTO @t (date,SBS,IIS)
SELECT '20150301 01:02:03', 10, 0
UNION ALL
SELECT '20150301 03:02:03', 10, 0
UNION ALL
SELECT '20150301 05:02:03', 10, 0
UNION ALL
SELECT '20150301 06:02:03', 10, 100
UNION ALL
SELECT '20150301 07:02:03', 100, 0
UNION ALL
SELECT '20150301 08:02:03', 100, 300
UNION ALL
SELECT '20150302 09:02:03', 100, 0
UNION ALL
SELECT '20150303 10:02:03', 100, 0
UNION ALL
SELECT '20150303 11:02:03', 100, 0
UNION ALL
SELECT '20150303 12:02:03', 100, 0
UNION ALL
SELECT '20150303 13:02:03', 100, 0
UNION ALL
SELECT '20150305 01:02:03', 100, 0
UNION ALL
SELECT '20150305 02:02:03', 100, 0
UNION ALL
SELECT '20150305 03:02:03', 100, 1000
26 апр 16, 11:25    [19106924]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

тогда запрос подправьте

;with cte as
(
	Select *, ROW_NUMBER() over(order by date) rn,
			  ROW_NUMBER() over (ORDER BY case IIS when 0 then 1 else 0 end,
										  case IIS when 0 then DATEDIFF(s, '20000101', date)
														  else  -DATEDIFF(s, '20000101', date)
										  end, date) gn
	from @t
)
select t.date, t.SBS, t.IIS, case IIS when 0 then 0 else case when s + SBS > IIS then IIS else s+SBS end end
from cte t outer apply(
	select SUM(SBS) s
	from cte cc
	where t.IIS != 0 and cc.IIS = 0 and dateadd(d, -5, t.date) <= cc.date and cc.date < t.date and cc.gn - cc.rn = t.gn
) o
order by t.date
26 апр 16, 11:48    [19107125]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
deadok
Member

Откуда:
Сообщений: 86
iljy, спасибо, но кое что не учитывает ваш запрос, сейчас попробую придумать как это учесть.

Не учитывается ситуация если нужно взять сумму из колонки SBS не только между не нулевыми IIS, но и глубжи

DECLARE @t table (date datetime, SBS decimal(38,5),IIS decimal(38,5))


INSERT INTO @t (date,SBS,IIS)
SELECT '20150301 01:02:03', 10, 0
UNION ALL
SELECT '20150301 03:02:03', 10, 0
UNION ALL
SELECT '20150301 05:02:03', 10, 0
UNION ALL
SELECT '20150301 06:02:03', 10, 100
UNION ALL
SELECT '20150301 07:02:03', 100, 0
UNION ALL
SELECT '20150301 07:02:04', 100, 0
UNION ALL
SELECT '20150301 07:02:04', 100, 0
UNION ALL
SELECT '20150301 08:02:03', 100, 300
UNION ALL
SELECT '20150302 09:02:03', 100, 0
UNION ALL
SELECT '20150303 10:02:03', 100, 0
UNION ALL
SELECT '20150303 11:02:03', 100, 0
UNION ALL
SELECT '20150303 12:02:03', 100, 0
UNION ALL
SELECT '20150303 13:02:03', 100, 0
UNION ALL
SELECT '20150305 01:02:03', 100, 0
UNION ALL
SELECT '20150305 02:02:03', 100, 0
UNION ALL
SELECT '20150305 03:02:03', 100, 1000



результат должен получиться такой:
date SBS IIS itog
2015-03-01 01:02:03.000 10 0 0
2015-03-01 03:02:03.000 10 0 0
2015-03-01 05:02:03.000 10 0 0
2015-03-01 06:02:03.000 10 100 40
2015-03-01 07:02:03.000 100 0 0
2015-03-01 07:02:04.000 100 0 0
2015-03-01 07:02:05.000 100 0 0
2015-03-01 08:02:03.000 100 300 300
2015-03-02 09:02:03.000 100 0 0
2015-03-03 10:02:03.000 100 0 0
2015-03-03 11:02:03.000 100 0 0
2015-03-03 12:02:03.000 100 0 0
2015-03-03 13:02:03.000 100 0 0
2015-03-05 01:02:03.000 100 0 0
2015-03-05 02:02:03.000 100 0 0
2015-03-05 03:02:03.000 100 1000 900
26 апр 16, 12:15    [19107369]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Дед-Папыхтет
Member [заблокирован]

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

По Вашему условию я так понимаю нужно что то типа такого?

DECLARE @t table (date datetime, SBS decimal(38,5),IIS decimal(38,5))


INSERT INTO @t (date,SBS,IIS) values
('20150301 01:02:03', 10, 0		),
('20150301 03:02:03', 10, 0		),
('20150301 05:02:03', 10, 0		),
('20150301 06:02:03', 10, 100	),
('20150301 07:02:03', 100, 0	),
('20150301 08:02:03', 100, 0	),
('20150301 09:02:03', 100, 0	),
('20150301 10:02:03', 100, 0	),
('20150301 11:02:03', 100, 0	),
('20150301 12:02:03', 100, 0	),
('20150301 13:02:03', 100, 0	),
('20150305 01:02:03', 100, 1000	)


select *,
	iif(s<IIS, s, IIS) itog
from
(
Select *
	,sum(SBS) over (order by [date] rows between 5 preceding and current row) s
from @t
) t


Но смотря на то что Вы вставляете в таблицу и то что выводите (кроме поля итог) данные у Вас не совпадают...
27 апр 16, 10:42    [19111565]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Добрый Э - Эх
Guest
Дед-Папыхтет,

ему нужно не ROWS BETWEEN, а RANGE BETWEEN.
Но в любом случае - на 2008-м сервере не взлетит
27 апр 16, 11:42    [19111946]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить