[독학! SQLP] 함수 정리!

집계 함수

• COUNT(*) : NULL 포함 전체 행 수, DISTINCT 키워드 사용 불가.
• COUNT(expr) : NULL 제외한 행 수
• SUM([DISTINCT | ALL] expr) : NULL 제외한 합계
• AVG([DISTINCT | ALL] expr) : NULL 제외한 평균
• MAX/MIN([DISTINCT | ALL] expr) : NULL 제외한 최대값 / 최소값. 숫자·문자·날짜 모두 가능.
• STDDEV(expr) : NULL 제외한 표준편차
• VARIANCE(expr) : NULL 제외한 분산

---

단일행 문자형 함수

• LOWER(str) / UPPER(str) / INITCAP(str)
  • 소문자 / 대문자 / 단어의 첫글자만 대문자

SELECT LOWER('SQLP')          -- sqlp : 소문자로 변환
     , UPPER('sqlp')          -- SQLP : 대문자로 변환 
     , INITCAP('sql expert')  -- Sql Expert : 단어의 첫글자만 대문자로 변환
  FROM DUAL;

• ASCII(str) / CHR(num) → 65 문자를 코드값으로
  • 문자를 코드값으로 / 코드값을 문자로

SELECT ASCII('A') -- 65 
     , CHR(65)    -- 'A'
  FROM DUAL;

• CONCAT(str1, str2)
  • 두개의 문자열 연결(2개만 가능)
  • 여러개의 문자열 연결은 '||' 사용

SELECT CONCAT('SQL', 'P')     -- SQLP
     , 'SQL' || 'P' || '2025' -- SQLP2025
  FROM DUAL;

• SUBSTR(str, pos, len) 
  
  • 문자열(str)을 특정위치(pos, 1 부터 시작)에서 시작해 길이(len)만큼 자른다.

SELECT SUBSTR('SQLP', 2, 2) -- 'QL'
  FROM DUAL;

• LENGTH(str)
  • 문자열의 글자 수를 구한다.

SELECT LENGTH('sqlp') -- 4
  FROM DUAL;

• INSTR(str, sub) → 5 (위치 반환) 
  • 문자열에서 특정 문자열의 시작위치 반환 (1부터 시작)

SELECT INSTR('SQL EXPERT', 'EX') -- 5
  FROM DUAL;

• LTRIM(str[, 제거 대상 문자열])
  • 문자열(str)의 좌측에서 공백 또는 제거 대상 문자열을 모두 제거한다. 

SELECT LTRIM('   SQLP   ') -- 'SQLP   '
  FROM DUAL;

SELECT LTRIM('123123SQLP', '123') -- 'SQLP'
  FROM DUAL;

• RTRIM(str[, 제거 대상 문자열])
  • 문자열(str)의 우측에서 공백 또는 제거 대상 문자열을 모두 제거한다. 

SELECT RTRIM('   SQLP   ') -- '   SQLP'
  FROM DUAL; 

SELECT RTRIM('SQLP123123', '123') -- 'SQLP'
  FROM DUAL;

• TRIM([LEADING | TRAILING | BOTH 제거 대상 문자열 FROM ] 문자열) → ‘SQL’  좌측 공백 혹은 특정 문자 제거
  • 문자열(str)의 앞 / 뒤 / 양쪽 모두에서 공백 또는 제거 대상 문자열을 모두 제거한다.

SELECT TRIM('   SQLP   ') -- 'SQLP'
  FROM DUAL; 

SELECT TRIM(LEADING '1' FROM '111SQLP111') -- 'SQLP111'
  FROM DUAL; 

SELECT TRIM(TRAILING '1' FROM '111SQLP111') -- '111SQLP'
  FROM DUAL; 

SELECT TRIM(BOTH '1' FROM '111SQLP111') -- 'SQLP'
  FROM DUAL;

• REPLACE(문자열, 찾을문자열 [, 바꿀문자열])
  
  • 찾을문자열만 지정 → 해당 문자열 삭제 효과
  • 바꿀문자열까지 지정 → 치환
  • 대소문자 구분함

-- 단순 치환
SELECT REPLACE('SQLP EXAM', 'EXAM', 'TEST') -- 결과: 'SQLP TEST'
  FROM DUAL;

-- 특정 문자열 제거
SELECT REPLACE('SQLP EXAM', 'EXAM') -- 결과: 'SQLP '   (EXAM 사라짐)
  FROM DUAL;

-- 여러 번 등장하는 경우 전부 치환
SELECT REPLACE('AAABBBCCCBBB', 'BBB', 'DDD') -- 결과: 'AAADDDCCCDDD'
  FROM DUAL;
  
