[SQL] 코딩 테스트 대비 함수 정리

SELECT

# 전체 출력하기
SELECT * 
FROM table_name;

# 컬럼명 지정하여 출력하기
SELECT column1, column2, ...
FROM table_name;

DISTINCT

# 중복 행 제거하고 출력하기
SELECT DISTINCT column1, column2, ...
FROM table_name;

WHERE

# 조건 지정하기
SELECT column1, column2, ...
FROM table_name
WHERE condition;

# 조건 여러 개 지정하기 1 (IN)
SELECT column1, column2, ...
FROM table_name
WHERE column1 IN ('condition1', 'condition2');

# 조건 여러 개 지정하기 2 (NOT IN)
SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT IN ('condition1', 'condition2');

LIKE

• % : 0개 이상의 모든 문자를 대체
• _ : 1개 이상의 모든 문자를 대체

# a로 시작하는 모든 값 찾기
SELECT *
FROM table_name
WHERE column1 LIKE 'a%';

# a로 끝나는 모든 값 찾기
SELECT *
FROM table_name
WHERE column1 LIKE '%a';

# a가 포함된 모든 값 찾기
SELECT *
FROM table_name
WHERE column1 LIKE '%a%';

# 두 번째 자리에 a가 있는 모든 값 찾기
SELECT *
FROM table_name
WHERE column1 LIKE '_a%';

# a로 시작하고 길이가 2글자 이상인 값 찾기
SELECT *
FROM table_name
WHERE column1 LIKE 'a_%';

BETWEEN

# 범위 조건 지정하기
SELECT * 
FROM table_name
WHERE column1 BETWEEN 50 AND 60;

Sub Query

# SELECT 절 결과를 쿼리로 사용하기
SELECT column2
FROM table_name
WHERE column1 IN (SELECT column1
                  FROM table_name
                  WHERE column1 = 'column1');

WITH

# 임시 테이블 만들어 사용하기
WITH new_table AS (
    SELECT column1
    FROM table_name
    WHERE column1 = 'column1'
)

SELECT column1
FROM new_table;

EXISTS

# 하위 쿼리가 하나 이상의 행을 반환하는지 확인하기
SELECT *
FROM table_name1
WHERE EXISTS (SELECT *
              FROM table_name2
              WHERE table_name1.column1 = table_name2.column2);

ANY

# 하위 쿼리가 하나라도 조건을 충족하는지 확인하기
SELECT *
FROM table_name1
WHERE colunm1 = ANY (SELECT column1
                     FROM table_name2);

ALL

# 하위 쿼리가 모두 조건을 충족하는지 확인하기
SELECT *
FROM table_name1
WHERE colunm1 = ALL (SELECT column1
                     FROM table_name2);

JOIN

# 두 테이블에서 일치하는 값이 있는 행을 반환하는 INNER JOIN
SELECT *
FROM table_name1
INNER JOIN table_name2 ON table_name1.column1 = table_name2.column1;

# 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환하는 LEFT JOIN
SELECT *
FROM table_name1
LEFT JOIN table_name2 ON table_name1.column1 = table_name2.column1;

# 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하는 행을 반환하는 RIGHT JOIN
SELECT *
FROM table_name1
LEFT JOIN table_name2 ON table_name1.column1 = table_name2.column1;

# 왼쪽 또는 오른쪽 테이블에 일치하는 항목이 있는 모든 행을 반환하는 OUTER JOIN
SELECT *
FROM table_name1
OUTER JOIN table_name2 ON table_name1.column1 = table_name2.column1;

GROUP BY

# 그룹 별 컬럼 출력하기
SELECT *
FROM table_name
GROUP BY column1;

HAVING

# 그룹 별 조건 지정하기
SELECT *
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 5;

ORDER BY

# 오름차순 정렬하기
SELECT * 
FROM table_name
ORDER BY column1;

SELECT * 
FROM table_name
ORDER BY column1 ASC;

# 내림차순 정렬하기
SELECT * 
FROM table_name
ORDER BY column1 DESC;

# 여러 열 기준으로 정렬하기
SELECT * 
FROM table_name
ORDER BY column1 ASC, column2 DESC;

LIMIT

# 반환할 행 수 지정하기
SELECT * 
FROM table_name
LIMIT 3;

UNION

# 결과 집합을 결합하기
SELECT *
FROM table_name1
WHERE column1 = 'A'
UNION
SELECT *
FROM table_name2
WHERE column1 = 'B'

# 결과 집합에서 중복을 허용하여 결합하기
SELECT *
FROM table_name1
WHERE column1 = 'A'
UNION ALL
SELECT *
FROM table_name2
WHERE column1 = 'B'

MAX

# 컬럼 별 최댓값 구하기
SELECT MAX(column1)
FROM table_name;

MIN

# 컬럼 별 최솟값 구하기
SELECT MIN(column1)
FROM table_name;

SUM

# 컬럼 합계 구하기
SELECT SUM(column1)
FROM table_name;

# 누적 합계 구하기
SELECT column1, SUM() OVER (ORDER BY column2 DESC) AS '누적합'
FROM table_name;

# 파티션 적용하기
SELECT column1, SUM() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS '파티션 별 누적합'
FROM table_name;

AVG

# 컬럼 평균 구하기
SELECT AVG(column1)
FROM table_name;

COUNT

# 행 개수 출력하기
SELECT COUNT(column1)
FROM table_name;

NULL

# 행이 NULL인 경우 찾기
SELECT column1
FROM table_name
WHERE column1 IS NULL;

# 행이 NULL이 아닌 경우 찾기
SELECT column1
FROM table_name
WHERE column1 IS NOT NULL;

IFNULL

# 컬럼의 값이 NULL일 때 다른 값을 대체하여 리턴하기
SELECT IFNULL(column1, 'Zero')
FROM table_name;

COALESCE

# 컬럼의 값이 NULL일 때 다른 값을 대체하여 리턴하기
SELECT COALESCE(column1, 'Zero', 'ZZero', 'ZZZero')
FROM table_name;

IF

# 조건이 참일 경우와 거짓일 경우에 따라 반환하기
SELECT IF(column1 > 5, '크다', '작다') AS result
FROM table_name;

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

# 년도 출력하기
SELECT YEAR(column1)
FROM table_name;

# 월 출력하기
SELECT MONTH(column1)
FROM table_name;

# 일 출력하기
SELECT DAY(column1)
FROM table_name;

# 시간 출력하기
SELECT HOUR(column1)
FROM table_name;

# 분 출력하기
SELECT MINUTE(column1)
FROM table_name;

# 초 출력하기
SELECT SECOND(column1)
FROM table_name;

CASE

# 조건에 따라 결과 반환하기, 조건이 모두 일치하지 않으면 ELSE 결과 반환하기
SELECT column1
CASE
    WHEN column1 > 30 THEN 'The column1 is greater than 30'
    WHEN column1 = 30 THEN 'The column1 is 30'
    ELSE 'The column1 is under 30'
END AS Column1Text
FROM table_name;

DATA_FORMAT

# Data String을 연도-월-일 형식으로 바꾸기 (2024-03-22)
SELECT DATA_FORMAT(column1, '%Y-%m-%d')
FROM table_name;

# Data String을 연도 4자리 형식으로 바꾸기 (2024)
SELECT DATA_FORMAT(column1, '%Y')
FROM table_name;

# Data String을 연도 2자리 형식으로 바꾸기 (24)
SELECT DATA_FORMAT(column1, '%y')
FROM table_name;

# Data String을 긴 월 형식으로 바꾸기 (July)
SELECT DATA_FORMAT(column1, '%M')
FROM table_name;

# Data String을 짧은 월 형식으로 바꾸기 (Jul)
SELECT DATA_FORMAT(column1, '%b')
FROM table_name;

