2023-12-19

여러번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

• 여러번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

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

#from으로 쓰일 테이블으로써 subquery문을 사용
#이 방식의 경우 a등으로 subquery문으로 인해 생긴 테이블 명 붙여주기
#빼먹으면 에러발생!

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

#강의 내용에서는 이 형태로 쓰는 연습을 못 해봤던 것 같은데(기분탓일 수 도)
#위 방식과 다르게 from으로 테이블은 다른 테이블을 가지고 오지만,
#필터조건으로써 subquery문을 사용

subquery에서 안에 별명? 변수명을 영어로 해줘야하는듯 “시도”이런식으로 한글로 하니 문자라서 그다음 바깥 쿼리에서 컬럼을 가져오질 못함 (아래 사진과 같이 나옴)

Query를 짜줄 때 사고 과정(특히, subquery, join사용 시?)

Ⅰ. Query 를 적기 전에 흐름을 정리해보기

1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가

Ⅱ. 구문으로 만들기

1. 어떤 테이블에서 데이터를 뽑을 것인가
2. 어떤 컬럼을 이용할 것인가
3. 어떤 조건을 지정해야 하는가
4. 어떤 함수 (수식) 을 이용해야 하는가

Ⅲ. 전체 구조로 합치기

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

여러 테이블에서 데이터를 불러오는 방법(흡사 테이블 붙여서 잇기)

• JOIN 의 기본 원리와 종류
  JOIN 은 기본적으로 엑셀의 Vlookup 과 유사

• 예시)

• LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미

• INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회 즉, 위의 예시에서 주문번호 14의 경우, 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외됩니다.

단, 전체 출력을 해보면 위 이미지와 다르게 실제로는 그냥 테이블 두 개를 바로 이어붙여지는 식으로 나타났다.

• JOIN 의 기본 구조
  

  -- LEFT JOIN
  select 조회 할 컬럼
  from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
  
  -- INNER JOIN
  select 조회 할 컬럼
  from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명
  ​

  공통컬럼은 묶어주기 위한 ‘공통 값’ 이기 때문에 같은 값을 의미만 한다면, 두 테이블의 컬럼명은 달라도 괜찮음(테스트는 해보지 않았지만, 아마 두 컬럼이 같은 무게 맥락이더라도 데이터가 서로 다른 사실에 대하여 (하나는 시멘트의 무게, 하나는 과일의 무게) 컬럼값을 가진다면 서로 매칭이 안되어 아마 안될 것으로 예상됨)
  
  테이블 1에 테이블2를 붙인다 붙이는 컬럼 정해주어야 함(on으로 지정) 그리고 추가로 꼭 순서대로 안하고 b.공통c=a.공통c해주어도 에러가 안 난다.
  
  위의 subquery때도 subquery문에 이름 붙여주는 것으로 예상했을 수 있지만, 테이블 역시 컬럼에 별명 붙이듯 별명을 붙여 축약하여 적는 것이 가능하기에 위에 구조들처럼 테이블1.공통c 안 하고 별명.공통c로 해줄 수 있다.
  
  그리고 확실한 것은 아니지만 subquery문에서 join할 때 쓰는 on말고 다른 부분 select부분에서 원래 기본적으로는 테이블명.컬럼명 하여 사용하는 것으로 들었는데 테이블명.을 떼고 컬럼만 바로 그냥 적어도 오류없이 작동하였으니, 나중에 확인해보면 좋을듯하다
• 테스트해본 예시 코드
  

  select cuisine_type, #이제 서브쿼리문이 a라는 이름의 결과물로 테이블이 된 것이기때문에 a.~하거나 서브쿼리는 보통 생략해도 되서 생략
         sum(price) "원래 가격",
         sum(price)-sum(discount_price) "할인 적용 가격",
         sum(discount_price) "할인 가격" 
  from 
  (
  select cuisine_type,
         f.price, #붙이기전 원래 테이블에 있던 컬럼은 테이블명. 안해줘도 오류는 안나는듯?
         f.price*((c.age-50)*0.005) discount_price #??c.도 안해줘도 오류 안나는데..?
  from food_orders f inner join customers c on f.customer_id=c.customer_id
  where c.age>=50
  ) a
  group by 1
  order by 4 descselect cuisine_type, #이제 서브쿼리문이 a라는 이름의 결과물로 테이블이 된 것이기때문에 a.~하거나 서브쿼리는 보통 생략해도 되서 생략
         sum(price) "원래 가격",
         sum(price)-sum(discount_price) "할인 적용 가격",
         sum(discount_price) "할인 가격" 
  from 
  (
  select cuisine_type,
         f.price, #붙이기전 원래 테이블에 있던 컬럼은 테이블명. 안해줘도 오류는 안나는듯?
         f.price*((c.age-50)*0.005) discount_price #??c.도 안해줘도 오류 안나는데..?
  from food_orders f inner join customers c on f.customer_id=c.customer_id
  where c.age>=50
  ) a
  group by 1
  order by 4 desc
  ​

조회한 데이터에 아무 값이 없다면 어떻게 해야할까?

select restaurant_name,
       avg(rating) average_of_rating,
       avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by 1

null 데이터가 없다는 의미

사용할 수 없는 값이 데이터에 포함되어 있을 경우 연산을 할 때 0으로 취급하여 계산

사용할 수 없는 값 포함하지 않고 제거하여 연산하고 싶을 때 null 사용

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

where ~ is not null : null아닌것만 출력

where ~ is null : null인것만 출력

ex) coalesce(b.age, 20) b.age컬럼에 값이 없을 경우(null) 20으로 대체해 줘

 

조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?

분석을 하다보면 상식적이지 않은 데이터가 나올 때, 어떻게 해야할지 알아봅시다

데이터가 비어있는 경우도 있지만, 상식적이지 않은 경우 →제외하거나, 상식적인 값으로 대처

 

SQL 로 Pivot Table 만들어보기

• Pivot table 이란? : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미
• Pivot table 의 기본 구조

• 

• Pivot table 의 예시

  

• Pivot view 구조 만들기

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

 

업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)

Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌

• Window Function 의 기본 구조
  

  window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

  
  
  • window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명 있음)
  • argument : 함수에 따라 작성하거나 생략
  • partition by : 그룹을 나누기 위한 기준, group by 절과 유사
  • order by : window function 을 적용할 때 정렬 할 컬럼 기준

• N 번째까지의 대상을 조회하고 싶을 때, Rank
  • Rank 는 이름에서 유추할 수 있듯이 ‘특정 기준으로 순위를 매겨주는’ 기능
  • Rank는 합계처럼 따로 대상이 되는 값 같은 것이 없어 ( )에 아무것도 안 적어도 됨
  • 윈도우 함수와 over는 쌍으로 같이 간다
  Q partition by와 group by랑 차이가 뭘까.. 먼가 다른 거 같은데 뭐가 다른지 정확히는 모르겠다(rank는 없던 거니 다른 게 확실한데) -나중에 더 자세히 볼 일이 있다면 확인해볼 듯함

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

select cuisine_type,
       restaurant_name,
       order_count,
       rn "순위"
