공부 기록

https://programmers.co.kr/learn/challenges?tab=sql_practice_kit

* SELECT

SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC; # 모든 레코드 조회

c.f. ORDER BY 1: 테이블 첫번째 컬럼 순서 번호에 따라 (오름차순)정렬

만약 ORDER BY를 생략하는 경우 DB에서 row에 접근하는 방식인 cursor의 스펙이 반영된다. (mysql의 경우는 PK 중심으로 정렬)

https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

SELECT NAME, DATETIME from ANIMAL_INS ORDER BY ANIMAL_ID DESC; # 역순 정렬

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS WHERE INTAKE_CONDITION='Sick' # 아픈 동물 찾기
ORDER BY ANIMAL_ID ASC;

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE INTAKE_CONDITION != 'Aged' # 어린 동물 찾기
ORDER BY ANIMAL_ID ASC;

SELECT ANIMAL_ID, NAME, DATETIME 
FROM ANIMAL_INS 
ORDER BY NAME ASC, # 오름차순 정렬
DATETIME DESC; # 내림차순 정렬

SELECT NAME from ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1; # 상위 N개 레코드

* SUM, MAX, MIN

SELECT DATETIME from ANIMAL_INS ORDER BY DATETIME DESC limit 1; # 최댓값 구하기
SELECT max(datetime) from ANIMAL_INS # 최댓값 구하기

SELECT count(*) AS count FROM ANIMAL_INS; # 동물의 수 구하기

SELECT COUNT(*) 
FROM (SELECT DISTINCT NAME FROM ANIMAL_INS WHERE NAME IS NOT NULL) AS A; # 중복 제거

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS

여기서 두번째 풀이는 COUNT 안에 컬럼이 들어갔기 때문에 NULL은 자동으로 제외되므로 따로 IS NOT NULL을 해줄 필요 없음

* GROUP BY

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

SELECT NAME, COUNT(NAME) AS COUNT 
FROM ANIMAL_INS 
GROUP BY NAME HAVING COUNT(NAME)>1 
ORDER BY NAME ASC;

c.f. COUNT(*)와 COUNT(NAME)의 차이:

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count

* 의 경우 특정 column이 null값인 경우도 포함한다. 이 문제에서는 NAME column이 NULL인 경우도 COUNT하게 된다.
하지만, COUNT(column)은 해당 column이 NULL값을 갖고 있는 row의 경우 COUNT하지 않는다.

c.f. 조건절 HAVING과 WHERE 차이

HAVING: SELECT 문에서 사용된 그룹이나 aggregate function(AVG, SUM, MIN, MAX, VAR,COUNT, GROUPING, STDEV 등)에 조건을 부여해서 결과를 세분화한다. SELECT 문과 함께만 사용할 수 있으며, 주로 GROUP BY와 함께 쓰인다. 그룹화된 결과에만 적용될 수 있다.

즉, where 같은 경우는 모든 필드에 대해 우선적으로 조건을 주고
having은 group by 된 이후 그룹화되어진 새로운 테이블에 조건을 줄 수 있다는 차이점이 있다.

SELECT hour(datetime) AS hour, count(*) AS count 
FROM animal_outs
ORDER BY hour

c.f. HOUR(time) : 해당날짜의 시간을 반환한다. 결과값은 0 에서 23 이다.

http://happycgi.com/community/bbs_detail.php?bbs_num=43&tb=board_man_story

WITH RECURSIVE rgen (n)
  AS (SELECT 0 # 0부터 22개 생성(0~23까지)
       UNION ALL
      SELECT n+1
        FROM rgen
       WHERE n < 23)
SELECT TIME.n AS HOUR, IF(RESULT.COUNT IS NULL , 0, RESULT.COUNT) AS COUNT
FROM(SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT 
        FROM ANIMAL_OUTS AS OUTS
        GROUP BY HOUR
        ORDER BY HOUR) AS RESULT RIGHT OUTER JOIN rgen AS TIME
ON RESULT.HOUR = TIME.n

이 문제가 어려웠던 이유는 이렇게 count가 0인 값도 나타내주어야 했는데,

그냥 count를 쓸 경우 0인 값이 나타나지 않았다. (행이 없으면 row가 만들어지지 않는다.)

따라서 count==0이면 0으로 반환하도록 하고싶을 때 뭔가 outer join이 필요하다.

위 풀이는 WITH RECURSIVE 구문을 이용하여 리스트 데이터를 생성하는 방법이다.

WITH RECURSIVE 구문을 이용하여 만든 테이블은 아래와 같이 생겼다.

 n

---

 0

 1

 ...

23

다른 방법)

사용자 정의 변수는 @를 앞에 붙이고 사용한다. (초기화 하지 않으면 디폴트 값은 NULL이다.)

SET 또는 SELECT 문을 사용하여 변수를 초기화 할 수 있다.

SET @HOUR = -1;
SELECT @HOUR := @HOUR + 1 AS HOUR, (
    select count(*) 
    from animal_outs 
    where hour(datetime) = @hour) AS COUNT 
FROM ANIMAL_OUTS 
GROUP BY HOUR 
HAVING HOUR BETWEEN 0 AND 23

* IS NULL

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID;

c.f. is null과 = null의 차이

https://stackoverflow.com/questions/9581745/sql-is-null-and-null

NULL은 'unknown' 값이므로 NULL과 비교한 쿼리의 결과는 항상 unknown이다.

(null is special value which does not equal anything --> 따라서 항상 false 리턴)

따라서 NULL인지 확인하는 IS (NOT) NULL 구문을 사용하도록 한다.

SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID;

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE FROM ANIMAL_INS;

c.f. IFNULL: 해당 필드의 값이 NULL을 반환할때 다른 값으로 출력할 수 있도록 하는 함수

