[𝟐𝟓.𝟎𝟐.𝟏𝟗] 𝐓𝐈𝐋 𝐍𝐎𝐓𝐄 𝟒

0. 개요

오늘은 SQL 과 PYTHON 에 대해서 배운 것들 중에서 일부 내용을 정리해보려고 한다.

---

1. SUBQURERY (보조절)

Subqurery 절은 여러 연산을 해야할 때, 사용해아 하는 명령문 중 하나이다. 쉽게 말하면 '해당 절을 참조해서 사용하라'라는 느낌인 듯 하다.

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

SELECT 칼럼 1, 특별한 칼럼
FROM
( -해당 영역부터는 Subquery 영역- /* subquery */
 SELECT 칼럼 1, 칼럼 2, 특별한 칼럼
 FROM Data table
) a--> 특별히 a 라고 쓸 필요는 없고 '지정 명칭

2. JOIN 절

JOIN 은 여러 테이블에서 같은 교집합, 합집합에 있는 데이터를 묶어서 출력을 같이(Join) 해주는 명령어로, 이는 엑셀에서 사용하는 vlookup 함수와 아주 유사하다.

엑셀 VLOOKUP 함수는 다음과 같다.
vlookup(고객ID, 데이터 테이블, 가져올 정보 위치, True of False)

JOIN 도 동일하다. 동일한 칼럼명이 있다면 다른 데이터 테이블을 가져올 수 있다. 이를 LEFT JOIN 과 INNER JOIN 을
통해서 이 두 함수의 값을 가져 올 수 있다.

코드 스니펫 - 비상업적 이용입니다.

LEFT JOIN

SELECT 조회할 칼럼
FROM 테이블1 (별명, 약칭 ..) LEFT JOIN 테이블2 (별명, 약칭 ...) ON 별명1.공통칼럼명=별명2.공통칼럼명

혹은 테이블 1 LEFT JOIN 테이블 2 ON 테이블1.공통칼럼명=테이블2.공통칼럼명 으로도 쓸 수 있다.

INNER JOIN

SELECT 조회할 칼럼
FROM 테이블 1 (별명, 약칭 ...) INNER JOIN 테이블 2 (별명, 약칭 ...) ON (별명,약칭)1.공통 칼럼명=(별명,약칭)2.공통칼럼명

동일하게 테이블 1 INNER JOIN 테이블 2 ON 테이블 1.공통칼럼명=테이블2.공통칼럼명 으로도 쓸 수 있다.
다만, 테이블 명.공통칼럼명은 길 수 있으므로 약칭, 별명을 붙이는 것이 편하며 해당 테이블에 약칭을 붙여둘 경우에는 SELECT 절에 들어가 있는 항목에도 (별명.약칭) 칼럼명 으로 넣을 수 있다.

---

일단 배웠던 내용을 활용해서 다시 절을 활용해서 SQL 구문 작성하기.

[실습 예제 문제] 음식 준비시간이 25분 보다 초과한 시간을 가져오기

[1] Subquery 구절에 들어갈 SQL 코드 작성

SELECT order_id, restaurant_name, food_preparation_time-25 AS over_time
FROM food_orders

먼저 전체 food_orders 테이블에서 'order_id, restaurant_name, 음식 준비시간 - 25'의 값을 준비한다.

그럼 아예 음수값으로 나온 것까지 포함해서 해당 값이 출력된다. 앞으로 해당 구절은 Subquery 구절로서 다음 SELECT 구절의 FROM 의 데이터 값이 될 것이다.

SELECT order_id, restaurant_name, IF(over_time>=0,'over_time', 0) As over_time
FROM
(
SELECT order_id, restaurant_name, food_preparation_time-25 AS over_time
FROM food_orders
) a

처음에 왜 안되지 싶었는데... food_preparation_time-25 = over_time 해두고서 잊어서 다시 설정하니까 맞게 나온다. 이번에는 다른 점이 음수 값이 나오지 않고, 모두 양수 값만 출력된다! (이는 SELECT 구절에서 over_time 구절을 0 이상의 것만 출력하라고 지시했기 때문이다.

[실습 예제 2] 다중 Subquery 문 사용하기

음식점 평균 단가별 1) 분류 를 진행하고, 2) 그룹에 따라서 수수료 연산하기 (2개 이상의 Subquery 를 요구하는 복잡한 SQL 문구 요구)

[1] 음식점 평균 단가 구하기

SELECT restaurant_name, avg(price/quantity) AS price_per_plate
FROM food_orders
GROUP BY restaurant_name

식당 이름, 그리고 그릇 당 가격을 구했다. 식은 '가격 / 양' = 해당 값이 그릇당 가격이다. (근데 이상하게 잘 생각이 안난다.) 그렇게 만들면 식당 전체에서 가격의 평균을 구하라고 했으므로 다음과 같이 나온다.

[2] 이제 평균 단가별로 수수료를 연산해야한다. 그릇이 얼마일때 얼마를 수수료로 받을 것인지.

SELECT restaurant_name,
       CASE WHEN price_per_plate<5000 then 0.005
            WHEN price_per_plate between 5000 and 19999 then 0.01
            WHEN price_per_plate between 20000 and 29999 then 0.02
            ELSE 0.03 END ratio_of_add,
       price_per_plate
FROM
(
SELECT restaurant_name, avg(price/quantity) AS price_per_plate
FROM food_orders
GROUP BY restaurant_name
) a

여기서 price_per_plate를 한번 더 쓴 이유는 해당 값이 잘 들어갔는지 확인하기 위해서 이다. 그래서 해당 쿼리를 입력했을 경우에는 다음과 같이 데이터가 출력된다.

[3] 각 그룹별 수수료 구하기

여기서 한번 더 나아가서 마지막으로!! 그룹을 지정했으므로 그릇당 가격과 단가별로 지정해주면 된다. 현재 해당 쿼리문에서는

price/quantity = 평균 음식값 --> price_per_plate // 그룹을 현재 'ratio_of_add'로 지정한 상태이다.그래서 price_per_plate * ratio_of_add 를 구한다면 각 그룹당 가격이 정해지면서 음식별 그룹이 지정된 채로 값이 출력될 것이다.

SELECT restaurant_name,price_per_plate*ratio_of_add AS "수수료"
FROM
(
SELECT restaurant_name,
       CASE WHEN price_per_plate<5000 then 0.005
            WHEN price_per_plate between 5000 and 19999 then 0.01
            WHEN price_per_plate between 20000 and 29999 then 0.02
            ELSE 0.03 END ratio_of_add,
       price_per_plate
FROM
(
SELECT restaurant_name, avg(price/quantity) AS price_per_plate
FROM food_orders
GROUP BY restaurant_name
) a
) b

그럼 다음과 같은 형식으로 출력된다.

만약기 5000원 미만은 0.5% (0.005) / 2만원 이하는 1% / 3만원 이하는 2% / 3만원 초과는 3% 로 계산 되어 해당 값으로
출력되었을 것이다.

[실습 예제 3] JOIN으로 한국 음식의 주문별 결제 수단과 수수료율 조회하기

[1] 일단 같은 조건을 가지고 있는 거 찾아보기

필요한 테이블 : food_orders, payments -- 한국 음식과 수단을 언급했으니까 해당 테이블이 필요하다.
INNER? LEFT? --> LEFT JOIN 을 사용하기로 했다. 두 테이블 모두 사용해야하니까.

SELECT (미정)
FROM food_orders f(별칭) left join payments p(별칭) on f.order_id=p.order_id

공통분모는 order_id 니까 order_id로 묶어줬다. 그 다음으로 해야하는게....

[2] 주문별 결제 수단과 수수료율 구하기

무슨 칼럼을 선택해야하는 지 고민했다. 흠....
order_id 는 필수, restaurant_name, price, pay_type, vat(부가세 수수료) 이렇게 5가지를 고르는 게 적절했다.
이를 SQL 문으로 작성하면 다음과 같다.

SELECT f.order_id, f.restaurant_name,f.price,p.pay_type,p.vat
FROM food_orders f(별칭) left join payments p(별칭) on f.order_id=p.order_id

이러면 많이 왔다! 문제는 여기서 '한식' 식당은 아직 필터가 덜 됐다는 것이다. 음... 한번더 필터링을 하기 위해서는 지금 배운 거로는 WHERE 절이 유일하다.

SELECT f.order_id, f.restaurant_name,f.price,p.pay_type,p.vat
FROM food_orders f left join payments p on f.order_id=p.order_id
WHERE cuisine_type='Korean'

이러면 한식으로 한번 더 필터링 된다. 이러면 결과문은 아까랑 달라지는데...

그래도 결론적으로 '한식'만 필터링 됐다! 이제 이 식당들의 리스트만 주르륵 읽일 것이다. 그러고보니 만약에 내가 모르는 상황이라면 어떻게 보면 좋을지 봤는데.

알고보니 데이터베이스 네비게이터에 Tables 란을 검색하면 모든 칼럼과 테이블들을 볼 수 있었다! 적어도 예시문을 푸는 입장이라면 여기서 참조해서 다시 공부해도 괜찮을 거 같았다. 이것도 좋은 배움.... 

오늘 한가지 안 것은 내 DB버에는 MY SQL 파일이 없었다.. 그래서 부랴부랴 서버도 깔고, 파일도 받아서 Local Host 를 연결 받는 방법도 겨우 배웠다. 비밀번호 잊어버리면 까먹으니까 절대로 잊어버리면 안된다..!! 절대로!!!

---

현재 4주차는 학습은 완료는 했는데 막상 JOIN 과 관련된 쿼리는 좀 어려움을 겪고 있어서 다시 공부하는 중이다. 내일 다시 한번 예제 문제 풀어보면서 다시 해야지... 파이썬은 막상 들으니 무슨 말인지 몰라서 계속 읽다보니 TIL 로 정리가 어렵다. 아... 이거 어쩌지...?

일단은 아는 만큼 쓰는 거니까 빠르게 정리해보기는 했다. 파이썬은 어떻게 해결해야할까....? 일단 반복문을 다시 써보면서 익숙해져야 겠다. 파이썬은 공부하다가 좌절하다가 공부하다가 좌절하느라 지금 TIL 로도 정리를 못하고 있다. 이해를 해야 정리도 하는데, 그냥 이해가 안간다... 아.. SQL 은 머리라도 때려박지 이건.. 머리로도 이해가 안간다...

그냥 할 수 있는 만큼 해야겠다. 오늘 몸도 계속 안좋은데, 천천히 느긋히 나가야지... 주말에는 AI 챗봇도 마저 제작하고 싶다.....