SQL 퀘리 진행 순서
- FROM: 쿼리의 대상이 되는 테이블을 선택합니다.
- WHERE: 특정 조건을 만족하는 행만 선택합니다.
- GROUP BY: 특정 열을 기준으로 그룹을 만듭니다.
- HAVING: 그룹에 대한 조건을 지정합니다.
- SELECT: 조회할 열을 선택합니다.
- DISTINCT: 중복된 결과를 제거합니다.
- ORDER BY: 결과를 정렬합니다.
- LIMIT/OFFSET: 결과를 제한하거나 오프셋을 지정합니다.
replace(바꿀 칼럼, 현재 값, 바꿀 값)
2번 실습
#2번 실습
SELECT addr "원래 주소",
replace(addr, '문곡리', '문가리') "바뀐 주소"
FROM food_orders
WHERE addr like "%문곡리%"
substr(조회 할 칼럼, 시작 위치, 글자 수)
SELECT addr "수정 전",
SUBSTR(addr, 1, 2)
FROM food_orders
WHERE addr LIKE "%서울%"concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ...)
SELECT restaurant_name "원래 이름",
addr "원래 주소",
concat("[", SUBSTR(addr, 1, 2),"]", restaurant_name) "바뀐 이름"
from food_orders
WHERE addr LIKE "%서울%"퀘리 작성 전 흐름 정리하기
- 어떤 테이블에서 데이터를 뽑을 것인가
- 어떤 컬럼을 이용할 것인가
- 어떤 조건을 지정해야 하는가
- 어떤 함수 (수식) 을 이용해야 하는가
[실습] ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기
SELECT CONCAT('[', substr(addr, 1, 2), ']', restaurant_name, ' (', cuisine_type, ')') "정보",
count(1) "주문건수"
FROM food_orders
group by 1if(조건, 조건을 충족할 때, 조건을 충족하지 않을 때)
# 음식 타입을'Korean'일 때는 '한식', 'Korean'이 아닌 경우에는 '기타'라고 지정
SELECT restaurant_name ,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "정리된 음식 타입"
FROM food_orders #02. 번 실습에서 ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정
SELECT addr "원래 주소",
if(addr like '%평택군%', REPLACE(addr, '문곡리', '문가리'), addr) "바뀐 주소"
FROM food_orders
WHERE addr like '%문곡리%'case 조건별로 적용 값 지정
case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end
#음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
SELECT CASE when cuisine_type = 'Korean' then '한식'
when cuisine_type in ('Japanese', 'Chinese') then '아시아'
else '기타'
END 음식타입,
cuisine_type
FROM food_orders select order_id,
price,
quantity,
case when quantity=1 then price
when quantity>=2 then price/quantity end "음식 단가"
from food_orders#주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용
SELECT restaurant_name,
addr,
case when addr like '%경기도%' then '경기도'
when addr like '%특별시%' or '%광역시%' then SUBSTR(addr, 1, 5)
else SUBSTR(addr, 1, 2) end "변경된 주소"
FROM food_orders 5주차 실습 정리
pivot table 만들기
2) [실습] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
# 1. 각 칼럼 확인하기
SELECT *
FROM food_orders
SELECT *
FROM payments #time정보의 시간 정보가 필요함
# 2. 베이스 데이터 만들기
SELECT f.restaurant_name ,
substr(p.time, 1, 2) hh,
count(1) cnt_order
from food_orders f inner join payments p on f.order_id = p.order_id
WHERE substr(p.time, 1, 2) between 15 and 20
group by 1, 2
#이게 베이스 데이터
# 3. 베이스 데이터를 서브퀘리로 같는 피멋 테이블 생성 구문 만들기
select restaurant_name, #행 정보
max(if(hh='15', cnt_order, 0)) "15", # 칼럼1
max(if(hh='16', cnt_order, 0)) "16", # 칼럼2 ...
max(if(hh='17', cnt_order, 0)) "17", # max를 사용해야 한다.. pivot을 위해! 라고만 이해해라.
max(if(hh='18', cnt_order, 0)) "18",
max(if(hh='19', cnt_order, 0)) "19",
max(if(hh='20', cnt_order, 0)) "20" #여기까지가 pivot table 구절
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 # 20시 기준으로 내림차순Window 함수
2) [실습1] Rank 함수, N 번째까지의 대상을 조회하고 싶을 때
예를 들어, 주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등이 가능합니다.
[실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
1. 음식 타입별, 음식점별 주문 건수 집계하기
# 1. 베이스 데이터 만들기, 음식 타입별, 음식점별 주문 건수 집계하기
SELECT cuisine_type,
restaurant_name,
count(1) cnt_order
FROM food_orders fo
group by 1, 2
# 2.
# 윈도우 함수는 "함수() over()" 이게 세트임.
# cuisine_type 별로 랭킹을 구할 것이며, 랭킹은 cnt_order의 내림차순으로 구할 것이다! 라는 뜻
SELECT cuisine_type,
restaurant_name,
count(1) cnt_order,
rank() over(partition by cuisine_type order by cnt_order desc) ranking
FROM
(
SELECT cuisine_type,
restaurant_name,
count(1) cnt_order
FROM food_orders fo
group by 1, 2
) a
group by 1, 2
# 3. 3위 까지만 조회할 것이다!
# 각 서브퀘리는 alias를 가져야 한다. 안그럼 오류 뜸 ㅋ
SELECT cuisine_type,
restaurant_name,
cnt_order,
ranking
FROM
(
SELECT cuisine_type,
restaurant_name,
count(1) cnt_order,
rank() over(partition by cuisine_type order by cnt_order desc) ranking
FROM
(
SELECT cuisine_type,
restaurant_name,
count(1) cnt_order
FROM food_orders fo
group by 1, 2
) a
group by 1, 2
) b
WHERE ranking <= 3sum 누적합 [실습2] 전체에서 차지하는 비율, 누적합을 구할 때
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날짜 함수
날짜 데이터 date함수
# 1. 문자열 데이터 날짜 데이터로 변환하기
SELECT date,
date(date) # date칼럼값을 문자열->날짜 데이터로 변환
FROM payments
# 2. 날짜 데이터 년,월,일,요일 데이터로 세분화 정리하기
select date(date) date_type,
date_format(date(date), '%Y') "년", # date_format함수를 사용하여 (대상칼럼, '%y') 로 년 데이터만 따로 정리
date_format(date(date), '%m') "월", # date_format함수를 사용하여 (대상칼럼, '%m') 로 월 데이터만 따로 정리
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"
from payments[실습2]
# 1. 문자열 데이터 날짜 데이터로 변환하기
SELECT date,
date(date) # date칼럼값을 문자열->날짜 데이터로 변환
FROM payments
# 2. 날짜 데이터 년,월,일,요일 데이터로 세분화 정리하기
select date(date) date_type,
date_format(date(date), '%Y') "년", # date_format함수를 사용하여 (대상칼럼, '%y') 로 년 데이터만 따로 정리
date_format(date(date), '%m') "월", # date_format함수를 사용하여 (대상칼럼, '%m') 로 월 데이터만 따로 정리
date_format(date(date), '%d') "일",
date_format(date(date), '%w') "요일"
from payments5주차 과제
#between 함수는 '='을 사용하지 않는다.
# 오류 이유: if절의 괄호 위치
# MAX(if(age BETWEEN 10 and 19), age, 0) "10대" -> MAX(if(age BETWEEN 10 and 19, age, 0)) "10대"
# select 문이 끝날 때 ,를 사용함... 빼라
SELECT cuisine_type,
MAX(if(age BETWEEN 10 and 19, age, 0)) "10대" ,
MAX(if(age BETWEEN 20 and 29, age, 0)) "20대" ,
MAX(if(age BETWEEN 30 and 39, age, 0)) "30대" ,
MAX(if(age BETWEEN 40 and 49, age, 0)) "40대" ,
MAX(if(age BETWEEN 50 and 59, age, 0)) "50대" #between 함수는 '='을 사용하지 않는다.
FROM
(
SELECT cuisine_type,
age
FROM food_orders f inner join customers c on f.customer_id = c.customer_id
where age BETWEEN 10 and 60
) a
group by 1