[내일배움 사전캠프 6일차]SQL, 테이블 합쳐 연산하기

SQL 챕터 4 강의를 수강하며 복잡한 연산을 편리하게 하는 방법에 대해 배우는 시간을 가졌다. Query를 작성할 시 sub로 묶고, 각 Table에 있는 데이터를 join하여 간편하게 연산을 진행하는 법을 습득할 수 있는 시간이었다.

AI Literacy 1-5 실습을 통해 GPT에게 품질경영기사 암기노트 제작을 부탁해보았다.

---

■ Subquery

▶ Subquery가 필요한 경우

- 여러 번의 연산을 수행해야 하는 경우

- 조건문에 연산 결과를 사용해야 하는 경우

- 조건에 Query 결과를 사용하고 싶은 경우

 

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

 

▶ Subquery 문 안을 수정해서, 음식 준비시간이 25분보다 초과한 시간을 가져오기

select order_id, restaurant_name, if(over_time>0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

 

▶ 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기

 ( 수수료 구간 - 5000원 미만 0.05%

  5000원 이상 ~ 20,000원 미만 1%

  20000원 이상 ~ 30,000원 미만 2%

  30000원 이상 3% )

select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
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) price_per_plate
from food_orders
group by 1
) a
) b

▶ 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

( 할인조건 수량이 5개 이하 → 10%

  수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%

  이 외에는 일괄 1% )

select restaurant_name,
	   total_price,
	   total_quantity,
	   case when total_quantity<=5 then 0.1
	   		when total_quantity>15 and total_price>=300000 then 0.005
	   		else 0.01 end discount_rate
from
(
select restaurant_name, 
       sum(quantity) total_quantity,
       sum(price) total_price
from food_orders
group by 1
) a

 

---

■ JOIN

함수	의미
Left Join	A의 데이터와 A,B의 공통된 데이터를 합쳐 가져온다
Inner Join	A,B의 공통된 데이터만 합쳐 가져온다

 

▶ left join

select *
from food_orders left join payments on food_orders.order_id=payments.order_id

 

▶ inner join

select *
from food_orders inner join payments on food_orders.order_id=payments.order_id

 

▶ 테이블에 별명 붙이고 각 테이블 컬럼 뽑아오기

select f.order_id,
	   f.customer_id,
	   f.restaurant_name,
	   f.price,
	   c.name,
	   c.age,
	   c.gender
from food_orders f left JOIN  customers c on f.customer_id=c.customer_id

 

▶ 고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회

select distinct c.name,
	   c.age,
	   c.gender,
	   f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
order by c.name

//중복 없도록 결과 도출할 때는 distinct 사용하기

 

▶ 두 개의 테이블 정보를 이용하여 연산하기

주문 가격과 수수료율을 곱하여 주문별 수수료 구하기 (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회

select f.order_id,
	   f.restaurant_name,
	   f.price,
	   p.vat,
	   f.price*p.vat vat2
from food_orders f inner join payments p on f.order_id=p.order_id

 

▶ 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격) *할인 : (나이-50)*0.005 * 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

select cuisine_type,
	   sum(price) price,
	   sum(price*discount_rate) discounted_price
