ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / SQL и "матрица"? Реально ли решить такую задачу?
22 сообщений из 22, страница 1 из 1
SQL и "матрица"? Реально ли решить такую задачу?
    #39657189
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
Всем привет товарищи!
У меня вопрос по одной задачке:
Имеется таблица:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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: Отображение таблицы не важно, колонки можно заменить в виде строки

Спасибо за помощь!
...
Рейтинг: 0 / 0
07.06.2018, 11:12
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657199
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
swd1986,

если число строк в sq - величина переменная, то реализовать можно исключительно при помощи динамического SQL.
...
Рейтинг: 0 / 0
07.06.2018, 11:23
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657203
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
Щукина Аннаswd1986,

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

А в случае если величина фиксированная?
...
Рейтинг: 0 / 0
07.06.2018, 11:32
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657208
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
swd1986,

тогда задача - элементарная...
cross join + pivot.
если с PIVOT-ом сложности - то его аналоги на CASE + GROUP BY
...
Рейтинг: 0 / 0
07.06.2018, 11:35
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657221
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
swd1986А в случае если величина фиксированная?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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;
...
Рейтинг: 0 / 0
07.06.2018, 11:49
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657223
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
swd1986,

дальше лень

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
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
...
Рейтинг: 0 / 0
07.06.2018, 11:49
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657229
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
swd1986А в случае если величина фиксированная?Один из возможных вариантов решения "в лоб" в этом случае:

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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


Из оптимизации видится следующее - преобразовать столбец в строку, размножить нужно число раз. Посчитать пересечения и колонки.
...
Рейтинг: 0 / 0
07.06.2018, 11:53
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657239
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
invmswd1986А в случае если величина фиксированная?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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;



Чуть допиленный ваш вариант:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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;
...
Рейтинг: 0 / 0
07.06.2018, 12:01
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657271
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
Щукина Анна,

TaPaK ,

invm ,

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

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

Так как я если сгенерирую "программно" колонки, я не могу к ним обратиться по имени или по индексу
...
Рейтинг: 0 / 0
07.06.2018, 12:20
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657280
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
swd1986Щукина Анна,

TaPaK ,

invm ,

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

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

Так как я если сгенерирую "программно" колонки, я не могу к ним обратиться по имени или по индексу
и что не так с вариантом через xml?
...
Рейтинг: 0 / 0
07.06.2018, 12:28
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657282
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
Щукина Анна,

Т.е. если оперировать строками, то я думаю образно так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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 или другим способом. А как это сделать - вот сейчас думаю...
...
Рейтинг: 0 / 0
07.06.2018, 12:28
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657290
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
TaPaKswd1986Щукина Анна,

TaPaK ,

invm ,

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

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

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

TaPaK ,
Спасибо! Я просто еще не до конца понял логику работы xml (или даже не знаю). Как раз читаю справочник на эту тему
...
Рейтинг: 0 / 0
07.06.2018, 12:34
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657291
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
это полсностью ваша матрица
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
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
...
Рейтинг: 0 / 0
07.06.2018, 12:35
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657298
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
TaPaK , вы супер! Респект и уважуха!

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


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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
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
...
Рейтинг: 0 / 0
07.06.2018, 12:38
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657301
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
swd1986,

хорошо что это собеседование не к нам :)
...
Рейтинг: 0 / 0
07.06.2018, 12:40
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657333
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
TaPaKswd1986,

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

:)

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

Итоговый вариант:
 
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
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


...
Рейтинг: 0 / 0
07.06.2018, 13:19
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657336
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
swd1986,

если смотреть на ваш пример, то вот 21475552 уже давно
...
Рейтинг: 0 / 0
07.06.2018, 13:22
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657338
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
TaPaKswd1986,

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


Ахах, да просто в кучу сообщения упали, я толком не разобрался по коду )
...
Рейтинг: 0 / 0
07.06.2018, 13:25
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39657449
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
swd1986,

Решать такую задачу лучше в OLAP (SSAS, MDX)
...
Рейтинг: 0 / 0
07.06.2018, 15:03
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39658031
swd1986
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
swd1986
Гость
a_voronin,

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

Или я еще не понимаю, что вы имели ввиду через Report, cube...
...
Рейтинг: 0 / 0
08.06.2018, 06:34
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39658539
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
swd1986a_voronin,

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

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

select, with (cte) есть в MDX и да можно сделать отчет от куба
...
Рейтинг: 0 / 0
08.06.2018, 18:02
| Ответить | Цитировать | Написать  
SQL и "матрица"? Реально ли решить такую задачу?
    #39658540
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
a_voroninswd1986a_voronin,

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

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

select, with (cte) есть в MDX и да можно сделать отчет от куба
не ясно почему до сих пор никто EXCEL не предложил...
...
Рейтинг: 0 / 0
08.06.2018, 18:03
| Ответить | Цитировать | Написать  
22 сообщений из 22, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / SQL и "матрица"? Реально ли решить такую задачу?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]