from
(
select cuisine_type,
       restaurant_name,
       rank() over (partition by cuisine_type order by order_count desc) rn,
       order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3
order by 1, 4
​

 

 

전체에서 차지하는 비율, 누적합을 구할 때, Sum

• Sum 은 앞서 배운 합계를 구하는 기능과 동일
• 다만, 누적합이 필요하거나 카테고리별 합계컬럼와 원본 컬럼을 함께 이용할 때 유용하게 사용가능

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

select cuisine_type,
restaurant_name,
cnt_order,
sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
from
(
select cuisine_type, restaurant_name, count(1) cnt_order
from food_orders
group by 1, 2
) a
order by 1,3
​

 

sum over 인데 order 없는 건 그냥 총 합계 group by에 의한 sum과 동일

sum over 인데 order 있는 건 그냥 누적 합계 점차 순서대로 그전 것과 합한 합계를 보여준다.

단, 순서 상 크기가 동일하거나 그런 건 공통 순위 느낌으로 한번에 더해서 같은 값을 가지게 하고 그 담 꺼 순차적으로 더해서 보여준다.

그런데 보통은 순서를 따지는 기준을 하나 더 넣어서 누적 합 할지도

 

select cuisine_type,
		restaurant_name,
	    cnt_order,
	    sum_cuisine,
	    cum_cuisine,
		cnt_order/sum_cuisine ratio
from
	(select cuisine_type,
	       restaurant_name,
	       cnt_order,
	       sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
	       sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
	from
	(
	select cuisine_type, restaurant_name, count(1) cnt_order
	from food_orders
	group by 1, 2
	) a
	order by 1,3)b

강의 내용에도 코드로는 비율 안 구해 놔서 직접 코드 추가해서 비율 구해 놨음

그리고 함수( )안의 컬럼 명은 순서로 못 대체하는 듯 order by cnt_order,

restaurant_name)이부분 ~3,2하니 에러났음 그냥 순서인 숫자로 적을 수 있는 경우가 group by, order by 바깥에 따로 있는 경우만 되는거 같기도함 → 확인해주면 좋을듯 되는데에선 숫자로 적으면 많이 편리해지니

 

날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)

데이터에 날짜를 지정하거나 조건에 날짜를 사용해야할 때 활용할 수 있는 기능

날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)

앞의 실습에서 시간에서 앞 두 글자 따와서 시간이라고 정정해주었는데 이는 컬럼이 문자 형태였기 때문에, 시간 형식의 컬럼이였 다면 번거롭게 앞 두 글자 따올 필요 없이 간단히 시간이나 날짜 중에서 원하는 부분 시, 분, 초, 년, 월, 일 등만 남길 수 있었을 것

시간이나 날짜 형태라면 시계모양표시

date 함수를 통해 날짜형식으로 변경 가능 (필요한 사전 조건이 먼지?)

라고 적혀있는데 날짜는 예시가 들어져있으니 확실하고 시간은 time함수가 있음

 

date(컬럼)

select date,
	date(date) change_date
from payments

 

time(컬럼)

select time,
		time(time) change_time
from payments

date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

1. 년 : Y (4자리), y(2자리)
2. 월 : M, m
3. 일 : d, e
4. 요일 : w

select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments

1. [실습2] 년도별 3월의 주문건수 구하기

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%Y %m') "년월",  #이런 식으로 이어서 하는 법도 가능
       count(1) order_count					
from food_orders a inner join payments b on a.order_id=b.order_id
group by 1, 2,3

년 월사이 띄어쓰거나 하고싶으면 ' ' 사이가 문자이기 때문에 그냥 띄어쓰고 싶거나 문자같은거 넣고싶으면 넣으면 되는듯

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%Y/%m') "년월",  #이런 식으로 이어서 하는 법도 가능
       count(1) order_count	#''사이가 문자이기 때문에 그냥 띄어쓰고 싶거나 문자같은거 넣고싶으면 넣으면 되는듯 
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03' #다시 문자로 되었기 때문에
group by 1, 2,3
order by 1

• 5주차 끝 & 숙제 안내
  음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

select cuisine_type,
		max(if(age_group='50대',cnt_order,0)) "50대",
		max(if(age_group='40대',cnt_order,0)) "40대",
		max(if(age_group='30대',cnt_order,0)) "30대",
		max(if(age_group='20대',cnt_order,0)) "20대",
		max(if(age_group='10대',cnt_order,0)) "10대"
from	
(select f.cuisine_type,
			case when c.age>=50 then '50대'
					when c.age>=40 then '40대'
					when c.age>=30 then '30대'
					when c.age>=20 then '20대'
					else '10대' end age_group,
			count(1) cnt_order #주문수
	from food_orders f inner join customers c on f.customer_id=c.customer_id
	where c.age between 10 and 59
	group by 1,2
	#order by 1
	)a
group by 1

 

• 추가 피벗 테이블 방식에 대한 코드 해석

#정상적인 피벗테이블
select cuisine_type,
		max(if(age_group='50대',cnt_order,0)) "50대",
		max(if(age_group='40대',cnt_order,0)) "40대",
		max(if(age_group='30대',cnt_order,0)) "30대",
		max(if(age_group='20대',cnt_order,0)) "20대",
		max(if(age_group='10대',cnt_order,0)) "10대"
from	
(select f.cuisine_type,
		case when c.age>=50 then '50대'
			when c.age>=40 then '40대'
			when c.age>=30 then '30대'
			when c.age>=20 then '20대'
			else '10대' end age_group,
		count(1) cnt_order #주문수
	from food_orders f inner join customers c on f.customer_id=c.customer_id
	where c.age between 10 and 59
	group by 1,2
	#order by 1
	)a
group by 1
	
#피벗테이블하기전 기본데이터
select f.cuisine_type,
		case when c.age>=50 then '50대'
			when c.age>=40 then '40대'
			when c.age>=30 then '30대'
			when c.age>=20 then '20대'
			else '10대' end age_group,
		count(1) cnt_order #주문수
	from food_orders f inner join customers c on f.customer_id=c.customer_id
	where c.age between 10 and 59
	group by 1,2
	#order by 2 desc

#group by의 의미를 알기위해 지운 코드
select cuisine_type,
		max(if(age_group='50대',cnt_order,0)) "50대",
		max(if(age_group='40대',cnt_order,0)) "40대",
		max(if(age_group='30대',cnt_order,0)) "30대",
		max(if(age_group='20대',cnt_order,0)) "20대",
		max(if(age_group='10대',cnt_order,0)) "10대"
from	
(select f.cuisine_type,
		case when c.age>=50 then '50대'
			when c.age>=40 then '40대'
			when c.age>=30 then '30대'
			when c.age>=20 then '20대'
			else '10대' end age_group,
			count(1) cnt_order #주문수
	from food_orders f inner join customers c on f.customer_id=c.customer_id
	where c.age between 10 and 59
	group by 1,2
	#order by 1
	)a
#group by 1

#max의 의미를 알기위해 지운 코드
select cuisine_type,
		(if(age_group='50대',cnt_order,0)) "50대",
		(if(age_group='40대',cnt_order,0)) "40대",
		(if(age_group='30대',cnt_order,0)) "30대",
		(if(age_group='20대',cnt_order,0)) "20대",
		(if(age_group='10대',cnt_order,0)) "10대"
from	
(select f.cuisine_type,
		case when c.age>=50 then '50대'
			when c.age>=40 then '40대'
			when c.age>=30 then '30대'
			when c.age>=20 then '20대'
			else '10대' end age_group,
			count(1) cnt_order #주문수
	from food_orders f inner join customers c on f.customer_id=c.customer_id
	where c.age between 10 and 59
	group by 1,2
	#order by 1
	)a
group by 1

• 다른 부분은 다른 부분에서도 한 기본적인 구조이므로 이해가 되었지만 마지막 group by와 위에 max(if~)부분은 일단 이렇다 생각하고 넘어가라고 하셔서 납득이 되지 않았다
• 우선 비교적 빠르게 이해 됐던 group by의 역할부터 생각해보면 음식 종류에 대하여 하지 않는다면 음식 종류 별로 확인하지 않고 그냥 50대인것중에 젤 큰 값 하나 40대에서 최대값하나…이런식으로 연령별 최대값하나씩만 출력하고 음식종류도 제일 처음 값하나만 의미없이 출력한다 따라서 group by를 통해 원하는 기준 컬럼별로 해줘야 한다.
• 두 번째로 max의 의미는 만약 max를 쓰지 않는다면 select문장에 의해서 데이터를 체크하여 해당하는 컬럼에 값을 다시 배치해주는데 데이터를 순서대로 본다면

• 우선 한식 30대 데이터이므로 한식에 대하여 30대에 대하여 주문 수를 입력하지만, 나머지 연령대에 대하여 0을 입력하도록 if가 되어 있기 때문에 0을 입력해버린다.
• 그런 식으로 연산을 해가던 도중 양식은 10대값은 입력하고 나머지는 0으로 입력되어 버린 상황인데, 8번째 데이터에서 양식 50대의 값이 있지만 이미 0으로 입력하였기 때문에 0으로 입력되어있는 상태로 지나며 연산이 끝나게 되는 것 같다 더 엄밀한 의미는 좀 더 공부해봐야 알겠지만 대략적인 의미는 이와 같은 것으로 생각되므로 납득이 된 듯 하다.
• 여담으로 max를 하면 8번째 데이터를 할 때 위의 4번째 데이터에서 입력한 양식 50대의 데이터에 대하여 0과 59의 데이터중 큰 값이 입력되므로 59가 남아있고, 9번째 데이터를 처리할 때도 50대의 값은 59와 0을 비교해 큰 값이므로 59으로 최종적으로 입력되어 출력된다.
• 추가로 sum을 해도 동일한 결과를 출력 받을 수 있었는데 이는 기본 데이터 처리로 인하여 각 음식 종류에 대한 연령대 값들이 서로 독립적이라 중복되는 것이 없어 0 또는 한 개의 값을 가지므로 동일한 결과를 얻는 것으로 생각된다.

+동명 동물 수 찾기  https://school.programmers.co.kr/learn/courses/30/lessons/59041

'동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요' 라는 것이 문제의 요구사항이다.

SELECT name,
        count(name) count
from ANIMAL_INS
where name is not null
group by 1
Having count>=2
order by 1

여기서 처음에는 2번이상 이라는 조건을 필터로 쓰기위해

having을 알지못하여 where문을 이용하려고 하였으나 where은 집계함수(sum 등)을 사용할 수 없다.

따라서 다른 방법을 통해 필터조건을 적용해주어야 하는데 HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다

그리고 HAVING절은 GROUP BY절과 함께 사용이 된다

추가로 Having절과 where절의 차이를 말하면, having은 전체 결과, where은 개별 행으로

Having절은 WHERE절과 비슷하지만 그룹 전체 즉, 그룹을 나타내는 결과 집합의 행에만 적용된다

반면 WHERE절은 개별 행에 적용이 된다

having은 SQL select문이 집계 값이 지정된 조건을 충족하는 행만 반환하도록 지정하는 SQL절/

where은 단일 테이블에서 데이터를 가져 오거나 여러 테이블과 결합하여 조건을 지정하는데 사용되는 SQL절

having절은 그룹을 필터링하는 데 사용/    where절을 행을 필터링 하는데 사용

집계 함수는 having 절과 함께 사용가능/   where절을 have절에 포함된 하위 쿼리에 있지않으면 집계함수와 함께 사용불가
(집계함수란, COUNT, MIN, MAX, SUM, AVG등)

having 절은 Group By 절 뒤에 사용/        Where 절은 Group By 절 앞에 사용

간단히 차이를 요약하자면 Having은 그룹화 또는 집계가 발생한 후 레코드를 필터링하는데 사용/

Where은 그룹화 또는 집계가 발생하기 전에 레코드를 필터링하는데 사용

(사실 다른 분이 올린 글을 보고 대략적인 쓰임은 이해가 된 듯 하지만 몇 번 둘의 차이를 느끼는 사용을 해보아야 확실히 이해할 수 있을 듯하다.)

 

+대강 날짜 순으로 가장 빠른 1개의 동물에 대해서 출력하는 문제인데, https://school.programmers.co.kr/learn/courses/30/lessons/59405

- 코드를 입력하세요
select NAME
from
(select NAME,
rank() over(order by DATETIME) rn
from ANIMAL_INS)a
where rn = 1

/*SELECT name
from ANIMAL_INS
order by DATETIME
limit 1*/

/*SELECT name
from ANIMAL_INS
where DATETIME=(select min(DATETIME) min_date
from ANIMAL_INS)*/

 

처음엔 우선적으로 수업시간에 하였던 rank를 이용하였으나, 다른 사람들의 풀이를 통해 최소를 찾는 min이나, 몇개의 한정된 데이터만을 출력하는 limit의 방법도 생각할 수 있게 되었다. 그중 limit는 있는 것을 몰랐는데, 간략히 설명을 적어보면

지정한 갯수만큼의 자료만을 보여주며 구조 순서상 order by 아래 위치로 가장 마지막에 쓰여진다.