개발자의 메모장

SUBSTRING

입력 받은 문자열의 부분 문자열을 리턴한다.

SQL Server에서 문자열 관련 시작 번호는 1부터 시작한다. (0 아님)

사용법

SUBSTRING(문자열, 시작자리번호, 자를 문자 길이)

사용 예시

SELECT
    SUBSTRING('www.naver.com', 1, 3) AS [sub_string1]
    , SUBSTRING('www.naver.com', 5, 5) AS [sub_string2]

LEFT, RIGHT

입력 받은 문자열의 왼쪽 또는 오른쪽부터 원하는 길이만큼 잘라낸다.

사용법

LEFT(문자열, 자를 문자 길이)
RIGHT(문자열, 자를 문자 길이)

사용 예시

SELECT
    LEFT('www.naver.com', 3)
    , RIGHT('www.naver.com', 3)

CHARINDEX

입력 받은 문자열에서 특정 문자의 위치를 반환한다.

문자열의 앞 또는 특정 위치부터 문자를 검색할 수 있다.

시작 위치 미지정시 문자열의 처음부터 탐색한다.

사용법

CHARINDEX(찾을 문자열, 문자열, 시작 위치)

사용 예시

SELECT
    -- 1번째부터 찾기
    CHARINDEX('straw', 'Apple, Strawberry, Banana') AS [result1]
    -- 10번째부터 찾기
    , CHARINDEX('straw', 'Apple, Strawberry, Banana, Strawberry', 10) AS [result2]
    -- 없는 문자열 찾은 경우
    , CHARINDEX('bbb', 'Apple, Strawberry, Banana, Strawberry', 10) AS [result3]
    -- 대소문자 구분하여 찾기
    , CHARINDEX('straw', 'Apple, Strawberry, Banana' COLLATE Latin1_General_CS_AS) AS [result4]

대소문자 구분 : COLLATE Latin1_General_CS_AS

대소문자 구분안함 : COLLATE Latin1_General_CI_AS

특정 문자 위치에서 자르기

CHARINDEX, SUBSTRING을 이용하여 편리하게 자를 수 있다.

DECLARE @TEMP_STR VARCHAR(20) = '2011,2012'

SELECT SUBSTRING(@TEMP_STR, CHARINDEX(',', @TEMP_STR) + 1, LEN(@TEMP_STR)) AS [result]

출처

https://gent.tistory.com/347

https://gent.tistory.com/436

https://coding-factory.tistory.com/99

데이터 추출 요청으로 VARCHAR 타입의 'hh:mm' 데이터 합계 처리가 필요한 업무가 들어왔다.

문자열 타입이므로 시, 분 데이터를 파싱하고, 분 단위로 변환하여 합계를 구한 후 다시 시, 분으로 변환하여 'hh:mm' 포맷으로 변환해야 한다.

기초 데이터

DROP TABLE IF EXISTS #TIME_DATA;
CREATE TABLE #TIME_DATA (
    RUN_TIME VARCHAR(5)
);

-- 예시 데이터 추가
INSERT INTO #TIME_DATA (RUN_TIME) VALUES ('10:30'), ('12:45'), ('08:15'), ('55:29');

시, 분 데이터 파싱

SELECT CAST(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) AS INT) AS T_MIN
	, CAST(RIGHT(RUN_TIME, LEN(RUN_TIME) - CHARINDEX(':', RUN_TIME)) AS INT) AS T_SEC
	, SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2) AS TOTAL_MIN
FROM #TIME_DATA;

시 : SUBSTRING 함수를 이용하여 구분 문자 ':' 기준 앞까지 부분 문자열을 뽑는다.

분 : RIGHT 함수를 이용하여 오른쪽부터 ':' 기준 오른쪽 문자열 글자의 갯수만큼 부분 문자열을 뽑는다.

분으로 환산 STRING은 * 60 연산에 의해 자동으로 정수로 변환되므로 별도 타입 캐스팅 불필요

합계

SELECT SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) AS [총합계]
FROM #TIME_DATA;

SUM 함수를 이용하여 간단히 합계를 분단위로 구할 수 있다.

시, 분으로 변환

SELECT SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) AS [총합분단위]
	, CAST(SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) / 60 AS VARCHAR)
	+ ':'
	+ CAST(SUM(SUBSTRING(RUN_TIME, 1, CHARINDEX(':', RUN_TIME) - 1) * 60 + SUBSTRING(RUN_TIME, 4, 2)) % 60 AS VARCHAR)
	AS [총합계 hh:mm]
FROM #TIME_DATA;

/, % 연산자를 이용하여 적절한 시, 분 단위로 변환하고, 다시 문자열 타입으로 변환 후 합치면 된다.

MS-SQL에 SPLIT 함수가 별도 존재하지 않으므로 직접 만들어 사용해야 한다.

입력된 문자열의 ':' 기준으로 문자열을 자른다면 아래 쿼리를 통해 구현할 수 있다.

DECLARE @IN_STR VARCHAR(100) = 'SECTION1.SECTION2.SECTION3:1234:2345:6789';

SELECT SUBSTRING(@IN_STR, 1, CHARINDEX(':', @IN_STR) - 1) AS [앞부분]
	, RIGHT(@IN_STR, LEN(@IN_STR) - CHARINDEX(':', @IN_STR)) AS [뒷부분];

SUBSTRING : 문자열의 시작 위치부터 일정 영역만큼 잘라내어 반환하다.

CHARINDEX : 문자열에서 특정 문자의 위치를 반환한다.

RIGHT, LEFT : 문자열을 받아서 오른쪽/왼쪽부터 원하는 길이만큼 잘라 반환한다.

단순하게 사용시 위 로직으로도 충분하지만, 스플릿할 문자가 여러 개인 경우에 로직이 복잡해질 수 있다.

이를 위해 자주 사용하는 함수를 만들어보았다.

CREATE FUNCTION [dbo].[UFN_SPLIT]
(  
	@IN_STR			VARCHAR(MAX),		/* 문자열 */
	@IDX			INT,				/* 추출순번 */
	@SEPARATOR		VARCHAR(10) = ','	/* SPLIT 기준문자 */
)  
RETURNS  VARCHAR(200)   
AS  
BEGIN  

DECLARE @TEMP_STR		VARCHAR(200)  
DECLARE @CUR_STR		VARCHAR(200)  
DECLARE @CUR_SEPARATOR	VARCHAR(10)  
DECLARE @I				INT  
    
SET @TEMP_STR = '';
SET @I = 1;
SET @CUR_SEPARATOR = LTRIM(RTRIM(@SEPARATOR));
SET @CUR_STR  = LTRIM(RTRIM(@IN_STR)) + @CUR_SEPARATOR;  -- 마지막 문자 파싱하기 위해 맨 뒤에 separator 추가

IF CHARINDEX(@CUR_SEPARATOR, @IN_STR) > 0  
    BEGIN  
    WHILE @IDX >= @I
        BEGIN
        IF CHARINDEX(@CUR_SEPARATOR, @CUR_STR) > 0  
            BEGIN  
            -- 문자열 스플릿
            SET @TEMP_STR = SUBSTRING(@CUR_STR, 1, CHARINDEX(@CUR_SEPARATOR, @CUR_STR) - 1);
							
            -- 반환될 문자는 버린후 좌우공백 제거     
            SET @CUR_STR = RIGHT(@CUR_STR, LEN(@CUR_STR) - (LEN(@TEMP_STR) + LEN(@SEPARATOR)));
            END
        ELSE
            BEGIN
            SET @TEMP_STR  = ''
            END
						 
        SET @I = @I + 1  
        END
    END
ELSE
    BEGIN  
        SET @TEMP_STR  = @IN_STR
    END

RETURN(@TEMP_STR)

END
GO

도전 과제

입력 문자열에 'section1 /section2/section3' 형식으로 사이에 공백이 들어간 경우, 제대로 동작하지 않는다.

로직 중 LEN 함수는 입력된 문자열의 맨 뒤에 입력된 공백을 카운트하지 않기 때문으로 보인다.

참고 사항

SQL Server 2016 부터 STRING_SPLIT 함수가 추가되어, 컬럼 문자열의 구분자를 행으로 분리할 수 있게 되었다.

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

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

SQL (Structured Query Language)

• RDBMS에서 데이터를 관리하기 위해 설계된 언어
• 데이터는 정해진 스키마에 따라 테이블에 저장된다.
  • 스키마에 맞지 않는 형식의 데이터는 저장할 수 없다.
• 데이터는 관계에 따라 여러 테이블에 분산된다. (중복 회피)
• 수직적 확장만을 지원한다.

NoSQL

• 전통적인 RDBMS와 다른 방식으로 데이터베이스를 관리한다.
• 정해진 스키마가 없어서 보다 유연하게 데이터를 추가할 수 있다.
  • 다른 구조의 데이터 타입이 컬렉션에 들어갈 수 있다.
  • 데이터 중복 위험이 존재한다. (데이터 수정/삭제시 관계된 모든 컬렉션을 수정해야 한다.)
• 조인 연산이 존재하지 않는다. (RDBMS 방식에 비해 데이터 읽는 속도가 빠르다고 볼 수 있다.)
• 수직, 수평적 확장 전부 가능하다.

트랜잭션 (Transation)

