금백조의 개발 블로그

[세 자리 콤마 처리] DECLARE @NUM NCHAR(06) SET @NUM = '20000' SELECT @NUM [BEFORE], FORMAT(CONVERT(NUMERIC,@NUM), N'#,0') [AFTER] [실행 결과] [해석] 문자열 숫자를 NUMERIC으로 변환 후 FORMAT 함수를 이용하여 세 자리 콤마 숫자 문자열 반환 [세 자리 콤마 삭제] DECLARE @NUM NCHAR(06) SET @NUM = '20,000' SELECT @NUM [BEFORE], CASE WHEN ISNUMERIC(@NUM)=1 THEN PARSE(@NUM AS decimal) ELSE @NUM END [AFTER] [실행 결과] [해석] ISNUMERIC으로 문자열이 숫자인지 판단(맞으면 1, 틀리면 ..

서론 회사에서 개발을 하면서 프로시저로 많은 비즈니스 로직을 구현했습니다. 그동안 프로시저를 사용해본 경험을 기반으로 장단점, 사용 예시에 대해 정리해볼까 합니다. 장단점 같은 경우는 네이버 기술 블로그 중 [백엔드 개발자를 꿈꾸는 학생개발자에게] 글에서 설명하는 내용을 참고하여 공감되는 부분 위주로 작성했습니다. (아래 링크 참고) 본론 저장 프로시저의 장점 1) 프로시저만 수정이 필요할 경우 애플리케이션을 배포하지 않고 프로시저만 배포하면 됩니다. → 즉 어플리케이션 코드 내에 SQL 로직이 포함되었을 경우 애플리케이션도 재배포 해야하지만 프로시저 내에 포함될 경우 프로시저만 수정하여 배포하면 됩니다. 2) 자연스럽게 프로시저에 인자를 추가하여 바인드 변수를 사용하게 되므로 SQL 하드 파싱을 걱정할..

서론 프로젝트 개발 진행 중 SQL 속도 개선 문의를 받았습니다. 원인을 파악해보니 기존에 개발된 SQL문이 품목 이동 이력 테이블(ITEM_MOVEMENT)을 조회하는데 데이터가 천만 건 이상 존재하여 속도가 느리다는 것이었습니다. 오늘은 이 개선 사례에 대해 좀 더 간소화된 예시로 다듬어 정리해보려고 합니다. 개발 환경 - MSSQL 본론 문제의 원인을 파악하기 위해 실행계획을 살펴보니... 품목 이동 이력 테이블(ITEM_MOVEMENT)에서 매번 테이블 FULL스캔으로 천만 건 이상을 조회하여 이동 수량(MV_QTY)을 집계하는 게 원인이었습니다. 프로그램 화면에서 조건을 품목코드(ITEM_CODE), FROM, TO 이동일(MOVEMENT_DT)을 필수 입력으로 받았지만 해당 조건들이 데이터를 ..

서론 데이터를 집계할 때 특정 데이터를 제외하고 집계를 해야 할 경우가 있습니다. 특히 AVG를 사용한 평균 집계인 경우 0을 제외하거나 NULL을 포함하여 평균을 구해달라는 요구사항을 종종 요청받을 때가 있습니다. 오늘은 이에 대한 내용을 포스팅하겠습니다. 본론 집계함수는 NULL 데이터를 제외하고 집계를 수행합니다. 따라서 아래 예시와 같이 CASE문을 사용하여 제외시킬 데이터를 NULL로 변환시키면 이를 제외하고 집계를 수행하게 됩니다. [예제] --테스트할 임시테이블 및 데이터 생성 SELECT 'ALICE' [NAME], 100 [SCORE] INTO #TEMP_TABLE UNION ALL SELECT 'BOB' , 0 UNION ALL SELECT 'SAM' , 30 UNION ALL SELE..

서론 리오더 프로젝트를 진행하다가 이력관리 테이블에서 스칼라 서브 쿼리로 각각의 날짜 컬럼을 조회하는 쿼리를 발견한 적이 있습니다. 같은 테이블에 반복적으로 액세스를 하게 되므로 비효율적이고 이후에 조회 컬럼이 추가되면 테이블 액세스 반복 횟수는 점점 증가하므로 개선을 해보고 싶었습니다. 이를 튜닝하는 방법을 [친절한 SQL 튜닝] 책에서 봤던 기억이 나 공부했던 내용을 토대로 실제 프로젝트에 적용하여 개선했습니다. 이 과정에서 다시 한번 복습하게 된 스칼라 서브 쿼리 튜닝 방법을 이번 포스팅에서 소개해보려고 합니다. 실행환경 SQL Server 13.0 [예제 테스트 데이터 생성 SQL] --테이블 생성 --회원 테이블 CREATE TABLE MEMBER( MEMBER_NO INT--회원번호 ,MEMB..

서론 FOR XML PATH와 STUFF를 이용하여 행 문자열들을 하나의 문자로 합치고 각 문자 사이에 개행 문자를 추가하려는 중 개행 문자 일부인 CHAR(13) 문자열이 문자열로 변환되는 현상이 나타났습니다. 이를 문자열 치환을 통해 해결한 방법을 소개하려 합니다. 실행환경 MSSQL 13.0 - CHAR(13) 문자열이 로 바뀌는 현상 발생 MSSQL 15.0 - CHAR(13) 문자열이 로 바뀌는 현상 발생하지 않음. 정상 작동 확인 본론 [상황] FOR XML PATH, STUFF를 이용하여 문자열 합치기를 수행 중 CHAR(13) 문자열이 문자열로 변환됨. SELECT STUFF((SELECT ',' + EMP_NO+'('+EMP_NM+')' FROM( SELECT 'E0001' EMP_NO,..

서론 예전부터 계층형 쿼리 개념에 대해 정리하려고 했었는데 SQLP 시험 문제 중 계층형 쿼리 문제를 마주친 계기로 포스팅했습니다. 계층형 쿼리 문법 개념과 예제, 실행 결과 중심으로 정리해보았습니다. 본론 [개념 정리] 계층형 쿼리 : 부모, 자식 간의 수직관계를 트리 구조 형태로 보여주는 쿼리 START WITH : 트리 구조의 최상위 행을 지정합니다. CONNECT BY : 부모, 자식의 관계를 지정합니다. PRIOR : CONNECT BY 절에 사용되며 PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾아갑니다. CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼 : 부모 → 자식 순방향 전개 CONNECT BY PRIOR 부모 컬럼 = 자식 컬럼 : 자식 → 부모 역방향 전개 ORDER SIBLIN..

서론 개발 요구 사항 중 기준일자 별로 변경된 품목의 설계서를 비교해야 하는 요구 사항이 있었습니다. 예를 들어 A품목이 있다면 2021-01-01 일자의 A품목의 부품은 B1, B2, C1이었는데 2021-07-17 일자의 A품목 부품은 B1, B3, C3로 변경될 수 있습니다. 이 경우 아래와 같이 부품의 순서는 유지하면서 추가, 삭제된 부품엔 빈 행을 추가하여 데이터를 조회해야 했습니다. 저는 두 데이터를 비교하여 빈행을 추가시키기 위해 FULL OUTER JOIN을 사용하여 해결했습니다. 따라서 이번 포스팅에서는 위 과정 속에서 알게된 FULL OUTER JOIN의 개념과 예제에 대해 정리해보겠습니다. 본론 [구문 형식] SELECT ... FROM T1 FULL OUTER JOIN T2 ON T..