다른 방법으로는 case when 조건 then 결과 문을 사용할 수 있다.

SELECT ANIMAL_TYPE, 
    case when NAME is null 
    then 'No name' 
    else NAME 
    end, 
    SEX_UPON_INTAKE 
FROM ANIMAL_INS;

* JOIN

c.f. join의 종류

1. INNER JOIN: default join 방식, 두 테이블 모두 있는 경우
2. OUTER JOIN: left 또는 right로 지정된 한쪽 테이블의 모든 결과를 보여줌
3. CROSS JOIN: 두개의 테이블에서 가능한 모든 조합을 찾는다. --> ON 구문을 사용할 수 없다
4. FULL OUTER JOIN: LEFT OUTER JOIN + RIGHT OUTER JOIN 후 중복 제거
5. SELF JOIN: 자기 자신과의 JOIN (Self join이라는 명령어가 따로 있는 것은 아니고, outer join이던 inner join이던 자기 자신과 조인할 경우는 self join이 된다)

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS AS OUTS
LEFT OUTER JOIN ANIMAL_INS AS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL;

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS AS INS JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME;

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS AS INS LEFT OUTER JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME ASC
LIMIT 3;

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM ANIMAL_INS AS INS INNER JOIN ANIMAL_OUTS AS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME AND INS.SEX_UPON_INTAKE LIKE '%Intact%'

c.f. LIKE 구문 사용법

https://support.microsoft.com/ko-kr/office/like-%EC%A1%B0%EA%B1%B4%EC%9D%84-%EC%82%AC%EC%9A%A9%ED%95%98%EC%97%AC-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%B0%BE%EA%B8%B0-65b07c8a-b314-435a-8b48-2b911856d4f9

* String, Date

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE 
FROM ANIMAL_INS 
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty') 
ORDER BY ANIMAL_ID;

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE ANIMAL_TYPE='Dog' AND (NAME LIKE '%EL%' OR NAME LIKE '%el%') 
ORDER BY NAME;

SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%', 'O', 'X') AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID


SELECT  ANIMAL_ID,
        NAME,
        IF(SEX_UPON_INTAKE REGEXP 'Neutered|Spayed', 'O' , 'X') AS 중성화
FROM    ANIMAL_INS

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS AS INS , ANIMAL_OUTS AS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY DATEDIFF(OUTS.DATETIME, INS.DATETIME) DESC
LIMIT 2;

c.f. 두 날짜 간 차이를 계산하는 함수 DATEDIFF vs TIMESTAMPDIFF vs TIMEDIFF

DATEDIFF: 단순한 날짜 차이

TIMEDIFF: 두 시간의 차이 계산

TIMESTAMPDIFF: 차이의 단위(년도, 분기, 월, 주, 일, 시, 분, 초)를 지정하여 계산

• SECOND : 초
• MINUTE : 분
• HOUR : 시
• DAY : 일
• WEEK : 주
• MONTH : 월
• QUARTER : 분기
• YEAR : 년도

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME ,'%Y-%m-%d' ) as 날짜
FROM ANIMAL_INS ORDER BY ANIMAL_ID

c.f. DATE_FORMAT: 시간을 원하는 형태로 반환하는 함수, 대소문자 주의

쿼리 처리 순서

1. 구문 분석(Parsing)

해당 쿼리가 문법적으로 틀리지 않은지 확인. 해당 구문을 SQL 서버가 이해할 수 있는 단위들로 분해하는 과정.

만약 구문이 부정확하다면 여기서 처리를 중단. 이 문장이 일괄 처리(batch) 내에 있다면 일괄 처리 전체를 중단.(Batch abort : Batch 중 하나라도 syntax error가 있다면 전체 batch가 실행되지 않는다.)

2. 표준화(Standardization)

실제로 필요없는 부분들이 제거. 표준화된 쿼리 트리(Standard Query Tree)가 만들어 진다.

3. 최적화(Optimization)

통계나 조각 정보 등을 바탕으로 실행 계획을 만들어 낸다. 쿼리처리에서 매우 중요한 단계

① 쿼리 분석 : 검색 제한자(SARG)인지 조인 조건인지 판단.

② 인덱스 선택 : 분포 통계 정보를 이용하여 인덱스검색이나 테이블 스캔 중의 하나를 선택. 여러 인덱스 중 가장 효율적인 인덱스를 선택

③ 조인 처리 : JOIN, UNION, GROUP BY, ORDER BY 절을 가지고 있는지 확인하여 적절한 작업 순서를 선택

이 단계의 출력은 실행 계획(Execution Plan) 이다.

4. 컴파일(Compilation)

컴파일을 하면 이진 코드가 생성된다. 일반적인 경우에는 컴파일하고 나면 .exe, .dll 등의 이진 파일이 만들어 지는데, SQL Server에서는 그냥 메모리(프로시저 캐시)에만 올린다. 그래서 컴파일 속도가 매우 빠르다.

5. 실행(Execute)

엑세스 루틴으로 가서 실제 처리를 하고 결과를 돌려준다.

SELECT 실행 순서

-문법 순서-

SELECT                            - 1

FROM                               - 2

WHERE                             - 3

GROUP BY                        - 4

HAVING                            - 5

ORDER BY                        - 6

-실행 순서-

FROM                               - 1 해당 데이터가 있는 곳을 찾아가서

WHERE                             - 2 조건에 맞는 데이터만 가져와서

GROUP BY                        - 3 원하는 데이터로 가공

HAVING                             - 4 가공한 데이터에서 조건에 맞는 것만

SELECT                            - 5 뽑아내서

ORDER BY                         - 6 정렬

출처: https://police84.tistory.com/69