[TIL] 스파르타) SQL (SELECT) 강의 2~5주차 수강

이번 2~5주차의 SQL 강의 정보 중 중요하다고 생각되는 부분들을 이해한 그대로 적어보았다.

 

--

 

학습 키워드: MySQL, query, select, order, group, if, case, subquery

 

1. SQL query select 기본

참고할 만한 이전 게시글:

[240421] TIL - 스파르타) 웹개발 종합반 완강, MySQL Query SELECT 연습 01

SELECT ANIMAL_ID, NAME, DATETIME from ANIMAL_INS
    ORDER by NAME, DATETIME desc

 - ORDER by: 쉼표( , )를 사용하면 여러개 column을 대상으로 정렬 할 수 있다고한다.

 - ORDER by 부분만 해석하면 'NAME 컬럼으로 우선 정렬하고 (기본값 asc여서 생략), NAME이 겹치는 데이터끼리는 DATETIME 컬럼을 기준으로 내림차순으로 정렬'한다는 뜻이다.

 

select cuisine_type, min(price) as min_price, max(price) as max_price  from food_orders
	group by cuisine_type
	order by min_price desc

 - GROUP by: 뒤에 적힌 cuisine_type 이라는 column을 카테고리로 지정해서 데이터를 묶는 개념이라고 한다. 만약 이 줄을 빼먹으면 테이블 전체에서 cuisine_type 하나와 price가 min인 값, max인 값 하나가 담긴 row 한 줄만 받게된다. 하지만 group by로 cuisine_type을 카테고리 column으로 지정하면 테이블에서 cuisine_type이 겹치는 항목들 끼리의 min_price와 max_price를 한 row에 담은 데이터를 받게된다.

 

 

2. SQL query 결과 가공하기, if, case

select addr, replace(addr, '문곡리', '문가리') "바뀐주소" from food_orders
	where addr like '%문곡리%'

 - replace(target_column, 'original', 'replaced'): target_column 에서 원래의 문자열을 다른 문자열로 교체해주는 작업이다. 다른 문자열 기능으로는 substring(target_column, index(1부터), count_from_index(생략하면 마지막까지))과 concat('붙일', '스트링', '쉼표로', '구분', substring(), '같은', '식도', '추가', '가능')이 있다

 - %: wildcard 개념인데, 앞에 붙으면 앞 쪽에 아무 문자열, 뒤에 붙으면 뒤 쪽에 아무 문자열, 앞 뒤로 붙으면 양쪽에 아무 문자열이라는 의미다.

 

select substr(addr, 1, 2) "지역", cuisine_type, avg(price) "평균 금액" from food_orders
	where addr like '서울%'	
	group by 1, 2

 - group by에서 컬럼을 반복적으로 적어줄 필요 없이 select에서 적은 순서로 1부터 해서 골라줄 수 있다.

 - 여기서는 1번이 "지역" 컬럼, 2번이 cuisine_type 컬럼이 된다.

 

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

 - if(condition, if_true, if_false): 조건문은 삼항 연산자 처럼 if(조건, 참일때, 거짓일때) 순서로 사용된다.

 

select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

 - case when 조건 then ~: if를 많이 작성해야하는 상황은 case문으로 작성한다. 조건에 걸리지 않는 나머지는 else에, case문의 종료는 end를 사용한다. end 뒤에 오는 부분이 이 컬럼의 이름이 된다.

 

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

 - 이런 식으로 숫자에서 문자, 문자에서 숫자로의 casting도 가능하다고 하니 참고하자.

 

3. Subquery

 - 앞에서처럼 쓰다보면 생기는 의문점이 조건이나 연산이 엄청 많은 쿼리를 어떻게 작성할까 하는 부분인데, 이걸 해결하는 방법이 바로 subquery문이다.

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

select column1, column2
from table1
where column1 = (select col1 from table2)

 - 쿼리의 중첩, 즉 한 쿼리의 결과에 대해서 쿼리를 한다고 생각하면 된다.

 - 첫 번째 블록처럼 table에 해당하는 부분을 subquery로 작성하거나, 두 번째 블록처럼 where에서 column1을 subquery로 정의해줄 수도 있다.

 

 

3. Join

-- LEFT JOIN
select a.컬럼명, b.컬럼명
from a left join b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select a.컬럼명, b.컬럼명
from a inner join b on a.공통컬럼명=b.공통컬럼명

 - a join b on a.colName=b.colName: 테이블 여러개에서 데이터를 취합하고 싶을 때 사용하는 키워드다. left join과 inner join으로 나뉜다고 한다 on뒤에는 두 테이블에서 공통으로 존재하는 (기준이 되는) 컬럼을 a.colName=b.colName의 형태로 정의해준다. 

 - a left join b: 밴 다이어그램으로 이해하면 쉽다. 공통컬럼명을 기준으로 a b 테이블 모두에 데이터가 있는 (a교집합b) row와 a테이블에만 데이터가 있는(a 그 자체) row의 데이터를 받아온다.

 - a inner join b: a b 테이블 모두에 데이터가 존재하는 (a교집합b)에 해당하는 데이터만 불러온다.

 

/*  식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬 */
select	restaurant_name,
		case when avg_price <= 5000 then 'price_group1'
			 when avg_price > 5000 and avg_price <= 10000 then 'price_group2'
			 when avg_price > 10000 and avg_price <= 30000 then 'price_group3'
			 when avg_price > 30000 then 'price_group4'
			 end 'price_group',
		case when avg_age < 30 then 'age_group1'
			 when avg_age >= 30 and avg_age < 40 then 'age_group2'
			 when avg_age >= 40 and avg_age < 50 then 'age_group3'
			 when avg_age >= 50 then 'age_group4'
			 end 'age_group'
from
(
	select	fo.restaurant_name,
			avg(fo.price) 'avg_price',
			avg(c.age) 'avg_age'
	from food_orders fo inner join customers c on fo.customer_id=c.customer_id
	group by 1
) a
order by 1

 - 앞의 학습 내용을 기반으로 쿼리를 작성해보았다. price 정보는 food_orders 테이블에, age 정보는 customers 테이블에 흩어져 있기 때문에 먼저 subquery a를 통해 food_orders 테이블과 customers 테이블에 공통으로 존재하는 customer_id 를 기준으로 두 테이블을 inner join (교집합) 으로 묶어서 각 restaurant_name의 '평균 주문 가격'과 '평균 주문자 나이'를 얻어냈다.

 - 다시 subquery a의 결과물인 테이블을 대상으로 쿼리문을 작성했는데, case 문을 이용하여 각 가격대 별로 price_group을 나눠주고, 나이대 별로 age_group을 나눠줬다.

 - 마지막으로 order by 1로 첫 번째 column으로 지정된 restaurant_name을 기준으로 (기본 오름차순) 정렬했다.

 

4. 잘 못된 값의 처리 (coalesce)

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

 - coalesce (colName, value): 잘못된 값을 처리하는 방식 중 하나인데, '데이터의 colName 컬럼에 해당하는 부분에 값이 없을 때 value의 값으로 대체한다' 라는 의미라고 한다.

 - 그 외 방법으로는 where 에서 is not null로 null이 아닌 데이터만 받아오는 방법 (저번 연습때 사용했던 그 방법)이나 if 구문의 조건을 통해 null 값인 항목을 체크하는 방법도 있다.

 

5. Pivot Table

 - 강의에서는 pivot table을 '2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것' 이라고 정의했다. Pivot table의 개념만 알면 앞에서 배운 내용의 반복(혹은 응용)이다.

pivot table 구조	구분 컬럼
집계 기준	데이터

 

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15",
       max(if(hh='16', cnt_order, 0)) "16",
       max(if(hh='17', cnt_order, 0)) "17",
       max(if(hh='18', cnt_order, 0)) "18",
       max(if(hh='19', cnt_order, 0)) "19",
       max(if(hh='20', cnt_order, 0)) "20"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

 - 위 쿼리를 기준으로 설명하면, restaurant_name 이 집계 기준이 되고, 각 restaurant_name 마다 15시부터 20시까지 매 시간 별 주문 건 수를 나타낸 pivot table을 얻을 수 있다.

 

select cuisine_type,
	sum(if(age >= 10 and age < 20, cnt_order, 0)) '10대',
	sum(if(age >= 20 and age < 30, cnt_order, 0)) '20대',
	sum(if(age >= 30 and age < 40, cnt_order, 0)) '30대',
	sum(if(age >= 40 and age < 50, cnt_order, 0)) '40대',
	sum(if(age >= 50 and age < 60, cnt_order, 0)) '50대'
from
(
	select fo.cuisine_type,
		c.age,
		count(1) cnt_order
	from food_orders fo inner join customers c on fo.customer_id=c.customer_id
	group by 1, 2
) a
group by 1

 - 다른 예시는 지문을 보고 직접 작성한 query 인데, 음식 타입과 연령대 별로 주문 건 수를 보여주는 pivot table을 만들어준다. subquery a를 음식 타입과 age 별로 묶어서 count 값을 지정하고, 바깥 쿼리에서 sum을 해주는동안 if 구문으로 해당 나이대만 걸러서 count를 사용해 합산해준다.

 - 이번 쿼리 강의중 가장 고전했던 파트인 것 같다. 내부의 서브쿼리는 쉽게 구현했는데, 바깥쪽 쿼리에서 나이를 case로 구현하고서는 한참을 헤맸다. 깨달은 점은 일단 내부 쿼리를 확실하게 만들어두는 것, if() 구문이 자체로 삼항 연산자처럼 작동한다는 사실을 까먹지 말 것, select 옆에 들어간 이름들은 column이 된다는 것도 까먹지 말 것 정도가 되겠다.

 

 

6. window 함수와 over

-- window function의 기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

select cuisine_type, restaurant_name, cnt_order,
	rank() over (partition by cuisine_type order by cnt_order desc) ranking
from
(
	select cuisine_type, fo.restaurant_name, count(1) cnt_order
	from food_orders fo 
	group by 1, 2
) a

 - rank() over(): 윈도우 함수인 rank() 인데, 윈도우 함수는 항상 over와 한 쌍이라 무조건 over를 붙여준다고 한다.

 - 또 다른 윈도우 함수인 sum() over() 는 말 그대로 합계를 구하는 함수다. partition by 에서 order by 절과 결합하여 누적 합계를 얻어낼 수도 있다.

 

 

7. date_format 과 date

select date_format(date(date), '%Y') '년',
	date_format(date(date), '%m') '월',
	date_format(date(date), '%Y%m') '년월',
	count(1) '주문건수'
from food_orders fo inner join payments p on fo.order_id=p.order_id
where date_format(date(date), '%m')='03'
group by 1, 2, 3
order by 1

 - 문자열 값으로 저장된 날짜가 있거나 할 때 쉽게 cast 하는 방법으로 date(날짜컬럼)가 있다. date() 안의 날짜 컬럼의 정보가 자동으로 date 형식으로 변환되는데, 이걸 date_format 이라는 함수에 적용하면 날짜 형식에서 년, 월, 일, 요일 등의 정보를 뽑아낼 수 있다.

 - %Y 는 연도, %m는 월, %d 는 일, %w는 요일 등으로 작성하고, %Y-%m-%d 처럼 중복 사용하여 원하는 형태로 조립할 수도 있다. %w의 경우 0이 일요일이라고 한다.