재밌는 코딩 공부

데이터베이스 작업을 하다 보면, 종종 여러 행에 있는 데이터를 하나의 문자열로 결합하여 표시해야 할 때가 있습니다. 예를 들어, 동일한 그룹의 모든 항목 이름을 하나의 열에 표시하고 싶을 때가 있습니다. MySQL에서 이 작업을 쉽게 수행할 수 있는 방법은 바로 GROUP_CONCAT 함수를 사용하는 것입니다.

GROUP_CONCAT 함수란?

GROUP_CONCAT 함수는 MySQL에서 제공하는 집계 함수 중 하나로, 그룹화된 데이터에서 여러 값을 하나의 문자열로 결합해 줍니다. 이 함수는 GROUP BY와 함께 사용되며, 여러 행에 걸쳐 있는 값을 하나의 결과로 만들어 줍니다. 결합된 값들은 기본적으로 쉼표(,)로 구분되지만, 다른 구분자를 사용할 수도 있습니다.

기본 사용법

GROUP_CONCAT 함수의 기본 구문은 다음과 같습니다:

GROUP_CONCAT(
  [DISTINCT] column_name 
  [ORDER BY column_name ASC|DESC] 
  [SEPARATOR 'separator_string']
  )

• DISTINCT: 중복된 값을 제거하고 싶을 때 사용합니다.
• ORDER BY: 결합할 값을 정렬할 때 사용합니다. 기본적으로 오름차순(ASC)으로 정렬됩니다.
• SEPARATOR: 결합된 값 사이에 사용할 구분자를 지정합니다. 기본값은 쉼표(,)입니다.

실습 예제: 클래스 이름 결합하기

이제 실제 예제를 통해 GROUP_CONCAT의 사용법을 살펴보겠습니다. 예를 들어, 특정 회사에서 제공하는 워크아웃 클래스의 이름을 하나의 열에 모아서 표시하고 싶다고 가정해 보겠습니다.

SELECT 
  ca.goods_id, 
  GROUP_CONCAT(DISTINCT wc.name ORDER BY wc.name SEPARATOR ', ') AS class_names 
FROM class_authority ca 
LEFT JOIN workout_class wc ON wc.id = ca.class_id 
WHERE wc.company_id = 296 
GROUP BY ca.goods_id;

쿼리 설명

• ca.goods_id: 클래스와 연결된 상품 ID입니다. 이 필드를 기준으로 그룹화하여 각 상품에 대한 결과를 하나의 행으로 표시합니다.
• GROUP_CONCAT(DISTINCT wc.name ORDER BY wc.name SEPARATOR ', '): 동일한 goods_id에 속하는 클래스 이름(wc.name)을 중복 제거 후 알파벳 순서대로 정렬하여 쉼표로 구분된 문자열로 결합합니다.
• LEFT JOIN workout_class wc ON wc.id = ca.class_id: class_authority 테이블과 workout_class 테이블을 조인하여 각 상품 ID에 해당하는 클래스를 가져옵니다.
• WHERE wc.company_id = 296: 특정 회사(예: company_id = 296)에 속하는 클래스만 선택합니다.
• GROUP BY ca.goods_id: goods_id를 기준으로 결과를 그룹화하여, 각 상품에 대해 하나의 행만 반환합니다.

결과 예시

위 쿼리를 실행하면 다음과 같은 결과를 얻을 수 있습니다:

여기서 goods_id = 101에 대한 클래스 이름이 "Yoga, Zumba, Pilates"로 결합되어 표시됩니다. 각 클래스 이름은 알파벳 순서로 정렬되어 쉼표로 구분되었습니다.

결론

GROUP_CONCAT 함수는 데이터를 집계하고 결합하는 데 매우 유용한 도구입니다. 이 함수는 특히 여러 값을 하나의 열에 결합하여 표시해야 할 때 유용하며, 데이터베이스 내에서 간단한 텍스트 조작을 할 수 있게 해줍니다. GROUP_CONCAT을 잘 활용하면 데이터베이스 쿼리를 더욱 강력하게 만들 수 있습니다.

이제 여러분도 GROUP_CONCAT을 사용하여 데이터를 그룹화하고, 필요에 따라 원하는 방식으로 결합해 보세요!

글쓴이의 경우 어떤 테이블에 있는 속성을 다른 곳의 테이블에 넣기 위해 해당 명령문으로 테이블의 속성들의 구체적인 값을 확인하였다.

select * from information_schema.columns
where table_name = '테이블 이름'

속성을 확인한 뒤 테이블에 속성을 추가하기 위해 해당 명령문을 사용했다.

alter table 테이블명 add 속성 varchar(6) NULL

위 명령문은 원하는 테이블에 varchar(6) 자료형을 가지며 널값을 허용하는 속성을 추가한다.

피벗(PIVOT)의 사전적 의미는 마찰을 적게 하기 위하여 회전하는 축의 지점 끝을 원뿔꼴로 한 것이다. 이 의미를 데이터베이스에서 나름 해석해보자면 테이블을 90도 회전 시켜 열을 행으로 바꿔버리는 그런 느낌의 의미라 할 수 있을 것이다. 

DECLARE @COL AS NVARCHAR(MAX)
DECLARE @QUERY AS NVARCHAR(MAX)


SELECT * FROM SALE 

SELECT DISTINCT
	@COL = STUFF((
		SELECT DISTINCT ',' + QUOTENAME(YYYYMM)
		FROM SALE
		FOR	XML PATH('')
	  ),1,1,'') 
FROM SALE 
SELECT @COL


SET @QUERY = 'SELECT * FROM 
(
SELECT  * FROM SALE
) AS RESULT 
PIVOT( SUM(AMT) FOR YYYYMM IN ('+@COL+'))AS pivot_result'

EXECUTE(@QUERY)

우선 아래와 같이 SALE 테이블을 만들어보자

다 만들었으면 아래와 같이 코드를 만들고 실행하여 보자

SELECT DISTINCT
	@COL = STUFF((
		SELECT DISTINCT ',' + QUOTENAME(YYYYMM)
		FROM SALE
		FOR	XML PATH('')
	  ),1,1,'') 
FROM SALE 
SELECT @COL

위 이미지와 같은 결과가 나타나게된다. 위의 결과를 자세히 살펴보면 YYYYMM 속성의 값들이 중복 없이 일열로 나열된 것을 확인할 수 있다. 이를 위해서 STUFF((쿼리문 FOR XML PATH('') ),1,1,'')을 활용하면 된다.  

STUFF에 관해 구체적으로 궁금하다면 아래 사이트를 참조하길 바란다. 

[MSSQL] STUFF와 FOR XML PATH를 활용한 문자열 합치기 (tistory.com)

그리고 아직 설명하지 않은 QUOTENAME()이 남아있다. QUOTENAME()이 어떤 기능을 가졌는지 이해하기 위해서 우선 QUOTENAME을 없애고 위의 코드를 실행시키면 아래와 같이 [] 대괄호가 사라진 것을 확인할 수 있다. 이 사실로 미루어 보아 QUOTENAME이 대괄호를 만들어주는 역할을 수행하는 것을 알 수 있지만 왜 필요한지 의문이 들 수 있다. 어느 블로그에서 확인한 바로는 피벗 테이블에서 속성으로 만들어주기 위해서는 MSSQL에서는 대괄호로 감싸져 있어야 한다고 한다.

이제 아래와 같이 피벗 테이블을 만들어주는 쿼리문을 작성하여 보자. 

SET @QUERY = 'SELECT * FROM 
(
SELECT  * FROM SALE
) AS RESULT 
PIVOT( SUM(AMT) FOR YYYYMM IN ('+@COL+'))AS pivot_result'

EXECUTE(@QUERY)

 피벗 테이블의 일반적인 구조는 아래와 같다. 

SELECT *
FROM (SELECT * FROM 피벗을 원하는 테이블) AS RESULT
PIVOT ( 그룹함수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값], ...) AS PIVOT_RESULT

일반 테이블을 피벗 테이블로 완성시킨 모습

PIVOT에 대해 추가적으로 학습하고 싶다면 아래의 블로그를 활용하길 바란다. 

[MSSQL] PIVOT 함수, 행을 열로 바꾸기 (크로스탭, 동적컬럼) (tistory.com)

https://coding-factory.tistory.com/

위 사이트 참조

ISNULL

- ISNULL은 반환 값이 NULL일 경우 설정된 값을 대신 반환하는 함수이다.

- ISNULL(칼럼, 칼럼이 NULL일 경우 대체할 값)

EX ) ISNULL(WSL.ORDERQTY, 0) - ISNULL(WSL.SHIPQTY, 0) AS NOTSHIPQTY

ISNULL을 사용하면 정의에서도 알 수 있드시 NULL값이 없어지게 된다.

IN

SELECT * FROM My_Table WHERE Age IN(20,24,26)

SELECT * FROM My_Table WHERE Age = 20 OR Age = 24 OR Age = 26

반대로 어떤 값에 해당사항이 없는 값을 불러오고 싶을 때는 NOT IN을 사용하면 된다.

참조하면 좋은 사이트

https://greatepig.tistory.com/19

PROCEDURE

프로지셔란 프로그래머가 생성해놓은 쿼리문을 마치 하나의 메서드 형식으로 관리된다.

기본적인 프로시져 형태 

CREATE PROCEDURE [프로시져명]
(
	@변수명 변수타입,
	@변수명 변수타입,
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

쿼리문

END;

변수명 앞에는 @을 사용해야 한다.

특징(사용자 정의 함수와 반대되는)

- 트랜잭션을 사용할 수 있다.

- SELECT, HAVING, WHERE 위에서 사용할 수 없다.

그 외 프로시져에서 자주 등장하는 구문

- SET NOCOUNT ON

  MSSQL에서 INSERT, UPDATE, 등을 사용하게 되면 영향받은 쿼리의 개수를 세어주는 기능이 있는데 이 기능은 딱히 의미 없는 기능이기 때문에 성능이 중요한 프로시져의 경우 SET NOCOUNT ON을 사용하여서 세어주는 기능을 사용하지 않을 수 있다.

- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITEED

ISOLATION LEVEL은 격리 수준으로 현재 READ UNCOMMITED을 사용하고 있다.

격리 수준은 4가지가 있고 아래로 갈수록 격리 수준이 강해진다.

- READ UNCOMMITTED

- READ COMMITTED

- REPEATABLE READ

- SERIALIZABLE

여기서 READ UNCOMMITTED 같은 경우에는 트랜잭션이 실행은 되었지만 커밋되지 않은 데이터도 읽겠다는 뜻이다.

이렇게 될 경우 정합성의 문제가 발생하거나 더티 리드 현상이 발생하게 될 수도 있다.

           SELECT
             WMS.COMPANYID        /*회사코드*/
            ,WMS.DIVISIONID       /*사업장코드*/
            ,WMS.SOID             /*수주번호*/
            ,WMS.CUSTOMERID       /*거래처코드*/
            ,WMC.CUSTOMERNAME     /*거래처명*/
            ,WMS.ORDERDATE        /*수주일자*/
            ,WMS.DELIVERYDATE     /*납기일자*/
            ,WML.PRICE         /*수주금액*/
            ,WML.SOLINENO         /*행번*/
            ,WML.ITEMID           /*아이템코드*/
            ,WMI.ITEMNAME         /*아이템명*/
            ,WML.ORDERQTY         /*수주수량*/
            ,WML.SHIPQTY          /*출고수량*/
            ,(WML.ORDERQTY - WML.SHIPQTY) AS NOTSHIPQTY /*미출고수량*/
            ,WML.SHIPSTATE        /*출고상태*/
            ,WMS.REMARK           /*설명*/
            ,WMS.ISVALID
            FROM      
			WM_SALESORDER     WMS
            INNER JOIN WM_SALESORDERLINE WML        ON WMS.SOID       = WML.SOID        AND WML.ISVALID = 'Y'
            LEFT  JOIN WM_CUSTOMERINFO   WMC        ON WMS.CUSTOMERID = WMC.CUSTOMERID  AND WMS.COMPANYID = WMC.COMPANYID  AND WMS.DIVISIONID = WMC.DIVISIONID
            LEFT  JOIN WM_ITEMINFO       WMI        ON WML.ITEMID     = WMI.ITEMID
            WHERE
            WMS.COMPANYID = 1000
            AND WMS.DIVISIONID = 1000
			AND WMS.ORDERDATE BETWEEN '2021-01-01' AND '2021-01-30'
			AND WMS.SOID LIKE '%SO%'

위 쿼리문에서 주의 깊게 봐야될 조건은 BETWEEN 조건 AND 조건 과, LIKE '%' 이다.

BETWEEN은 어떤 값보다는 작고 어떤 값보다는 큰 조건을 만들 때 편리하게 사용할 수 있다.

어떤 단어를 검색할 때 특정 키워드만 사용하고 싶으면 %를 활용하면 된다. 

'-' : 글자숫자를 정해줌(EX 컬럼명 LIKE '홍_동')

'%' : 글자숫자를 정해주지않음(EX 컬럼명 LIKE '홍%')

SELECT * FROM WM_SALESORDER WHERE SOID = 'SO2011120002'

BEGIN TRAN
DELETE FROM WM_SALESORDER WHERE SOID = 'SO2011120002'
ROLLBACK TRAN

SELECT * FROM WM_SALESORDER WHERE SOID = 'SO2011120002'

위 쿼리문은 트랜잭션을 실험해보기 위함이다. 

BEGIN TRAN이 실행된 뒤 다음에 실행되는 쿼리문을 ROLLBACK TRAN을 이용하여 되돌린 후 다시 SELECT문을

사용하여 DELETE되기 전으로 되돌아 갔는지 확인하였고 결과는 ROLLBACK이 성공적으로 이루어 졌다.

INNER JOIN LEFT OR RIGHT OUTER JOIN

https://m.blog.naver.com/PostView.nhn?blogId=wideeyed&logNo=221435077767&proxyReferer=https:%2F%2Fwww.google.com%2F

LEFT OUTER JOIN 과 LEFT JOIN은 같고 위에 WHERE 절이 빠지게 되면 교집합 부분도 포함하게 된다.