패스트 캠퍼스 ~ 패스트 캠퍼스~~
난도 🤦♂️🤦♂️🤦♂️🤦♂️
postgresql - dvdrental 실습(6)
패스트캠퍼스~ 패스트 캠퍼스으~ 1. 영화 예매 시스템을 구축하고자 한다. 아래의 요구조건을 부합하는 물리 테이블을 생성하고 임의의 테스트 데이터를 입력하시오. ❕ 고객 테이블 생성 ❕ 예
coo-coo2021.tistory.com
위에와 이어지는 실습 내용!
데이터 넣기 다 적어두었당.
고객 테이블의 고객 등급별 통계!!! 를 알고자 하는 것
(목표 출력값)
-전체고객수
-등급의개수
-등급별 평균고객수
-등급별 최대고객수
-등급별 최소고객수
-최소고객수의 등급
-최대고객수의 등급
문제 풀이 전 데이터 추가해줌.!
INSERT INTO TB_MOVIE_CUST(CUST_ID,CUST_NM,SEX,BIRTH_DATE,ADDRESS,PHONE_NUMBER,CUST_GRADE,JOIN_DT)
VALUES
('0000000004','이승우','남자', TO_DATE('1984-06-12','yyyy-mm-dd'), '경기도 안양시 동안구 비산동 1-1', '010-1234-1234', 'S', TO_DATE('2017-01-01','yyyy-mm-dd')),
('0000000005','안정환','남자', TO_DATE('1971-07-04','yyyy-mm-dd'), '경기도 안양시 동안구 비산동 1-2', '010-4321-4321', 'A', TO_DATE('2018-06-01','yyyy-mm-dd')),
('0000000006','고종수','남자', TO_DATE('1994-12-28','yyyy-mm-dd'), '경기도 안양시 동안구 비산동 1-3', '010-5678-5678', 'C', TO_DATE('2019-12-01','yyyy-mm-dd')),
('0000000007','기성용','남자', TO_DATE('1984-06-12','yyyy-mm-dd'), '경기도 안양시 동안구 비산동 1-1', '010-1234-1234', 'B', TO_DATE('2017-01-01','yyyy-mm-dd')),
('0000000008','고종수','남자', TO_DATE('1971-07-04','yyyy-mm-dd'), '경기도 안양시 동안구 비산동 1-2', '010-4321-4321', 'C', TO_DATE('2018-06-01','yyyy-mm-dd')),
('0000000009','박지성','남자', TO_DATE('1994-12-28','yyyy-mm-dd'), '경기도 안양시 동안구 비산동 1-3', '010-5678-5678', 'C', TO_DATE('2019-12-01','yyyy-mm-dd'))
;
보니가.. 2번 3번 이름 잘못 넣은 듯 하여 3번의 이름은 이수지로 바꾸기로!
-> DML 해주기 😤
UPDATE 이용
UPDATE tb_movie_cust
SET cust_nm = '이수지'
WHERE cust_id ='0000000002'
;
데이터 값을 바꿔주고,
돌려보니 이수지.....온냐가 맨 밑에 가있다.
안되 ~~
SELECT * FROM tb_movie_cust
ORDER BY cust_id ;
를 해서 순서대로 다시 보쟈
+ 추가로 박지성 등급도 D로 바꿔줬다!
+ 추가로 이수지 등급도 B로 바꿔줬다!
우선 내가 먼저 풀어보기로.... 🙏🙏
one more~
고객 테이블의 고객 등급별 통계!!! 를 알고자 하는 것
(목표 출력값)
1. 전체고객수
2. 등급의개수
3. 등급별 평균고객수
4. 등급별 최대고객수
5. 등급별 최소고객수
6. 최소고객수의 등급 🤦♂️
7. 최대고객수의 등급 🤦♂️
나눠서 먼저 고민해보기로,,,!
1. 전체고객수
SELECT count(cust_id)
FROM tb_movie_cust;
2. 등급 갯수
SELECT count(DISTINCT cust_grade)
FROM tb_movie_cust;
3. 등급별 평균 고객수
SELECT avg(num)
FROM (
SELECT count(cust_id) AS num, cust_grade
FROM tb_movie_cust
GROUP BY cust_grade
) B
;
4. 등급별 최대 고객수
SELECT max(num)
FROM (
SELECT count(cust_id) AS num, cust_grade
FROM tb_movie_cust
GROUP BY cust_grade
) B
;
5. 등급별 최소 고객수
SELECT min(num)
FROM (
SELECT count(cust_id) AS num, cust_grade
FROM tb_movie_cust
GROUP BY cust_grade
) B
;
여기까지는 했는데 6,7번은 도저히 모르겠다 ㅜㅜ 오류천지...
우선 1~5까지 한 쿼리문에 작성해보면,
SELECT count(a.cust_id) "전체 고객수"
,count(DISTINCT a.cust_grade) 등급갯수
,avg(num) 등급별평균고객수
,max(num) 등급별최대고객수
,min(num) 등급별최소고객수
FROM (SELECT count(cust_id) AS num
,cust_grade
FROM tb_movie_cust
GROUP BY cust_grade
) B
,tb_movie_cust a
;
❔❔❔❔뢔따시... 9가 아닌 45로 나올까....?
SELECT sum(num) "전체 고객수"
,count(DISTINCT cust_grade) 등급갯수
,avg(num) 등급별평균고객수
,max(num) 등급별최대고객수
,min(num) 등급별최소고객수
FROM (SELECT count(cust_id) AS num
,cust_grade
FROM tb_movie_cust
GROUP BY cust_grade
) B
;
이렇게 하면 9가 나온다...
고민해본 결과 ...
총 인원수 9명 group 5개로
총인원수 9*45 ??
뭐예요....? 알려줘요...🤦♂️
👉👉👉
아아! join조건이 없이 저렇게 나열되어 있으면 Cartesian Product [카티션 곱] 이 발생하게 되는데
Cartesian Product [카티션 곱]
: 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터가 반환되는 현상
조인 조건절을 적지 않고 조인하였을때 발생
곱셈을 하게 된다. 따라서 9*5 하여 =45의 결과를 주는 것
일렬로 밑으로 주욱 모든 데이터가 나열되면서 값을 계속 읽어들인 탓!
예로
sample 1 data
sample 2 data
를 조인 조건 없이 조인한다면,
이렇게 3건*2건 =6개의 행을 내보내준다.
여기부터 원래 주어진 정답(?) ....!
---등급별--
SELECT cust_grade, count(*)
FROM tb_movie_cust
GROUP BY cust_grade;
인라인뷰에 넣기
SELECT avg(cnt) avg_by_grade
,max(cnt) max_by_grade
,min(cnt) min_by_grade
FROM ( SELECT count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
);
--🐱🏍등급별 최소, 최대 고객수 --
--최소
SELECT cust_grade AS grade_by_min_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt
) A
LIMIT 1
;
--최대
SELECT cust_grade AS grade_by_max_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt desc
) A
LIMIT 1
;
한번에 합쳐주는 작업을 하면!!
SELECT *
FROM (
SELECT avg(cnt) avg_by_grade
,max(cnt) max_by_grade
,min(cnt) min_by_grade
FROM ( SELECT count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
) B
) B
,(
SELECT cust_grade AS grade_by_min_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt
) A1
LIMIT 1
) A1
,( SELECT cust_grade AS grade_by_max_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt desc
) A2
LIMIT 1
) A2
;
1. 전체고객수
2. 등급의개수
이 두가지만 더 조인하여 구하면 된다!
SELECT *
FROM tb_movie_cust
,(
SELECT avg(cnt) avg_by_grade
,max(cnt) max_by_grade
,min(cnt) min_by_grade
FROM ( SELECT count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
) B
) B
,(
SELECT cust_grade AS grade_by_min_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt
) A1
LIMIT 1
) A1
,( SELECT cust_grade AS grade_by_max_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt desc
) A2
LIMIT 1
) A2
;
전의 코드에서 하나를 더 추가해줬다. 어떤 결과가 나올까?
tb_movie_cust =9건
B=1건
A1=1건
A2=1건
으로
9*1*1*1 = 9건이 나온다!
결국 다 합치면 ❕❕
SELECT count(*) "전체고객수"
,count(DISTINCT cust_grade) "등급의 개수"
,max(avg_by_grade) "등급별 평균 고객수" -
,max(max_by_grade) "등급별 최대 고객수"
,max(min_by_grade) "등급별 최소 고객수"
,max(grade_by_max_emp_count) "최소고객수의등급"
,max(grade_by_min_emp_count) "최소고객수의등급"
-> 여기 부분들은 max로 하는 이유는 이미 집계함수를 썼기 때문에 뒤에 값들도 집계함수로 감싸주어야함.
어차피 한 table에 결과 1개만 반환되므로 max, min 아무것도 상관없이 사용가능!
FROM tb_movie_cust
,(
SELECT avg(cnt) avg_by_grade
,max(cnt) max_by_grade
,min(cnt) min_by_grade
FROM ( SELECT count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
) B
) B
,(
SELECT cust_grade AS grade_by_min_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt
) A1
LIMIT 1
) A1
,( SELECT cust_grade AS grade_by_max_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt desc
) A2
LIMIT 1
) A2
;
이것 보고 한번더 만져서 확인해보았는데,
SELECT *
FROM (
SELECT count(cust_id), count(DISTINCT cust_grade)
FROM tb_movie_cust
) C -> 이렇게 넣어서 한번에 뽑아내도 결과는 같다!! 하지만, 나중에 유지보수 할때 안좋은 방법일듯.
,(
SELECT avg(cnt) avg_by_grade
,max(cnt) max_by_grade
,min(cnt) min_by_grade
FROM ( SELECT count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
) B
) B
,(
SELECT cust_grade AS grade_by_min_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt
) A1
LIMIT 1
) A1
,( SELECT cust_grade AS grade_by_max_emp_count
FROM (SELECT cust_grade
,count(*) cnt
FROM tb_movie_cust
GROUP BY cust_grade
ORDER BY cnt desc
) A2
LIMIT 1
) A2
;
'DB > postgres' 카테고리의 다른 글
psql: 치명적오류: 사용자 "postgres"의 password 인증을 실패했습니다 (0) | 2021.06.10 |
---|---|
postgresql - dvdrental 실습(7) : Cartesian Product (0) | 2021.03.24 |
postgresql - dvdrental 실습(6) (0) | 2021.03.11 |
postgresql - dvdrental 실습(5) (0) | 2021.03.10 |
postgresql - dvdrental 실습(4) (0) | 2021.03.09 |
postgresql - dvdrental 실습(3) (0) | 2021.02.25 |