부트캠프 SQL 강의 3일차

2-1. 2주 차 배울 것

저번 주차에서 sql의 개념과 기본구조에 대해서 학습했다. 이번 주차는 sql안에 있는 여러 가지 기능을 배우고 실습해 보자.

2-2. 데이터 조회와 엑셀 함수 적용을 한 번에 끝내기(SUM, AVG, COUNT, MIN, MAX)

연산

SELECT 구문 안에서 연산을 할 수 있다.

아래 예시를 보자.

SELECT food_preparation_time, delivery_time , food_preparation_time + delivery_time as total_time 
FROM food_orders

food_praparation_time + delivery_time을 더해준 컬럼을 만들어준다.

+이외에도 '-', '*', '/'도 사용 가능하다.

 

엑셀에 있는 함수들을 SQL에서 적용해보자.

SUM

SUM은 모든 행을 더한 값을 구할 때 사용된다.

food_orders 테이블

food_orders 테이블에서 모든 가격을 더한 값을 구해보자.

SELECT SUM(price) // as ~~ 별칭 가능
FROM food_orders fo

SUM 결과

SUM() 안에 모두 더해줄 컬럼명을 넣으면 된다. 이는 좀 이따 나올 AVG, COUNT, MIN, MAX 동일하다.

AVG

AVG는 컬럼의 평균값을 구할 때 사용된다.

마찬가지로 food_orders 테이블에서 평균 가격을 구해보자

SELECT AVG(price)
FROM food_orders

AVG 결과

COUNT

컬럼의 행 개수를 알 수 있는 COUNT를 사용해보자.

SELECT COUNT(1) count_of_orders, count(DISTINCT customer_id) count_of_Customers
FROM food_orders

실행결과

COUNT( ''" ) 카운트의 괄호 안에는 컬럼명을 넣어줄 수 있고, 아니면 모든 행의 개수를 알 수 있는 * 혹은 1을 입력해줄 수 있다.

두번째 카운트에 있는 DISTINCT는 중복제거 키워드이다. COUNT는 중복 상관없이 개수가 나오므로 중복된 데이터를 세지않으려면 DISTINCT 키워드를 사용해주자.

MIN / MAX

최소값과 최대값을 구하는 함수이다.

food_orders에서 가격의 최대값과 최소값을 구해보자.

SELECT MIN(price) as "최소 가격", MAX(price) as "최대 가격"
FROM food_orders

실행결과

2-3. [실습] where절로 원하는 데이터를 뽑고, 계산하자

저번에 배웠던 where절과 이번에 배운 함수들을 사용해서 데이터를 뽑아서 계산해보자.

실습은 두가지가 있었다.

1) 주문 금액이 30,000원 이상인 주문건의 갯수 구하기

2) 한국 음식의 주문 당 평균 음식 가격 구하기

 

실습1)

주문건의 갯수를 구하는 것이니 COUNT()함수를 사용하고 주문 금액이 30,000원 이상인 주문건을 추리면 될 것 같다.

SELECT COUNT(*) 
FROM food_orders
WHERE price >= 30000

실습1. 결과

실습2)

한국음식으로 필터링 해준 뒤 평균 음식 가격이니 AVG(price)를 해주면 될 것 같다.

SELECT AVG(price)
FROM food_orders
WHERE cuisine_type = 'Korean'

실습2. 결과

2-4. GROUP BY로 범주별 연산을 해보자

위에 했던 실습2 에서 만약 한국음식, 중국음식, ETC... 의 개수를 센다고 생각해보자. 여러번 쿼리를 돌리면 되기야 하겠지만 성능이 떨이진다. 이 때 활용할 수 있는 것이 GROUP BY 이다.

GROUP BY는 카테고리 컬럼 지정, 그리고 Group by를 적어주는 것으로 사용할 수 있다.

기본구조는 아래와 같다.

