알고리즘 코딩테스트를 위한 SQL 개념 정리
자주 출제되는 SQL 내장함수
날짜,시간 데이터
- 날짜 형식 변경
# DATE_FORMAT(필드명, 형식)
DATE_FORMAT(CREATED_DATE, "%Y-%m-%d")
# 날짜가 아닌걸 날짜로 바꿈.
TO_DATE(날짜, format)- 날짜 차이 계산
# 30일을 기준으로 장기대여/단기대여 분류해 RENT_TYPE에 저장
CASE
WHEN DATEDIFF(ENDTIME, STARTTIME)+1 > 30 THEN '장기대여'
WHEN DATEDIFF(ENDTIME, STARTTIME)+1 = 30 THEN '일반대여'
ELSE '단기대여'
END RENT_TYPE
# 내가 지정한 단위 기준으로 차이 계산
TIMESTAMPDIFF(단위, 날짜1, 날짜2)
# 날짜 더하기/빼기
DATE_ADD(DATE, INTERVAL 1 HOUR)
DATE_SUB(DATE, INTERVAL 3 HOUR)- 날짜 범위 계산
-- 2022-10-16일에 차를 빌릴 수 있는지 여부
SELECT CAR_ID,
MAX(CASE WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE THEN "대여중" ELSE "대여 가능" END) AS AVAILABILTY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC
문자열 가공
- 특정 문자열 찾기
% : 0개 이상의 문자열
_ : 1개의 문자열
^a : a로 시작하는 문자열
a$ : a로 끝나는 문자열
[abc] : a,b,c 중 하나의 문자
[^abc] a,b,c를 제외한 하나의 문자
[a-z] : a~z 중 하나의 문자
.* : 모든 문자열
# 데이터 찾기
WHERE LANGAUGE LIKE "%파이썬%"
WHERE LANGAUGE REGEXP 정규식
WHERE LANGAUGE IN ('가','나','다')
# 문자열 위치 찾기
LOCATE(str, 찾는 문자열) # 문자열 내에서 찾는 문자열이 처음으로 나타내는 위치를 찾아서 해당 위치를 반환, 존재 안하면 0 반환 (시작 인덱스는 1)
# 문자열 길이
LENGTH(str)- 특정 문자열 사용
# 필드의 문자열 중 첫번쨰 문자부터 두번쨰 문자까지만 추출한다.
SUBSTRING(PRODUCT_CODE, 1, 2)
LEFT(PRODUCT_CODE, 2)
REPLACE(str, 바꾸고 싶은 문자, 바꿀 문자)
TRIM(제거하고 싶은 문자열 FROM str)
UPPER(대문자로 바꾸고 싶은 문자열)
LOWER(소문자로 바꾸고 싶은 문자열)- 문자열 합치기
식별자 사용 x ; CONCAT()
식별자 사용 o ; CONCAT_WS('식별자', ...)
SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address
FROM Customers;숫자 데이터
- 숫자 조작
ROUND(값, 반올림 결과 자릿수) : 반올림
CEIL(값, 올림 결과 자릿수) : 올림
FLOOR(값, 내림 결과 자릿수) : 내림
TRUNCATE(값, 버릴 자릿수) : 버림
POW([숫자],지수) : 제곱
SQRT([숫자]) : 제곱근
윈도우 함수
- 순위 메기기 / 범위 자르기에 같이 사용됨.
RANK()
OVER ~ PARTITION BY()
* RANK() 대신, SUM() 등의 함수로 대체 가능.
DENSE_RANK() : 공동 순위 반영.
SELECT
department_id,
employee_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank // 급여 순으로 순위 메기기
FROM
employees;SQL 핵심 개념
서브 쿼리
*서브 쿼리 내부에 ORDER BY문 사용 불가능.
- 단일 행 서브쿼리 : 쿼리 실행 결과가 항상 1건 이하인 경우.
- 다중 행 서브쿼리 : 결과가 2개 이상인 경우. -> IN, ALL, ANY, EXISTS를 함께 사용 !!
- FROM 서브쿼리
# 단일 행 서브쿼리
SELECT * FROM A WHERE AGE = (SELECT MAX(AGE) FROM A)
# 다중 행 서브쿼리
SELECT * FROM A WHERE AGE > ANY (SELECT AGE FROM A)
# JOIN (SELECT ...) ON ~
SELECT USER_ID, NICKNAME,TOTAL_SALES
FROM USED_GOODS_USER as a
JOIN (SELECT SUM(PRICE) AS TOTAL_SALES, WRITER_ID
FROM USED_GOODS_BOARD
WHERE STATUS = 'DONE'
GROUP BY WRITER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES
) as b
ON a.USER_ID = b.WRITER_ID다양한 SQL 구문
- IF문
- IF(조건문, 참일 때 값, 거짓일 때 값)
- IFNULL(컬럼명, 대체하고 싶은 값)
- WITH RECURSIVE 구문
- 테이블 생성에 사용됨
WITH RECURSIVE [원하는 테이블명] AS (
SELECT [초기값] AS [원하는 컬럼명]
UNION ALL
SELECT [원하는 컬럼명] + 1
FROM [원하는 테이블명]
WHERE [원하는 컬럼명] < [원하는 컬럼 개수]
)- WITH 구문
- 서브쿼리 정의할 때 사용됨
WITH [원하는 테이블명] AS (
SELECT ...
)[예시] 특정 조건을 만족하는 그룹 ROW 뽑기
SELECT *
FROM [테이블명] AS A
WHERE [컬럼명] = (SELECT MAX([컬럼명])
FROM [테이블명] AS B
WHERE A.[그룹 컬럼명] = B.[그룹 컬럼명])[참고] SQL Join의 종류
냥