어느덧 루틴화 된 내 일상
오늘은 SQL 기초 강의 완강까지
가 보 자 고!

[Code Kata]

1. 동명 동물 수 찾기

: having 을 사용해서 2회 이상 반복 되는 이름을 추출 할 수 있도록 group by에 조건을 부여한다.

2. 상위 n개 레코드

: subquery를 사용하여 where절에 조건을 부여한다.

3. 최솟값 구하기

: "가장 먼저"와 같은 시간에서의 최솟값을 구할때도 min()을 사용한다.

4. 어린 동물 찾기

: 'Aged'가 나이가 든 이라는 의미이기 때문에 INTAKE_CONDITION 컬럼에서 Aged가 아닌 데이터들을 추출해야한다. <>를 사용하여 '아닌' 항목을 추출할 수 있다.

5. 아픈 동물 찾기

: 간단하게 WHERE절로 조건을 부여하면 된다.

[5주차 강의 요약]

1. subquery & join 복습

-subquery-

Query의 결과를 Query에서 다시 사용할 때 ()로 묶어서 사용한다.

-join-

여러 테이블에서 데이터를 추출해야 할 때
조건에 따라 left join과 inner join등을 사용하여 결합할 수 있다.

-null-

값이 없다.
(단, null은 문자 'null' 값을 말하는 것이며, 0이나 공백인 필드와는 다르다.)

-is null / is not null-

값이 없다. / 값이 없지 않다.
(빈칸을 말한다.)

2. 테이블에 없는 값 제거하기

: rating을 계산하고자 하는데 컬럼 속에 'Not given'처럼 값이 없다면, 이를 제거하여 더 정확한 값을 구해야 한다.

**if(rating<>'Not given', rating, null)**

: 만약 rating에 Not given이 없으면 rating값을 그대로 적용하고, 있으면 그 값을 없애줘

: where 절에 is not null 추가하면 값이 없는 것을 제외할 수 있다.

**WHERE b.customer_id is not null**

: b.customer_id 컬럼의 빈칸이 아닌 것만 추출해줘

3. 테이블의 값 수정하기

**WHERE b.age is null**

: b.age컬럼에서 값이 없는거만 뽑아줘

**coalesce(b.age, 20) "null 제거"**

: b.age컬럼에 값이 없다면 20으로 대체해줘 그리고 그 컬럼을 "null제거"라고 불러줘

: case when 함수를 사용하여 비상식적인 값을 변경할 수 있다.

4. PIVOT TABLE 만들기

1) 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

1-1) BASE DATA 만들기

food_orders 테이블을 기준으로 데이터를 합쳐야하므로 left join을 사용한다.

1-2) BASE DATA를 활용하여 조건 작성하기

1-3) max함수로 깔끔한 PIVOT view 만들기

1-4) max, avg, sum 등의 함수를 사용하였으므로 Group by 절 작성하기

1-5) Order by 7 desc 로 20시간 기준으로 내림차순 정렬하기

2) 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

2-1) BASE DATA 만들기

두 테이블에 모두 속하는 데이터로 PIVOT TABLE을 작성해야 하므로 inner join을 사용한다.

2-2) BASE DATA를 활용하여 조건 작성하기

성별로 깔끔하게 정리하기 위해 if 사용하여 컬럼을 정렬해준다.

2-3) max함수로 깔끔한 PIVOT view 만들기 ❗(괄호 조심)

2-4) Group by 절 작성하기

2-5) Order by 1 desc로 연령 순으로 내림차순 정렬하기

5.WINDOW FUNCTION - Rank, Sum

-Rank-

: 순위를 매길 때 사용한다.

1) 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

rank() over (partition by cuisine_type order by cnt_order desc) ranking

rank() over (partition by 음식 타입별로 랭킹을 구할거야 order by 순위는 음식 종류가 많은 것부터 내림차순으로 매길거야) 랭킹이름은 ranking이라고 불러줘

❗합계를 구하는 것처럼 대상이 되는 값이 존재하는 것이 아니기 때문에 괄호 안을 비워둘 수 있다.
❗over는 window 함수와 세트다.
❗3위까지만 조회하기 위해서 subquery를 한번 더 사용한다.
❗WHERE 절은 항상 가장 아래에 작성한다.

-SUM-

: 합계를 계산할 때 사용한다.

2) 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

2-1) 음식 타입별로 합계 구하기

sum(cnt_order) over(partition by cuisine_type) sum_cuisine

sum(주문 건수의 합계를 구할거야) over(음식 종류별로 합계를 구해서) sum_cuisine 이라고 불러줘

2-2) 음식 타입별로 누적합계 구하기

sum(cnt_order) over(partition by cuisine_type order by cnt_order) cum_cuisine

sum(주문 건수의 합계를 구할건데) over(음식 종류별로 구해서 order by 순차적으로 더해서 값을 표시해줘) cum_cuisine 이라고 불러줘

❗누적합은 위에서부터 차례대로 합산하여 값을 표시해야하기 때문에 order by 가 있어야 한다.

❗ sum_cuisine은 American의 전체 합이기 때문에 584이다.
❗ cum_cuisine은 누적합이기때문에 순차적으로 합산한다.
❗ cum_cuisine이 999999999 인 이유는, cnt_order가 1이기때문에 우열을 가릴 수 없어서 1에 해당하는 모든 값을 더하여 9로 나타난 것이다.

6. 날짜 포맷

DATE(컬럼명)

: 컬럼의 데이터들을 날짜화 해줘

✅ 글자로 취급되었던 date컬럼이 날짜로 취급되도록 변경되었다.

date_format(DATE(date), '%w') "요일"

: date_format은 날짜 타입 컬럼의 형식을 지정해줘(데이트 타입으로 변경해서 해줘(얘를), '%w'만 포함하는 값만 남겨줘) "요일"이라고 불러줘

❗ date_format을 통해 요일, 주 수 등 한번에 계산할 수 있다.
❗ %w는 요일을 의미하고, 일요일=0, 월요일=1이다.
❗ %M은 문자로 월을 나타내고, %m은 숫자로 나타낸다.

7. 5주차 연습 문제

[음식 타입별, 연령별 주문건수 PIVOT VIEW 만들기]

1) BASE DATA 만들기

2) BASE DATA를 활용하여 조건 작성하기

3) max 함수를 활용하여 PIVOT 데이터 묶어주기

4) Group by 절 작성하기

+) 후기

솔직히... 첫번째 강의를 완강했는데... 이제 코드를 직접 짜서 원하는 결과값을 도출해낼 수 있지만, 어떤 원리로 그 코드를 사용해야하는 것인지는 이해가 힘들다.. 강의에서 더 많은 예시를 다루고 더 설명해주시면 좋겠다는 바램이 있다..☆

특히, left join과 inner join을 사용할 때 어떤 상황에서 어떤 함수를 사용해야 하는지 구분하기 힘들고,

방금 푼 실습 문제에서도 count를 사용하는 코드에서
당연히 count를 사용하는게 맞다는 걸 아는데,
왜 어떤 이유로 count(1)을 사용하는지를 모르겠다..

내일은 TIL 복습하면서 부족한 부분들 보충하고
코드카타 풀면서 직접 적용해봐야겠다.

너무 많은 내용을 한번에 습득해서 정리가 안되는 것이라 생각한다..

내일도 화이팅.