[스파르타 내일배움캠프 / 데이터 분석 트랙] TIL(Today I Learned)_1주차_24.11.27

Today's Goals

1. 아티클 스터디 1개

2. SQL 기억 되살리기 - 코드카타 레벨2 ing, SQL 강의 3주차

 

 

 

1. 아티클 스터디 - 비개발자를 위한 엑셀로 이해하는 SQL : FROM 과 SELECT

 

https://onion95.tistory.com/8

[스파르타 내일배움캠프 / 데이터 분석 트랙] article study 5 - 비개발자를 위한 엑셀로 이해하는 SQL

 

기본적인 SQL 문법에 대한 이야기지만, 감을 되살리기엔 좋은 내용이다. 어떤 조작을 해서 데이터를 조회하더라도 원본 데이터에는 아무 영향이 없다는 기억을 다시 새겨 주었고, ERD 작성 연습에 대한 목표도 겟

 

 

 

2. SQL 기억 되살리기 - 코드카타 레벨2 (프로그래머스 코딩 연습), SQL 강의 3주차

 

슬슬 헷갈리는게 눈덩이처럼 불어난다. 공부를 쉰 내 탓이지 누굴 탓해...!

오늘 연습한 내용이 좀 많다. 하나하나 다시 정리하면서 기억하자.

 

 

코드카타 SQL

---

[NULL 값 대체, IFNULL, IF]

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC

• IFNULL(컬럼1, 'NO') : 컬럼1의 모든 NULL 값을 'NO'로 치환
• IF(컬럼1 IS NULL, 'NO', NULL 아닐 때 치환할 값) : NULL이 아닐 때 기존 값을 그대로 두고싶다면 컬럼1을 입력

 

 

[가장 비싼, 가장 싼 값 찾기]

SELECT MAX(PRICE) AS MAX_PRICE
FROM PRODUCT

• ORDER BY와 LIMIT을 사용하지 않고 MIN, MAX로 비싼 값, 싼 값을 찾을 수 있다는 것!

 

 

[와일드카드 '%블라블라%']

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%'
AND ANIMAL_TYPE='Dog'
ORDER BY NAME

• '%단어%' : 특정 문자, 단어 등이 들어간 값을 찾기 위해서 사용, 따옴표는 필수!
• MySQL에서는 %%안에 있는 단어의 대소문자를 구분하지 않는다. 위의 경우 el이라고 했지만 EL, El, eL로 적힌 것도 조횐된다는 뜻

 

 

[DATE_FORMAT, Y-M-D 와 y-m-d의 차이]

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

• DATE_FORMAT : 날짜/시간 데이터를 년-월-일-시-분-초 에서 원하는 속성만 나타낼 수 있는 기능
• 여기서 속성을 나타내는 Y,M,D 등은 대소문자가 나타내는 것이 다르다.

source : https://pig-programming.tistory.com/17

더 많은 포맷이 있으니 https://pig-programming.tistory.com/17 여기서 참고!

 

 

[가격이 제일 비싼 것 출력, LIMIT 말고 MAX를 쓴다면?]

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE 
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE 
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE)
               FROM FOOD_PRODUCT)

• 두 쿼리는 결과가 같다. 아래 쿼리는 MAX를 사용하기 위해 서브쿼리를 이용한 것. 강의에서 서브쿼리 파트가 나올때 자세히 듣자.

 

 

[DATE 정보에서 추출하기, YEAR(), MONTH()]

SELECT *
FROM USER_INFO
WHERE YEAR(JOINED) = 2021 AND AGE BETWEEN 20 AND 29

• YEAR() : JOINED에 있는 날짜 값에서 년도를 추출
• 월을 추출하고 싶다면 MONTH, 일은 DAY

 

 

[COUNT(*)와 COUNT(컬럼명)의 차이]

• COUNT(*) : NULL 포함 모든 데이터를 카운팅
• COUNT(컬럼명) : 해당 컬럼에서 NULL을 제외한 값이 있는 데이터만 카운팅

SELECT NAME, COUNT(NAME) AS COUNT 
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME 
HAVING COUNT(NAME) >= 2
ORDER BY NAME ASC

따라서 위의 쿼리에서 WHERE절은 필요없다.

 

[정답1] COUNT(*) 사용 시 WHERE 조건으로 NULL 제거 필요!
[정답2] COUNT(name) 사용 시 WHERE 조건 없이도 가능!

 

 

 

SQL 강의

---

[where date between '2020-01-01' and '2020-01-07', 1월 7일에 해당하는 데이터가 모두 조회될까?]

• NOPE.
• YYYY-MM-DD의 뒤에는 00:00:00이라는 디폴트 값이 있다고 생각해야 함. 그래서 00:00:1초에 구매한 사람은 조회할 수 없음
• 7일 구매자를 모두 포함하고 싶다면, date >= 1일 and date < 8일 로 조건절을 작성하자

 

 

[REPLACE]

• REPLACE(컬럼, 원래 단어, 바꾸는 단어) : 특정 단어를 다른 단어로 대체하고 싶을 때
• 이 때 꿀팁! - 바꾸려는 원래 단어를 포함한 행만 추출하는 WHERE 구문을 써서 딱 필요한 영역에만 REPLACE가 적용되게 하자. 데이터 분석인데 정확한게 조차나! (WHERE ~ LIKE '%원래 단어%') 

 

 

[ SUBSTRING/SUBSTR]

• SUBSTRING/SUBSTR(컬럼, 추출 문자 위치, 추출하려는 총 글자 수)  : 컬럼의 값에서 특정 문자만 남기 싶을 때, 예를들면 주소 정보에서 앞 두글자만 따면 시도를 알 수 있음!

 

 

[CONCAT]

• CONCAT('문자', ' - ', '문자') : 문자 - 문자 라는 결과가 나옴. 즉 문자를 합치고 싶을 때 사

 

 

[GROUP BY에 별칭 사용?]         중요한 내용!

• MySQL에서는 GROUP BY에 SELECT에서 설정한 별칭을 사용할 수 있다. (GROUP BY 자체에서 별칭을 설정하는 기능은 수행할 수 없다)
• 여기서 혼란이 온다. GROUP BY가 SELECT보다 먼저 실행된다며?
• 그냥... MySQL만의 특징이다... 뭐 파싱 어쩌구저쩌구지만, 웬만하면 정석대로 작성하는 연습을 하는게 좋겠다.
• 어쨌든 아래를 보면서 어리둥절 했던 것을 정리해보자

select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "음식점",
       count(1) "주문건수"
from food_orders
group by 1

• 1은 SELECT 된 컬럼 중 첫번째로, CONCAT된 컬럼을 뜻한다. 그렇다면 1대신 별칭인 "음식점"을 써도 되나?
• NOPE. 쿼리를 실행하면 오류는 없지만 출력 내용이 달라진다.
• 내부적으로 쿼리를 수행하면서 순서에 따라 오류가 발생할 수 있다. 예를 들면 원본 테이블에는 존재하지 않는 음식점이라는 컬럼을 찾게 된다던가... 때문에 출력 되어 결과는 달라질 수 있는 것
• 따라서 GROUP BY 뒤에 concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') 을 그대로 쓰거나 이 자체를 의미하는 1을 쓰자. 코드의 간소화와 유지보수를 위해서는 1이 좋다.

select substring(addr, 1, 2) "시도",
       cuisine_type "음식 종류",
       avg(price) "평균 금액"
from food_orders
where addr like '%서울%'
group by 1, 2

• GROUP BY 1,2 대신 SUBSTR(addr,1,2), CUSINE_TYPE 가능
• SUBSTR 대신 ADDR은 안될까? 안된다. 각각을 그룹화 하면 애초에 그룹의 종류가 다르다.
• 다만, 이미 WHERE 에서 서울이 한번 필터링 되었기 때문에 시도에 대해서 또 그룹화 할 필요가 없는 풀이법도 있다.

select substring(addr, 1, 2) "시도",
       cuisine_type "음식 종류",
       avg(price) "평균 금액"
FROM food_orders
WHERE addr LIKE '%서울%'
GROUP BY cuisine_type

 

 

[IF]

• IF의 기본 사용 구조

if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

• 사용 예시

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders


select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

 

 

[CASE]

• CASE의 기본 사용 구조

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

• esle는 생략 가능하다. 혹시라도 모든 조건에 해당되지 않는 CASE가 있는 경우를 위해 정의하는 용도라고 볼 수 있다.
• 사용 예시

select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity end "음식 단가"
from food_orders