SELECT REPLACE('SQLP', 'sql', 'ABC') -- 결과: 'SQLP'  (치환 안 됨, 대소문자 구분)
  FROM DUAL;

---

단일행 숫자형 함수

• ROUND(숫자, 자릿수)
  • 숫자를 해당 자릿수까지 반올림

SELECT ROUND(123.456, 2) -- 123.46
  FROM DUAL;

• TRUNC(숫자, 자릿수)
  • 숫자를 해당 자릿수아래로 버림

SELECT TRUNC(123.456, 2) -- 123.45
  FROM DUAL;

• MOD(m, n)
  • 숫자 m을 n으로 나눈 나머지
  • n(제수)은 0이면 에러

SELECT MOD(10, 3) -- 1
  FROM DUAL;

• CEIL(num) / FLOOR(num)
  • num 이상인 가장 작은 정수 (올림) / num 이하인 가장 큰 정수 (내림)

SELECT CEIL(10.1)  -- 11
     , FLOOR(10.9) -- 10
  FROM DUAL;

• ABS(num)
  • 절대값 반환.

SELECT ABD(-5) -- 5
  FROM DUAL;

---

단일행 날짜형 함수

• SYSDATE
  • OS 기준 현재 날짜

SELECT SYSDATE FROM DUAL;
-- 2025-08-19 18:35:22

• CURRENT_DATE, CURRENT_TIMESTAMP
  • "세션" 시간대 기준
  • 접속한 세션(Session)의 TIME ZONE 설정 기준으로 반환
  • 세션 타임존은 DB 접속할 때마다 다르게 설정할 수 있음
  • 전 세계 여러 지역에서 같은 DB에 접속할 경우 유용

SELECT CURRENT_DATE FROM DUAL;
-- 2025-08-19 18:35:22

SELECT CURRENT_TIMESTAMP FROM DUAL;
-- 19-AUG-25 06.35.22.123456 PM +09:00

• MONTHS_BETWEEN(d1, d2) : 개월 수 차이

SELECT MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, -3)) -- 3
  FROM DUAL;

• ADD_MONTHS(d, n) : 날짜에 n개월 더함

SELECT ADD_MONTHS(DATE '2025-08-19', 3) FROM DUAL;
-- 2025-11-19

SELECT ADD_MONTHS(DATE '2025-08-19', -2) FROM DUAL;
-- 2025-06-19

• NEXT_DAY(d, 요일) 
  • d 이후에 나오는 가장 가까운 해당 요일의 날짜 반환

SELECT NEXT_DAY(DATE '2025-08-19', 'FRIDAY') FROM DUAL;
-- 2025-08-22   (화요일 이후 첫 금요일)

• LAST_DAY(d) : 해당 달의 마지막 날짜

SELECT LAST_DAY(DATE '2025-08-19') FROM DUAL;
-- 2025-08-31

SELECT LAST_DAY(DATE '2025-02-10') FROM DUAL;
-- 2025-02-28   (윤년 아니면 28일, 윤년이면 29일)

• ROUND(date, ‘단위’)
  • 날짜를 지정한 단위로 반올림 (15일 기준)

SELECT ROUND(DATE '2025-08-19', 'MONTH') FROM DUAL;
-- 2025-09-01   (19일 → 15일 기준 반올림되어 9월 1일)

SELECT ROUND(DATE '2025-08-10', 'MONTH') FROM DUAL;
-- 2025-08-01   (10일 → 반올림 기준보다 작아서 8월 1일)

• TRUNC(date, ‘단위’)
  • 날짜를 지정한 단위로 잘라냄(버림)
  • 'MONTH' : 무조건 그 달의 1일
  • 'YEAR' : 무조건 그 해의 1월 1일

SELECT TRUNC(DATE '2025-08-19', 'MONTH') FROM DUAL;
-- 2025-08-01

SELECT TRUNC(DATE '2025-08-19', 'YEAR') FROM DUAL;
-- 2025-01-01

---

변환형 함수

• TO_CHAR(date/number[, format]) : 형식화 → 문자열 

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(1234, '9,999') FROM DUAL; -- '1,234'

• TO_DATE(str[, format]) : 문자열 → 날짜

SELECT TO_DATE('2025-08-19', 'YYYY-MM-DD') FROM DUAL;

• TO_NUMBER(str[, format]) : 문자열 → 숫자

SELECT TO_DATE('2025-08-19', 'YYYY-MM-DD') FROM DUAL;

---

NULL관련 함수

• NVL(표현식 1, 표현식 2) (Oracle) / ISNULL(표현식 1, 표현식 2) (SQL Server)
  • 표현식1이 NULL이면 표현식 2 반환, NULL이 아니면 표현식 1 반환

SELECT NVL(NULL, 'SQLP') -- SQLP
     , NVL('SQL', 'SQLP') -- SQL
  FROM DUAL;

• NULLIF(표현식 1, 표현식 2)
  • 두 값이 같으면 NULL, 다르면 표현식 1 반환.

SELECT NULLIF(10, 10) AS CASE1  -- NULL
     , NULLIF(10, 20) AS CASE2  -- 10
  FROM DUAL;

• COALESCE(표현식 1, 표현식 2, …)
  • 인수 중 처음으로 NULL이 아닌 값 반환.

SELECT COALESCE(NULL, NULL, 'A', 'B') AS RESULT1 -- A
     , COALESCE(NULL, 100, 200) AS RESULT2 -- 100
  FROM DUAL;

---

윈도우 함수

그룹 함수(집계 함수)와 달리, 전체 행을 그룹핑하지 않고 → 각 행별로 결과 반환

OVER() 절을 통해 “창(Window)”을 정의

 

기본문법은 아래와 같다.

함수명(...) OVER (
    PARTITION BY 컬럼
    ORDER BY 컬럼
    ROWS BETWEEN ...
)

 

1) 순위 함수

• ROW_NUMBER() : 동일 값 있어도 순번 1,2,3… (중복 없음)
• RANK() : 동일 값이면 같은 순위, 다음은 건너뜀 (1,1,3,4)
• DENSE_RANK() : 동일 값이면 같은 순위, 다음은 연속 (1,1,2,3)

2) 집계함수 + 윈도우

• SUM(), AVG(), MIN(), MAX(), COUNT()
• 누적합, 누적평균 등 가능

3) 행 위치 참조

• LAG(col, n, default) : 이전 n번째(기본값 1) 값, default는 참조할 이전 행이 없을때 노출 할 값 (기본 NULL)
• LEAD(col, n, default) : 다음 n번째(기본값 1) 값, default는 참조할 다음 행이 없을때 노출 할 값 (기본 NULL)

4) 비율  / 분석 함수

• NTILE(n) : 데이터를 n등분으로 나눠 그룹 배정
• RATIO_TO_REPORT(expr) : 전체 합 대비 비율
• CUME_DIST() : 누적 분포 (비율)
• PERCENT_RANK() : 백분위 순위
  

5) 프레임(Frame) 지정

• ROWS BETWEEN … AND …
• 분석 범위를 명시적으로 지정 가능
  • UNBOUNDED PRECEDING : 파티션 시작부터
  • CURRENT ROW : 현재 행
  • UNBOUNDED FOLLOWING : 파티션 끝까지

※ 윈도우 함수 전체 예시 데이터 및 SQL

WITH EMP(EMPNO, ENAME, DEPTNO, SAL) AS (
  SELECT 1,'SMITH', 20,  800 FROM DUAL UNION ALL
  SELECT 2,'MILLER',10, 1300 FROM DUAL UNION ALL
  SELECT 3,'CLARK', 10, 2450 FROM DUAL UNION ALL
  SELECT 4,'JONES', 20, 2975 FROM DUAL UNION ALL
  SELECT 5,'SCOTT', 20, 3000 FROM DUAL UNION ALL
  SELECT 6,'FORD',  20, 3000 FROM DUAL UNION ALL
  SELECT 7,'KING',  10, 5000 FROM DUAL
)
SELECT
  ENAME, DEPTNO, SAL

  -- 1) 순위
, ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RN
, RANK()       OVER(ORDER BY SAL DESC) AS RK
, DENSE_RANK() OVER(ORDER BY SAL DESC) AS DR

  -- 2) 누적합 (행 기준 Frame 지정)
, SUM(SAL) OVER(
    PARTITION BY DEPTNO
    ORDER BY SAL
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RUNNING_SUM

  -- 3) 이동 평균 (현재행 ±1행)
, AVG(SAL) OVER(
    ORDER BY SAL
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS MOVING_AVG

  -- 4) 전체 평균 (파티션 시작 ~ 끝까지, 사실상 AVG OVER()와 동일)
, AVG(SAL) OVER(
    PARTITION BY DEPTNO
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS AVG_FULL

  -- 5) 이전/다음 행
, LAG(SAL,1,0)  OVER(ORDER BY SAL) AS PREV_SAL
, LEAD(SAL,1,0) OVER(ORDER BY SAL) AS NEXT_SAL

  -- 6) 등분/비율/분포
, NTILE(4)             OVER(ORDER BY SAL) AS TILE_4
, RATIO_TO_REPORT(SAL) OVER()             AS SAL_RATIO
, CUME_DIST()          OVER(ORDER BY SAL) AS CUME
, PERCENT_RANK()       OVER(ORDER BY SAL) AS PCT_RNK

FROM EMP
ORDER BY SAL;