SELECT 카테고리 컬럼, SUM(계산 컬럼)
FROM 테이블
GROUP BY 카테고리 컬럼

위 예시를 가지고  음식 종류별 주문 금액 합계를 구해보자.

SELECT cuisine_type, SUM(price) as sum_of_price
FROM food_orders
GROUP BY cuisine_type

결과

실습

두 가지 실습을 진행해보자

1) 음식점별 주문 금액 최댓값 조회하기

2) 결제 타입별 가장 최근 결제일 조회하기

 

실습1. 코드 및 결과

GROUP BY를 사용해서 음식점별로 묶어준 뒤 최댓값을 구해보자.

SELECT restaurant_name, MAX(price) max_price
FROM food_orders
GROUP BY restaurant_name

실습1. 결과

실습2. 코드 및 결과

GROUP BY를 사용해서 결제 타입별로 묶어준 뒤 MAX함수를 사용해서 최근 결제일을 구해보자.

SELECT pay_type, MAX(date)
FROM payments p 
GROUP BY pay_type

실습2. 결과

2-5. 쿼리 결과를 정렬하자(ORDER BY)

데이터에 추가나 삭제가 이루어지면 동일한 출력을 보장받을 수 없으므로, 출력 결과를 자신의 용도에 맞춰 정렬할 필요가 있다. 이 때 사용하는 키워드가 바로 ORDER BY이다. ORDER BY를 사용해 주문 금액이 높은 순 혹은 낮은 순으로 정렬 할 수 있다. 

SELECT price
FROM food_orders
ORDER BY price //ASC, DESC

기본적으로 구조는 위와 같다. 만약 ASC를 생략하면 ORDER BY는 오름차순으로 정렬된다. 역순으로 정렬 하려면 DESC 키워드를 붙이면 된다.

ORDER BY는 여러개가 사용가능하다. 가격기준 오름차순으로 정렬하고 가격이 같다면 수량 오름차순으로 정렬을 구현해보자.

SELECT price, quantity 
FROM food_orders fo 
ORDER BY price, quantity

결과

실행해보면 수량이 같으면 수량대로 정렬 돼 있다.

ORDER BY를 여러 개 사용하면 왼쪽부터 순차적으로 되기 때문에 고려해서 적용하자.

실습

1) 음식점별 주문 금액 최댓값 조회하기 - 최댓값 기준으로 내림차순 정렬

2) 고객을 이름 순으로 오름차순으로 정렬하기

 

실습1. 코드 및 결과

SELECT restaurant_name, MAX(price) as MaxPrice
FROM food_orders fo 
GROUP BY restaurant_name
ORDER BY MaxPrice DESC

실습1. 결과

실습2. 코드 및 결과

SELECT * 
FROM customers c 
ORDER BY name

실습2. 결과

2-6. SQL 구조 마스터

완성된 SQL 문의 기본 구조는 아래와 같다.

SELECT
FROM
WHERE
GROUP BY
ORDER BY

순서를 잘 기억하자.

퀴즈

명령어들을 순서대로 나열해보자

정답: 5 -> 4 -> 3 -> 2 -> 1

정답: 2 -> 5 -> 1 -> 4 -> 3

숙제

문제: 음식 종류별 가장 높은 주문 금액과 가장 낮은 주문금액을 조회하고, 가장 낮은 주문금액 순으로(내림차순) 정렬하기

문제를 읽고 난 후 어떤걸 적용할지 정리했다.

• SELECT
  • 음식 종류
  • 가장 높은 주문 금액
  • 가장 낮은 주문 금액
• GROUP BY
  • 음식 종류
• ORDER BY
  • 가장 낮은 주문금액
  • 내림차순

다 했으니 이제 구현해보자.

SELECT cuisine_type, MAX(price) as max_price, MIN(price) as min_price
FROM food_orders fo 
GROUP BY cuisine_type 
ORDER BY min_price DESC

숙제 결과