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

Откуда: Жатай->Подольск
Сообщений: 137
Есть набор с полями:
value int not null
time datetime not null
индексов никаких нет, таблица может быть очень большой и соответственно периодов может быть также очень много

Нужно получить дату начала периода, когда value>"какое то значение" и дату конца этого периода
т.е. набор из двух полей
begindate | enddate

Сделал так:
declare @value int
set @value="какое то значение";

select MIN(a.time) as begintime, a.endtime from (
select a.value, a.time, MIN(b.time) as endtime from
	(select * from [test] where value>@value) a
	inner join [test] b on b.time>a.time and b.speed<@value
group by a.speed, a.time) a
group by a.endtime
12 май 12, 10:42    [12540663]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
А так подойдет?
declare @value int
set @value="какое то значение";

select 
	MIN(a.time) as begintime
	,max(a.time) as endtime
from	test a
where a.value>@value
12 май 12, 10:49    [12540714]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
а почему индексов нет? Если требуется быстродействие по этому запросу и value имеет хорошее распределение, то индекс стоит ввести
12 май 12, 10:51    [12540730]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Мистер Хенки,

Не, так не пойдет, Вы 1 период нашли, да к тому же не верно

приведу примерный набор данных:

10 | 2000-01-01 12:00:00.000
20 | 2000-01-01 12:01:00.000
70 | 2000-01-01 12:02:00.000
90 | 2000-01-01 12:03:00.000
90 | 2000-01-01 12:04:00.000
90 | 2000-01-01 12:05:00.000
70 | 2000-01-01 12:06:00.000
20 | 2000-01-01 12:07:00.000
20 | 2000-01-01 12:08:00.000
20 | 2000-01-01 12:09:00.000
20 | 2000-01-01 12:10:00.000
70 | 2000-01-01 12:11:00.000
70 | 2000-01-01 12:12:00.000
80 | 2000-01-01 12:13:00.000
20 | 2000-01-01 12:14:00.000
12 май 12, 11:09    [12540865]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
тогда так можно еще
declare @test table(time datetime,value int)
insert into @test(
	time
	,value
)
values	('20100101',1)
		,('20100102',1)
		,('20100103',2)
		,('20100104',-1)
		,('20100105',1)
declare @value int 
set @value = 1		
		
;with ot(num,value,time) as (	
select 
pp = row_number() over(order by time) 
,value
,time
from @test
)

select
	t.value
	,t.num
	,t.time begindate
	,ed.datelast
from	ot t
		cross apply (
						select datelast = min(t2.time)
						from ot t2
						where	t2.num>=t.num	
								and not exists(	select 1 
												from ot t3
												where	t3.value>=@value
														and t3.num= t2.num+1)
					) ed
where t.value >=@value			
12 май 12, 11:26    [12541017]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
если уж совсем точнее то
declare @test table(time datetime,value int)
insert into @test(
	time
	,value
)
values	('20100101',1)
		,('20100102',1)
		,('20100103',2)
		,('20100104',-1)
		,('20100105',1)
declare @value int 
set @value = 1		
		
;with ot(num,value,time) as (	
select 
pp = row_number() over(order by time) 
,value
,time
from @test
)

select	
	min(t.time) begindate
	,ed.datelast
from	ot t
		cross apply (
						select datelast = min(t2.time)
						from ot t2
						where	t2.num>=t.num	
								and not exists(	select 1 
												from ot t3
												where	t3.value>@value
														and t3.num= t2.num+1)
					) ed
where t.value >@value		
group by ed.datelast
12 май 12, 11:32    [12541063]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Мистер Хенки,

немного уточню, в Вашем примере value>1 начинается '20100103' и заканчиваться должно '20100104' поскольку '20100103' все-таки еще превышает
12 май 12, 11:44    [12541162]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Matroskin
Мистер Хенки,

немного уточню, в Вашем примере value>1 начинается '20100103' и заканчиваться должно '20100104' поскольку '20100103' все-таки еще превышает


Если заканчиватся должно следующим днем, то можно так
declare @test table(time datetime,value int)
insert into @test(
	time
	,value
)
values	('20100101',1)
		,('20100102',1)
		,('20100103',2)
		,('20100104',-1)
		,('20100105',1)
declare @value int 
set @value = 1		
		
;with ot(num,value,time) as (	
select 
pp = row_number() over(order by time) 
,value
,time
from @test
)

select	
	min(t.time) begindate
	,ed.datelast
from	ot t
		cross apply (
						select datelast = min(isNull(t4.time,t2.time))
						from	ot t2
								left join ot t4
								on	t4.num = t2.num+1 
						where	t2.num>=t.num	
								and not exists(	select 1 
												from ot t3
												where	t3.value>@value
														and t3.num= t2.num+1)
					) ed
