Присоединиться по составному полю (в формате CSV)
Обновлено: Я полностью осознаю, что это плохая практика РСУБД, но вопрос не в том, так ли это и как я могу повторно обучить администраторов баз данных, которые создали эту архитектуру. Вопрос в том, как я могу обойти сложившуюся ситуацию. Я ценю помощь сообщества и должен признать, что это действительно интересная проблема.
В SQL Server 2017 у меня есть таблица поиска, содержащая коды, и таблица транзакций с кодами в формате CSV:
CREATE TABLE #t(cd VARCHAR(100))
CREATE TABLE #cd (id INT, cd VARCHAR (1000))
INSERT INTO #t SELECT 'c1'
INSERT INTO #t SELECT 'c1,c2'
INSERT INTO #t SELECT 'c1,c2,c3'
INSERT INTO #cd SELECT 10, 'c1'
INSERT INTO #cd SELECT 20, 'c2'
INSERT INTO #cd SELECT 30, 'c3'
Итак, поиск
id cd
10 c1
10 c1
20 c2
30 c3
и в таблице транзакций есть:
cd
c1
c1,c2
c1,c2,c3
Мне нужно заменить коды на соответствующие идентификаторы, сохранив их в формате CSV.
Я бы хотел избежать курсора, потому что он слишком медленный. Есть ли способ анализировать коды, выполнять JOIN и как-то эффективно рекомбинировать идентификаторы? Я полагаю, COALESCE может быть полезен, но мне нужна помощь в его применении. Возможно, в t-SQL уже есть функция, которая выполняет такие поисковые запросы.
Вывод необходимо в другой столбец в таблице транзакций:
id
10
10,20
10,20,30
Это должно быть действительно обычное домашнее задание. Это по крайней мере третий вопрос сегодняшнего дня в этой действительно странной просьбе.
@JoelCoehoorn Номер телефона? Почтовый индекс?
@nicomp и IP-адрес. Разделители в этих полях - это способы форматирования одного значения.
@ Джоэл: Я понимаю, что это плохая практика, но преподать мне урок не поможет в решении проблемы. Я не являюсь разработчиком этой таблицы и не могу ее изменить. Однако я должен использовать его в своих проектах машинного обучения, и мне нужна помощь сообщества. Пожалуйста, воздержитесь от несправедливой оценки моего вопроса.
@ Шон, Джоэл, это не присвоения HW и не почтовые индексы. У меня есть реальные и беспорядочные данные. Я сильно упростил вопрос. Пожалуйста, не спешите ставить мне оценку за этот вопрос. Вместо этого любезно помогите. В настоящее время у меня есть решение на Python, но есть ли простой способ сделать это в SQL? На самом деле это очень интересный вопрос. Джоэл, да, я видел телефонные номера, хранящиеся таким образом, но в моем случае это технические коды. Их значение не имеет значения.
@JoelCoehoorn Не совсем. Телефонный номер - это код города, префикс и номер линии.
Мне просто кажется забавным, что сегодня целых три человека пытаются объединить такие данные с разделителями. Сами данные не имеют значения, проблема в том, что структура данных нарушает 1NF с несколькими значениями в одном кортеже.
@ Шон. РЖУ НЕ МОГУ. Стоит ли удалить вопрос и задать его завтра? Это сломало бы тенденцию? Этот вопрос беспокоит меня несколько месяцев. Конечно, я могу подождать неделю, если это настроит позитив :)
Хах нет. :) Я скоро найду эту другую ветку.
Вот. stackoverflow.com/questions/49652929/… Это почти то же самое, что вы пытаетесь сделать. Соединение - это та часть, которую вы можете использовать. Или вы можете использовать разделитель строк, чтобы превратить это во что-то более удобное. Вот мой первый выбор сплиттеров. sqlservercentral.com/articles/Tally+Table/72993 Здесь есть еще несколько отличных вариантов. sqlperformance.com/2012/07/t-sql-queries/split-strings
@ Шон. Это интересно, но это другая проблема (требуется поиск подстроки). В моем случае мне нужны соединения. На самом деле, я могу использовать CROSS APPLY для разделения данных, затем JOIN, затем COALESCE. Вот и все. stackoverflow.com/questions/5493510/…
Ответы 1
Вы можете сначала вырезать запятую в списке, а затем присоединиться и получить правильные идентификаторы для кодов, а затем добавить их обратно с запятыми. Я использовал row_number заранее, чтобы получить уникальную вещь, к которой можно снова присоединиться в моем запросе.
CREATE TABLE #t(cd VARCHAR(100))
CREATE TABLE #cd (id INT, cd VARCHAR (1000))
INSERT INTO #t SELECT 'c1'
INSERT INTO #t SELECT 'c1,c2'
INSERT INTO #t SELECT 'c1,c2,c3'
INSERT INTO #cd SELECT 10, 'c1'
INSERT INTO #cd SELECT 20, 'c2'
INSERT INTO #cd SELECT 30, 'c3'
; WITH X AS
(
SELECT
C.id,P1.rn
FROM
(
SELECT *, row_number() over( order by (select 1)) rn,
cast('<X>'+replace(P.cd,',','</X><X>')+'</X>' as XML) AS xmlitems FROM #t P
)P1
CROSS APPLY
(
SELECT fdata.D.value('.','varchar(100)') AS splitdata
FROM P1.xmlitems.nodes('X') AS fdata(D)) O
LEFT JOIN #cd C
ON C.cd= LTRIM(RTRIM(O.splitdata ))
)
SELECT
rn,
id= STUFF((
SELECT ',' + cast(id as varchar(100)) FROM X AS x2
WHERE x2.rn = x.rn
ORDER BY rn FOR XML PATH,
TYPE).value(N'.[1]',N'varchar(max)'), 1, 1, '')
FROM
X
GROUP BY rn
Примечание: С SQL server 2017 вы также можете использовать функцию SPLIT_STRING () и функции STRING_AGG ()
Код SQL SERVER 2017:
select
id=STRING_AGG(id,',')
from
(
select V=value, rn
from
(
select
rn=row_number() over( order by (select 1)),
cd
from #T
)T
cross apply STRING_SPLIT(cd, ',')
) T
left join #cd C
on cd= v
group by rn
Супер! как раз то, что мне было нужно.
очень сильно - плохая практика помещать данные с разделителями в один столбец. Исправьте свою схему, и это внезапно станет легкой проблемой.