Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / SQL и "матрица"? Реально ли решить такую задачу? / 22 сообщений из 22, страница 1 из 1
07.06.2018, 11:12
    #39657189
swd1986
swd1986
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
Всем привет товарищи!
У меня вопрос по одной задачке:
Имеется таблица:

Код: 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:23
    #39657199
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
swd1986,

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

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

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

тогда задача - элементарная...
cross join + pivot.
если с PIVOT-ом сложности - то его аналоги на CASE + GROUP BY
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.06.2018, 11:49
    #39657221
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
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
    #39657223
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
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:53
    #39657229
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
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, 12:01
    #39657239
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
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:20
    #39657271
swd1986
swd1986
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
Щукина Анна,

TaPaK ,

invm ,

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

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

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

TaPaK ,

invm ,

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

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

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

Т.е. если оперировать строками, то я думаю образно так
Код: 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:34
    #39657290
swd1986
swd1986
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
TaPaKswd1986Щукина Анна,

TaPaK ,

invm ,

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

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

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

TaPaK ,
Спасибо! Я просто еще не до конца понял логику работы xml (или даже не знаю). Как раз читаю справочник на эту тему
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.06.2018, 12:35
    #39657291
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
это полсностью ваша матрица
Код: 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:38
    #39657298
swd1986
swd1986
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
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:40
    #39657301
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL и "матрица"? Реально ли решить такую задачу?
swd1986,

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

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

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


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

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

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

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

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

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

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

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

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

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


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