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

Откуда:
Сообщений: 152
Добрый день.
Есть задачка такая. Есть набор счетчиков, с которых периодически приходят показания. Шкала у счетчиков ограничена и при превышении макс. значения, показания начинаются с 0. Т.е. последовательно показаний выглядит так - 1,2,3,4,1.5,2.1,3,4,2.5 и т.д.
Все это сваливается в табличку вида
CREATE TABLE T1(
ParentId INT NOT NULL -- идентификатор счетчика
,Cdate SMALLDATETIME NOT NULL -- дата снятия показания
,CurrValue MONEY NOT NULL -- показание счетчика
,-- всякие доп. сведения
)


Надо получить для каждого счетчика информацию в тот момент, когда его показание стало больше некоего заданного. Если последнее показание меньше заданного, то ничего не выводить. Т.е. для значения 2.5 и вот такого набора входных данных
ParentIdCdateCurrValue...
1'20110101'1...
1'20110201'2...
1'20110301'3...
1'20110401'1...
1'20110501'7...
1'20110517'8...
2'20110101'1...
2'20110701'1.5...
3'20110115'0.5...
3'20110120'1.7...
3'20110123'3...

я ожидаю результат вида
ParentIdCdateCurrValue...
1'20110501'7...
3'20110123'3...

Вопрос - можно ли это сделать проще и быстрее, чем вот так?
DECLARE
	@Value MONEY
SET
	@Value=2.5
SELECT 
	B.*
FROM (
-- п.1. последняя дата, когда показание было меньше заданного
		SELECT 
			ParentId
			,MAX(Cdate) Cdate
		FROM T1
		WHERE
			CurrValue<@Value
		GROUP BY 
			ParentId
) A
-- данные из строки, следующей за датой и п.1
CROSS APPLY (SELECT TOP 1 X.* FROM T1 AS X WHERE X.ParentId=A.ParentId AND X.Cdate>A.Cdate ORDER BY X.Cdate) B


Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
28 мар 12, 14:56    [12327665]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
select top (1) with ties
 *
from
 T1
where
 CurrValue < @Value
order by
 row_number() over (partition by ParentId order by CurrValue);
28 мар 12, 15:16    [12327860]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
Glamorama
Member

Откуда:
Сообщений: 152
invm



Такой вариант вернет состояние ДО перехода через @Value, а надо ПОСЛЕ.
28 мар 12, 16:12    [12328386]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Glamorama
Такой вариант вернет состояние ДО перехода через @Value, а надо ПОСЛЕ.
И что, так трудно поменять условия?
28 мар 12, 18:09    [12329335]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
Glamorama
Member

Откуда:
Сообщений: 152
Извиняюсь!
Не очень точно выразился в исходном посте.
Вместо
"Надо получить для каждого счетчика информацию в тот момент, когда его показание стало больше некоего заданного"
надо
"Надо получить для каждого счетчика информацию в тот момент, когда его показание последний раз стало больше некоего заданного"
При таких раскладах, как ни поверни запрос, получается не то.
28 мар 12, 19:53    [12329857]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Glamorama
Надо получить для каждого счетчика информацию в тот момент, когда его показание последний раз стало больше некоего заданного
select top (1) with ties
 *
from
 T1
where
 CurrValue > @Value
order by
 row_number() over (partition by ParentId order by CDate desc);
28 мар 12, 20:05    [12329898]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
Glamorama
Member

Откуда:
Сообщений: 152
Плохой из меня, видать, рассказчик.

Этот вариант не соответствует условию "Если последнее показание меньше заданного, то ничего не выводить"
Кончено, можно к нему прицепить условие
 (SELECT TOP 1 CurrValue FROM T1 ORDER BY Cdate DESC)>@Value
,
но уже не так красиво и лаконично получается.
28 мар 12, 21:50    [12330248]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Glamorama
Плохой из меня, видать, рассказчик.

Этот вариант не соответствует условию "Если последнее показание меньше заданного, то ничего не выводить"

Инетересно, как можно при условии CurrValue > @Value, получить в результирующем наборе "показание меньше заданного"?

Задание:
"Надо получить для каждого счетчика информацию в тот момент, когда его показание последний раз стало больше некоего заданного"

declare @t table (ParentId int,	Cdate date,	CurrValue numeric(18,2));

insert into @t
values
(1,	'20110101',	1),
(1,	'20110201',	2),
(1,	'20110301',	3),
(1,	'20110401',	1),
(1,	'20110501',	7),
(1,	'20110517',	8),
(2,	'20110101',	1),
(2,	'20110701',	1.5),
(3,	'20110115',	0.5),
(3,	'20110120',	1.7),
(3,	'20110123',	3);

declare @Value;

select
 @Value = 2.5;
 
select top (1) with ties
 *
from
 @t
where
 CurrValue > @Value
order by
 row_number() over (partition by ParentId order by CDate desc);
А теперь распишите алгоритм, по которому вы ожидаете получить
ParentIdCdateCurrValue...
1'20110501'7...
3'20110123'3...
28 мар 12, 23:00    [12330470]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
Glamorama
Member

Откуда:
Сообщений: 152
В задаче 2 условия:
1)нужны только те счетчики, у которых последнее по Cdate показание более @Value (либо равно)
2)нужны только первые показания после последнего перехода через @Value
Собственно ожидаемый результат как раз и отражает эти два условия:
1) счетчик с 2 ещё не перешел через @Value, поэтому не показывается
2) счетчик 3 один раз перешел через @Value и имеет только одно показание более @Value (оно и показывается)
3) счетчик 1 перешел через @Value 2 раза, но нас интересует только последний переход и показание следующие сразу за ним (Cdate='20110501' и CurrValue=7)

У меня-то алгоритм топорный:
1. Находим максимальную дату, когда показание счетчика было меньше @Value.
2. Следующее за этим показание (если оно существует) - наш клиент.
28 мар 12, 23:38    [12330594]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
with x as
(
 select
  *,
  row_number() over (partition by ParentId, case when CurrValue < @Value then 1 else 0 end order by Cdate) as rn1,
  row_number() over (partition by ParentId, case when CurrValue >= @Value then 1 else 0 end order by Cdate desc) as rn2
 from
  @t
)
select top (1) with ties
 ParentId, Cdate, CurrValue
from
 x
where
 rn1 = rn2
order by
 row_number() over (partition by ParentId order by Cdate desc);
29 мар 12, 00:19    [12330717]     Ответить | Цитировать Сообщить модератору
 Re: Последняя запись, у которой значение больше заданного  [new]
aleks2
Guest
declare @t table (ParentId int,	Cdate datetime,	CurrValue numeric(18,2));

insert into @t values(1,	'20110101',	1)
insert into @t values(1,	'20110201',	2)
insert into @t values(1,	'20110301',	3)
insert into @t values(1,	'20110401',	1)
insert into @t values(1,	'20110501',	7)
insert into @t values(1,	'20110517',	8)
insert into @t values(2,	'20110101',	1)
insert into @t values(2,	'20110701',	1.5)
insert into @t values(3,	'20110115',	0.5)
insert into @t values(3,	'20110120',	1.7)
insert into @t values(3,	'20110123',	3);

declare @Value numeric(18,2)
select @Value = 2.5;

;with
-- последние счетчики БЕЗ превышением
lastLo as ( 
select  X.ParentId, X.CDate
FROM 
(select T.*, row_number() over (partition by T.ParentId order by T.CDate desc) N from @t T WHERE T.CurrValue < @Value) X
WHERE N=1 
),
-- первые после последних БЕЗ превышения счетчики С превышением
firstHi as (
select  *
FROM 
(select T.*, row_number() over (partition by T.ParentId order by T.CDate asc) N from @t T INNER JOIN lastLo L ON L.ParentId=T.ParentId AND L.CDate<T.CDate) X
WHERE N=1 
)
select * FROM firstHi
29 мар 12, 06:24    [12330958]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить