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

исходная: source (idWork, countWork, day01, day02, … day99) PK is idWork

целевая: target (idWork, numday, stateday) PK is (idWork, numday)

нужно, чтобы в целевой для каждого idWork:
- получилось source.countWork записей;
- при этом значения target.numday имели целое значение от нуля до (source.countWork - 1);
- значения target.stateday были равны соответственно:

target.stateday = source.day_01 для target.numday = 0,
target.stateday = source.day_02 для target.numday = 1,

target.stateday = source.day_N для target.numday = (N – 1),
всего source.countWork записей.

не хватает знаний, может быть кто-то знает ?
3 апр 16, 13:38    [19010895]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
with n(n) as
(
 select 1
 union all
 select n + 1 from n where n <= 99
)
select
 s.idWork, n.n - 1 as numday,
 case n.n
  when 1 then s.day01
  when 2 then s.day02
  ...
  when 99 then s.day99
 end as stateday
from
 source s join
 n on n.n <= s.countWork;
3 апр 16, 15:32    [19011046]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
greenSpaider
Guest
invm,
ещё не понял, как работает, попробую разобраться.
3 апр 16, 16:42    [19011175]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
greenSpaider,

Просто и красиво.
with n(n)
генерирует таблицу n с колонкой n со значениями от 1 до 99

join к каждой записи из таблицы sources присоединяет все записи из таблицы n, где значения n <=sources.countWork
case в зависимости от значения n возвращает значение соответствующей колонки.

Вот еще вариант, чуть покороче будет:
select 
idWork
, cast(right(clmnName, 2) as int)-1 as numdays --откусываем номер колонки, конвертируем в int, вычитаем 1.
,statedays
from source s 
unpivot (
statedays for clmnName in ([day01], [day02], [day03], ..., [day99])
) unpvt
4 апр 16, 13:33    [19014205]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Eugene_p1

Вот еще вариант, чуть покороче будет:

Не учел условие про countWork. Вот прямо с проверочной частью:

declare @source as table (idWork int, countWork int, day01 float, day02 float, day03 float)
insert into @source values(1, 2, 10,11,null)
insert into @source values(2, 1, 1,null,null)
insert into @source values(3, 3, 100,200,300)

select idWork
, right(clmnName, 2)-1 as numdays
,statedays
from @source s 
unpivot (
statedays for clmnName in ([day01], [day02], [day03])
) unpvt
where right(clmnName, 2)-1 < countWork
4 апр 16, 13:36    [19014215]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Кстати, мой вариант примерно в 3 раза быстрее. :))
4 апр 16, 13:39    [19014230]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
greenSpaider
Guest
Eugene_p1, вот спасибо.
не знал про конструкцию with n(n). Я в подобных случаях создавал таблицы с целыми значениями, но, оказывается, существует WITH <common_table_expression>.
4 апр 16, 14:47    [19014691]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
iljy
Member

Откуда:
Сообщений: 8711
greenSpaider
Eugene_p1, вот спасибо.
не знал про конструкцию with n(n). Я в подобных случаях создавал таблицы с целыми значениями, но, оказывается, существует WITH <common_table_expression>.


Таблицы с целыми значениями работают намного быстрее рекурсивного СТЕ, так что в базе есть смысл такую таблицу иметь.
4 апр 16, 15:05    [19014836]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Adx
Guest
Можно воспользоваться динаскулем )
Чтоб не писать в коде:

case ...
when 1 then s.day01
when 2 then s.day02
...
when 99 then s.day99

А если столбцов там 365? если 1000?
4 апр 16, 16:30    [19015381]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Adx
Можно воспользоваться динаскулем )
Чтоб не писать в коде:

case ...
when 1 then s.day01
when 2 then s.day02
...
when 99 then s.day99

А если столбцов там 365? если 1000?
И кто же будет смотреть эту тысячу столбцов?
4 апр 16, 16:38    [19015448]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
greenSpaider
Eugene_p1, вот спасибо.
не знал про конструкцию with n(n). Я в подобных случаях создавал таблицы с целыми значениями, но, оказывается, существует WITH <common_table_expression>.