from
(
select f.cuisine_type,
	   f.price,
	   c.age,
	   (c.age-50)*0.005 discount_rate
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a
group by 1
order by discounted_price desc

//subquery 작성 후 테이블 불러올 때는 cuisine_type 또는 a.cuisine_type 둘 다 가능
//order by 절에 discounted_price 또는 3 둘 다 가능

---

[실습] 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Sefmentation 하기

SELECT restaurant_name,
	   case when avg_price<=5000 then 'price_group 1'
	   		when avg_price between 5001 and 9999 then 'price_group 2'
	   		when avg_price between 10000 and 29999 then 'price_group 3'
	   		else 'price_group 4' end price_group,
	   case when avg_age<=29 then 'age_group 1'
	   		when avg_age between 30 and 39 then 'age_group 2'
	   		when avg_age between 40 and 49 then 'age_group 3'
	   		else 'age_group 4' end age_group
FROM 
(
SELECT f.restaurant_name,
	   avg(f.price) avg_price,
	   avg(c.age) avg_age
FROM food_orders f inner join customers c on f.customer_id=c.customer_id
group by 1
) a
order by 1

// case when절 
//case when price <=5000 then 'price_group1'
            when price >5000 and price <=10000 then 'price_group2'
            when price >10000 and price <=30000 then 'price_group3'
            when price >30000 then 'price_group4' end price_group,
       case when age <30 then 'age_group1'
            when age between 30 and 39 then 'age_group2'
            when age between 40 and 49 then 'age_group3'
            else 'age_group4' end age_group 					 도 가능 //

---

오늘 배운 내용들은 앞서 배워왔던 함수와 SQL 개념들과 다르게 실무적인 부분에서 많이 유용하게 사용할 수 있을 것 같다고 느꼈다. 점점 복잡해지고 어려워지는 느낌이라 복습은 필수로 하여 내 것으로 만드는 시간이 필요한 것 같다.
실습 문제를 풀어가며 오류가 발생했고, 어느 부분에서 문제가 발생한 건지 잘 모르겠어서 GPT와 대화를 통해 오류를 찾아냈다. case함수 사용 중에 각 그룹별로 명칭을 지정해 줄 때 '(작은따옴표)를 작성하지 않아 오류가 발생함을 알게 되었고, 다음부터 Query를 작성할 때 '(작은따옴표) 를 빠트리진 않았는지 먼저 확인해 봐야겠다는 생각이 들었다.

---

■ AI Literacy 1-5 실습

▶ 품질경영기사 자격증 취득을 위한 암기노트 만들기

★ Prompt ★

품질경영기사 취득 방법에 대해 조사하여 말해라.

그 정보를 이용하여 암기노트를 만들어라.

{

"system": "너는 품질경영기사 문제를 제출하는 출제자이다."

"user": "위 자료를 바탕으로 암기노트를 만들어라."

"data": "1. 암기노트 형식은 '핵심 암기노트'이다'.",

"2. 요점에 대해 작성해라.",

"3. 내용을 요약하여 작성해라.",

"4. 각 과목에서 중요한 point가 무엇인지 도출해라.",

"5. 각 과목 공부 방법 팁을 말해라."

"6. 각 과목별로 표 형식으로 정리해라."

}

품질경영기사 핵심 암기노트 (과목별 표 정리)

---

1과목 : 실험계획법

구분핵심 내용암기 POINT공부 TIP

실험계획 3원칙	반복·랜덤화·블록화	시험 최빈출	무조건 암기
요인(Factor)	실험 조건	온도·압력 등	예시로 기억
수준(Level)	요인의 단계 수	100도·200도	요인과 구분
분산분석(ANOVA)	집단 차이 분석	F값 클수록 영향 큼	계산 반복
직교배열	최소 실험 횟수	L8, L16 자주 출제	표 형태 암기

대표 공식:

2과목 : 통계적 품질관리(SQC)

구분핵심 내용암기 POINT공부 TIP

X-bar 관리도	평균 관리	계량값 관리	관리도 비교 암기
R 관리도	범위 관리	산포 관리	특징 비교
p 관리도	불량률 관리	불량 비율	np와 구분
np 관리도	불량 개수 관리	표본수 일정	p와 비교
공정능력지수(Cp)	공정 수준 평가	1.33 이상 우수	공식 반복
샘플링 검사	품질 판정	AQL·LTPD 중요	용어 암기

대표 공식:

​

---

3과목 : 생산시스템

구분핵심 내용암기 POINT공부 TIP

EOQ	경제적 주문량	공식 빈출	계산 반복
개별생산	주문형 생산	다품종 소량	특징 비교
로트생산	묶음 생산	중간 형태	장단점 암기
연속생산	대량 생산	자동화 중심	사례 기억
PERT	일정 계획	네트워크 계산	문제 반복
CPM	공정 관리	임계경로 중요	계산 연습

대표 공식:

​​

---

4과목 : 품질경영

구분핵심 내용암기 POINT공부 TIP

TQM	전사적 품질관리	고객 중심	정의 암기
ISO 9001	품질경영 국제표준	문서화 중요	특징 비교
QC 7가지 도구	품질 개선 도구	그대로 출제	순서 암기
파레토도	핵심 원인 분석	80:20 법칙	그림 기억
특성요인도	원인 분석	Fishbone	형태 암기
관리도	공정 상태 관리	중심선 중요	관리도 비교

QC 7가지 도구:

• 파레토도
• 특성요인도
• 히스토그램
• 산점도
• 체크시트
• 관리도
• 층별

5과목 : 신뢰성관리

구분핵심 내용암기 POINT공부 TIP

MTBF	평균고장간격	공식 최빈출	계산 반복
고장률	제품 고장 특성	욕조곡선 중요	그래프 암기
초기고장	초반 불량	감소형	특징 구분
우발고장	안정 상태	일정 고장률	시험 자주 출제
마모고장	노후 고장	증가형	욕조곡선 연결
신뢰도 함수	제품 신뢰도	지수함수 형태	공식 반복

대표 공식:

신뢰도 공식:

시험 직전 최종 암기표

최우선 암기반드시 계산

QC 7가지 도구	ANOVA
관리도 종류	공정능력지수
Cp 공식	EOQ
MTBF 공식	PERT
ISO 특징	신뢰도 계산

---

합격 공부 전략 요약

기간공부 방법

1주차	이론 1회독
2주차	공식 암기
3주차	기출문제 반복
4주차	오답노트 정리
시험 직전	암기표 반복

---

출제자 관점 핵심 POINT

중요도핵심 영역

★★★★★	관리도
★★★★★	공정능력지수
★★★★★	QC 7가지 도구
★★★★☆	실험계획법
★★★★☆	신뢰성 공식
★★★☆☆	ISO·TQM

---

GPT를 평소에 사용할 때는 단순하게 내가 궁금한 것을 제로형식으로 질문만 했었는데, 구조화하여 질문을 하니 답변의 퀄리티가 상승한 것을 느꼈다. 내가 요구하는 바를 구체적으로 제시하고 형식과 목적, 원하는 방향성을 함께 전달하니 더욱 체계적이고 정확한 결과를 얻을 수 있었다.

특히 단순 요약이 아닌 표 형식 정리, 핵심 포인트 도출, 공부 방법 제시 등 실제로 활용 가능한 수준의 답변을 받을 수 있어 AI를 효과적으로 활용하는 방법에 대해 다시 생각해 보게 되었다. 앞으로 AI를 사용할 때 원하는 바를 구체적으로 제시하여 원하는 답변을 얻을 수 있도록 프롬프트 방식으로 변경해 봐야겠다.