목적
- 보려는 항목(들)에 대한 테이블 상의 값 자체로 보는 것이 아니라 뭔가 처리를 해서 보려고 할 때
- 주로 집계 처리믈 하게 되는 경우가 많이 있다.
- 어떤 것을 기준으로 묶어서 할 것인가 ! group by
- 여러개의 값을 대표화(평균, 중앙값, 맨 앞, 총합, 카운팅 etc)
- avg(), min(), max(),stdev() etc : 집계함수
- count(), count (distinct ~ ) : 카운팅
- ***where/ having 공통점 : 데이터를 중심으로 필터링
- where : 원본 데이터 값을 중심으로 필터링
- having : 내가 group by 새롭게 생성한 집계/생성한 값들로 필터링
- ⇒ having/where 명확하게 구별해야함
- Q) 구매 관련된 정보들 중에서 buytbl → 고객별로 우리 회사의 구매수량(총합)을 기준으로: 고객등급!!
# 다시 원래 sqldb 동네로 가겠다.
use sqldb;
select * from buytbl;
# --> buytbl 데이터/레코드 기준 : 1개 종류 팔릴 때 마다 찍힌 pos 로그
# --> 이 매출 데이터를 고객 기준으로 묶어서 보자!
# : 원본테이블 그대로 보는 것이 아니라 나의 목적으로 재형성
select * from buytbl
group by userID; -- 12건의 레코드가 5건으로 레코드 변형
# ==> 결과: 고객별로 엄청 많이 구매한 이력이 있다고 해도, 이 쿼리 결과는 처음 구매한 상품(buytbl pk 기준 첫번째)의 정보만 나오게 된다
# + 이 중에서 필요한 정보 -> userID, amount, : 기준을 단순 구매 수량으로 기준
select userID, amount from buytbl
group by userID;
# --> 이 결과의 애매한 점 : userID OK, amount --> 누적이 아니라 1번 구매이력
select userID, sum(amount) from buytbl
group by userID;
select userID, sum(amount) `총 구매건수` from buytbl
group by userID;
- Q) 고객들의 1건의 구매가 발생할 때 (1번 buytbl 테이블에 레코드가 생성) ==> 구매 수량의 평균 계산!
select * from buytbl;
# --> amount 여러 값을 1개의 대표값 (avg)
select avg(amount) from buytbl;
# ==> 주의!! 집계함수가 꼭 group by가 있어야만 사용되는 것은 아님
# 주로 같이 사용되는 경우가 많은 것이지 필수는 아님
select avg(amount) `평균구매수량` from buytbl;
- Q) 회원id, 그 사람이 구매한 수량의 정보만 보고싶다.
select userID, amount from buytbl;
select userID, amount from buytbl
group by userID;
# ---> amount : 전체가 아니라 userID별로 처음의 amount값만 보임
# + userID 별로 묶어서 진행...
# + userID에 대해서 정렬(a-z)
select userID, sum(amount) from buytbl
group by userID;
select userID, sum(amount) from buytbl
group by userID
order by userID asc;
select userID, sum(amount) from buytbl
group by userID
order by userID desc;
# ==> 우량 고객 중심으로 본다고 하면 ? 정렬순서/기준 이 안 맞는다.
- Q) 정리된 데이터는 맞다
- 보고싶은/ 정렬하는 기준은 userID의 a-z 사전 순이 아니라
- 누적구매 수량 중심으로 내림차순이 보고싶다.
select userID, sum(amount)
from buytbl
group by userID
order by sum(amount) desc;
# ---> 불편함 => sum(amout) 굳이 반복
select userID, sum(amount) `총 구매건수`
from buytbl
group by userID
order by sum(amount) desc;
# 사람들이 자주 많이 사용하는 스타일 pandas --> pd 이런 느낌...?
# ==> 숫자를 사용해서 간접 지칭하는 방식이 있다.
select userID `사용자ID`, sum(amount) `총 구매건수`
from buytbl
group by userID
order by `사용자ID` desc; -- 내가 만든 별칭으로 정렬 기준 언급해도 됨
select userID `사용자ID`, sum(amount) `총 구매건수`
from buytbl
group by userID
order by 2 desc;
# **2 : 내가 select 뒤에 나열한 항목들을 숫자로 언급
select userID `사용자ID`, sum(amount) `총 구매건수`
from buytbl
group by userID
order by 1 desc;
#==> 쿼리 스타일을 파악하셔야 하기 때문에 보셔야 하는 것임alter
# 참고) 기준을 작성할 때 : 내가 보고자 하는 것들을 나열한 그 순서 기준
# => 항목을 직접 언급, 그것에 대한 별칭, 순서를 언급(1~)
- Q) 회원id별로, 총 소비금액 확인해보자 → 이 자료를 기반으로 우리 회사에 많은 금액을 지출하신 분들을 중심으로 정렬 보기 위해 (내림차순)
select * from buytbl;
select userID, price, amount from buytbl; -- 개별 구매 건수 중심
select userID, sum(price*amount)
from buytbl
group by userID;
#
select userID, sum(price*amount) `총 구매금액`
from buytbl
group by userID
order by `총 구매금액` desc;
#
select userID, sum(price*amount) `총 구매금액`
from buytbl
group by userID
order by 2 desc;
- Q) 1번 구매를 할 떄, 누적으로 제일 많은 수량(amount)을 사신 고객들 중에서 3명을 선정해서 (기준 : 많은 수 우선), 감사쿠폰/감사메시지 이벤트!
select * from buytbl;
#
select userID, sum(amount) from buytbl
group by userID
order by 2 desc;
#
select userID, sum(amount) from buytbl
group by userID
order by 2 desc
limit 3;
# --> 이 3분에게 문자발송을 하려고 하는데 어떻게?
# 전화번호가 없다-> 어디에 있나 -> buytbl
# 전화번호는 usertbl에 존재함
# +++ 다른 테이블에 내용을 가져와야할 때 -> join : 은근슬쩍 usertbl 전환
- 참고) 잡다한 함수 몇개를 보도록 하겠다. --> 주의가 필요함
- Q) 회원 정보 테이블에서, 가장 키가 큰 회원의 이름과 키를 출력하자
select * from usertbl;
#
select name, height from usertbl;
#
select name, max(height) from usertbl;
# --> sql입장에서 언어변환 : usertbl에서 name, 키 본다
# 키 중에서 최대값만 본다 -> 1개만 보여줌
# name은 작성자가 언급 안 해서 그냥 처음 것을 보여줌 (이름따로 키따로 출력된 것)
# ==> 의도 : name 중에서는 제일 처음 이름을 보고싶다
# 키는 제일 큰 값을 보고싶다
# : 독립적인 정보 2개를 보고싶어서 요청/ 나열
#name과 max(height)를 연결된 정보로 처리하고 싶다면
# sol1) 서브 쿼리 사용해서 연결
select name, height
from usertbl
where height = 최대키;
#
select name, height
from usertbl
where height = (select max(height) from usertbl);
#sol2) group by
select name, max(height)
from usertbl
group by name
order by 2 desc
limit 1;
# ==> 2가지 쿼리가 머리 속에서 그림이 그려져야함
# ++ 쿼리마다 속도의 차이도 발생을 함
- 카운팅
- Q) 고객들 중에서 핸드폰 정보를 입력한 회원은 몇 명?
# --> counting 숫자를 세자
select * from usertbl;
# ---> 전체 이 테이블에 레코드/데이터 수는 몇 개?
select count(*) from usertbl;
# --> 사용할 때 주의해야하는 쿼리 : 테이블이 엄청 크면, 버벅거림이 심한 코드
# count(*) 주의해서 사용할 쿼리
# --> 대안은?
# 대안1) PK나 특정 컬럼(not null인)을 지정하기
select count(userID) from usertbl; -- 10개
#
select count(name) from usertbl; -- 10개
#
select count(mobile1) from usertbl; -- 에러 없지만 숫자 8개...
# ==> 전체 테이블의 레코드 수 : null이 없는 컬럼/필드명 중심으로 해야함
# 대안2) +1 카운팅 누적
select count(1) from usertbl;
# ===> 구분적으로 카운팅 해야하는 경우들이 있다.
# 업그레이드 : sum ~~ case when ~~~ : 상황별 집계/카운팅
# --> 좀 데이터가 있는 테이블의 레코드를 체크 해보겠다.
select count(*) from employees.employees; -- 약 30만건 300024, 14s
select count(1) from employees.employees; -- 약 30만건 300024,14s
select count(emp_no) from employees.employees; -- 30만건 300024, 14s
# 주의 ! --> count(특정 컬럼) : null의 유뮤에 따라서 결과 숫자가 다를 수 있음 => 속편하게 count(1) 써라
- ***having vs where
- where : 기존 테이블에 있는 레코드 값 기준으로 필터링
- having : 내가 새롭게 만든 값을 기준으로 필터링
- Q) 고객별로, 구매 금액이 총합을 기준으로 내림차순 정렬!
select userID, sum(price*amount)
from buytbl
group by userID;
#
select userID, sum(price*amount) `누적금액`
from buytbl
group by userID
order by 2 desc;
# ---> Q) 위의 결과/고객별로 총누적금액의 기준으로 1000이상인 고객들에 한해서 ~~~~
# : 필터링 기준 --> 테이블 상에 원본 값이 아니라,
# 그걸로 1차 가공한 누적 금액이 기준!
select userID, sum(price*amount) `누적금액`
from buytbl
group by userID
having sum(price*amount) > 1000;
#
select userID, sum(price*amount) `누적금액`
from buytbl
group by userID
where sum(price*amount) > 1000; -- 에러남
#
select userID, sum(price*amount) `누적금액`
from buytbl
group by userID
having sum(price*amount) > 1000
order by sum(price*amount) asc;
#
select userID, sum(price*amount) `누적금액`
from buytbl
group by userID
having 2 > 1000 -- 이건 또 에러남 2>1000?:?? 말이 안 되는 수식 발생
order by sum(price*amount) asc;
#
select userID, sum(price*amount) `누적금액`
from buytbl
group by userID
having sum(price*amount) > 1000
order by 2 asc;
# 참고) 무조건 숫자로 변경이 능사가 아니다 --> 2>1000 같은 경우가 될 수도... 보면서 쓰자
정리
- select 쿼리를 작성하는 순서에 대한 대략적인 그림
select 볼 대상(들), 집계, ...(여러개일 때는 독립인지 종속인지도 고려해야함)
from 출처 ~~~~
where 기존 값을 기준으로 필터링(between A and B, in (), 서브쿼리) etc
group by 기준 ~
having 가공값을 중심으로 필터링
order by ~~~~~~
limit ~~~~
