재밌는 코딩 공부

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

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 절이 빠지게 되면 교집합 부분도 포함하게 된다.