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

Откуда:
Сообщений: 2
Добрый день!
Есть таблица:
Дата Событие
01.01.2017 01:20:00 1
01.01.2017 02:15:00 2
01.01.2017 03:10:00 1
01.01.2017 03:50:00 2
01.01.2017 04:15:00 1
01.01.2017 04:40:00 2

События 1 и 2 следуют друг за другом. Необходимо получить такой результат:
Дата начало Дата конец
01.01.2017 01:20:00 01.01.2017 02:15:00
01.01.2017 03:10:00 01.01.2017 03:50:00
01.01.2017 04:15:00 01.01.2017 04:40:00

Как лучше сделать?
28 фев 17, 14:12    [20250927]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Vladimir1986,

автор
Как лучше сделать?

Наклонив голову.
И использовать поиск, тема раз в неделю
28 фев 17, 14:19    [20250958]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK,
вы хоть ключевое слово давайте, а то ж не конструктивно...


Vladimir1986,
ключевое слово PIVOT
28 фев 17, 14:21    [20250966]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

Откуда:
Сообщений: 5758
Vladimir1986
Добрый день!
Есть таблица:
Дата Событие
01.01.2017 01:20:00 1
01.01.2017 02:15:00 2
01.01.2017 03:10:00 1
01.01.2017 03:50:00 2
01.01.2017 04:15:00 1
01.01.2017 04:40:00 2

События 1 и 2 следуют друг за другом. Необходимо получить такой результат:
Дата начало Дата конец
01.01.2017 01:20:00 01.01.2017 02:15:00
01.01.2017 03:10:00 01.01.2017 03:50:00
01.01.2017 04:15:00 01.01.2017 04:40:00

Как лучше сделать?


+ так пойдет?


;with dt as 
	(select '20120101 14:00:00' [d], 1 [s] union all
	select '20120101 15:10:00' [d], 2 [s] union all
	select '20120101 15:20:00' [d], 1 [s] union all
	select '20120101 16:05:00' [d], 2 [s] union all
	select '20120101 16:15:00' [d], 1 [s] union all
	select '20120101 17:01:00' [d], 2 [s] union all
	select '20120101 17:02:00' [d], 1 [s] union all
	select '20120101 17:03:00' [d], 2 [s]
	)
,new as 
	( select 
		ROW_NUMBER() over (order by d asc,s asc) [n]
		,d
		,s
	from dt 
	)
select 
	t1.s
	,t1.d
	,t2.d
	,t2.s
from new t1
join new t2 on t2.n=t1.n+1
and t1.s=1



28 фев 17, 14:26    [20250986]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Vladimir1986
Member

Откуда:
Сообщений: 2
komrad, спасибо я думаю такой вариант подойдет
Шыфл, PIVOT здесь не очень
TaPaK, тоже спасибо)) но не знаю как правильно сформулировать задачу, чтобы найти её решение
28 фев 17, 14:34    [20251015]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
uaggster
Member

Откуда:
Сообщений: 1068
komrad, народ, ну что вы как в прошлом веке, ей богу!
Самосоединение, да еще по синтезированному полю. Ой-вэй!

set dateformat dmy;

Declare @t table (a datetime, b int)

Insert into @t values
('01.01.2017 01:20:00',	1),
('01.01.2017 02:15:00',	2),
('01.01.2017 03:10:00',	1),
('01.01.2017 03:50:00',	2),
('01.01.2017 04:15:00',	1),
('01.01.2017 04:40:00',	2)

;With t as (
    Select a, b, lead(a) over (order by a asc, b asc) aa
    from @t)
Select a, aa from t
Where b=1
2 мар 17, 08:04    [20256143]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

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

не, я не претендую на оптимальность в данном случае, да и код спрятал в спойлер ;)

Однако, вот вывод set statistics time/io моего скрипта

+ мой
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(4 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


а вот твоего:
+ твой
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table '#A2098F8A'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

(6 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#A2098F8A'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 8 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


Имхо, мой быстрее ;)
2 мар 17, 11:58    [20256908]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
komrad,

все кто так говорит
автор
народ, ну что вы как в прошлом веке, ей богу!

потом сидят и говорят - скрипт рабочий, сервер плохой :)
2 мар 17, 12:21    [20257010]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
komrad: "Имхо, мой быстрее ;)"