# Data String을 긴 요일 이름 형식으로 바꾸기 (Monday)
SELECT DATA_FORMAT(column1, '%W')
FROM table_name;

# Data String을 짧은 요일 이름 형식으로 바꾸기 (Mon)
SELECT DATA_FORMAT(column1, '%w')
FROM table_name;

# Data String을 분 형식으로 바꾸기
SELECT DATA_FORMAT(column1, '%i')
FROM table_name;

# Data String을 시간:분:초 형식으로 바꾸기 (12:55:13)
SELECT DATA_FORMAT(column1, '%T')
FROM table_name;

# Data String을 월 두 자리 형식으로 바꾸기 (03)
SELECT DATA_FORMAT(column1, '%m')
FROM table_name;

# Data String을 월 한 자리 형식으로 바꾸기 (3)
SELECT DATA_FORMAT(column1, '%c')
FROM table_name;

# Data String을 일자 두 자리 형식으로 바꾸기 (05)
SELECT DATA_FORMAT(column1, '%d')
FROM table_name;

# Data String을 일자 한 자리 형식으로 바꾸기 (5)
SELECT DATA_FORMAT(column1, '%e')
FROM table_name;

# Data String을 시간 12시간 단위 형식으로 바꾸기 (1)
SELECT DATA_FORMAT(column1, '%I')
FROM table_name;

# Data String을 시간 24시간 단위 형식으로 바꾸기 (13)
SELECT DATA_FORMAT(column1, '%H')
FROM table_name;

# Data String을 시간:분:초 오전/오후 형식으로 바꾸기 (12:55:13 AM)
SELECT DATA_FORMAT(column1, '%r')
FROM table_name;

# Data String을 초 형식으로 바꾸기
SELECT DATA_FORMAT(column1, '%s')
FROM table_name;

CELING

# 올림하여 반환하기 (13)
SELECT CEILING(12.3);

ROUND

# 반올림하여 반환하기 (13)
SELECT ROUND(12.9);

FLOOR

# 내림하여 반환하기 (12)
SELECT CEILING(12.9);

UPPER

# 대문자로 변환하여 반환하기 (APPLE)
SELECT UPPER('apple');

LOWER

# 소문자로 변환하여 반환하기 (apple)
SELECT LOWER('APPLE');

RANK

# 순위 값 중 동등 순위 번호를 같게 나오고 그 다음 순위를 다음 번호를 뺀 그 다음 값을 반환하기 (1 1 3)
SELECT column1, RANK() OVER (ORDER BY column2 DESC) AS '등수'
FROM table_name;

# 파티션 적용하기
SELECT column1, RANK() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS '파티션 별 등수'
FROM table_name;

DENSE RANK

# 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 다음 번호로 반환하기 (1 1 2)
SELECT column1, DENSE_RANK() OVER (ORDER BY column2 DESC) AS '등수'
FROM table_name;

# 파티션 적용하기
SELECT column1, DENSE_RANK() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS '파티션 별 등수'
FROM table_name;

ROW NUMBER

# 동등 순위를 인식하지 않고 매번 증가되는 번호를 반환하기 (1 2 3)
SELECT column1, ROW_NUMBER() OVER (ORDER BY column2 DESC) AS '등수'
FROM table_name;

# 파티션 적용하기
SELECT column1, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS '파티션 별 등수'
FROM table_name;

LEAD

# 지정된 칼럼 이전의 행 값을 반환하기
SELECT column1, LEAD(column1, 1) OVER (ORDER BY column2 DESC) AS 'next_column'
FROM table_name;

# 파티션 적용하기
SELECT column1, LEAD(column1, 1) OVER (PARTITION BY column1 ORDER BY column2 DESC) AS 'next_column'
FROM table_name;

LAG

# 지정된 칼럼 이후의 행 값을 반환
SELECT column1, LAG(column1, 1) OVER (ORDER BY column2 DESC) AS 'prev_column'
FROM table_name;

# 파티션 적용하기
SELECT column1, LAG(column1, 1) OVER (PARTITION BY column1 ORDER BY column2 DESC) AS 'prev_column'
FROM table_name;

FIRST_VALUE

# 각 그룹 별 첫 번째 값 하나만 반환하기
SELECT column1, FIRST_VALUE(column1) OVER (ORDER BY column2 DESC) AS 'first_value'
FROM table_name;

# 파티션 적용하기
SELECT column1, FIRST_VALUE(column1) OVER (PARTITION BY column1 ORDER BY column2 DESC) AS 'first_value'
FROM table_name;

LAST_VALUE

# 각 그룹 별 마지막 값 하나만 반환하기
SELECT column1, LAG_VALUE(column1) OVER (ORDER BY column2 DESC) AS 'last_value'
FROM table_name;

# 파티션 적용하기
SELECT column1, LAG_VALUE(column1) OVER (PARTITION BY column1 ORDER BY column2 DESC) AS 'last_value'
FROM table_name;

NTILE

# 지정된 수만큼의 파티션으로 등급을 나누어 각 등급 번호를 반환하기
SELECT column1, NTILE(4) OVER (ORDER BY column2 DESC) AS '등급'
FROM table_name;

# 파티션 적용하기
SELECT column1, NTILE(4) OVER (PARTITION BY column1 ORDER BY column2 DESC) AS '파티션 별 등급'
FROM table_name;

CUME_DIST

# 상대적인 누적분포도 값을 반환하기
# 반환 값의 범위는 0 초과 1 이하 사이의 값
SELECT column1, column2,
       CONCAT(ROUND(CUME_DIST() OVER(ORDER BY column2), 1) * 100, '%') as '누적 분포' 
FROM table_name
WHERE column2 is not null 
GROUP BY column1;

# 파티션 적용하기
SELECT column1, column2,
       CONCAT(ROUND(CUME_DIST() OVER(PARTITION column1 ORDER BY column2), 1) * 100, '%') as '파티션 별 누적 분포' 
FROM table_name
WHERE column2 is not null 
GROUP BY column1;

RANK_PERCENT

# 상위 퍼센트 값을 반환하기
SELECT column1, column2,
       CONCAT(ROUND(PERCENT_RANK() OVER(ORDER BY column2), 1) * 100, '%') as '상위 퍼센트',
FROM table_name
WHERE column2 IS NOT NULL
GROUP BY column1;

# 파티션 적용하기
SELECT column1, column2,
       CONCAT(ROUND(PERCENT_RANK() OVER(PARTITION column1 ORDER BY column2), 1) * 100, '%') as '파티션 별 상위 퍼센트' 
FROM table_name
WHERE column2 IS NOT NULL
GROUP BY column1;

LTRIM, RTRIM, TRIM

# 왼쪽 공백 제거하기 (hi)
SELECT LTRIM(' hi');

# 왼쪽의 특정 문자열 제거하기 (hi)
SELECT LTRIM('ohi', 'o');

# 왼쪽의 반복 문자열 제거하기 (i)
SELECT LTRIM('hhi', 'h');

# 오른쪽 공백 제거하기 (hi)
SELECT RTRIM('hi ');

# 오른쪽의 특정 문자열 제거하기 (hi)
SELECT RTRIM('hio', 'o');

# 오른쪽의 반복 문자열 제거하기 (h)
SELECT RTRIM('hii', 'i');

# 양쪽 공백 제거하기 (hi)
SELECT TRIM(' hi ');

SUBSTRING

# 문자열에서 원하는 문자열을 추출하기 (TRI)
SELECT SUBSTRING('STRING', '2', '3');

REPLACE

# 문자열에서 원하는 문자 치환하기 (1BCDE)
SELECT REPLACE('ABCDE', 'A', 1)