Её с осторожностью применять нужно, в данном случае быстрее работает UNPIVOT.
4 апр 16, 16:48    [19015501]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
iljy
Member

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

вообще самым быстрым тут будет вот такой вариант:
declare @t table (id int, cnt int, day1 int, day2 int, day3 int)

insert @t values (1, 2, 2, 3, 4), (2, 3, 1, 2, 3), (3, 1, 6, 7, 8)

select id, cnt, d
from @t outer apply (
	select top(cnt) d from (
		select day1 union all
		select day2 union all
		select day3
	) t(d)
) o
4 апр 16, 17:08    [19015624]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
iljy
greenSpaider,

вообще самым быстрым тут будет вот такой вариант:
declare @t table (id int, cnt int, day1 int, day2 int, day3 int)

insert @t values (1, 2, 2, 3, 4), (2, 3, 1, 2, 3), (3, 1, 6, 7, 8)

select id, cnt, d
from @t outer apply (
	select top(cnt) d from (
		select day1 union all
		select day2 union all
		select day3
	) t(d)
) o

По-моему, он выдает неверный результат. Что касается быстродействия, то большой разницы с UNPIVOT я не заметил, по крайней мере на этом примере.
Переделал код под наши колонки:

select idWork, CountWork, stateday
from @source 
outer apply (
			select top(CountWork) stateday 
			from (
				select day01 from @source
				union all
				select day02 from @source
				union all
				select day03 from @source
				) t(stateday)
			) o

Результат:
idWork CountWork stateday
1 2 10
1 2 11
2 1 1
3 2 100
3 2 200


В предыдущих 2 случаях результат:
idWork numdays stateday
1 0 10
1 1 11
2 0 1
3 0 100
3 1 200


То есть в сути там классический UnPivot:
idWork clmnName numdays statedays
1 day01 0 10
1 day02 1 11
2 day01 0 1
3 day01 0 100
3 day02 1 200

(из номера дня вычитается 1.)
5 апр 16, 18:53    [19020382]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
iljy
Member

Откуда:
Сообщений: 8711
Eugene_p1
По-моему, он выдает неверный результат.


.... неверный потому, что вместо номера дня просто количество выдает? Вы серьезно???? Добавьте номер во внутренние селекты.

А с UNPIVOT разницы по производительности и не будет, потому что UNPIVOT ровно в такой запрос и развертывается (без ТОР, конечно). Я производительность сравнивал с генерацией последовательности через СТЕ либо использованием таблицы чисел. Там разница некоторая будет, плюс - мой код проще. Кстати, вашего он, ИМХО, тоже проще - не нужно разбирать строки для фильтров.
6 апр 16, 14:00    [19023193]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
iljy,

Вот код, который возвращает одинаковые результаты.
Сравните план и время выполнения.
Допускаю, что есть более оптимальный способ получить numdays в вашем варианте (напишите?), но даже с ним, скорее всего, будет разница.

declare @source as table (idWork int, countWork int, day01 float, day02 float, day03 float)
insert into @source values(1, 2, 10,11,null), (2, 1, 1,null,null), (3, 2, 100,200,300)

select idWork
,right(clmnName, 2)-1 as numdays
,statedays
from @source s 
unpivot (
	statedays for clmnName in ([day01], [day02], [day03])
	) unpvt
where right(clmnName, 2)-1 < countWork


select  idWork, row_number() OVER (PARTITION BY idWork ORDER BY current_timestamp)-1 As numdays, stateday
from @source 
outer apply	(
		select top(CountWork) stateday 
		from (
			select day01 union all
			select day02 union all
			select day03
			) t(stateday)
		) o
7 апр 16, 02:06    [19025773]     Ответить | Цитировать Сообщить модератору
 Re: не могу выполнить "поворот" таблицы  [new]
iljy
Member

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

select  idWork, N, stateday
from @source 
outer apply	(
		select top(CountWork) N, stateday 
		from (
			select 1, day01 union all
			select 2, day02 union all
			select 3, day03
			) t(N, stateday)
		) o

Можно заменить top на where во внешнем запросе, планы вообще будут почти идентичны.
7 апр 16, 04:11    [19025806]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить