개이득인개발
4k
2020-10-28 20:24:11 작성 2020-10-28 20:24:37 수정됨
3
256

postgresql 테이블 조인 결과 쿼리를 만드려고 합니다.


room 테이블

no     roomName   teacher

1          보통반         이름1

2          심화반         이름2


score 테이블

room_no    student_no    kor_scoer    eng_score   math_score

     1                   1                 70                  50               90

     1                   2                 90                  80               70

     1                   3                 75                  50               56

                                                  .

                                                  .

                                                  .


인 테이블이 있다고 가정하겠습니다. room 테이블과 score테이블은 1:n 관계입니다.

제가 두 테이블은 조인해서 쿼리를 아래처럼 뽑고 싶은데 어떻게 시작해야할 지 잘 모르겠습니다..

아시는분 있으면 조언좀 부탁드립니다

 room_no    scoer1     scoer2     scoer3

      1              70            90            75           <-- 학생 세 명의 국어 점수

      1              50            80            50           <-- 학생 세 명의 영어 점수

      1              90            70            56           <-- 학생 세 명의 수학 점수

                                                  .

                                                  .

                                                  .


이렇게 세로가 아닌 가로로 한줄로 세 명의 학생 점수를 한 로우로 표현하고 싶습니다. 

 

0
  • 답변 3

  • 행복한나날을
    89
    2020-10-29 00:47:11 작성 2020-10-29 00:48:49 수정됨

    일단 제가 PostgreSQL 관련하여 프로젝트에서 고민되었던 부분이 행과 열을 서로 바꾸는 부분인데...
    모처럼 관련 제가 답을 할 수 있는 듯 해서 많이 찾아봤는데...

    완전한 형태는 아니지만 제가 최대한 찾아본 걸로 해서 답을 드리겠습니다.

    일단 저는 join을 쓰지 않았구요. PostgreSQL에서는 행과 열을 바꾸는 extension인 crosstab이 있습니다.
    이를 사용해봐도 좋지만 사용법이 저한테는 불편해서 이거 안 쓰고 표현할 수 있는 방법이 없을까 찾아봤습니다.

    일단 두 단계를 거쳐 표현을 해보았는데...

    1. https://stackoverflow.com/a/10625294 에서 언급한 방법을 토대로 쿼리를 구성하여 

    SELECT room_no,
           unnest(array['kor_score', 'eng_score', 'math_score']) AS "subject",
           unnest(array[kor_score, eng_score, math_score]) AS "score",
           student_no
    FROM score
    ORDER BY "subject", student_no;


    일단 이 형태로 변환을 1차로 진행하였습니다.


    2. https://stackoverflow.com/a/40093700 와 https://stackoverflow.com/a/42041676 의 답변을 토대로 쿼리를 아래와 같이 구성하여 아래와 같은 결과를 얻었습니다(1번 결과를 score_list 란 임시 테이블 또는 뷰를 생성).

    SELECT room_no, subject, 
      json_object_agg(student_no, score) AS score
    FROM score_list
    RIGHT JOIN
      (
        (SELECT DISTINCT subject FROM score_list) a
        CROSS JOIN
        (SELECT DISTINCT student_no FROM score_list) b
      ) c USING (subject, student_no)
    GROUP BY room_no, subject
    ORDER BY subject;

    일단 제가 해볼 수 있는 부분은 여기까진 듯 합니다... 더 좋은 답을 기대해 봅니다. 그럼!

  • 개이득인개발
    4k
    2020-10-29 09:38:03

    행복한나날을

    성의있는 답변 너무나 감사합니다. 정말 고맙습니다.

    혹시 PostgreSQL관련해서 궁금한점 더 물어볼 수 있을까요?

  • 행복한나날을
    89
    2020-10-29 21:37:03 작성 2020-10-29 21:37:41 수정됨

    개이득인개발

    PostgreSQL로 웹 프로젝트를 하고는 있지만 제가 신입이라 찾아서 답을 드리는 거라서요ㅠㅠ
    도움이 안 될 수 있지만 말씀해주세요.

  • 로그인을 하시면 답변을 등록할 수 있습니다.