[mysql] SQL split string to multiple rows

---

다음과 같은 문자열 데이터를 아래와 같이 멀티행으로 변환하는 방법에 대해서 알아본다.

document ----------------  'a,b,c'

word --------- a b c

SELECT 	j.*
FROM	(SELECT 'a,b,c' AS doc) AS docs
	JOIN  
	JSON_TABLE(
  		docs.doc,
  		'$[*]' COLUMNS (word VARCHAR(50) PATH '$')
	) AS j
;

 

 

예제1)

-- 원본 문서 
WITH cte_doc AS (
SELECT 1 as  id ,
'심층 학습(深層學習) 또는 딥 러닝(영어: deep structured learning, deep learning 또는 hierarchical learning)은 여러 ''비선형 변환기법''의 조합을 통해 높은 수준의 추상화(abstractions, 다량의 데이터나 복잡한 자료들 속에서 핵심적인 내용 또는 기능을 요약하는 작업)를 시도하는 기계 학습 알고리즘의 집합[1]으로 정의되며, 큰 틀에서 사람의 사고방식을 컴퓨터에게 가르치는 기계학습의 한 분야라고 이야기할 수 있다.
어떠한 데이터가 있을 때 이를 컴퓨터가 알아 들을 수 있는 형태(예를 들어 이미지의 경우는 픽셀정보를 열벡터로 표현하는 등)로 표현(representation)하고 이를 학습에 적용하기 위해 많은 연구(어떻게 하면 더 좋은 표현기법을 만들고 또 어떻게 이것들을 학습할 모델을 만들지에 대한)가 진행되고 있으며, 이러한 노력의 결과로 deep neural networks, convolutional deep neural networks, deep belief networks와 같은 다양한 딥 러닝 기법들이 컴퓨터 비전, 음성인식, 자연어 처리, 음성/신호처리 등 최첨단 분야에 적용되고 있다.
2012년 스탠포드대학의 앤드류 응과 구글이 함께한 딥 러닝 프로젝트에서는 16,000개의 컴퓨터 프로세서와 10억 개 이상의 neural networks 그리고 DNN(deep neural networks)을 이용하여 유튜브에 업로드 되어 있는 천만 개 넘는 비디오 중 고양이 인식에 성공하였다.[2] 이 소프트웨어 프레임워크를 논문에서는 DistBelief로 언급하고 있다[3]. 이뿐만 아니라 마이크로소프트, 페이스북[4] 등도 연구팀을 인수하거나 자체 개발팀을 운영하면서 인상적인 업적들을 만들어 내고 있다.
' AS v_text 
UNION ALL 
SELECT 
2 as  id ,
'딥 러닝이 부활하게 된 이유는 크게 세 가지로 꼽힌다. 첫 번째는 앞서 딥 러닝의 역사에서 언급한 바 있는 기존 인공신경망 모델의 단점이 극복되었다는 점이다. 그러나 과적합 문제만 해결되었다고 해서 느린 학습시간이 줄어드는 것은 아니다. 두 번째 이유로, 여기에는 하드웨어의 발전이라는 또다른 요인이 존재 한다. 특히 강력한 GPU는 딥 러닝에서 복잡한 행렬 연산에 소요되는 시간을 크게 단축시켰다. 마지막으로 언급하지만 가장 중요한 세 번째 이유로 빅 데이터를 들 수 있다. 대량으로 쏟아져 나오는 데이터들, 그리고 그것들을 수집하기 위한 노력 특히 SNS 사용자들에 의해 생산되는 다량의 자료와 태그정보들 모두가 종합되고 분석 되어 학습에 이용될 수 있다. - 인공신경망의 학습에 사용되는 트레이닝벡터는 이름이 붙어 있는(labeled) 데이터여야 하는데(supervised learning의 경우) 대량의 트레이닝셋 모두에 label을 달아주는 일은 불가능한 일이다. 이런 이유로 초기 학습에 사용되는 일부 데이터에 대해서만 지도학습(supervised learning)을 수행하고 나머지 트레이닝셋에 대해서는 비지도학습(unsupervised learning)을 진행하며, 학습된 결과는 기존 학습의 결과와 앞서 분석된 메타태그 정보들을 종합하여 인식기가 완성 된다.
딥 러닝의 부활 이후 다양한 분야, 특히 자동 음성 인식(ASR, automatic speech recognition)과 컴퓨터비전 분야에서 최고수준의 성능을 보여주고 있으며 이들은 보통 딥 러닝의 새로운 응용들의 지속적인 성능 향상을 위해 만들어진 TIMIT(Texas Instruments와 MIT가 제작한 음성 Database), MNIST(이미지 클러스터링을 위한 hand-written 숫자 이미지 데이터베이스로 National Institute of Standards and Technology가 제작) 등의 데이터베이스를 사용했다. 최근에는 Convolution Neural Networks 기반의 딥 러닝 알고리즘이 뛰어난 성능을 발휘하고 있으며, 컴퓨터비전과 음성인식등의 분야에서 특히 탁월한 성능을 보이고 있다.
' AS v_text 
),
-- 1차 가공
doc2 AS (
    SELECT 	id 
    ,		v_text         src_text
    		-- stopword 제거.
    ,		trim( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( v_text , ':', ' ') , '''', '') , ')', ' ') , '(', ' ') , '\r', ' '), '/', ' '), '!', ' '), '.', ' '), ',', ' '), '\n', ' '), '  ', ' ')) AS v_text
	FROM    cte_doc 
)       
SELECT 	t.id
,      	ROW_NUMBER() OVER(PARTITION BY t.id) n
,      	j.word
FROM   	doc2  AS t 
		join 
        json_table(
        		replace(json_array(t.v_text), ' ', '","'),
                '$[*]' columns (word varchar(100) path '$')
		) AS j

 

관련문서

* MySQL 8.0 :: JSON_ARRAY Function

* MySQL 8.0 :: JSON TABLE Function

 

 

https://unsplash.com/photos/iar-afB0QQw