Мне стало интересно, действительно ли?

Составим пакет из создания и заполнения временной таблицы, и запросов uaggster и komrad. Запрос komradа немного изменим, чтобы он брал данные из временной таблицы.

Получилось в пакете (Query cost (relative to the batch)) 18% - создание и заполнение временной таблицы, 24% - запрос uaggster, 59% - запрос komrad.

Результат вполне ожидаемый.

+
set dateformat dmy;

Declare @t table (a datetime, b int)

Insert into @t values
('01.01.2017 01:20:00',	1),
('01.01.2017 02:15:00',	2),
('01.01.2017 03:10:00',	1),
('01.01.2017 03:50:00',	2),
('01.01.2017 04:15:00',	1),
('01.01.2017 04:40:00',	2)

;With t as (
    Select a, b, lead(a) over (order by a asc, b asc) aa
    from @t)
Select a, aa from t
Where b=1

;with --dt as 
--	(select '20120101 14:00:00' [d], 1 [s] union all
--	select '20120101 15:10:00' [d], 2 [s] union all
--	select '20120101 15:20:00' [d], 1 [s] union all
--	select '20120101 16:05:00' [d], 2 [s] union all
--	select '20120101 16:15:00' [d], 1 [s] union all
--	select '20120101 17:01:00' [d], 2 [s] union all
--	select '20120101 17:02:00' [d], 1 [s] union all
--	select '20120101 17:03:00' [d], 2 [s]
--	)
--,
new as 
	( select 
		ROW_NUMBER() over (order by a asc,b asc) [n]
		,a
		,b
	from @t 
	)
select 
	t1.b
	,t1.a
	,t2.a
	,t2.b
from new t1
join new t2 on t2.n=t1.n+1
and t1.b=1
2 мар 17, 13:10    [20257263]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
o-o
Guest
пора для себя открыть вранье оценок в попугаях.
и уж если тестить на данных, которые умещаются на 1 страницу,
то как минимум смотреть, сколько раз к этой странице обращались
2 мар 17, 13:16    [20257304]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
o-o,

Открыл, везде одно вранье!
2 мар 17, 13:22    [20257342]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

Откуда:
Сообщений: 5758
Wlr-l
komrad: "Имхо, мой быстрее ;)"

Мне стало интересно, действительно ли?

там смайлик был, и не один
нет смысла тестировать что-то, что отличается по времени выполния на миллисекунды, если только это не будет использоваться в нагруженных системах с миллионами-миллиардами вызовов


Wlr-l
Составим пакет из создания и заполнения временной таблицы, и запросов uaggster и komrad. Запрос komradа немного изменим, чтобы он брал данные из временной таблицы.

дьявол кроется в деталях

Wlr-l
Получилось в пакете (Query cost (relative to the batch)) 18% - создание и заполнение временной таблицы, 24% - запрос uaggster, 59% - запрос komrad.

получается сделан вывод, что "не совсем запрос комрада работает медленнее"
самому не смешно ? ;)
2 мар 17, 13:24    [20257358]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
o-o
Guest
Wlr-l
o-o,

Открыл, везде одно вранье!

ну, значит ничего не открыл: временами оценочное совпадает с реальным.
да и зачем вообще передо мной отчитываться, мне все равно, кто во что верит.
я просто не люблю, когда фигню выдают за истину, вот тогда и встреваю
2 мар 17, 13:25    [20257366]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
Главное, чтобы потом не сказали, что процессор в сервере не тот!
2 мар 17, 13:28    [20257384]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
uaggster
народ, ну что вы как в прошлом веке, ей богу!
Самосоединение, да еще по синтезированному полю. Ой-вэй!
+ Наслаждайтесь
declare @t table (a datetime, b int)

insert into @t values
('20170101 01:20:00',	1),
('20170101 02:15:00',	2),
('20170101 03:10:00',	1),
('20170101 03:50:00',	2),
('20170101 04:15:00',	1),
('20170101 04:40:00',	2);

if object_id('tempdb..#t', 'U') is not null
 drop table #t;

create table #t (a datetime, b int);

insert into #t
select
 dateadd(day, n.n, t.a), t.b
from
 @t t cross join
 (
  select top (100000)
   row_number() over (order by (select 1)) as n
  from
   master.dbo.spt_values a cross join
   master.dbo.spt_values b
 ) n;

create index IX_#t__1 on #t (a, b);
create index IX_#t__2 on #t (b, a);
go

declare @a1 datetime, @a2 datetime;

set statistics xml, time, io on;

with t as (
    Select a, b, lead(a) over (order by a asc, b asc) aa
    from #t)
Select @a1 = a, @a2 = aa from t
Where b=1
option
 (maxdop 1);

with new as 
	( select 
		ROW_NUMBER() over (order by a asc,b asc) [n]
		,a
		,b
	from #t 
	)
select 
	@a1 = t1.a
	,@a2 = t2.a
from new t1
join new t2 on t2.n=t1.n+1
and t1.b=1
option
 (maxdop 1);

select
 @a1 = t1.a, @a2 = t2.a
from
 #t t1 cross apply
 (select top (1) a from #t where b = 2 and a > t1.a order by a) t2
where
 t1.b = 1
option
 (maxdop 1);

set statistics xml, time, io off;

/*
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________000000000445'. Scan count 1, logical reads 1944, physical reads 0, read-ahead reads 59, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1061 ms,  elapsed time = 1079 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________000000000445'. Scan count 2, logical reads 3888, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 592 ms,  elapsed time = 913 ms.
Table '#t__________________________________________________________________________________________________________________000000000445'. Scan count 300001, logical reads 958215, physical reads 0, read-ahead reads 12, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 687 ms,  elapsed time = 725 ms.
*/
2 мар 17, 13:52    [20257492]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
komrad
Member

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

респект :)
2 мар 17, 13:56    [20257504]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

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

ну вообщем самым оптимальным является вариант с ROW_NUMBER()
2 мар 17, 14:05    [20257539]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
o-o
Guest
главное, не предлагать процессор с сервера invm товарищу WIr-I
2 мар 17, 14:08    [20257548]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
TaPaK
ну вообщем самым оптимальным является вариант с ROW_NUMBER()
По IO или по elapsed time? :)
2 мар 17, 14:11    [20257565]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
invm
TaPaK
ну вообщем самым оптимальным является вариант с ROW_NUMBER()
По IO или по elapsed time? :)


в среднем по больнице :)

кстати у меня на 3х разных серверах получается всегда немного другая картина, кроме чтений
+

SQL Server Execution Times:
CPU time = 921 ms, elapsed time = 928 ms.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t__________________________________________________________________________________________________________________000000000052'. Scan count 2, logical reads 3882, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 594 ms, elapsed time = 581 ms.
Table '#t__________________________________________________________________________________________________________________000000000052'. Scan count 300001, logical reads 958188, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 813 ms, elapsed time = 822 ms.
2 мар 17, 14:15    [20257579]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
TaPaK
кстати у меня на 3х разных серверах получается всегда немного другая картина, кроме чтений
Это из-за set statistics xml. Нужно убрать.
2 мар 17, 14:29    [20257632]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
invm
TaPaK
кстати у меня на 3х разных серверах получается всегда немного другая картина, кроме чтений
Это из-за set statistics xml. Нужно убрать.

первое что убирал :)

set statistics time on;

SQL Server Execution Times:
CPU time = 968 ms, elapsed time = 968 ms.

SQL Server Execution Times:
CPU time = 594 ms, elapsed time = 589 ms.

SQL Server Execution Times:
CPU time = 828 ms, elapsed time = 838 ms.
2 мар 17, 14:35    [20257654]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
TaPaK,
Если убрать ограничение
option
 (maxdop 1)


То тертий вариант эффективно распаралеливается, второй плохо распаралеливается, а первый - так вообще никак :)
+ maxdop

maxdop 1

SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 675 ms.

SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 464 ms.

SQL Server Execution Times:
CPU time = 532 ms, elapsed time = 530 ms.

no maxdop

SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 670 ms.

SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 449 ms.

SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 146 ms.


2 мар 17, 15:10    [20257756]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Шыфл,

такие смешные, типа распараллеливание это хорошо :) а миллион ридов это даже лучше
2 мар 17, 15:23    [20257804]     Ответить | Цитировать Сообщить модератору
 Re: задача повернуть таблицу  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
TaPaK
а миллион ридов это даже лучше
Иногда да.
2 мар 17, 15:30    [20257825]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить