SQL 코딩테스트 문법 정리

종종 코딩테스트 치면서 SQL이 나올때가 있다. 그것도 제법 까다롭게 나오는 경우도 많다... 평소에 프로젝트할때 쿼리 짠다고는 해도 끽해야 JOIN이나 집계함수 수준이어서, 그 외에 자주 안 쓰이는 복잡한 쿼리들을 좀 정리해놓으려고 한다. (MySQL 기준)

집계함수

• AVG
• COUNT
• CUME_DIST : 누적분포 백분위
• FIRST :그룹 내 첫번째 행
• LAST: 그룹 내 마지막 행
• MAX
• MEDIAN : 중앙값
• MIN
• PERCENT_RANK : 백분율 순위
• PERCENTILE_CONT : 지정 백분율 이하의 값 개수
• SUM
• STD, STDDEV : 표준편자

분석함수

• AVG
• CORR :변수 상관관계
• COUNT
• DENSE_RANK : 주어진 값에 대해 몇번째로 큰 값인지 (중복되지 않는 순위)
• FIRST_VALUE
• LAST_VALUE
• LEAD :현재 행 다음에 오는 행의 값
• RANK
• SUM
• NTILE : 버킷 개수 분할

JSON

MySQL에는 JSON 자료형이 있다. 이걸 다루는 function들로 다음과 같은 것들이 있다.

• JSON_ARRAY
• JSON_LENGTH
• JSON_EXTRACT
• JSON_UNQUOTE
• JSON_SEARCH
• JSON_CONTAINS
• JSON_REPLACE

숫자

숫자를 다루는 문제도 나온다.

• ABS
• SIGN (-1:음수, 0:0, 1:양수)
• MOD(N,M)
• FLOOR
• CELINIG
• ROUND
• POWER(X,Y)
• RAND()
• INSTR(s1,s2,n,k) : n번째문자부터 시작해서 찾고자하는 문자열 s2가 k번째 나타나는 문자열 위치 반환. ex) INSTR('CORPORATE FLOOR', 'OR', 3,2) = 14

날짜/시간

날짜를 다루는 문제도 종종 나온다.

• DATE_FORMAT, STR_TO_DATE
• DATE_ADD, DATE_SUB
• UNIX_TIMESTAMP, FROM_UNIXTIME
• DAYOFWEEK(date): 요일값 출력 - 1이 일요일
• WEEKDAY : 0이 월요일
• DAYOFMONTH(date)
• DAYOFYEAR(date)
• MONTH(date)
• DAYNAME(date)
• MONTHNAME(date)
• QUARTER(date) : 4분기 중 분기 수 값
• WEEK(date, first) : 1년 중 몇번째 주
• YEAR(date)
• YEARWEEK(date, first) : 해당 날짜의 년도와 1년 중 몇번째 주인지 출력
• HOUR(time)
• MINUTE(time)
• SECOND(time)
• PERIOD_ADD(P,N) : P(YYYYMM)에서 N달을 뺸 값
• PERIOD_DIFF(P1, P2)
• TO_DAYS(date): 0년부터 date까지의 날 수
• FROM_DAYS(N): N날에 해당하는 날짜
• CUR_DATE(), CURTIME(), NOW(), SYSDATE()

기타 내장 함수

• RPAD, LPAD (특정문자를 원하는만큼 넣기)
• RTRIM, LTRIM, TRIM (공백 제거)
• CHAR(N) : N값의 아스키코드를 문자로 출력
• 문자열 결합: CONCAT
• CONCAT_WS(seperator, str1, ...)
• LENGTH(str)
• LOCATE(suvstr, str): str에서 substr 첫번째 위치
• LEFT(str, len)/RIGHT(str, len): str에서 len만큼 왼쪽/오른쪽에서부터 문자열 출력
• SUBSTRING(str, pos, len): pos ~ len만큼 문자열 출력
• SPACE(N): N개만큼의 공백문자
• REPLACE(str, from_str, to_str): str에서 from_str -> to_str
• REVERSE(str)
• INSERT(str, pos, len, newstr): pos ~ len만큼을 삽입
• GROUP BY 문자열 결합: GROUP_CONCAT
• 값 비교/대체: CASE WHEN ... THEN ... END
• 타입 변환: CAST, CONVERT
• 이진값/16진수 문자열 변환: HEX, UNHEX
• 암호화 및 해시 함수: MD5, SHA, SHA2
• 처리 대기: SLEEP

고급문법

• 집합

SELECT * FROM A,B --cartesian product

SELECT * FROM A
UNION
SELECT * FROM B --합집합

SELECT * FROM A INNER JOIN B ON ~ --교집합
SELECT * FROM A LEFT JOIN B ON ~ WHERE B.id IS NOT NULL --교집합
SELECT * FROM B LEFT JOIN A ON ~ WHERE A.id IS NOT NULL --교집합

SELECT * FROM A LEFT JOIN B ON ~ WHERE B.id IS NULL -- A-B 차집합
SELECT * FROM A RIGHT JOIN B ON ~ WHERE A.id IS NULL - B-A 차집합
SELECT * FROM A
WHERE NOT EXISTS(
	SELECT DISTINCT B.id
    FROM B
    WHERE A.id = B.id); -- A-B 차집합
SELECT * FROM A
WHERE A.id NOT IN(
	SELECT DISTINCT B.id
    FROM B); -- A-B 차집합
-- B-A차집합은 A,B를 바꿔주면 됨

SELECT * FROM
(
	SELECT A.id AS id, A.Name AS name
    FROM A
    UNION ALL
    SELECT B.id AS id, B.Name AS name
    FROM B
) AS Tbl
GROUP BY Id, Name HAVING COUNT(*) = 1; --대칭차집합
SELECT *
FROM A
WHERE NOT EXISTS(
	SELECT DISTINCT *
    FROM B
    WHERE A.Id = B.Id
)
UNION ALL
SELECT *
FROM B
WHERE NOT EXISTS(
	SELECT DISTINCT *
    FROM A
    WHERE A.Id = B.Id
) -- 대칭차집합
SELECT *
FROM A
WHERE A.Id NOT IN (
	SELECT DISTINCT B.Id
    FROM B
)
UNION ALL
SELECT *
FROM B
WHERE B.Id NOT IN(
	SELECT DISTINCT A.Id
    FROM A
) --대칭차집합

• 변수 사용

DECLARE @변수명 데이터형식
SET @변수이름 = 변수값
SELECT @변수명

PREPARE 지정이름 FROM 'sql문 + ?'
EXECUTE 지정이름 USING @변수명
DEALLOCATE PREPARE 지정이름;

• IF문 / PROCEDURE

IF 조건문 THEN
   실행문
END IF; -- if문 한 개

DROP PROCEDURE IF EXISTS ifProc;
DELIMITER $$
CREATE PROCEDURE ifProc(INOUT count INT(4), IN inc INT(4))
BEGIN
	DECLARE num INT;
    SET num = 200;
    IF num = 100 THEN
        SET count = count+inc;
    ELSE
    	SET count = count - inc;
    END IF;
END $$
DELIMITER ;

SET @counter = 1;
CALL ifProc(@counter, 1); --2
SELECT @counter;

• CASE문

CASE
	WHEN 조건 THEN 결과
    ...
    ELSE
END

• WHILE문

WHILE 조건식 DO
	SQL문
END WHILE;

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
	DECLARE i INT;
    DECLARE hap INT;
    SET i=1;
    SET hap = 0;
    
    myWhile:
    WHILE (i<=100) DO
    	IF(i%4=0) THEN
        	SET i = i+1;
            ITERATE myWhile;
        END IF;
        SET hap = hap + i;
        IF (hap>1000) THEN
        	LEAVE myWhile;
        END IF;
        SET i = i+1;
    END WHILE;
    
    SELECT '1부터 100까지 4의 배수 제외 합, 1000 넘으면 종료', hap;
END $$
DELIMITER ;
CALL whileProc();

• VIEW

CREATE VIEW 뷰이름(열 이름)
AS SQL문

CREAE OR REPLCAE VIEW 뷰이름(열 이름)
AS SQL문

DROP VIEW 뷰이름;

• PARTITION BY

SELECT SUM(a) OVER(PARTITION BY b)
FROM table

• WITH

WITH A as
( SELECT * FROM B )

SELECT * FROM A