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

Откуда: KZ
Сообщений: 36
Всем привет товарищи!
У меня вопрос по одной задачке:
Имеется таблица:

with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
),


Из нее нужно получить таблицу вида
1 2 3 4 5 6
2 2 0 1 0 1
3 1 3 1 0 1
4 1 0 4 0 1
5 1 0 1 5 1
6 1 0 1 0 6

То есть, из таблицы sq превратить в квадрат, где число строк и колонок равно max(sq(n))
При этом в месте пересечения строки и столбца ячейка равна строке/столбу (диагональ)
А остальные ячейки – если колонка четная, то 0. Не четная 1.

Оттуда возникает вопрос, как можно хотя бы таблицу разбить на квадрат, размерностью в число строк?
При этом использовать только select, with (cte)

PS: Отображение таблицы не важно, колонки можно заменить в виде строки

Спасибо за помощь!
7 июн 18, 11:12    [21475119]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
Щукина Анна
Member

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

если число строк в sq - величина переменная, то реализовать можно исключительно при помощи динамического SQL.
7 июн 18, 11:23    [21475177]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
Щукина Анна
swd1986,

если число строк в sq - величина переменная, то реализовать можно исключительно при помощи динамического SQL.


А в случае если величина фиксированная?
7 июн 18, 11:32    [21475211]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
Щукина Анна
Member

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

тогда задача - элементарная...
cross join + pivot.
если с PIVOT-ом сложности - то его аналоги на CASE + GROUP BY
7 июн 18, 11:35    [21475226]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
invm
Member

Откуда: Москва
Сообщений: 9351
swd1986
А в случае если величина фиксированная?
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6
)
select
 sq.n,
 case when sq.n = 0 then 1 else 0 end as [0],
 case when sq.n = 1 then 1 else 0 end as [1],
 case when sq.n = 2 then 1 else 0 end as [2],
 case when sq.n = 3 then 1 else 0 end as [3],
 case when sq.n = 4 then 1 else 0 end as [4],
 case when sq.n = 5 then 1 else 0 end as [5],
 case when sq.n = 6 then 1 else 0 end as [6]
from
 sq
order by
 sq.n;
7 июн 18, 11:49    [21475283]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
TaPaK
Member

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

дальше лень

with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 
),  b as
(
	SELECT 
		r =ROW_NUMBER() OVER (ORDER BY 1/0) ,
		n
	FROM sq
)
SELECT b.*
FROM b a
CROSS APPLY
(
	SELECT	
		l = STUFF(( SELECT ', ' + 
					CASE 
						WHEN a.r = b.r THEN CAST(n as VARCHAR(1)) 						
						ELSE '0' 
					END 
	FROM b FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') 
)  b 
7 июн 18, 11:49    [21475287]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
swd1986
А в случае если величина фиксированная?
Один из возможных вариантов решения "в лоб" в этом случае:

+
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6
)


select v1.n [ ]
     , max(case v0.n when 0 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [0]
     , max(case v0.n when 1 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [1]
     , max(case v0.n when 2 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [2]
     , max(case v0.n when 3 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [3]
     , max(case v0.n when 4 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [4]
     , max(case v0.n when 5 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [5]
     , max(case v0.n when 6 then case when v0.n = v1.n then v1.n when v0.n%2 = 0 then 1 else 0 end  else 0 end) as [6]
from   sq v0 cross join sq v1
group  by v1.n

Из оптимизации видится следующее - преобразовать столбец в строку, размножить нужно число раз. Посчитать пересечения и колонки.
7 июн 18, 11:53    [21475303]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
invm
swd1986
А в случае если величина фиксированная?
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6
)
select
 sq.n,
 case when sq.n = 0 then 1 else 0 end as [0],
 case when sq.n = 1 then 1 else 0 end as [1],
 case when sq.n = 2 then 1 else 0 end as [2],
 case when sq.n = 3 then 1 else 0 end as [3],
 case when sq.n = 4 then 1 else 0 end as [4],
 case when sq.n = 5 then 1 else 0 end as [5],
 case when sq.n = 6 then 1 else 0 end as [6]
from
 sq
order by
 sq.n;


Чуть допиленный ваш вариант:
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6
)
select
 sq.n,
 case when sq.n = 0 then sq.n else 0 end as [0],
 case when sq.n = 1 then sq.n else 1 end as [1],
 case when sq.n = 2 then sq.n else 0 end as [2],
 case when sq.n = 3 then sq.n else 1 end as [3],
 case when sq.n = 4 then sq.n else 0 end as [4],
 case when sq.n = 5 then sq.n else 1 end as [5],
 case when sq.n = 6 then sq.n else 0 end as [6]
from
 sq
order by
 sq.n;
7 июн 18, 12:01    [21475342]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
Щукина Анна,

TaPaK ,

invm ,

Благодарю Вас товарищи! Низкий поклон! )

Уточнил задачку, все таки речь идет о динамической таблице, их может быть элементов 6, 9, 100 и т.д. Поэтому в задаче была пометка, что можно отобразить в виде строк без колонок.

Так как я если сгенерирую "программно" колонки, я не могу к ним обратиться по имени или по индексу
7 июн 18, 12:20    [21475468]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
swd1986
Щукина Анна,

TaPaK ,

invm ,

Благодарю Вас товарищи! Низкий поклон! )

Уточнил задачку, все таки речь идет о динамической таблице, их может быть элементов 6, 9, 100 и т.д. Поэтому в задаче была пометка, что можно отобразить в виде строк без колонок.

Так как я если сгенерирую "программно" колонки, я не могу к ним обратиться по имени или по индексу

и что не так с вариантом через xml?
7 июн 18, 12:28    [21475515]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
Щукина Анна,

Т.е. если оперировать строками, то я думаю образно так
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 
)


select n, REPLICATE('0',n)t from sq


В результате будет строка

+


0
1 0
2 00
3 000
4 0000
5 00000
6 000000



И далее, построчно вычислить необходимые значения PATINDEX или другим способом. А как это сделать - вот сейчас думаю...
7 июн 18, 12:28    [21475518]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
TaPaK
swd1986
Щукина Анна,

TaPaK ,

invm ,

Благодарю Вас товарищи! Низкий поклон! )

Уточнил задачку, все таки речь идет о динамической таблице, их может быть элементов 6, 9, 100 и т.д. Поэтому в задаче была пометка, что можно отобразить в виде строк без колонок.

Так как я если сгенерирую "программно" колонки, я не могу к ним обратиться по имени или по индексу

и что не так с вариантом через xml?


TaPaK ,
Спасибо! Я просто еще не до конца понял логику работы xml (или даже не знаю). Как раз читаю справочник на эту тему
7 июн 18, 12:34    [21475547]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
это полсностью ваша матрица
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 
),  b as
(
	SELECT 
		r =ROW_NUMBER() OVER (ORDER BY 1/0) ,
		n
	FROM sq
)
SELECT b.*
FROM b a
CROSS APPLY
(
	SELECT	
		l = STUFF(( SELECT ' , ' + 
					CASE 
						WHEN a.r = b.r THEN CAST(n as VARCHAR(1)) 						
						WHEN a.r = 1 THEN CAST(b.n as VARCHAR(1)) 						
						WHEN b.r = 1 THEN CAST(a.n as VARCHAR(1)) 						
						WHEN b.r%2 = 0 THEN '1'
						ELSE '0' 
					END 
	FROM b FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') 
)  b 
7 июн 18, 12:35    [21475552]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
TaPaK , вы супер! Респект и уважуха!

Динамически таблицу я получил, осталось разобраться с чет / не чет колонки


и что не так с вариантом через xml?

with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7
),  b as
(
	SELECT 
		r =ROW_NUMBER() OVER (ORDER BY 1/0) ,
		n
	FROM sq
)
SELECT b.*
FROM b a
CROSS APPLY
(
	SELECT	
		l = STUFF(( SELECT ', ' + 
					CASE 
						WHEN a.r = b.r THEN CAST(n as VARCHAR(1)) 						
						ELSE '0' 
					END 
	FROM b FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') 
)  b 

0, 0, 0, 0, 0, 0, 0, 0
0, 1, 0, 0, 0, 0, 0, 0
0, 0, 2, 0, 0, 0, 0, 0
0, 0, 0, 3, 0, 0, 0, 0
0, 0, 0, 0, 4, 0, 0, 0
0, 0, 0, 0, 0, 5, 0, 0
0, 0, 0, 0, 0, 0, 6, 0
0, 0, 0, 0, 0, 0, 0, 7
7 июн 18, 12:38    [21475578]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
TaPaK
Member

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

хорошо что это собеседование не к нам :)
7 июн 18, 12:40    [21475590]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
TaPaK
swd1986,

хорошо что это собеседование не к нам :)


:)

