개발자의 메모장

정렬 수행 중 의도하지 않은대로 정렬되는 경우가 있다.

1, 11, 12, 21, 23, 7, 8, ... 순으로 정렬되었다.

칼럼의 타입이 달라 발생하는 문제로, 형 변환 기능에 대해 정리해 보고자 한다.

변환 가능 데이터 타입

• BINARY
• CHAR
• DATE
• DATETIME
• DECIMAL
• JSON
• NCHAR
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

CAST

변환하고자 하는 타입을 AS로 직접 명시한다.

SELECT * FROM USERS ORDER BY CAST(USER_ID AS UNSIGNED);

// 문자에서 숫자로 변환
SELECT CAST('123' AS UNSIGNED) AS test;
SELECT CAST('123' AS UNSIGNED) FROM DUAL;

// 숫자에서 문자로 변환
SELECT CAST(2 AS char(1)) AS test;
SELECT CAST(123 AS CHAR(3)) FROM DUAL;

// 문자 또는 숫자를 날짜 타입으로 변환
SELECT CAST(20230418 AS DATE) FROM DUAL; // 2023-04-18
SELECT CAST('20230418' AS DATE) FROM DUAL; // 2023-04-18

CONVERT

두 번째 인수로 변환하고자 하는 타입을 직접 전달한다.

USING 절은 서로 다른 문자 셋 간의 데이터 변환을 위해 사용된다.

// 숫자를 문자열로 변환
SELECT CONVERT(202304180950, CHAR); // "202304180950"

// 숫자를 날짜 타입으로 변환
SELECT CONVERT(20230418, DATE); // 2023-04-18

// SIGNED 타입으로 변환
SELECT CONVERT(NOW(), SIGNED); // 20230418135712

출처

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%ED%83%80%EC%9E%85-%EB%B3%80%ED%99%98-cast-convert

https://hoehen-flug.tistory.com/27

- 반복적으로 작동되는 쿼리

- 자료들의 계층을 파악하는데 용이하다.

구조

WITH RECURSIVE cte AS (
    -- NON-RECURSIVE 문, 처음 한 번 실행됨 (Anchor Member)
    SELECT 1 AS n
    -- 반드시 UNION 사용해야 함
    UNION ALL
    -- RECURSIVE 문 (Recursive Member)
    -- 서브 쿼리에서 가상 테이블 참조하는 문장 필요
    SELECT n + 1
    FROM cte
    -- Termination Condition (종료 조건)
    WHERE n < 10
)
SELECT * FROM cte

예시

2011년 ~ 2020년 출력하기

WITH RECURSIVE cte2 AS (
	SELECT 2011 AS yyyy
	UNION ALL
	SELECT yyyy + 1 FROM cte2
	WHERE yyyy < 2020
)
SELECT yyyy FROM cte2

2020년 1월 1일 ~ 2020년 2월 28일 출력하기

WITH RECURSIVE cte3 AS (
	SELECT DATE_FORMAT('2020-01-01', '%Y-%m-%d') AS dt FROM DUAL
	UNION ALL
	SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM cte3
	WHERE dt < '2020-02-28'
)
SELECT * FROM cte3

예시 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59413

풀이 1

WITH RECURSIVE cte AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM cte
    WHERE HOUR < 23
), tmp AS (
    SELECT 
        CAST(DATE_FORMAT(DATETIME, '%H') AS UNSIGNED) AS HOUR
        , COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
)

SELECT
    cte.HOUR
    , IFNULL(tmp.COUNT, 0) AS COUNT
FROM cte
LEFT JOIN tmp ON tmp.HOUR = cte.HOUR

풀이 2

WITH RECURSIVE cte AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM cte
    WHERE HOUR < 23
)

SELECT
    cte.HOUR
    , COUNT(DATETIME) AS COUNT
FROM cte
LEFT JOIN ANIMAL_OUTS AS ao ON cte.HOUR = HOUR(ao.DATETIME)
GROUP BY cte.HOUR

https://school.programmers.co.kr/learn/courses/30/lessons/301650

풀이

WITH RECURSIVE TMP AS (
    SELECT ID
        , PARENT_ID
        , 1 AS GEN
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT
        e.ID
        , e.PARENT_ID
        , (t.GEN + 1) AS GEN
    FROM ECOLI_DATA e
    INNER JOIN TMP t ON e.parent_id = t.id
)
SELECT id
FROM TMP
WHERE GEN = 3

출처

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

https://allmana.tistory.com/144

https://velog.io/@glad415/MySQL-RECURSIVE-QUREY-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC