database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 10356 게시물 읽기
No. 10356
데이터 분석함수(집계, 그룹, 윈도우, ..) 중복 사용법 질문
작성자
포스트그레스
작성일
2022-12-14 15:14
조회수
494

 

윈도우함수를 활용하여 구한 값을 group by 절에 사용할 수 있나요?

FIRST_VALUE(S.area) OVER (PARTITION BY C.title ORDER BY s.rank) as class_area <<- 아래 예시 쿼리 참고

윈도우함수나 그룹함수를 사용하지 않더라도 다른 방안이 있으면 조언 부탁드립니다. (속도가 빠른 방법을 선호합니다...)


예시) 각 수업별로 수강신청 학생 중에서 석차가 가장 높은 학생이 선호하는 지역에 수업을 개설한다면,

각 주제별로 각 지역에서 개설된 수업의 건수를 구하는 쿼리를 작성해보려고 합니다.


수업 (class)

제목 (title) 주제 (subject) 개설 지역 (class_area)
가나다라 국어 ?
1111 수학 ?
1234 수학 ?


학생 (student)

 

이름 (name) 석차 (rank) 선호 지역 (area)
뽀로로 1 강남
피카추 2 서초
꼬부기 3 송파
이상해 4 강남
파이리 5 서초


수강신청 (apply)

 

제목 (class.title) 학생 (student.name)
가나다라 뽀로로
가나다라 피카추
가나다라 꼬부기
1111 이상해
1111 파이리
1234 꼬부기
1234 파이리



쿼리 >>> 구문 오류로 실행 불가!

SELECT C.subject as subject,

          FIRST_VALUE(S.area) OVER (PARTITION BY C.title ORDER BY s.rank) as class_area,

          count(distinct C.title) as cnt

FROM class C

INNER JOIN apply A on A.title = C.title

INNER JOIN student S on S.name = A.name

GROUP BY C.subject, class_area


수강신청 목록 (3개 테이블 조인)

 

제목 (title) 주제 (subject) 이름 (name) 석차 (rank) 선호 지역 (area)
가나다라 국어 뽀로로 1 강남
가나다라 국어 피카추 2 서초
가나다라 국어 꼬부기 3 송파
1111 수학 이상해 4 강남
1111 수학 파이리 5 서초
1234 수학 꼬부기 3 송파
1234 수학 파이리 5 서초


< 희망 결과 >

국어(가나다라) > 뽀로로(1) > 강남

수학(1111) > 이상해(4) > 강남

수학(1234) > 꼬부기(3) > 송파


결과

 

주제 (subject) 개설 지역 (class_area) 건수 (cnt)
국어 강남 1
과학 강남 1
과학 송파 1

 

이 글에 대한 댓글이 총 2건 있습니다.

윈도우함수를 활용하여 구한 값을 group by 절에 사용할 수 있나요?


이게 맞다면, 정말 이걸 하고 싶다면,


select ... from (윈도우함수를 활용하여 구한 집합) ... group by


이렇게 인라인뷰를 쓰면 됩니다.


제가 한 번 짜본 쿼리는


 

select distinct on (c.title) 1 as cnt, c.subject, s.area

from class as c, student as s, apply as a

where a.class_title = c.title and s.name = a.student_name

order by c.title, s.rank;

 

cnt subject area
1 수학 강남
1 수학 송파
1 국어 강남


이렇네요 이게 인라인뷰가 되고, group by 하고, cnt의 sum()이면 될듯


저가 보기에는 apply에 subject를 함께 포함하여 비정규화 하면 자료가 많을 경우 성능이 엄청나게 좋아질 것으로 예상합니다.

김상기(ioseph)님이 2022-12-15 13:35에 작성한 댓글입니다.
집계함수와 분석함수로 나누어 생각해보면
분석함수(집계함수) 형태의 중첩은 가능하지만
집계함수(분석함수) 형태의 중첩은 불가능합니다.
가능하게 하는 간단한 방법은 인라인뷰를 사용하는 것입니다.
그리고 first_value 보다는 row_number 을 많이 사용합니다.

SELECT *
  FROM (SELECT c.title
             , c.subject
             , s.area
             , ROW_NUMBER() OVER(PARTITION BY c.title ORDER BY s.rank) rn
          FROM class c
         INNER JOIN apply a
            ON a.title = c.title
         INNER JOIN student s
            ON s.name = a.name
        ) a
 WHERE rn = 1
;

 
마농(manon94)님이 2022-12-19 14:13에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
10382ERROR: uncommitted xmin 4194661 from before xid cutoff 62528296 needs to be frozen 관련 문의 사항 [8]
반지현
2023-01-12
210
10381Postgresql Auto Failover (PAF) 관련 질문 [1]
카비
2023-01-02
423
10358[디린이] Postgresql13 DB계정 권한 변경하는 방법 [1]
디린이
2022-12-26
386
10356데이터 분석함수(집계, 그룹, 윈도우, ..) 중복 사용법 질문 [2]
포스트그레스
2022-12-14
494
10355DB 삭제후 Slave DB에서만 지속적인 slow query 발생 [1]
형씨
2022-12-07
486
10354ora2pg blob -> bytea 전환시 blob 크기 2배 되는 현상 [3]
조수연
2022-11-16
732
10353pgday.Seoul 2022 행사에 초대합니다.
김상기
2022-11-07
908
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.067초, 이곳 서비스는
	PostgreSQL v14.2로 자료를 관리합니다