where t.value >@value		
group by ed.datelast
12 май 12, 11:53    [12541242]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Добрый Э - Эх
Guest
Для затравки:
select min(time), max(time)
  from (
         select value, time, 
                row_number() over(partition by case when value >= 70 then 1 else 0 end order by time)-
                row_number() over(order by time) as grp_id
           from test
       )
 where value >= 70
 group by grp_id
12 май 12, 11:54    [12541253]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3059
мой вариант
declare @x table (value int, time datetime)

insert into @x(value, time) values 
(10, '2000-01-01 12:00:00.000'),
(20, '2000-01-01 12:01:00.000'), 
(70, '2000-01-01 12:02:00.000'),
(90, '2000-01-01 12:03:00.000'), 
(90, '2000-01-01 12:04:00.000'),
(90, '2000-01-01 12:05:00.000'),
(70, '2000-01-01 12:06:00.000'),
(20, '2000-01-01 12:07:00.000'),
(20, '2000-01-01 12:08:00.000'),
(20, '2000-01-01 12:09:00.000'),
(20, '2000-01-01 12:10:00.000'),
(70, '2000-01-01 12:11:00.000'),
(70, '2000-01-01 12:12:00.000'),
(80, '2000-01-01 12:13:00.000'),
(20, '2000-01-01 12:14:00.000')


declare @value int
set @value=20;

; with
	cte
	as (
		select
			x.value
			, x.time
			, dateadd(mi, ROW_NUMBER() over (partition by x.value order by x.time desc), x.time) gp
		from
			@x x
	)
select
	c.value
	, min(c.time) begintime
	, max(c.time) endtime
from
	cte c
where
	c.value > @value
group by
	c.value
	, c.gp
12 май 12, 12:01    [12541323]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Добрый Э - Эх,

а как Ваш вариант допилить с учетом комментария ?
12 май 12, 12:11    [12541411]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
HandKot,

не, что-то не то
12 май 12, 12:11    [12541415]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3059
тогда покажите на тестовых данных ожидаемый результат
12 май 12, 12:15    [12541439]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Добрый Э - Эх
Guest
Matroskin
Добрый Э - Эх,

а как Ваш вариант допилить с учетом комментария ?


Какой именно комментарий?


Matroskin
HandKot,

не, что-то не то

У него там магия данных Расчитано все на то, что даты идут строго с интервалом 1 минута. Если интервал будет отличаться от минуты - то результат будет неверным.
12 май 12, 12:16    [12541451]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
HandKot,

при value>60

2000-01-01 12:02:00.000 | 2000-01-01 12:07:00.000
2000-01-01 12:11:00.000 | 2000-01-01 12:14:00.000
2000-01-01 12:15:00.000 | NULL
12 май 12, 12:17    [12541458]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
Добрый Э - Эх,

можно и не минуту, а секунду или час, или год - не суть

из-за ошибки при граничных значениях изменил свой вариант на:

select MIN(a.time) as begintime, a.endtime from (
	select a.value, a.time, MIN(b.time) as endtime from
		(select * from [test] where value>60) a
		left join [test] b on b.time>a.time and b.value<60
	group by a.value, a.time) a
group by a.endtime
12 май 12, 12:20    [12541496]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Matroskin
Добрый Э - Эх,

а как Ваш вариант допилить с учетом комментария ?

я не он, но допилить можно

declare @test table(time datetime,value int)
insert into @test(
	time
	,value
)
values	('20100101',1)
		,('20100102',1)
		,('20100103',2)
		,('20100104',-1)
		,('20100105',-1)
		,('20100106',-1)
		,('20100107',2)
		,('20100108',2)
		,('20100109',-1)
declare @value int 
set @value = 1	

 select value, time, 
                row_number() over(partition by case when value >= @value then 1 else 0 end order by time)-
                row_number() over(order by time) as grp_id
         ,row_number() over(partition by case when value >= @value then 1 else 0 end order by time)        
         ,row_number() over(order by time) as grp_id
           from @test
select min(tt.time), max(isNull(ev.val,tt.time) )
  from (
         select value, time, 
                row_number() over(partition by case when value >= @value then 1 else 0 end order by time)-
                row_number() over(order by time) as grp_id
           from @test
           
       ) as tt
       cross apply (select val=MIN(time) from @test where time >tt.time)ev
 where value >= @value
 group by grp_id	
12 май 12, 12:23    [12541524]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Добрый Э - Эх
Guest
Matroskin
Добрый Э - Эх,

можно и не минуту, а секунду или час, или год - не суть
Как раз суть.
То, что автор привел пример с ровными интервалами, ещё не значит, что так оно и будет.
Ведь может оказаться, что интервал - "плавающий". Те есть данные могут быть одновременно и через минуту, и через две, и через секунду, час, месяц. ;)
12 май 12, 12:28    [12541563]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
HandKot
тогда покажите на тестовых данных ожидаемый результат


+1

Matroskin -> Рекомендации по оформлению сообщений в форуме
12 май 12, 12:29    [12541567]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Добрый Э - Эх
Guest
Мистер Хенки
я не он, но допилить можно


Что же тебя так все усложнять-то тянет?

Как вариант "допила":
with
  x_test as
    (
      select value, time, case when value >= @value then 1 else 0 end as flag,
             row_number() over(partition by case when value >= @value then 1 else 0 end order by time) as rn1,
             row_number() over(order by time) as rn
        from test
    )

select min(t1.time), max(coalesce(t2.time, t1.time))
  from (select * from x_test where flag = 1) t1
  left join
       (select * from x_test where flag = 0) t2
    on t1.rn = t2.rn - 1
group by t1.rn - t1.rn1
12 май 12, 12:46    [12541688]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Добрый Э - Эх
Мистер Хенки
я не он, но допилить можно


Что же тебя так все усложнять-то тянет?

Как вариант "допила":
with
  x_test as
    (
      select value, time, case when value >= @value then 1 else 0 end as flag,
             row_number() over(partition by case when value >= @value then 1 else 0 end order by time) as rn1,
             row_number() over(order by time) as rn
        from test
    )

select min(t1.time), max(coalesce(t2.time, t1.time))
  from (select * from x_test where flag = 1) t1
  left join
       (select * from x_test where flag = 0) t2
    on t1.rn = t2.rn - 1
group by t1.rn - t1.rn1


и так и так придется два раза сканировать test
12 май 12, 13:00    [12541785]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Мистер Хенки
Добрый Э - Эх
пропущено...


Что же тебя так все усложнять-то тянет?

Как вариант "допила":
with
  x_test as
    (
      select value, time, case when value >= @value then 1 else 0 end as flag,
             row_number() over(partition by case when value >= @value then 1 else 0 end order by time) as rn1,
             row_number() over(order by time) as rn
        from test
    )

select min(t1.time), max(coalesce(t2.time, t1.time))
  from (select * from x_test where flag = 1) t1
  left join
       (select * from x_test where flag = 0) t2
    on t1.rn = t2.rn - 1
group by t1.rn - t1.rn1


и так и так придется два раза сканировать test

в моем случае 1 раз без лишних сортировок.
12 май 12, 13:03    [12541798]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Добрый Э - Эх
Guest
Мистер Хенки
в моем случае 1 раз без лишних сортировок.
Пересмотрел все твои варианты. Не увидел ни одного однопроходного...
12 май 12, 15:27    [12542902]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Добрый Э - Эх
Мистер Хенки
в моем случае 1 раз без лишних сортировок.
Пересмотрел все твои варианты. Не увидел ни одного однопроходного...

да, они не однопроходные. просто вот это
+

declare @test table(time datetime,value int)
insert into @test(
	time
	,value
)
values	('20100101',1)
		,('20100102',1)
		,('20100103',2)
		,('20100104',-1)
		,('20100105',-1)
		,('20100106',-1)
		,('20100107',2)
		,('20100108',2)
		,('20100109',-1)
declare @value int 
set @value = 1	

 
select min(tt.time), max(isNull(ev.val,tt.time) )
  from (
         select value, time, 
                row_number() over(partition by case when value >= @value then 1 else 0 end order by time)-
                row_number() over(order by time) as grp_id
           from @test
           
       ) as tt
       cross apply (select val=MIN(time) from @test where time >tt.time)ev
 where value >= @value
 group by grp_id


Не дает в плане еще одной ненужной сортировки
в отличии от
+

with
  x_test as
    (
      select value, time, case when value >= @value then 1 else 0 end as flag,
             row_number() over(partition by case when value >= @value then 1 else 0 end order by time) as rn1,
             row_number() over(order by time) as rn
        from test
    )

select min(t1.time), max(coalesce(t2.time, t1.time))
  from (select * from x_test where flag = 1) t1
  left join
       (select * from x_test where flag = 0) t2
    on t1.rn = t2.rn - 1
group by t1.rn - t1.rn1

12 май 12, 15:43    [12543029]     Ответить | Цитировать Сообщить модератору
 Re: Как еще можно сделать ?  [new]
Добрый Э - Эх
Guest
Мистер Хенки, план запроса - далеко не истина в последней инстанции.
Нужно смотреть на статистику выполнения. Лично у меня есть устойчивое убеждение, что CROSS APPLY - далеко не самая быстрая конструкция в T-SQL.

Было бы неплохо, если бы автор прогнал варианты на своих данных и привел статистику выполнения обоих вариантов запроса...
12 май 12, 19:42    [12544231]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить