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

Откуда: Гомель-Минск
Сообщений: 474
Добрый день,

Прощу помощи с решеним следующей SQL задачи:
Есть данные:
[DATE],                          [MODE]
2013-03-30 03:19:17.0000000     MODE1
2013-03-30 03:19:48.0000000     MODE1
2013-03-30 03:20:21.0000000     MODE2
2013-03-30 03:20:56.0000000     MODE2
2013-03-30 03:21:24.0000000     MODE2
2013-03-30 03:21:57.0000000     MODE1
2013-03-30 03:22:59.0000000     MODE1
2013-03-30 03:23:28.0000000     MODE1
2013-03-30 03:24:00.0000000     MODE3
2013-03-30 03:24:30.0000000     MODE2
2013-03-30 03:25:02.0000000     MODE2

Сортировка по дате, каждому таймстемпу соотвествует один некоторый "режим".
Необходимо посчитать количество этих самых режимов в группе, учитывая последжовательность дат, т.е.
на выходе я ожидаю такое:
[DATE]                         [MODE]     CntInGroup
2013-03-30 03:19:17.0000000     MODE1        2
2013-03-30 03:19:48.0000000     MODE1        2
2013-03-30 03:20:21.0000000     MODE2        3
2013-03-30 03:20:56.0000000     MODE2        3 
2013-03-30 03:21:24.0000000     MODE2        3
2013-03-30 03:21:57.0000000     MODE1        3
2013-03-30 03:22:59.0000000     MODE1        3
2013-03-30 03:23:28.0000000     MODE1        3
2013-03-30 03:24:00.0000000     MODE3        1
2013-03-30 03:24:30.0000000     MODE2        2
2013-03-30 03:25:02.0000000     MODE2        2 

SQL 2012, без использования курсора разумеется.
3 июн 13, 13:32    [14383624]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
в данном случае я бы попробовал CTE

хотя может в 2012 есть для этого более удобные вещи
3 июн 13, 13:50    [14383736]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Fire83
без использования курсора разумеется.

так может с курсором быстрее всего выйдет.
3 июн 13, 13:54    [14383763]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Гость333
Member

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

declare @tab table ([DATE] datetime2, [MODE] varchar(50));

insert @tab ([DATE], [MODE])
select '2013-03-30 03:19:17.0000000', 'MODE1' union all
select '2013-03-30 03:19:48.0000000', 'MODE1' union all
select '2013-03-30 03:20:21.0000000', 'MODE2' union all
select '2013-03-30 03:20:56.0000000', 'MODE2' union all
select '2013-03-30 03:21:24.0000000', 'MODE2' union all
select '2013-03-30 03:21:57.0000000', 'MODE1' union all
select '2013-03-30 03:22:59.0000000', 'MODE1' union all
select '2013-03-30 03:23:28.0000000', 'MODE1' union all
select '2013-03-30 03:24:00.0000000', 'MODE3' union all
select '2013-03-30 03:24:30.0000000', 'MODE2' union all
select '2013-03-30 03:25:02.0000000', 'MODE2';

select x.[DATE], x.[MODE], count(*) over(partition by x.XXX) as counter
from
(
   select t.*, row_number() over(order by t.[DATE]) - row_number() over(partition by t.[MODE] order by t.[DATE]) as xxx
   from @tab t
) x
order by x.[DATE];


Правда, это для 2008R2, а для 2012 можно бы подумать над использованием функции lag.
3 июн 13, 13:56    [14383779]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
chromatograph
Member

Откуда:
Сообщений: 33
+

Гость333
Fire83,

declare @tab table ([DATE] datetime2, [MODE] varchar(50));

insert @tab ([DATE], [MODE])
select '2013-03-30 03:19:17.0000000', 'MODE1' union all
select '2013-03-30 03:19:48.0000000', 'MODE1' union all
select '2013-03-30 03:20:21.0000000', 'MODE2' union all
select '2013-03-30 03:20:56.0000000', 'MODE2' union all
select '2013-03-30 03:21:24.0000000', 'MODE2' union all
select '2013-03-30 03:21:57.0000000', 'MODE1' union all
select '2013-03-30 03:22:59.0000000', 'MODE1' union all
select '2013-03-30 03:23:28.0000000', 'MODE1' union all
select '2013-03-30 03:24:00.0000000', 'MODE3' union all
select '2013-03-30 03:24:30.0000000', 'MODE2' union all
select '2013-03-30 03:25:02.0000000', 'MODE2';

select x.[DATE], x.[MODE], count(*) over(partition by x.XXX) as counter
from
(
   select t.*, row_number() over(order by t.[DATE]) - row_number() over(partition by t.[MODE] order by t.[DATE]) as xxx
   from @tab t
) x
order by x.[DATE];


Правда, это для 2008R2, а для 2012 можно бы подумать над использованием функции lag.


Охренеть, и ведь работает!
3 июн 13, 14:31    [14383969]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Гость333
Правда, это для 2008R2, а для 2012 можно бы подумать над использованием функции lag.
Вот, Mnior как-то писал: вычислить промежуток времени работы помогите очень надо

По-моему, что-то похожее предлагалось уже не раз.
Поискать надо.
3 июн 13, 14:37    [14384018]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Или вот:
Индексирование СМЕНЫ значений поля в заданном порядке
3 июн 13, 14:41    [14384056]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

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

для 2000:

declare @tab table ([DATE] datetime2, [MODE] varchar(50));

insert @tab ([DATE], [MODE])
select '2013-03-30 03:19:17.0000000', 'MODE1' union all
select '2013-03-30 03:19:48.0000000', 'MODE1' union all
select '2013-03-30 03:20:21.0000000', 'MODE2' union all
select '2013-03-30 03:20:56.0000000', 'MODE2' union all
select '2013-03-30 03:21:24.0000000', 'MODE2' union all
select '2013-03-30 03:21:57.0000000', 'MODE1' union all
select '2013-03-30 03:22:59.0000000', 'MODE1' union all
select '2013-03-30 03:23:28.0000000', 'MODE1' union all
select '2013-03-30 03:24:00.0000000', 'MODE3' union all
select '2013-03-30 03:24:30.0000000', 'MODE2' union all
select '2013-03-30 03:25:02.0000000', 'MODE2';

select t1.[date], t1.[mode],
	(select count(*)
	 from @tab t3
	 where t3.DATE < 
		isnull(
			(select MIN([date])
			 from @tab t2
			 where t2.MODE <> t1.MODE
				and t2.DATE >= t1.date)
			,(select dateadd(DAY, 1, max([date])) from @tab))
		and t3.DATE > isnull((select max(t4.DATE) from @tab t4 where t4.DATE < t1.DATE and t1.MODE <> t4.MODE), (select dateadd(DAY, -1, min([date])) from @tab))	
	)
from @tab t1;
3 июн 13, 16:07    [14384704]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Гость333
Member

Откуда:
Сообщений: 3683
super-code
Fire83,

для 2000:

Это печально, лучше уж через курсор.
3 июн 13, 16:09    [14384725]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
Гость333,

А что печального, план выполнения?
3 июн 13, 16:38    [14384934]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
Гость333, просто хотелось бы конкретики, для общего развития, мне кажется, что с курсором не будет быстрее точно. Давай ты напишешь с курсором, и проверим скорость на тестовых записях?
3 июн 13, 16:39    [14384945]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
И ещё, есть в T-SQL, какие-то константы для минимального и максимального значения типа datetime, datetime2, date... ?
Чтобы заменить вот это "select dateadd(DAY, 1, max([date])) from @tab" на константу.
3 июн 13, 16:42    [14384972]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Вы точно знаете значение слова "константа" ?
3 июн 13, 17:06    [14385124]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

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

Да, знаю. Мне нужна максимальная дата, которая поместиться в datetime2, и вызовет переполнение. Она всегда одинаковая, и даже написана в MSDN, просто не хочется вводить число. Хочется написать название и получить это число. В C# выглядело вот так DateTime.MaxValue, к примеру. По t-sql похоже не нашел. Нет такого?
3 июн 13, 17:14    [14385193]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
Прошу прощения. "вызовет переполнение" - "НЕ вызовет переполнение".
3 июн 13, 17:14    [14385201]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Glory
Member

Откуда:
Сообщений: 104751
super-code
В C# выглядело вот так DateTime.MaxValue, к примеру. По t-sql похоже не нашел. Нет такого?

Нет.
3 июн 13, 17:16    [14385221]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Гость333
Member

Откуда:
Сообщений: 3683
super-code
Гость333, просто хотелось бы конкретики, для общего развития, мне кажется, что с курсором не будет быстрее точно. Давай ты напишешь с курсором

Вот с курсором:
declare @tab table ([DATE] datetime2, [MODE] varchar(50));

insert @tab ([DATE], [MODE])
select '2013-03-30 03:19:17.0000000', 'MODE1' union all
select '2013-03-30 03:19:48.0000000', 'MODE1' union all
select '2013-03-30 03:20:21.0000000', 'MODE2' union all
select '2013-03-30 03:20:56.0000000', 'MODE2' union all
select '2013-03-30 03:21:24.0000000', 'MODE2' union all
select '2013-03-30 03:21:57.0000000', 'MODE1' union all
select '2013-03-30 03:22:59.0000000', 'MODE1' union all
select '2013-03-30 03:23:28.0000000', 'MODE1' union all
select '2013-03-30 03:24:00.0000000', 'MODE3' union all
select '2013-03-30 03:24:30.0000000', 'MODE2' union all
select '2013-03-30 03:25:02.0000000', 'MODE2';

declare @accum table ([DATE] datetime2, [MODE] varchar(50));
declare @result table ([DATE] datetime2, [MODE] varchar(50), COUNTER int);

declare @date datetime2, @mode varchar(50), @old_mode varchar(50), @counter int;

set @counter = 1;

declare cur cursor local fast_forward for
   select t.[DATE], t.[MODE]
   from @tab t
   order by t.[DATE];

open cur;

fetch next from cur into @date, @mode;

while @@fetch_status = 0
begin
   if @mode = @old_mode
   begin
      set @counter = @counter + 1;
   end
   else
   begin
      insert @result ([DATE], [MODE], COUNTER)
      select a.[DATE], a.[MODE], @counter
      from @accum a;
      
      delete @accum;
      
      select @counter = 1, @old_mode = @mode;
   end;

   insert @accum ([DATE], [MODE]) values(@date, @mode);
   
   fetch next from cur into @date, @mode;
end;

insert @result ([DATE], [MODE], COUNTER)
select a.[DATE], a.[MODE], @counter
from @accum a;

close cur;

deallocate cur;

select * from @result r order by r.[DATE];

Естественно, должен быть индекс по полю DATE.

super-code
и проверим скорость на тестовых записях?

Создавайте тестовые записи, проверяйте
3 июн 13, 17:26    [14385302]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
zozo12345
Guest
Гость333,

данные
create table t([DATE] datetime2, [MODE] varchar(50));

insert into t([date], [mode])
select dateadd(SECOND, a.id, '19950101'), CAST(rand()*3 AS NUMERIC(1, 0))
from sysobjects a cross join sysobjects b cross join sysobjects c

create index idx_t_date on t([date], [mode])


решение с row_number - 48 с - 52 с (с индексом/без индекса)
курсор - 3 мин 4 сек. (с индексом)
3 июн 13, 17:56    [14385515]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
ящящ1234567890
Guest
решение super-code с индексом - дождался до 3 мин 30 сек дальше ждать не стал. оно и понятно.
3 июн 13, 18:01    [14385539]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
ящящ1234567890,

set nocount on

declare @date1 datetime
declare @date2 datetime

print @@VERSION

if object_id('tempdb..#tab') is not null
	drop table #tab

create table #tab ([DATE] datetime2, [MODE] varchar(50));

declare @i int = 1000

WHILE @i > 0
BEGIN
	insert #tab ([DATE], [MODE])
	select dateadd(DAY, @i, CAST('2013-03-30 03:19:17.0000000' as datetime2)), 'MODE1' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:19:48.0000000' as datetime2)), 'MODE1' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:20:21.0000000' as datetime2)), 'MODE2' union all
	select dateadd(DAY, 1, CAST('2013-03-30 03:20:56.0000000' as datetime2)), 'MODE2' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:21:24.0000000' as datetime2)), 'MODE2' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:21:57.0000000' as datetime2)), 'MODE1' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:22:59.0000000' as datetime2)), 'MODE1' union all
	select dateadd(DAY, 1, CAST('2013-03-30 03:23:28.0000000' as datetime2)), 'MODE1' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:24:00.0000000' as datetime2)), 'MODE3' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:24:30.0000000' as datetime2)), 'MODE2' union all
	select dateadd(DAY, @i, CAST('2013-03-30 03:25:02.0000000' as datetime2)), 'MODE2';

	set @i = @i - 1
END

CREATE INDEX #IDX1 ON #tab ([date],[MODE]) --include ([mode])

-- super select
set @date1 = getutcdate()

select x.[DATE], x.[MODE], count(*) over(partition by x.XXX) as counter
from
(
   select t.*, row_number() over(order by t.[DATE]) - row_number() over(partition by t.[MODE] order by t.[DATE]) as xxx
   from #tab t
) x
order by x.[DATE];

set @date2 = getutcdate()

print 'select3:'+CAST(datediff(MILLISECOND, @date1,@date2) as nvarchar(1000))

-- 1 (select)
set @date1 = getutcdate()

declare @minDate datetime = (select dateadd(DAY, -1, min([date])) from #tab)
declare @maxDate datetime = (select dateadd(DAY, 1, max([date])) from #tab)

select t1.[date], t1.[mode], t6.value
from #tab t1
cross apply	(select	isnull(
			(select MIN([date])
			 from #tab t2
			 where t2.MODE <> t1.MODE
				and t2.DATE >= t1.date)
			,(select dateadd(DAY, 1, max([date])) from #tab)) as [value]) t8
cross apply (select isnull((select max(t4.DATE) from #tab t4 where t4.DATE < t1.DATE and t1.MODE <> t4.MODE), @minDate) as [value]) t7
cross apply (select count(*) as [value] from #tab t3 where t3.DATE < t8.value and t3.DATE > t7.value) t6

set @date2 = getutcdate()

print 'select1:'+CAST(datediff(MILLISECOND, @date1,@date2) as nvarchar(1000))

DROP INDEX #tab.#IDX1

CREATE INDEX #IDX2 ON #tab ([date])

-- 2 (cursor)
set @date1 = getutcdate()

declare @accum table ([DATE] datetime2, [MODE] varchar(50));
declare @result table ([DATE] datetime2, [MODE] varchar(50), COUNTER int);

declare @date datetime2, @mode varchar(50), @old_mode varchar(50), @counter int;

set @counter = 1;

declare cur cursor local fast_forward for
   select t.[DATE], t.[MODE]
   from #tab t
   order by t.[DATE];

open cur;

fetch next from cur into @date, @mode;

while @@fetch_status = 0
begin
   if @mode = @old_mode
   begin
      set @counter = @counter + 1;
   end
   else
   begin
      insert @result ([DATE], [MODE], COUNTER)
      select a.[DATE], a.[MODE], @counter
      from @accum a;
      
      delete @accum;
      
      select @counter = 1, @old_mode = @mode;
   end;

   insert @accum ([DATE], [MODE]) values(@date, @mode);
   
   fetch next from cur into @date, @mode;
end;

insert @result ([DATE], [MODE], COUNTER)
select a.[DATE], a.[MODE], @counter
from @accum a;

close cur;

deallocate cur;

select * from @result r order by r.[DATE];

set @date2 = getutcdate()

print 'select2:'+CAST(datediff(MILLISECOND, @date1,@date2) as nvarchar(1000))


select3:163
select1:796
select2:1810 (cursor)
3 июн 13, 18:09    [14385591]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Гость333
Member

Откуда:
Сообщений: 3683
zozo12345
CAST(rand()*3 AS NUMERIC(1, 0))

В итоге во всех строках получилось одинаковое значение. Чтобы были разные — можно использовать, например, newid().

zozo12345
решение с row_number - 48 с - 52 с (с индексом/без индекса)

Причём данные получаются неправильные, т.к. запрос предполагает уникальность поля DATE. Без уникального поля (или уникального набора полей) такой подход не работает.

zozo12345
курсор - 3 мин 4 сек. (с индексом)

Так, на всякий случай — set nocount on поставили?
3 июн 13, 18:11    [14385600]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
super-code,

версия:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

select3:146
select1:730
select2:1813
3 июн 13, 18:12    [14385604]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
super-code, О, извиняюсь в 2000 не было CROSS APPLY, если оставить мой запрос без изменений, то курсор быстрее!
3 июн 13, 18:17    [14385638]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
Гость333
Member

Откуда:
Сообщений: 3683
super-code
select3:163
select1:796
select2:1810 (cursor)

Поставил @i=10000, и результат уже такой:
select3:1633
select1:25493
select2:8740 (cursor)
3 июн 13, 18:18    [14385640]     Ответить | Цитировать Сообщить модератору
 Re: SQL задача  [new]
super-code
Member

Откуда:
Сообщений: 244
Гость333,

ок, верю! Не так страшен курсор, как про него рассказывают.
4 июн 13, 17:03    [14390370]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить