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

Откуда: Екатеринбург
Сообщений: 45
Добрый день.

Помогите преобразовать непростые данные. Имеем версию SQL

Microsoft SQL Server 2005 - 9.00.3054.00 (X64) 

Имеем следующую таблицу с данными, где ID уникальный ключ, а в поле PARAM находятся данные в записи в виде объектов javascript

IDPARAM
24424{'M':{w:55,h:74}}
24427{'S':{w:57,h:67}}
24455{'L':{w:57,l:76}}
24522{'M':{w:56,h:74}}
24524{'M':{w:56,h:70}}
24577{'L':{w:59,h:77}}
24591{'2XL':{w:67,h:77}}
24592{'L':{w:61,h:75}}
24600{'M':{w:54,h:70}}
25138{'L':{w:60,h:75}}
25140{'L':{w:57,h:67,'L':61}}
25142{'XL':{w:63,h:80}}
25146{'M':{w:57,h:74}}
28516{'L':{w:58,h:79}}
28565{'L':{w:55,h:75}}
30330{'XL':{w:61,h:80}}
43805{'L':{w:52,l:75}}
43842{'L':{w:56,h:76,l:58}}
88274{'L':{w:59,h:74}}
88696{'L':{w:57,h:78}}
90000{'L':{w:60,h:76}}
94863{'L':{w:56,h:77}}
94907{'L':{w:60,h:78}}
96456{'2XL':{w:60,h:85,l:62}}
97143{'L':{w:54,h:74}}
97803{'L':{w:59,h:74}}
99633{'2XL':{w:66,h:80,l:60}}
99734{'L':{w:57,h:79,'L':61}}
99887{'L':{w:56,'L':78}}
99980{'L':{w:56,h:76}}
99987{'L':{w:56,h:76}}
99988{'L':{w:57,h:76}}
100846{'XL':{w:62,l:78}}
105658{'L':{w:56,h:74}}
110043{'M':{w:60,l:81}}
115032{'XL':{w:63,l:73}}
1445188{'L':{w:56,h:73},'2XL':{w:71,h:81}}
1445569{'L':{w:58,l:75}}
1466226{'L':{w:59,h:78}}
1466229{'2XL':{w:66,h:77}}
1474248{'M':{w:56,h:70,'L':63}, 'L':{w:62,h:76,'L':71}, '2XL':{w:62,h:78,l:68}}
1480112{'2XL':{w:62,h:81}}
1498200{'M-L':{w:59,l:62}}
1500529{'XL':{w:64,h:84}}
1510132{'L':{w:49,h:71}, '2XL':{w:57,h:76}}
1515187{'XL':{w:63,h:84}}
1519345{'L':{w:55,h:74},'2XL':{w:63,h:78}}
1558898{'XL':{w:100,h:200},l:{w:200,h:300},xs:{w:50.1,h:54.5}}
1560023{'S':{w:53,h:68,l:60}}
1564056{'M':{w:53,h:75}, L:{w:58,h:79}}
1564652{'L':{w:56,h:86,'L':67}}
1568501{'XL':{w:71,h:79,l:63}}
1590897{'XL':{w:63,l:82}}
1594073{'M':{w:53,h:75}}
1594085{'L':{w:58,h:79}}
1594300{'L':{w:57,h:75}}
1609556{'L':{w:60,h:82,'L':71}}
1612874{'M':{w:53,h:74}}
1612885{'L':{w:57,h:76}}
1613415{'M':{w:52,l:74}}
1613432{'2XL':{w:64,h:80}}
1614160{'XL':{w:60,h:76}}
1614197{'L':{w:52,h:79}}
1617985{'L':{w:59,h:74},'2XL':{w:67,h:80}}
1619477{'L':{w:60,h:78}}
1619524{'L':{w:56,h:76}}
1619537{'L':{w:58,h:75}}
1619558{'2XL':{w:65,h:78}}
1623541{'S':{w:57,h:74},'L':{w:59,h:78}}
1623545{'L':{w:64,h:78}}
1623556{'M':{w:57,h:73,l:57}}
1625190{'L':{w:57,h:70}}
1625192{'XL':{w:55,h:73}}
1627684{'M':{w:55,h:76}}
1627687{'M':{w:59,h:71}}
1627859{'L':{w:59,l:73}}
1628842{'L':{w:58,h:75}}
1629519{'L':{w:58,h:78}}
1630601{'L':{w:57,h:77}}
1631172{'L':{w:51,h:71}}
1632490{'L':{w:56,h:79}}
1634744{'2XL':{w:72,h:84,l:87}}
1639440{'L':{w:56,h:79}}
1640154{'L':{w:58,h:75}}


Необходимо получить данные в следующем виде - значения указанные в {w,h,….} записать в таблицу и сослаться на ID, т.е. должно получиться, что то типа

IDwhL
244245574null
244275767null
244555776null
245225674null
............
1474248567063
1474248627671
1474248627868


1. При этом значения w,h,l float.
2. Кроме w,h,l может быть ещё много параметров, но полагаю, что смогу самостоятельно масштабировать скрипт на большее кол-во значений.
3. Умею писать внешние процедуры на .NET для MSSql, но ищу решение на T-SQL


create table #test (
	id         bigint,
	param varchar(max)
)

insert #test values(24424,'{''M'':{w:55,h:74}}')
insert #test values(24427,'{''S'':{w:57,h:67}}')
insert #test values(24455,'{''L'':{w:57,l:76}}')
insert #test values(24522,'{''M'':{w:56,h:74}}')
insert #test values(24524,'{''M'':{w:56,h:70}}')
insert #test values(24577,'{''L'':{w:59,h:77}}')
insert #test values(24591,'{''2XL'':{w:67,h:77}}')
insert #test values(24592,'{''L'':{w:61,h:75}}')
insert #test values(24600,'{''M'':{w:54,h:70}}')
insert #test values(25138,'{''L'':{w:60,h:75}}')
insert #test values(25140,'{''L'':{w:57,h:67,''L'':61}}')
insert #test values(25142,'{''XL'':{w:63,h:80}  }')
insert #test values(25146,'{''M'':{w:57,h:74}}')
insert #test values(28516,'{''L'':{w:58,h:79}}')
insert #test values(28565,'{''L'':{w:55,h:75}}')
insert #test values(30330,'{''XL'':{w:61,h:80}}')
insert #test values(43805,'{''L'':{w:52,l:75}}')
insert #test values(43842,'{''L'':{w:56,h:76,l:58}}')
insert #test values(88274,'{''L'':{w:59,h:74}}')
insert #test values(88696,'{''L'':{w:57,h:78}}')
insert #test values(90000,'{''L'':{w:60,h:76}}')
insert #test values(94863,'{''L'':{w:56,h:77}}')
insert #test values(94907,'{''L'':{w:60,h:78}}')
insert #test values(96456,'{''2XL'':{w:60,h:85,l:62}}')
insert #test values(97143,'{''L'':{w:54,h:74}}')
insert #test values(97803,'{''L'':{w:59,h:74}}')
insert #test values(99633,'{''2XL'':{w:66,h:80,l:60}}')
insert #test values(99734,'{''L'':{w:57,h:79,''L'':61}}')
insert #test values(99887,'{''L'':{w:56,''L'':78}}')
insert #test values(99980,'{''L'':{w:56,h:76}}')
insert #test values(99987,'{''L'':{w:56,h:76}}')
insert #test values(99988,'{''L'':{w:57,h:76}}')
insert #test values(100846,'{''XL'':{w:62,l:78}}')
insert #test values(105658,'{''L'':{w:56,h:74}}')
insert #test values(110043,'{''M'':{w:60,l:81}}')
insert #test values(115032,'{''XL'':{w:63,l:73}}')
insert #test values(1445188,'{''L'':{w:56,h:73},''2XL'':{w:71,h:81}}')
insert #test values(1445569,'{''L'':{w:58,l:75}}')
insert #test values(1466226,'{''L'':{w:59,h:78}}')
insert #test values(1466229,'{''2XL'':{w:66,h:77}}')
insert #test values(1474248,'{''M'':{w:56,h:70,''L'':63}, ''L'':{w:62,h:76,''L'':71}, ''2XL'':{w:62,h:78,l:68}}')
insert #test values(1480112,'{''2XL'':{w:62,h:81}}')
insert #test values(1498200,'{''M-L'':{w:59,l:62}}')
insert #test values(1500529,'{''XL'':{w:64,h:84}}')
insert #test values(1510132,'{''L'':{w:49,h:71}, ''2XL'':{w:57,h:76}}')
insert #test values(1515187,'{''XL'':{w:63,h:84}}')
insert #test values(1519345,'{''L'':{w:55,h:74},''2XL'':{w:63,h:78}}')
insert #test values(1558898,'{''XL'':{w:100,h:200},l:{w:200,h:300},xs:{w:50.1,h:54.5}}')
insert #test values(1560023,'{''S'':{w:53,h:68,l:60}}')
insert #test values(1564056,'{''M'':{w:53,h:75}, L:{w:58,h:79}}')
insert #test values(1564652,'{''L'':{w:56,h:86,''L'':67}}')
insert #test values(1568501,'{''XL'':{w:71,h:79,l:63}}')
insert #test values(1590897,'{''XL'':{w:63,l:82}}')
insert #test values(1594073,'{''M'':{w:53,h:75}}')
insert #test values(1594085,'{''L'':{w:58,h:79}}')
insert #test values(1594300,'{''L'':{w:57,h:75}}')
insert #test values(1609556,'{''L'':{w:60,h:82,''L'':71}}')
insert #test values(1612874,'{''M'':{w:53,h:74}}')
insert #test values(1612885,'{''L'':{w:57,h:76}}')
insert #test values(1613415,'{''M'':{w:52,l:74}}')
insert #test values(1613432,'{''2XL'':{w:64,h:80}}')
insert #test values(1614160,'{''XL'':{w:60,h:76}}')
insert #test values(1614197,'{''L'':{w:52,h:79}}')
insert #test values(1617985,'{''L'':{w:59,h:74},''2XL'':{w:67,h:80}}')
insert #test values(1619477,'{''L'':{w:60,h:78}}')
insert #test values(1619524,'{''L'':{w:56,h:76}}')
insert #test values(1619537,'{''L'':{w:58,h:75}}')
insert #test values(1619558,'{''2XL'':{w:65,h:78}}')
insert #test values(1623541,'{''S'':{w:57,h:74},''L'':{w:59,h:78}}')
insert #test values(1623545,'{''L'':{w:64,h:78}}')
insert #test values(1623556,'{''M'':{w:57,h:73,l:57}}')
insert #test values(1625190,'{''L'':{w:57,h:70}}')
insert #test values(1625192,'{''XL'':{w:55,h:73}}')
insert #test values(1627684,'{''M'':{w:55,h:76}}')
insert #test values(1627687,'{''M'':{w:59,h:71}}')
insert #test values(1627859,'{''L'':{w:59,l:73}}')
insert #test values(1628842,'{''L'':{w:58,h:75}}')
insert #test values(1629519,'{''L'':{w:58,h:78}}')
insert #test values(1630601,'{''L'':{w:57,h:77}}')
insert #test values(1631172,'{''L'':{w:51,h:71}}')
insert #test values(1632490,'{''L'':{w:56,h:79}}')
insert #test values(1634744,'{''2XL'':{w:72,h:84,l:87}}')
insert #test values(1639440,'{''L'':{w:56,h:79}}')
insert #test values(1640154,'{''L'':{w:58,h:75}}')

select * from #test


create table #result (
	id         bigint,
	w float,
	h float,
	l float
)
17 сен 13, 17:08    [14850602]     Ответить | Цитировать Сообщить модератору
 Re: Непростое преобразование данных - объектов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ИванПетрович,

Я бы подумал не над тем, как при помощи T-SQL нормализовать данные, а над тем, как загружать в БД уже нормализованные данные.
17 сен 13, 17:40    [14850791]     Ответить | Цитировать Сообщить модератору
 Re: Непростое преобразование данных - объектов  [new]
ИванПетрович
Member

Откуда: Екатеринбург
Сообщений: 45
Гость333,

Можно подумать, но придумать в какой более удобный вид преобразовать данные, чтобы человеку было удобно их вводить мне не понятно :(
17 сен 13, 17:43    [14850804]     Ответить | Цитировать Сообщить модератору
 Re: Непростое преобразование данных - объектов  [new]
Гость333
Member

Откуда:
Сообщений: 3683
ИванПетрович,

Человек может вводить в текущем виде, если ему так удобно.
Программа, в которую он всё это вводит, может парсить введённый текст и вставлять в БД в нормализованном виде.
Конечно, это если есть возможность доработать используемую программу.
17 сен 13, 17:54    [14850856]     Ответить | Цитировать Сообщить модератору
 Re: Непростое преобразование данных - объектов  [new]
qwerty112
Guest
выглядит "страшненько", конечно, но вроде - "работает" ... ))
select id, /*a.param_xml,*/ b.w, b.h, b.l
from #test t
cross apply (select cast(
	replace(replace(replace(replace(replace(replace(replace(replace
	(lower(t.param), '''', ''), ' ', ''), '},', '}} {'), ':{', ' '), '{', '<haba'), ':', '="'), ',', '" '), '}}', '"/>') as xml) as param_xml) a
cross apply (select
	 t.c.value('@w', 'float') as w
	,t.c.value('@h', 'float') as h
	,t.c.value('@l', 'float') as l
from a.param_xml.nodes('*') as t(c)) b

+

id                   w                      h                      l
-------------------- ---------------------- ---------------------- ----------------------
24424                55                     74                     NULL
24427                57                     67                     NULL
24455                57                     NULL                   76
24522                56                     74                     NULL
24524                56                     70                     NULL
24577                59                     77                     NULL
24591                67                     77                     NULL
24592                61                     75                     NULL
24600                54                     70                     NULL
25138                60                     75                     NULL
25140                57                     67                     61
25142                63                     80                     NULL
25146                57                     74                     NULL
28516                58                     79                     NULL
28565                55                     75                     NULL
30330                61                     80                     NULL
43805                52                     NULL                   75
43842                56                     76                     58
88274                59                     74                     NULL
88696                57                     78                     NULL
90000                60                     76                     NULL
94863                56                     77                     NULL
94907                60                     78                     NULL
96456                60                     85                     62
97143                54                     74                     NULL
97803                59                     74                     NULL
99633                66                     80                     60
99734                57                     79                     61
99887                56                     NULL                   78
99980                56                     76                     NULL
99987                56                     76                     NULL
99988                57                     76                     NULL
100846               62                     NULL                   78
105658               56                     74                     NULL
110043               60                     NULL                   81
115032               63                     NULL                   73
1445188              56                     73                     NULL
1445188              71                     81                     NULL
1445569              58                     NULL                   75
1466226              59                     78                     NULL
1466229              66                     77                     NULL
1474248              56                     70                     63
1474248              62                     76                     71
1474248              62                     78                     68
1480112              62                     81                     NULL
1498200              59                     NULL                   62
1500529              64                     84                     NULL
1510132              49                     71                     NULL
1510132              57                     76                     NULL
1515187              63                     84                     NULL
1519345              55                     74                     NULL
1519345              63                     78                     NULL
1558898              100                    200                    NULL
1558898              200                    300                    NULL
1558898              50,1                   54,5                   NULL
1560023              53                     68                     60
1564056              53                     75                     NULL
1564056              58                     79                     NULL
1564652              56                     86                     67
1568501              71                     79                     63
1590897              63                     NULL                   82
1594073              53                     75                     NULL
1594085              58                     79                     NULL
1594300              57                     75                     NULL
1609556              60                     82                     71
1612874              53                     74                     NULL
1612885              57                     76                     NULL
1613415              52                     NULL                   74
1613432              64                     80                     NULL
1614160              60                     76                     NULL
1614197              52                     79                     NULL
1617985              59                     74                     NULL
1617985              67                     80                     NULL
1619477              60                     78                     NULL
1619524              56                     76                     NULL
1619537              58                     75                     NULL
1619558              65                     78                     NULL
1623541              57                     74                     NULL
1623541              59                     78                     NULL
1623545              64                     78                     NULL
1623556              57                     73                     57
1625190              57                     70                     NULL
1625192              55                     73                     NULL
1627684              55                     76                     NULL
1627687              59                     71                     NULL
1627859              59                     NULL                   73
1628842              58                     75                     NULL
1629519              58                     78                     NULL
1630601              57                     77                     NULL
1631172              51                     71                     NULL
1632490              56                     79                     NULL
1634744              72                     84                     87
1639440              56                     79                     NULL
1640154              58                     75                     NULL

(94 row(s) affected)
17 сен 13, 18:09    [14850914]     Ответить | Цитировать Сообщить модератору
 Re: Непростое преобразование данных - объектов  [new]
ИванПетрович
Member

Откуда: Екатеринбург
Сообщений: 45
qwerty112,

Спасибо, проверил, работает действительно :)
18 сен 13, 10:13    [14852542]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить