프로젝트를 수행하다 보면 사이트별로 다르긴 하겠지만 제조업회사들은 (중소, 중견) 주로 RDBMS(관계형 데이터베이스)는 MSSQL를 주로 사용한다. Oracle, PostgreSQL, DB2 등등 사용하는 사이트 들로 봐왔지만, 거의 대기업들은 ORACLE DB를 주로 사용한다.
현재 나는 MSSQL DB로 사용하는 사이트에서 프로젝트를 하고있는데, 실제 현업에서 사용하는 함수위주로 정리해보려고 한다.
1. 문자열 처리 함수 (품번, 바코드, 공정 코드 가공)
제조업 데이터는 품번(Item Code), 설비 코드, 바코드 등 규칙성 있는 문자열을 자르고 붙이는 작업이 핵심입니다.
◼️ LEFT / RIGHT / SUBSTRING (문자열 자르기)
코드의 특정 위치에 의미가 부여된 경우(예: 품번 앞 2자리는 제품군, 뒤 4자리는 일련번호)에 사용합니다.
-- 품번에서 앞 3자리 제품군 코드만 추출
SELECT LEFT(ITEM_CODE, 3) AS ItemGroup FROM ITEM_MASTER;
-- 바코드에서 5번째 글자부터 6자리 공정 코드 추출
SELECT SUBSTRING(BARCODE_NO, 5, 6) AS ProcessCode FROM BARCODE_INFO;
◼️ CONCAT / + 연산자 (문자열 합치기)
여러 컬럼의 값을 조합하여 UI에 보여주거나, 새로운 키(Key)를 생성할 때 사용합니다. CONCAT 함수는 NULL 값이 섞여 있어도 에러 없이 빈 문자열로 처리해 주어 안전합니다.
-- 공정코드와 작업지시번호를 조합하여 유니크한 Key 생성
SELECT CONCAT(PROC_CODE, '-', WORK_ORDER_NO) AS TargetKey FROM PROD_RESULT;
◼️ FORMAT / STRING_PAD (자리수 맞추기)
순번(Sequence) 데이터 앞에 '0'을 채워 고정된 길이의 코드를 만들 때 유용합니다.
-- 5를 '00005' 형태로 5자리 포맷팅
SELECT FORMAT(5, '00000') AS SerialNo;
-- (MSSQL 2022 이상) 오른쪽/왼쪽 패딩
SELECT LPAD('123', 5, '0') AS PadResult; -- 결과: 00123
2. 날짜 및 시간 함수 (생산 실적, 작업 트래킹의 핵심)
제조 공정 데이터에서 '시간'은 생산성을 측정하는 가장 중요한 척도입니다. 설비 가동 시간, 교대 근무(Shift) 기준, 일단위/월단위 실적 집계에 필수적입니다.
◼️ GETDATE() (현재 시간 구하기)
실적 데이터가 입력된 시점(Create Date)을 기록할 때 기본적으로 사용됩니다.
INSERT INTO PROD_HISTORY (ITEM_CODE, PROD_QTY, CREATE_DT)
VALUES ('ITEM-A', 150, GETDATE());
◼️ DATEADD (날짜/시간 더하고 빼기)
특정 시점으로부터 전후 시간을 계산합니다. (예: 1시간 전 가동률 계산, 3달 전 자재 재고 조회 등)
-- 현재 시간에서 1일(하루) 전 날짜 구하기
SELECT DATEADD(day, -1, GETDATE()) AS Yesterday;
-- 설비 시작 시간에서 30분 뒤 종료 시간 예측
SELECT DATEADD(minute, 30, START_DT) AS ExpectedEndDT FROM EQUIP_LOG;
◼️ DATEDIFF (시간 차이 계산하기)
설비의 비가동 시간(Downtime), Cycle Time, 또는 작업 소요 시간을 계산할 때 뼈대가 되는 함수입니다.
-- 작업 시작 시간과 종료 시간의 차이를 '분(Minute)' 단위로 계산
SELECT DATEDIFF(minute, START_DT, END_DT) AS LeadTime_Min FROM WORK_ORDER;
◼️ CONVERT (날짜 포맷 변경)
시·분·초가 포함된 시간 데이터에서 날짜만 추출하여 실적을 일단위로 묶을 때(GROUP BY) 가장 많이 씁니다.
-- 'YYYY-MM-DD' 형태로 변환 (스타일 코드 23번은 실무 암기 필수!)
SELECT CONVERT(VARCHAR(10), GETDATE(), 23) AS DailyDate;
-- 집계 쿼리 예시
SELECT CONVERT(VARCHAR(10), PROD_DT, 23) AS ProdDate, SUM(PROD_QTY)
FROM PROD_RESULT
GROUP BY CONVERT(VARCHAR(10), PROD_DT, 23);
3. 조건 및 NULL 처리 함수 (예외 처리와 데이터 정제)
현장 데이터는 설비 센서 오작동이나 작업자 실수로 인해 NULL 값이 빈번하게 발생합니다. 이를 깔끔하게 정제해 주어야 시스템 에러를 막을 수 있습니다.
◼️ ISNULL / COALESCE (NULL 값 대체)
실적 수량이 NULL로 들어올 경우 0으로 치환하여 연산 오류를 방지합니다.
-- 양품 수량이 NULL이면 0으로 처리하여 총 수량 계산
SELECT ISNULL(GOOD_QTY, 0) + ISNULL(REJECT_QTY, 0) AS TotalQty FROM PROD_RESULT;
◼️ CASE WHEN (분기 조건문)
품질 검사 결과 값에 따라 '합격/불합격' 판정을 내리거나, 생산 수량에 따라 달성률 구간을 나눌 때 사용합니다.
CASE문은 정말 자주 사용하는 함수이다. 사용법을 제대로 익히면 쿼리 작성시 정말 유용하게 사용할수 있는 함수이니 필히 기억하고 있자.
-- 검사 수치에 따른 합격 여부 판정 (예: LSL 기준)
SELECT INSP_ITEM,
CASE WHEN INSP_VALUE >= 99.2 THEN 'PASS'
ELSE 'FAIL'
END AS ResultStatus
FROM QUALITY_INSP_LOG;
4. 고급 분석 및 통계 함수 (SPC 및 트렌드 분석)
스마트 팩토리와 통계적 공정 관리(SPC)가 중요해지면서, raw 데이터를 분석용 데이터로 가공할 때 자주 쓰이는 함수들입니다.
◼️ ROW_NUMBER() OVER (순번 매기기 / 최신 데이터 추출)
설비별로 가장 최근에 발생한 알람(Alarm)이나, 품목별 최종 변경된 단가 등 "그룹별 최신 데이터 1건"을 뽑아낼 때 치트키처럼 사용됩니다.
-- 설비별로 가장 최근 발생한 이벤트 1건 조회
SELECT * FROM (
SELECT EQUIP_CD, EVENT_MSG, CREATE_DT,
ROW_NUMBER() OVER (PARTITION BY EQUIP_CD ORDER BY CREATE_DT DESC) AS RowNum
FROM EQUIP_EVENT_LOG
) A WHERE A.RowNum = 1;
◼️ STDEV / AVG (표준편차와 평균 - Cp, Cpk 계산용)
공정 능력 지수(Cp, Cpk) 계산이나 3-Sigma 관리 한계선(UCL, LSL)을 산출하여 품질 트렌드 차트를 그릴 때 기초 데이터 가공용으로 활용됩니다.
-- 특정 공정의 평균 수치와 표준편차 산출
SELECT PROCESS_CD,
AVG(MEASURE_VAL) AS AvgValue,
STDEV(MEASURE_VAL) AS StdDevValue
FROM QUALITY_DATA
GROUP BY PROCESS_CD;
TIP.
제조업 현장 DB는 실시간으로 수많은 설비 데이터와 바코드 태깅 데이터가 밀려들기 때문에 조회 성능(Performance)이 매우 중요하다는걸 느낀다.
특히 WHERE 절이나 JOIN 조건절에서 컬럼을 CONVERT나 SUBSTRING 함수로 감싸서 비교하게 되면, 해당 컬럼의 인덱스(Index)를 타지 못하고 Full Table Scan이 발생하여 시스템이 초반에는 빠르겠지만 데이터가 어느정도 적재가 되면 느려지는 현상을 마주치게 된다. 가능하면 조건절 우변(비교 대상 값)을 가공하는 습관을 들이는 것이 좋다.
댓글 쓰기