Еще раз всем спасибо большое! Тапак!
Спасибо!

Итоговый вариант:
+
[spoiler]
with sq(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7
),  

b as
(
	SELECT 
		r =ROW_NUMBER() OVER (ORDER BY 1/0) ,
		n
	FROM sq
)

SELECT b.*
FROM b a
CROSS APPLY
(
	SELECT	
		l = STUFF(( SELECT ', ' + 
					CASE 
						WHEN a.r = b.r THEN CAST(n as VARCHAR(1))
						WHEN b.r%2 = 0 THEN '0' 
						ELSE '1' 						 
					END 
	FROM b FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') 
)  b 

[/spoiler]
7 июн 18, 13:19    [21475804]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
TaPaK
Member

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

если смотреть на ваш пример, то вот 21475552 уже давно
7 июн 18, 13:22    [21475825]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
TaPaK
swd1986,

если смотреть на ваш пример, то вот 21475552 уже давно



Ахах, да просто в кучу сообщения упали, я толком не разобрался по коду )
7 июн 18, 13:25    [21475839]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
a_voronin
Member

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

Решать такую задачу лучше в OLAP (SSAS, MDX)
7 июн 18, 15:03    [21476289]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
swd1986
Member

Откуда: KZ
Сообщений: 36
a_voronin,

Спасибо, читал, как из способов отчетов, но условие было "При этом использовать только select, with (cte)"

Или я еще не понимаю, что вы имели ввиду через Report, cube...
8 июн 18, 06:34    [21477820]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4740
swd1986
a_voronin,

Спасибо, читал, как из способов отчетов, но условие было "При этом использовать только select, with (cte)"

Или я еще не понимаю, что вы имели ввиду через Report, cube...


select, with (cte) есть в MDX и да можно сделать отчет от куба
8 июн 18, 18:02    [21479912]     Ответить | Цитировать Сообщить модератору
 Re: SQL и "матрица"? Реально ли решить такую задачу?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
a_voronin
swd1986
a_voronin,

Спасибо, читал, как из способов отчетов, но условие было "При этом использовать только select, with (cte)"

Или я еще не понимаю, что вы имели ввиду через Report, cube...


select, with (cte) есть в MDX и да можно сделать отчет от куба

не ясно почему до сих пор никто EXCEL не предложил...
8 июн 18, 18:03    [21479915]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить