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

Откуда:
Сообщений: 438
Здравствуйте! Есть таблица примерно такого содержания:

nomerDatav1v2
AAAA2011-07-15 16:02:14.34010100
AAAA2011-07-15 16:03:15.72012150
AAAA2011-07-15 16:04:20.58015140
BBBB2011-07-15 16:10:22.12722200
BBBB2011-07-15 16:11:27.98025250
AAAA2011-07-15 17:08:11.68355400
AAAA2011-07-15 17:09:38.12390350
CCCC2011-07-15 19:01:45.45730300


Необходимо получить
NomerDatav1v2
AAAA2011-07-15 16:04:20.57915140
BBBB2011-07-15 16:11:27.98025250
AAAA2011-07-15 17:09:38.12390350
CCCC2011-07-15 19:01:45.45630300


Здесь для каждого Nomer найти строки с максимальной датой, причем новый блок начинается, если разница между двумя одинаковыми Nomer больше часа.

Тестовые данные:
declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
 select 'AAAA', '2011-07-15 16:02:14.340', 10, 100 union all
 select 'AAAA', '2011-07-15 16:03:15.720', 12, 150 union all
 select 'AAAA', '2011-07-15 16:04:20.579', 15, 140 union all 
 select 'BBBB', '2011-07-15 16:10:22.125', 22, 200 union all
 select 'BBBB', '2011-07-15 16:11:27.980', 25, 250 union all
 select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
 select 'AAAA', '2011-07-15 17:09:38.123', 90, 350 union all  
 select 'CCCC', '2011-07-15 19:01:45.456', 30, 300

select * from @t


select @@version
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 
20 июл 11, 12:50    [10999541]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Начинающий SQL 2008,

select top(1) with ties
	* 
from 
	@t
order by
	row_number() over(partition by nomer, datediff(hh,data,'19000101') order by data desc)
?
особо не проверял...
20 июл 11, 13:03    [10999651]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
SomewhereSomehow,
Круто! Сортировку на реальных данных подправлю...
Спасибо!
20 июл 11, 13:12    [10999723]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
автор
причем новый блок начинается, если разница между двумя одинаковыми Nomer больше часа

усложним задачу

declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
 select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
 select 'BBBB', '2011-07-15 16:10:22.125', 22, 200 union all
 select 'BBBB', '2011-07-15 16:11:27.980', 25, 250 union all
 select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
 select 'AAAA', '2011-07-15 17:09:38.123', 90, 350 union all  
 select 'CCCC', '2011-07-15 19:01:45.456', 30, 300

select top(1) with ties
	* 
from 
	@t
order by
	row_number() over(partition by nomer, datediff(hh,data,'19000101') order by data desc)

nomer   Data    v1  v2
AAAA    2011-07-15 17:09:38.123 90  350
AAAA    2011-07-15 16:30:20.580 15  140
BBBB    2011-07-15 16:11:27.980 25  250
CCCC    2011-07-15 19:01:45.457 30  300
20 июл 11, 13:29    [10999822]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
HandKot,
да, действительно.

На таких данных
nomerDatav1v2
BBBB2011-07-15 16:10:22.12722200
BBBB2011-07-15 16:11:27.98025250
AAAA2011-07-15 16:30:14.34010100
AAAA2011-07-15 17:08:11.68355400
AAAA2011-07-15 17:09:38.12390350
CCCC2011-07-15 19:01:45.45730300

Должно быть
nomerDatav1v2
AAAA2011-07-15 17:09:38.12390350
BBBB2011-07-15 16:11:27.98025250
CCCC2011-07-15 19:01:45.45730300
20 июл 11, 13:44    [10999917]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
AmKad
Member

Откуда:
Сообщений: 5308
ХП + Курсор.
20 июл 11, 14:33    [11000440]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
AmKad
ХП + Курсор.
Какой ещё курсор??
SET NOCOUNT ON;

declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
 select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
 select 'BBBB', '2011-07-15 16:10:22.125', 22, 200 union all
 select 'BBBB', '2011-07-15 16:11:27.980', 25, 250 union all
 select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
 select 'AAAA', '2011-07-15 17:09:38.123', 90, 350 union all  
 select 'CCCC', '2011-07-15 19:01:45.456', 30, 300


SELECT t.nomer, t.Data, t.v1, t.v2
FROM @t t
WHERE NOT EXISTS
(
 SELECT *
 FROM @t tt
 WHERE tt.nomer=t.nomer AND tt.Data>t.Data AND tt.Data<=DATEADD(HOUR,1,t.Data)
)
ORDER BY nomer;
20 июл 11, 14:40    [11000497]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Начинающий SQL 2008,

а, так у вас нужно чтобы разница между соседними была 60 минут, тогда начинает считаться новый интервал? в таком случае придется наверное задействовать cte...хотя вот вертится у меня в памяти какое-то решение без него, подумаю как немного освобожусь.

iap,

насколько я понял, новый интервал отщелкивается, как только появляется разница более часа, т.е. на таких данных:
select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
select 'AAAA', '2011-07-15 17:39:38.123', 90, 350 union all 
select 'AAAA', '2011-07-15 18:30:38.123', 90, 350 union all
select 'AAAA', '2011-07-15 18:45:38.123', 90, 350 union all
должно быть два значения 17:39, когда превышение было первый раз и 18.45, когда превышение было во второй раз от значения предыдущего превышения. А у вас будет всего одно 18.45.

Хотя мож я опять не так понял, имхо, задача требует уточнения!
20 июл 11, 14:55    [11000626]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iap,
Спасибо! Работает правильно
+ на таких

nomerDatav1v2
AAAA2011-07-15 16:02:14.34010100
AAAA2011-07-15 16:03:15.72012150
AAAA2011-07-15 16:04:20.58015140
BBBB2011-07-15 16:10:22.12722200
BBBB2011-07-15 16:11:27.98025250
AAAA2011-07-15 17:08:11.68355400
AAAA2011-07-15 17:09:38.12390350
CCCC2011-07-15 19:01:45.45730300

+ и на таких
nomerDatav1v2
AAAA2011-07-15 16:30:14.34010100
AAAA2011-07-15 17:08:11.68355400
AAAA2011-07-15 17:09:38.12390350
BBBB2011-07-15 16:10:22.12722200
BBBB2011-07-15 16:11:27.98025250
CCCC2011-07-15 19:01:45.45730300
данных
20 июл 11, 14:58    [11000649]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
SomewhereSomehow,
Вы правильно выделили строки:

select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
select 'AAAA', '2011-07-15 17:39:38.123', 90, 350 union all 
select 'AAAA', '2011-07-15 18:30:38.123', 90, 350 union all
select 'AAAA', '2011-07-15 18:45:38.123', 90, 350 union all

На таких данных
declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
select 'AAAA', '2011-07-15 17:39:38.123', 90, 350 union all 
select 'AAAA', '2011-07-15 18:30:38.123', 90, 350 union all
select 'AAAA', '2011-07-15 18:45:38.123', 90, 350 union all
select 'CCCC', '2011-07-15 19:01:45.456', 30, 300

Должно быть
AAAA2011-07-15 17:39:38.12390350
AAAA2011-07-15 18:45:38.12390350
CCCC2011-07-15 19:01:45.45630300

Тот хороший вариант, что предложил iap, к сожалению не подходит :(
20 июл 11, 15:06    [11000747]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
SomewhereSomehow
должно быть два значения 17:39, когда превышение было первый раз
Чего-то превышения не вижу.
Или глаз замылился.
20 июл 11, 15:08    [11000758]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL 2008
SomewhereSomehow,
Вы правильно выделили строки:

select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
select 'AAAA', '2011-07-15 17:39:38.123', 90, 350 union all 
select 'AAAA', '2011-07-15 18:30:38.123', 90, 350 union all
select 'AAAA', '2011-07-15 18:45:38.123', 90, 350 union all

На таких данных
declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
select 'AAAA', '2011-07-15 17:39:38.123', 90, 350 union all 
select 'AAAA', '2011-07-15 18:30:38.123', 90, 350 union all
select 'AAAA', '2011-07-15 18:45:38.123', 90, 350 union all
select 'CCCC', '2011-07-15 19:01:45.456', 30, 300

Должно быть
AAAAt2011-07-15 17:39:38.123t90t350
AAAAt2011-07-15 18:45:38.123t90t350
CCCCt2011-07-15 19:01:45.456t30t300

Тот хороший вариант, что предложил iap, к сожалению не подходит :(
Не понял, а 18:30 мы типа игнорируем?
Где разница >=1 час между соседними?

Или надо не между соседними?
То есть в группу включаем все записи, начиная с некоторой, если разница между максимальным и минимальным временем группы не превышает 1 часа?
20 июл 11, 15:12    [11000784]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iap
Не понял, а 18:30 мы типа игнорируем?
Да

iap
Где разница >=1 час между соседними?
Превышение в 1 час от значения предыдущего превышения
20 июл 11, 15:28    [11000931]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL 2008
Превышение в 1 час от значения предыдущего превышения
А для самого первого превышения?
Относительно самого первого значения?
20 июл 11, 15:54    [11001177]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iap,
да
20 июл 11, 15:54    [11001184]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Пахнет рекурсией.
20 июл 11, 15:55    [11001193]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
user_Sql2005
Guest
Начинающий SQL 2008,
как-то так

select t1.nomer,t1.data,t1.v1,t1.v2
from
(
select *,row_number() over(partition by nomer order by data ) as num from @t
)t1
left join
(
select *,row_number() over(partition by nomer order by data ) as num from @t
)t2 on t1.nomer=t2.nomer and t1.num=t2.num-1
where case when t2.data>=DATEADD(HOUR,1,t1.Data) or t2.data is null then 1 else 0 end=1
20 июл 11, 16:19    [11001381]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
а так ?

SET NOCOUNT ON;

declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
 select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
 select 'BBBB', '2011-07-15 16:10:22.125', 22, 200 union all
 select 'BBBB', '2011-07-15 16:11:27.980', 25, 250 union all
 select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
 select 'BBBB', '2011-07-15 17:08:30.684', 55, 400 union all  
 select 'AAAA', '2011-07-15 17:09:38.123', 90, 350 union all  
 select 'CCCC', '2011-07-15 19:01:45.456', 30, 300
SELECT 
	t.nomer, t.Data, t.v1, t.v2
FROM 
	@t t
WHERE 
	t.nomer<> ISNULL((
		 SELECT TOP (1) tt.nomer
		 FROM @t tt
		 WHERE t.Data < tt.Data AND tt.Data <= DATEADD(HOUR,1,t.Data)
		 ORDER BY tt.Data), '')
ORDER BY data;


Nomer Data v1 v2
BBBB 2011-07-15 16:11:27.980 25 250
AAAA 2011-07-15 17:08:11.683 55 400
BBBB 2011-07-15 17:08:30.683 55 400
AAAA 2011-07-15 17:09:38.123 90 350
CCCC 2011-07-15 19:01:45.457 30 300
20 июл 11, 16:33    [11001486]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
user_Sql2005,
немного не то.
+
На таких данных
declare @t table (nomer varchar(4), Data datetime, v1 int, v2 int)
insert @t
select 'AAAA', '2011-07-15 16:30:14.340', 10, 100 union all
select 'AAAA', '2011-07-15 17:08:11.684', 55, 400 union all  
select 'AAAA', '2011-07-15 17:39:38.123', 90, 350 union all 
select 'AAAA', '2011-07-15 18:30:38.123', 90, 350 union all
select 'AAAA', '2011-07-15 18:45:38.123', 90, 350 union all
select 'CCCC', '2011-07-15 19:01:45.456', 30, 300
Должно быть
nomerdatav1v2
AAAA2011-07-15 17:39:38.12390350
AAAA2011-07-15 18:45:38.12390350
CCCC2011-07-15 19:01:45.45630300
20 июл 11, 16:34    [11001493]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
HandKot,
:(

На Ваших данных должно быть
nomerDatav1v2
AAAA2011-07-15 17:09:38.12390350
BBBB2011-07-15 17:08:30.68355400
CCCC2011-07-15 19:01:45.45730300
20 июл 11, 16:43    [11001555]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
да, некоторые посты не прочитал
надо немного не то
20 июл 11, 16:46    [11001579]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
Учитывая
Начинающий SQL 2008
Превышение в 1 час от значения предыдущего превышения

я бы назвал это - разбить на диапазоны
и получилось бы следующее

;with cte(nomer, mn)
as(
	Select 
		nomer
		, min(Data) mn
	from 
		@t t
	group by
		nomer)
select 
	t.nomer 
	, MAX(t.Data)
from @t t
inner join cte c on c.nomer = t.nomer
group by
	t.nomer, DATEDIFF(hh, c.mn, t.data)

ЗЫЖ либо все таки в условии задачи где-то кроется ошибка
20 июл 11, 17:15    [11001815]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
stimpi
Member

Откуда: Киев, Украина
Сообщений: 662
HandKot,

нестыковка ?
nomer Data v1 v2
AAAA 2011-07-15 16:30:14.340 10 100
AAAA 2011-07-15 17:08:11.684 55 400
AAAA 2011-07-15 17:39:38.123 90 350
AAAA 2011-07-15 17:46:38.123 90 350
AAAA 2011-07-15 18:30:38.123 90 350
AAAA 2011-07-15 18:45:38.123 90 350


nomer data
AAAA 2011-07-15 16:30:14.340
AAAA 2011-07-15 17:46:38.123
AAAA 2011-07-15 18:45:38.123
20 июл 11, 18:25    [11002289]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
iap
Пахнет рекурсией.
А можно вариант с рекурсией?
21 июл 11, 12:08    [11005622]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом.  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
HandKot,
есть маленькие несостыковки, но идея хорошая, пригодится для других задач, взял на заметку.
21 июл 11, 12:14    [11005719]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить