[SQL] 7편 MSSQL의 활용 - 프로시저 기본 + [통계] kr유저의 입출금 합계 리스트 2 in 1 posting

[SQL] 7편 MS-SQL의 활용 - 프로시저 기본

사실 6편까지 비개발자 SQL 통계뽑기 편들을 작성하며 더 심화과정으로 들어가게 되면 비개발자의 의미가 없는 것 같아서 작성하길 꺼려하고 있는 제게...

이렇게 강한 푸시를 주시니 ㅋㅋㅋ 7편을 작성해봅니다. 이번편부터는 비개발자 타이틀을 빼고 작성합니다. 프로시저부터는 비개발자분에겐 적합하지 않다고 생각이 듭니다. 그래도 @forhappywomen님처럼 적극적으로 배우시는 분이라면 충분히 따라올만한 내용일 것입니다. 저는 신입때부터 약 3년간 mssql을 써봤고 당시 주요 업무들의 처리가 거의 모두 프로시저로 되어있었기에 그때 프로시저를 많이 써봤습니다.

이 역시 구시대의 유물로 느껴지긴 합니다만... 지금은 오라클을 사용합니다. 프로시저는 몇몇 특수한 업무 외에는 사용하지 않게 되었구요. 물론 사이드 시스템들중 mssql을 사용하는 것도 있고 mysql과 tibero도 있습니다. ㅎㅎ 저도 오랜만에 인터넷 휘적휘적 보면서 작성하니 리마인드 되고 좋네요.

가장 최근에 프로시저를 업무에 사용한 것은 제작년엔가... 20년정도 된 시스템(as400인가...)에서 신규 오라클 시스템으로 데이터 마이그레이션 프로젝트를 진행했었습니다. 이 때 테이블 구조와 데이터 유형이 완전히 달라 정제 과정이 매우 복잡했었죠. 이 때 대부분의 처리를 프로시저로 만들어서 처리했었던 기억이 납니다.

프로시저는 쉽게 말하자면 N개의 쿼리를 모아서 한꺼번에 실행하는 겁니다. 지금까지 1개의 쿼리로 통계를 추출했지만 실제로 데이터 복잡도나 추출 계산 단계, 그리고 성능상의 이슈 등으로 1개의 쿼리로 해결이 불가능한 경우가 많습니다. 또 배치성의 대량 데이터 처리를 위해서 프로시저를 만들기도 하지요.

더 쉽게 이미지로 보여드리면... 요래 모여있음.

장점은?
IF문과 WHILE문 TRY~CATCH등의 완전한 언어의 문법을 구사할수 있기때문에 언어의 도움없이도 왠만한 처리는 스스로 가능합니다. 또한 프로시저를 만들어놓으면 SQL 문장들이 컴파일 되어 동작하기 때문에 속도에서도 우월성을 갖습니다. 보안적으로 봤을 때에도 가장 뛰어나다고 할 수 있습니다만...(밖에서 봤을 때 DB 구조를 알수 없음. )

단점은?
버뜨 프로시저의 작성법이 DBMS마다 다르고(Dependency) 프로그램 소스 파일과 같이 형상관리시스템이 되어있지 않기때문에 몇 몇 특별한 경우가 아니면 사용을 꺼리게 되는 것 같습니다. 더군다나 DB서버의 리소스가 가장 귀하기 때문에 최대한 다른 서버(일반적으로 was) 에 분산해서 처리를 해주는게 좋을것 같다는 생각입니다.

가장 간단한 프로시저를 만들어봅시다. 사용자 수를 출력하는 겁니다.

DECLARE @ROW_COUNT NUMERIC -- 값을 담는 변수 설정
SELECT @ROW_COUNT= COUNT(*) FROM ACCOUNTS (NOLOCK) -- 변수에 값을 담는다.
SELECT @ROW_COUNT AS ROW_COUNT -- 이렇게 값을 출력 가능.

사실 이렇게 간단한 것은 바로 출력하면 되겠지만 처리가 복잡할수록 이렇게 나눠서 처리할수 있게 되면 할수 있는 것이 많겟죠?

이런식으로 IF문을 써서 프로그래밍 처리도 가능하구요~

DECLARE @ROW_COUNT NUMERIC
SELECT @ROW_COUNT= COUNT(*) FROM ACCOUNTS (NOLOCK)
IF @ROW_COUNT > 470000
    SELECT @ROW_COUNT
ELSE 
    SELECT 'NOT ENOUGH'

가장 많이 사용하게 되는 이유 중 하나인 CURSOR LOOP에 대해서 예를 들어 말하자면 회사에서 고객 1만명에게 SMS를 발송해야 하는데 내용이 제각각 조금씩 다릅니다. 그리고 1회성 업무입니다. 이때 간편하게 만들어 이용할 수 있는게 프로시저가 되겠습니다.

이번에도 엄청나게 심플한 예제를 준비했습니다. segyepark님, asbear님, 그리고 저의 이름을 출력하는 프로시저입니다.

DECLARE @NAME VARCHAR(20) -- 변수정의
DECLARE account_cursor CURSOR FOR -- 커서 정의

-- 커서에 사용될 쿼리
SELECT 
    NAME 
FROM ACCOUNTS (NOLOCK)
WHERE NAME IN (
    'nhj12311','segyepark', 'asbear'
)

OPEN account_cursor -- 커서 열고
FETCH NEXT FROM account_cursor INTO @NAME -- 하나의 행 읽기

WHILE (@@FETCH_STATUS = 0 ) -- 행이 없을 때까지 반복
BEGIN
    PRINT 'NAME : ' + @NAME -- 이름 출력

    FETCH NEXT FROM account_cursor INTO @NAME -- 다음행 읽기
END
CLOSE account_cursor -- 커서 닫기
DEALLOCATE account_cursor -- 커서 메모리 해제

처음에는 그냥 이런 초간단 샘플을 가지고 필요한 부분을 바꿔가면서 사용하면 됩니다. 오늘은 와~ 쿼리로 프로그래밍을 할수 있구나. 이런식으로 하는구나~. 그리고 샘플까지 저장해서 필요할 때 활용할 수 있으면 굿~인것 같습니다.

프로시저 기본은 여기서 마치겠습니다. ^^

---

[통계] kr 유저의 입출금 합계 리스트

이제 지난시간에 말씀드렸던 kr 유저의 입출금 합산 리스트를 뽑아봅시다.

11월 kr 포스팅 유저의 스팀과 SBD 총 입출금 리스트

kr에서 활동하시는 분들은 이 리스트에 거의 다 있으실겁니다.(보팅만 하시는분 빼고) 어떤식으로 뽑았을까요?

-- 11월 1포스팅이상의유저리스트를 #TMP 임시 테이블에 담음.
 SELECT 
    C.AUTHOR AS AUTHOR INTO #TMP
 FROM COMMENTS C (NOLOCK) 
 WHERE CHARINDEX('KO', BODY_LANGUAGE) > 0 -- 한국어로 쓰인 포스팅
     AND C.PARENT_AUTHOR ='' --댓글 제외
     AND TITLE <>''
     AND C.CREATED BETWEEN '2017-11-01' AND '2017-11-30' --11월 1일 이후 작성된 글
  GROUP BY C.AUTHOR
 
 SELECT
    AUTHOR
    , ISNULL(SUM(SBD입금), 0) AS SBD입금
    , ISNULL(SUM(SBD출금), 0) AS SBD출금
    , ISNULL(SUM(STEEM입금), 0) AS STEEM입금
    , ISNULL(SUM(STEEM출금), 0) AS STEEM출금
 FROM (
     SELECT
         C.AUTHOR
         ,0 AS SBD입금
         ,(CASE WHEN T.[FROM]= C.AUTHOR AND T.AMOUNT_SYMBOL= 'SBD' THEN T.AMOUNT END) AS SBD출금
         ,0 AS STEEM입금
         ,(CASE WHEN T.[FROM]= C.AUTHOR AND T.AMOUNT_SYMBOL= 'STEEM' THEN T.AMOUNT END) AS STEEM출금
     FROM [DBO].[TXTRANSFERS] AS T (NOLOCK)
     INNER JOIN #TMP AS C
         ON (T.[FROM]= C.AUTHOR )
    
     WHERE 1=1
     AND [TYPE] = 'TRANSFER ' -- POWER UP한 사람은 제외 
     
     UNION ALL
     
     SELECT
         D.AUTHOR
         ,(CASE WHEN T.[TO]= D.AUTHOR AND T.AMOUNT_SYMBOL= 'SBD' THEN T.AMOUNT END) AS SBD입금
         ,0 AS SBD출금
         ,(CASE WHEN T.[TO]= D.AUTHOR AND T.AMOUNT_SYMBOL= 'STEEM' THEN T.AMOUNT END) AS STEEM입금
         ,0 AS STEEM출금
     FROM [DBO].[TXTRANSFERS] AS T (NOLOCK)
     INNER JOIN #TMP AS D
         ON (T.[TO]= D.AUTHOR ) 
     WHERE 1=1
     AND [TYPE] = 'TRANSFER ' -- POWER UP한 사람은 제외
    
 ) AS MERGE_T
 GROUP BY AUTHOR
 
 DROP TABLE #TMP -- 임시 테이블 삭제

네 or 조건을 쓰지않고 union all을 사용했습니다. or 조건을 사용하게 되는 경우 속도가 매우 느리기때문입니다. steemsql 같은 경우는 아예 오류가 나버리더군요. (메모리 부족인걸로 생각됩니다. )

제 계정의 입출금 내역과 비교해보니 얼추 맞는듯 했습니다. 각자 생각했던 입출금 금액의 규모와 실제 데이터와 비교해보니 제가 생각했던 수치와 달라서 느껴지는 게 다르더군요. 한번씩 자신의 데이터를 확인해보는것도 좋을것 같습니다. ^^

---

지난 회차 살펴보기

• 1편 - 데이터베이스 개념편
• 2편 - 백문이불여일견 일단 깔고 봅시다.-접속 프로그램 설치와 간단한 실행 편!
• 3편 - 본인 혹은 다른이의 포스팅 리스트 뽑아보기
• 4편 - 이젠 진짜 통계를 뽑아봅시다.
• 5편 - 좀 더 복잡한 통계에 도전해봅시다. Join편!
• 6편 - 검색을 잘하는 것도 실력이다!

---