[MySQL] 자주 쓰이는 함수 정리

1. 문자열 부분 가져오기 (LEFT, MID, RIGHT)

• LEFT: 문자에 왼쪽을 기준으로 일정 갯수를 가져오는 함수 ex) LEFT('A1000011', 2) = A1
• MID: 문자에 지정한 시작 위치를 기준으로 일정 갯수를 가져오는 함수 ex) `MID('ABCDE', 2, 4) = BCDE
• RIGHT: 문자에 오른쪽을 기준으로 일정 갯수를 가져오는 함수 ex) RIGHT('A1000011', 2) = 11

-- LEFT
LEFT(문자, 가져올 갯수)
-- MID
MID(문자, 시작위치, 가져올 갯수)
SUBSTR(문자, 시작위치, 가져올 갯수)
SUBSTRING(문자, 시작위치, 가져올 갯수)
-- RIGHT
RIGHT(문자, 가져올 함수)

 

2. 날짜 형식 설정 (DATE_FORMAT)

• DATE_FORMAT(날짜, 형식) : 날짜를 지정한 형식으로 출력
• STR_TO_DATE(문자열, 형식) : 문자열을 DATE나 DATETIME으로 변환
• DATE_FORMAT(날짜, 형식) : 날짜를 원하는 형식의 문자열로 출력
• DATE(DATETIME) : DATETIME에서 날짜만 추출

DATE_FORMAT("2025-10-24 12:23:22", "%Y-%m-%d") -- 2025-10-24
SELECT STR_TO_DATE('2026-03-27', '%Y-%m-%d');  -- 2026-03-27
SELECT STR_TO_DATE('2026-03-27', '%Y-%m-%d');  -- 2026-03-27
SELECT DATE('2026-03-27 14:30:00');   -- 2026-03-27

 

3. 날짜 차이 구하기(DATEDIFF)

• TIMESTAMPDIFF(unit, datetime1, datetime2) : 원하는 단위로 시간 차이 계산
  • 사용가능 단위: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR
• DATEDIFF(date1, date2) : 두 날짜 간 일(day) 차이만 계산. 단, 순서가 바뀌면 음수가 나온다

-- DATEDIFF
SELECT DATEDIFF('2024-01-10', '2024-01-01');  -- 일 차이, 결과: 9

-- TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10');  -- 일 차이, 결과: 9
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 10:00:00', '2024-01-01 15:00:00');  -- 시간 차이, 결과: 5
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2024-01-01'); -- 연도 차이, 결과: 4

 

4. 조건문

• IF(condition, true\_value, false\_value) : 간단한 조건 분기
• IFNULL(column, replacement) : NULL 처리
• COALESCE(value1, value2, value3, ...) : 여러 값 중에서 NULL이 아닌 첫 번째 값을 반환
• CASE ~ END : 복잡한 조건 분기

SELECT IF(pm10 >= 30, '나쁨', '보통') AS air_status FROM measurements;
SELECT IFNULL(pm10, 0) FROM measurements;  -- NULL이면 0으로 대체
COALESCE(pm10, pm25, 0) -- pm10있으면 사용, 없으면 pm25, 둘다 없으면 0
SELECT 
  CASE
    WHEN pm10 >= 80 THEN '매우 나쁨'
    WHEN pm10 >= 30 THEN '나쁨'
    ELSE '보통'
  END AS status
FROM measurements;

 

 

5. 문자열 처리(CONCAT)

• CONCAT(str1, str2, ...) : 여러 문자열을 하나로 이어 붙이는 함수. NULL 포함되면 결과도 NULL
• SUBSTRING(str, start, length) : 문자열의 특정 위치(start)부터 일정길이(length)만큼 자름. index는 1부터 시작
• SUBSTR(str, start, length) : SUBSTRING과 동일. 
• SUBSTRING_INDEX(str, delimiter, count) : 특정 구분자를 기준으로 문자열 자르기. count < 0이면 뒤에서부터(-1)

SELECT CONCAT('Hello', ' ', 'World');  -- Hello World
SELECT SUBSTRING('ABCDEFGHI', 2, 4);  -- BCDE
SELECT SUBSTR('ABCDEFGHI', 3, 2);  -- CD
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 2);   -- a, b

 

 

6. 서브 쿼리 CTE 방식(WITH 절)

• 서브 쿼리에서 이름을 붙여 재사용하는 방식
• 서브 쿼리가 길 때, 같은 결과를 여러 번 쓸 때, GROUP BY 결과를 다시 사용할 때 주로 사용
• MySQL 8.0이상만 지원한다.

WITH T AS (
    SELECT ...
)

-- 서브 쿼리에서 조회
SELECT * FROM T;

 

 

7. EXISTS vs IN

• IN : 값을 비교해 존재하는 값만 선택한다. NULL 포함 시 문제 발생
• EXISTS : 조건을 만족하는 행의 존재 여부 확인. NULL에 대한 영향이 없다.

WHERE ID IN (SELECT ID FROM TABLE2)  -- TABLE2에 존재하는 ID와 같은 값만 선택
WHERE EXISTS (
    SELECT 1 FROM TABLE2 WHERE 조건   -- 조건을 만족하는 행이 하나라도 존재하면 TRUE
)

 

 

8. Window 함수

• PARTITION으로 그룹을 나누고, 그 안에서 순서를 정해 함수를 실행한다.
  • ex) ROW_NUMBER() OVER (PARTITION BY CATEGORY) 이면, 카테고리 별로 순위를 따로 매기게 된다.

함수() OVER (
    PARTITION BY 컬럼
    ORDER BY 컬럼
)

 

1) 순위 매기기

• ROW_NUMBER() : 중복없이 순위 매기기
• RANK() : 공동 순위 존재
• DENSE_RANK() : 공동 순위 + 번호를 건너뛰지 않음

예시)

값	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
100	2	1	1
90	3	3	2

 

ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC);
RANK() OVER (ORDER BY PRICE DESC);
DENSE_RANK() OVER (ORDER BY PRICE DESC);

 

 

 

2) 이전 값 불러오기

• LAG : 이전(Previous) 행의 값 반환
  • 처음 이전 값은 NULL
  • 전날 대비 증가량 등을 구할 때 사용
• LEAD : 다음(Next) 행의 값 반환
  • 다음 값 혹은 미래 데이터 비교에 사용

# 구조
LAG  (컬럼, 옵셋, 기본값) OVER ([PARTITION BY 그룹핑_컬럼] ORDER BY 순서_컬럼)
LEAD (컬럼, 옵셋, 기본값) OVER ([PARTITION BY 그룹핑_컬럼] ORDER BY 순서_컬럼)

# 예시
LAG(pm10, 1, 0) OVER (ORDER BY measured_at)
-- pm10 기준 1번째 전 행 데이터 반환.
-- 첫날의 pm10에 대해 이전 기록이 없어 NULL 될 경우, 0을 반환
-- 정렬은 measured_at 오름차순 기준

 

 

예시) 하루 전 PM10값과 이틀 전 PM10 값을 오늘의 PM10값과 비교

WITH t AS (
  SELECT measured_at, station, pm10,
         LAG(pm10) OVER ( PARTITION BY station ORDER BY measured_at) AS prev1,
         LAG(pm10, 2) OVER ( PARTITION BY station ORDER BY measured_at) AS prev2
  FROM measurements
)

SELECT measured_at as date_alert FROM t
WHERE (pm10 > prev1 AND prev1 > prev2) AND pm10 >= 30
ORDER BY measured_at;

 

 

3) 누적 합

• SUM() OVER
• 누적 매출, 누적 방문자 구할 때 사용

SELECT 
    DATE,
    SALES,
    SUM(SALES) OVER (ORDER BY DATE) AS CUM_SUM
FROM SALES_TABLE;

 

 

DATE	SALES	CUM_SUM
1일	100	100
2일	200	300
3일	50	350

 

 

4) 이동 평균

• AVG() OVER 
• 최근 N개의 평균값 구할 때 주로 사용

-- 최근 3개의 평균
SELECT 
    DATE,
    SALES,
    AVG(SALES) OVER (
        ORDER BY DATE
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MOVING_AVG
FROM SALES_TABLE;

 

 

5) 그룹 내 처음 값 / 마지막 값

• FIRST_VALUE()
• LAST_VALUE()

SELECT 
    CATEGORY,
    PRICE,
    FIRST_VALUE(PRICE) OVER (
        PARTITION BY CATEGORY 
        ORDER BY PRICE DESC
    ) AS MAX_PRICE
FROM FOOD_PRODUCT;

 

 

6) 그룹 나누기

• NTILE(n) : n개의 그룹으로 나눔
• 상위 25%, 50% 이런 식으로 나눌 때 사용

SELECT 
    NAME,
    SCORE,
    NTILE(4) OVER (ORDER BY SCORE DESC) AS QUARTILE
FROM STUDENTS;

 

 

7) 개수 세기

• COUNT() OVER
• 행을 유지한 채로 갯수를 센다

SELECT 
    CATEGORY,
    COUNT(*) OVER (PARTITION BY CATEGORY) AS CNT
FROM FOOD_PRODUCT;

 

 

GROUP BY와의 차이

• 예시 데이터

CATEGORY	NAME
과자	A
과자	B
음료	C

 

• COUNT() OVER 사용 시

CATEGORY	CNT
과자	2
과자	2
음료	1

 

 

• GROUP BY 사용 시

CATEGORY	CNT
과자	2
음료	1