• 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위, 한꺼번에 모두 수행되어야 하는 일련의 연산들을 의미한다.

특성

• 원자성 (Atomicity) : 트랜잭션이 DB에 모두 반영되거나, 혹은 모두 반영되지 않아야 한다.
• 일관성 (Consistency) : 트랜잭션의 작업 처리 결과는 항상 일관성이 있어야 한다.
• 독립성 (Isolation) : 복수 개의 트랜잭션이 동시에 병행 실행되는 경우 어떤 트랜잭션도 다른 트랜잭션에 끼어들 수 없어야 한다. 각각의 트랜잭션은 서로 간섭없이 독립적으로 수행되어야 한다.
• 지속성 (Durability) : 성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영되어야 한다.

Operation

• Commit : 하나의 트랜젝션이 성공적으로 끝났고, DB가 일관된 상태일 때 이 트랜젝션이 완료된 것을 트랜젝션 관리자에게 알려주기 위해 사용하는 연산
• Rollback : 하나의 트랜젝션 처리가 비정상적으로 종료되어 원자성이 깨진 경우, 이 트랜젝션이 행한 모든 연산을 취소하는 연산

즉, Commit은 해당 트랜젝션으로 반영된 DB 변경사항을 저장하는 것이고, Rollback은 해당 트랜젝션으로 반영된 DB 변경사항을 취소하는 것을 말한다.

교착상태
둘 이상의 트랜잭션이 특정 자원의 락을 보유한 채 다른 트랜잭션이 소유하고 있는 락을 요구했을 때, 아무리 기다려도 상황이 바뀌지 않는 상태

UNDO
수정된 페이지들이 버퍼 교체 알고리즘에 따라서 디스크에 출력될 수 있다. 트랜잭션과 무관하게 버퍼의 상태에 따라서 버퍼가 교체된다. 이로 인해 정상 종료되지 않은 트랜잭션이 변경한 페이지들은 원상 복구되어야 한다.

격리 수준 (Isolation Level)

여러 트랜잭션이 동시에 처리될 때, 트랜잭션끼리 얼마나 고립되어 있는지 나타내는 것을 말한다.

즉, 특정 트랜잭션에서 다른 트랜잭션이 변경한 데이터를 볼 수 있도록 허용 여부를 결정한다.

독립성을 보장하기 위해 엄격하게 락을 걸어서 트랜잭션을 순차 처리하면 퍼포먼스가 떨어지므로 효율적인 locking 방법이 필요하다.

• 레벨 0 : Read Uncomitted
  한 트랜잭션에서 처리중인 데이터에 다른 트랜잭션의 접근을 허용한다. 즉, 커밋하지 않은 데이터를 읽을 수 있다.
  DB의 일관성 유지 불가
• 레벨 1 : Read Comitted
  커밋된 트랜잭션만 읽을 수 있다.
• 레벨 2 : Repeatable Read
  트랜잭션이 완료되기 전가지 한 번 조회한 데이터를 반복해서 조회해도 내용이 항상 동일함을 보장한다.
  트랜잭션 영역에 해당하는 데이터의 수정 불가
• 레벨 3 : Serializable
  가장 엄격한 격리 수준, 트랜잭션 영역에 해당하는 데이터의 수정, 입력 불가

낮은 단계 Isolation Level 활용할 때 발생하는 현상들

• 레벨 0 : Dirty Read
  수정되었지만 commit 되지 않은 데이터를 다른 트랜잭션이 읽을 때 발생하는 현상
• 레벨 0, 1 : Non-repeatable Read
  한 트랜잭션에서 같은 쿼리를 두 번 수행할 때, 그 사이에 다른 트랜잭션이 값을 수정/삭제 하면서 일관성이 깨지는 현상
• 레벨 0, 1, 2 : Phantom Read
  한 트랜잭션에서 일정 범위의 레코드를 두 번 이상 읽었을 때, 첫 번째 쿼리에 없던 레코드가 두 번째 쿼리에서 나타나는 현상
  트랜잭션 도중 새로운 레코드 삽입을 허용하기 때문에 나타난다.

갱신 내용 손실 : 동시에 하나의 데이터가 갱신될 때 하나의 갱신이 누락되는 경우
현황 파악 오류 : 하나의 데이터 갱신이 끝나지 않은 시점에서 다른 트랜잭션이 해당 데이터를 조회하는 경우
모순성 : 두 트랜잭션이 동시에 실행될 때 데이터베이스가 일관성이 없는 모순된 상태로 남는 문제
연쇄 복귀 : 두 트랜잭션이 하나의 레코드를 갱신할 때 하나의 트랜잭션이 롤백하면 다른 하나의 트랜잭션이 마저 롤백되는 문제