16 Tue

데이터 분석을 위한 고급 SQL

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

시간 관련 함수

SET 문법

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

정규 표현식

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)

Previous17 Wed Next15 Mon

Last updated 4 years ago

Was this helpful?