집계
SELECT * FROM bestproducts.items;
# 데이터베이스 사용
USE bestproducts;
# 직접 데이터 베이스 선언 없이도 해당 db 테이블 접근 가능
select * from items;
SELECT MIN(dis_price) FROM items;
select avg(dis_price) from items;
select count(dis_price) from items;
SQL
복사
Group by
show tables;
desc items;
select avg(dis_price) from items group by provider;
# group by
## 판매자별 판매 item 개수
select provider, count(*) from items group by provider;
## 판매자 별 판매 item 개수 order by 내림차순
select provider, count(*) from items group by provider order by count(*) desc;
# distinct
select distinct provider from items;
# 가격이 10000원 넘는 item을 판매하는 provider과 avg 아이템들을 조회
# (select avg(a.dis_price) from a group by a.provider)
select
distinct provider, avg(dis_price) as avg_price
from items group by provider having avg(dis_price) > 10000 ;
SQL
복사
Group by + having
# group by + having 절
select
provider, count(*)
from items
group by provider having count(*) >= 100 ;
# 스마일 배송과 null이 아닌 provider 중 베스트 아이템 개수가 100개 이상인 것
select
provider, count(*) as no_best_item
from items
where (provider != '스마일배송' and provider = '')
group by provider
having count(*) >= 100
order by count(*) desc;
SQL
복사
Join(inner)
# join 구문 익히기
## inner join 연습
show tables;
select *
from items
inner join
ranking on ranking.item_code = items.item_code
where ranking.main_category = "ALL" ;
select *
from items a
inner join ranking b
on a.item_code = b.item_code
where b.main_category = "ALL";
# 전체 베스트상품 중 메인 카테고리가 ALL인 상품들 중에서 판매자별 베스트 상품 개수 출력
select b.provider, count(*)
from ranking a
inner join items b
on a.item_code = b.item_code
where a.main_category = "ALL"
group by b.provider
order by count(*) desc
limit 100;
# 연습문제 2
# 메인 카테고리가 패션 의류인 서브 카테고리 포함,
# 패션 의류 전체 베스트상품에서 판매자별 베스트상품 갯수가 5이상인 판매자와 베스트 상품 갯수 출력해보기
show tables;
desc ranking;
desc product;
desc items;
/*
where 절은 데이터를 그룹으로 나누기 전에 행들을 미리 제거
having절은 groupby 절의 기준, 소그룹의 집계 함수를 이용한 조건을 표시 가능
*/
select items.provider, count(*) as 베스트상품_갯수
from ranking
inner join items
on ranking.item_code = items.item_code
where ranking.main_category = "패션의류"
group by items.provider
having count(*)>= 5
order by count(*) desc;
# 연습문제 3
# 메인 카테고리가 신발/잡화인 서브 카테고리 포함, 전체 베스트상품에서 판매자별 베스트상품 갯수가 5 이상인
# 판매자와 베스트 상품 갯수를 베스트 상품 갯수 순으로 출력해보기
select items.provider, count(*)
from ranking
inner join items
on ranking.item_code = items.item_code
where ranking.main_category = '신발/잡화'
group by items.provider
having count(*) >=5
order by count(*) desc
;
# 연습 문제 4
# 메인 카테고리가 화장품/헤어인 서브 카테고리 포함, 전체 베스트 상품의 평균, 최대, 최소 가격을 출력해보기
desc items;
select avg(items.dis_price), max(items.dis_price), min(items.dis_price)
from ranking
inner join items
on ranking.item_code = items.item_code
where ranking.main_category = "화장품/헤어"
;
# 아이템 별 개수 조회
select items.title, count(*)
from ranking
inner join items
on ranking.item_code = items.item_code
where ranking.main_category = "화장품/헤어"
group by items.title
;
# 아이템 별 판매자 조회
# 판매자별 아이템 조회
# distinct 조건1, 조건2
select distinct items.provider, items.title
from ranking
inner join items
on ranking.item_code = items.item_code
where ranking.main_category = "화장품/헤어"
order by items.title desc
;
SQL
복사
Outer Join
## Outer Join
# left outer join
select *
from items
left outer join ranking
on ranking.item_code = items.item_code
;
# right outer join
select *
from items
right outer join ranking
on ranking.item_code = items.item_code
;
SQL
복사
서브 쿼리
# 서브쿼리를 이용
select items.title
from items
where item_code in
(
select item_code
from ranking
where sub_category = '여성신발'
)
;
# 서브 카테고리가 여성신발 인 상품 중 할인 가격이 가장 높은 상품의 할인 가격 가져오기
select max(a.dis_price)
from items a
where a.item_code in
(
select item_code
from ranking
where
sub_category = '여성신발'
)
;
# 메인 카테고리별로
# 할인 가격이 10만원 이상인 상품이 몇 개 있는지를 출력해보기
select a.main_category, count(*)
from ranking a
where item_code in
(select item_code from items where dis_price >= 100000)
group by a.main_category
;
SQL
복사
여러가지 문제
INNER JOIN
INNER JOIN은 OUTER(외부) JOIN과 대비해서 내부JOIN 이라고 하며
JOIN 조건에서 동일한 값이 있는 행만 반환한다.
INNER JOIN 표시는 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 이나 ON을 필수적으로 같이 써야한다.
NATURAL JOIN
두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 대해 EQUI(=) JOIN을 수행한다.
NATURAL JOIN 은 USING, ON 과 같이 사용할 수 없다.
sql server 에서는 지원하지 않는 기능이다.
별도의 JOIN 컬럼을 지정하지 않아도 두 개 테이블에서 DEPTNO라는 공통된 컬럼을 자동으로 인식하여 조인 처리한다.
조인 처리된 컬럼은 같은 데이터 유형이어야 한다.
ALIAS나 테이블명과 같은 접수다를 붙일 수 없다.
NATURAL JOIN 은 JOIN에 사용되는 컬럼명이 동일해야 하는데, 모델링 상의 부주의로 동일한 컬럼명인데 다른 용도로 사용되는 경우도 있으므로 주의해야 한다.
# 복합 문제
# 메인 카테고리, 서브 카테고리에 대해, 평균 할인 가격과 평균 할인율을 출력해보기
select main_category, sub_category,
round(avg(dis_price), 2) as 평균_할인_가격, round(avg(discount_percent),2) as 평균_할인율
from items
natural join ranking
group by main_category, sub_category
;
# 판매자별 베스트상품 갯수, 평균 할인 가격, 평균 할인율을
# 베스트 상품 갯수가 높은 순으로 출력해보기
select items.provider, count(*), avg(items.dis_price), avg(items.discount_percent)
from items
inner join ranking on items.item_code = ranking.item_code
group by items.provider
order by count(*)
;
# 각 메인 카테고리에서 베스트 상품 갯수가 20개 이상인
# 판매자의 판매자별 평균 할인 가격, 평균할인율, 베스트 상품 갯수 출력해보기
select ranking.main_category, items.provider, count(*), avg(items.discount_percent), avg(items.dis_price)
from items
inner join ranking on items.item_code = ranking.item_code
group by ranking.main_category, items.provider
having count(*) >= 20
order by ranking.main_category, count(*) desc
;
SQL
복사