16 Tue

데이터 분석을 위한 고급 SQL

수업에서 언급하지 않은 함수나 문법

시간 관련 함수

[MYSQL] 시간 관련 함수 정리 - happycgihappycgi.com

[MySQL] 날짜 차이 가져오기 (DATEDIFF, TIMESTAMPDIFF 함수)확장형 뇌 저장소

SET 문법

[삼쾌한 IT강의] MySQL 변수 종류 및 사용법삼쾌한 IT강의

SET @HOUR_IT := -1; 
SELECT @HOUR_IT := @HOUR_IT+1 AS 'HOUR', 
    (SELECT COUNT(*) 
        FROM ANIMAL_OUTS 
        WHERE HOUR(DATETIME) = @HOUR_IT) AS 'COUNT' 
    FROM ANIMAL_OUTS 
    WHERE @HOUR_IT < 23;

• 위 코드는 각 시간별(00~23) 데이터를 출력하는 코드이다. 위와 같이 SET 용법을 쓴 이유는 다음과 같다.

  • 비어있는 데이터에 대한 출력을 할 수 없다.

  • 실제 데이터가 8-13시 까지만 존재한다면 0-7시와 14-23시 데이터는 출력할 수 없다.

  • 이는 IFNULL 이나 CASE WHEN으로도 표현할 수 없다. (데이터가 NULL인 것이 아니라 데이터 자체가 존재하지 않기 때문)

• SET을 쓰지 않으면 다음 코드처럼 푸는 것이 최선이다. (Reccurssive 등의 문법 제외)

  • 이외에도 SELECT UNION ALL 을 굉장히 많이 반복하는 어마어마한 코드도 제외했다.

SELECT HOURS.HOUR, IFNULL(AO.CNT, 0) AS COUNT
FROM
    JSONTABLE(
        "[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23]",
        '$[]' COLUMNS (HOUR INT PATH '$')
    ) HOURS
    LEFT JOIN (
        SELECT HOUR(DATETIME) as HOUR, COUNT() as CNT
        FROM ANIMALOUTS
        GROUP BY HOUR(DATETIME)
    ) AS AO ON AO.HOUR = HOURS.HOUR

RLIKE

SQL | MySQL | LIKE, RLIKE, REGEXPIm between cherry

정규 표현식

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou].*'

• REGEXP

  • 정규 표현식을 의미

  • ^ : 이후 문자열로 시작

  • [] : 다음 문자열 중 하나 이상 포함

  • .* : 와일드카드와 동일한 의미. 어떤 문자가 와도 가능. * 는 0번 이상이라는 의미. + 는 1번 이상이라는 의미.

[심화] 사용자 정의 함수

• 에디터에 따라 구조가 다르지만 기본적으로 다음과 같은 구조를 가진다.

• 에디터에서 제공하므로 외울 필요는 없다.

CREATE FUNCTION 'function name' ('parameter name', 'datatype')
    RETURNS 'datatype' (DETERMINISTIC)
BEGIN
    DECLARE 'variable name' 'datatype';
    SET ;
    RETURN (Query) / 'variable name';
END

• 2 : DETERMINISTIC 을 설정할 경우 입력한 인자가 동일하면 결과를 항상 동일하게 반환하라는 의미. 이것을 설정하지 않으면 NOT DETERMINISTIC 이 기본으로 설정된다

  • TODAY()나 RAND() 같은 함수. 즉 입력이 같지만 출력이 달라지는 함수들에 대해서는 이 옵션 설정에 유의해야 한다.

• 3, 7 : BEGIN 과 END 로 선언 및 종료한다.

  • 이 때 내부는 항상 세미콜론을 끝에 붙여야 한다.

• 4, 5 : DECLARE 와 SET을 통해서 변수를 선언한다.

  • DECLARE 는 변수의 이름과 타입을 선언한다.

  • SET 은 변수의 값을 정의한다.

    • SET 은 주로 다음과 같이 사용된다.

BEGIN
    DECLARE customerLevel VARCHAR(20);

    IF credit > 50000 THEN
		SET customerLevel = 'PLATINUM';
    ELSEIF (credit >= 50000 AND 
			credit <= 10000) THEN
        SET customerLevel = 'GOLD';
    ELSEIF credit < 10000 THEN
        SET customerLevel = 'SILVER';
    END IF;
	-- return the customer level
	RETURN (customerLevel);
END

• 6 : 반환

• 사용 방법

  • SELECT 'function name' (parameter)