DB/postgres

postgresql - dvdrental 실습(7) : Cartesian Product

@예민한 방구석여포 힘히힘 2021. 3. 24. 10:24
반응형

패스트 캠퍼스 ~ 패스트 캠퍼스~~

 

난도 🤦‍♂️🤦‍♂️🤦‍♂️🤦‍♂️

 

 

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

Tag

, , ,

'DB/postgres'의 다른글

관련글

댓글 0
1 2 3 4 5 6 ··· 8
더보기

티스토리툴바