# 16 Tue

## 데이터 분석을 위한 고급 SQL <a href="#sql" id="sql"></a>

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

시간 관련 함수

{% embed url="<http://happycgi.com/community/bbs_detail.php?bbs_num=43&tb=board_man_story>" %}

{% embed url="<https://extbrain.tistory.com/78>" %}

SET 문법

{% embed url="<https://three-pleasure.tistory.com/256>" %}

```sql
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 을 굉장히 많이 반복하는 어마어마한 코드도 제외했다.

```sql
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

{% embed url="<https://it-mi.tistory.com/17?category=422177>" %}

###

### 정규 표현식

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

* REGEXP
  * 정규 표현식을 의미
  * `^` : 이후 문자열로 시작
  * `[]` : 다음 문자열 중 하나 이상 포함
  * `.*` : 와일드카드와 동일한 의미. 어떤 문자가 와도 가능.  `*` 는 0번 이상이라는 의미. `+` 는 1번 이상이라는 의미.

### \[심화] 사용자 정의 함수

* 에디터에 따라 구조가 다르지만 기본적으로 다음과 같은 구조를 가진다.
* 에디터에서 제공하므로 외울 필요는 없다.

```sql
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` 은 주로 다음과 같이 사용된다.

```sql
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)`
