(SQL Server) 고급 T-SQL 함수 활용 가이드: 핵심 요약 및 상세 예제

SQL Server에서 제공하는 고급 T-SQL 함수와 쿼리 구문들은 modern data engineering과 AI-driven analytics 환경에서 매우 중요한 역할을 합니다. 이 포스트에서는 STRING_AGG, STRING_SPLIT, FOR XML PATH, CURSOR, PARTITION BY, COUNT_BIG, DECLARE SET, WITH, CASE 등의 핵심 기능들을 practical examples와 함께 자세히 살펴보겠습니다.

 

요약: 이러한 함수들을 사용하면 얻을 수 있는 장점

• 데이터 집계와 문자열 처리 효율 극대화
• 복잡한 비즈니스 로직의 가독성 및 유지보수성 향상
• 대용량 데이터 처리 시 성능 최적화
• 계층형·분석 쿼리 구현 간소화

---

1. 문자열 집계 및 분할

1.1 STRING_AGG

• 설명: 여러 행의 값을 구분자(delimiter)로 결합
• 장점: 복잡한 FOR XML PATH 패턴 대체, 코드 간결화

  SELECT STRING_AGG(ProductName, ', ') AS Product목록
  FROM Products
  WHERE CategoryID = 1;

1.2 STRING_SPLIT

• 설명: 구분 문자열을 기준으로 분할해 테이블 반환
• 장점: CSV, 태그 목록 등 동적 파라미터 처리 간편

  DECLARE @Tags NVARCHAR(100) = 'AI,Data,BI';
  SELECT value AS Tag
  FROM STRING_SPLIT(@Tags, ',');

1.3 FOR XML PATH + STUFF

• 설명: 전통적 문자열 결합 방식
• 장점: SQL Server 2016 이전 버전 호환, 유연한 XML 인코딩 제어

  SELECT CategoryID,
  STUFF((
    SELECT ', ' + ProductName
    FROM Products p2
    WHERE p2.CategoryID = p1.CategoryID
    FOR XML PATH('')),1,2,'') AS 결합문자열
  FROM Products p1
  GROUP BY CategoryID;

반응형

---

2. 윈도우 함수 (Window Functions)

PARTITION BY

• 설명: 그룹 내 순위, 합계, 평균 등 계산
• 장점: 서브쿼리 없이 누적합, 순위 부여 가능

  SELECT EmployeeID,
       DepartmentID,
       Salary,
       ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS 순위,
       AVG(Salary) OVER(PARTITION BY DepartmentID) AS 부서평균
  FROM Employees;

---

3. 대용량 집계 함수

COUNT_BIG

• 설명: bigint 타입으로 대량 행 개수 집계
• 장점: 21억 건 초과 시 정확도 보장

  SELECT COUNT_BIG(*) AS 전체행수
  FROM LargeTable;

---

4. 제어 흐름 및 변수 관리

4.1 CURSOR

• 설명: 행 단위 처리용 커서
• 장점: 복잡한 순차 처리 로직 구현 가능

  DECLARE c CURSOR FOR SELECT CustomerID FROM Customers WHERE Active = 1;
  OPEN c;
  FETCH NEXT FROM c INTO @CustID;
  WHILE @@FETCH_STATUS = 0
  BEGIN
  EXEC ProcessOrder @CustID;
  FETCH NEXT FROM c INTO @CustID;
  END
  CLOSE c; DEALLOCATE c;

4.2 DECLARE & SET

• 설명: 변수 선언 및 값 할당
• 장점: 복잡한 쿼리 내 동적 값 관리

  DECLARE @StartDate DATE = '2025-01-01', @EndDate DATE;
  SET @EndDate = GETDATE();

---

5. 모듈화 쿼리: WITH (CTE)

• 설명: 복잡 쿼리 분할, 재귀 처리 가능
• 장점: 가독성·유지보수성 향상

  WITH MonthlySales AS (
  SELECT YEAR(SaleDate) AS Yr, MONTH(SaleDate) AS Mo, SUM(Amount) AS Total
  FROM Sales
  GROUP BY YEAR(SaleDate), MONTH(SaleDate)
  ),
  Growth AS (
  SELECT Yr, Mo, Total,
         LAG(Total) OVER(ORDER BY Yr, Mo) AS PrevTotal
  FROM MonthlySales
  )
  SELECT Yr, Mo, Total,
       CASE WHEN PrevTotal IS NULL THEN NULL
            ELSE (Total - PrevTotal)*100.0/PrevTotal END AS 성장율
  FROM Growth;

---

6. 조건 분기: CASE

• 설명: 다양한 조건에 따른 값 반환
• 장점: 복잡한 할인 정책·분류 로직 구현

  SELECT OrderID,
       CASE
         WHEN Amount >= 1000 THEN 'High'
         WHEN Amount >= 500 THEN 'Medium'
         ELSE 'Low'
       END AS OrderLevel
  FROM Orders;

---

최적화 팁

• 인덱스 활용: PARTITION BY, ORDER BY 컬럼에 복합 인덱스 생성
• 임시 테이블: CTE 과부하 시 물리 테이블 사용 검토
• NVARCHAR(MAX): 대용량 문자열 집계 시 제한 회피

이 가이드를 통해 효율적이고 유지보수성 높은 SQL Server 쿼리를 구현할 수 있습니다.