SELECT A.이름, A.성별, A.과목, A.점수 , MAX(점수) OVER (PARTITION BY 이름) AS 개인별최고점수 , AVG(점수) OVER (PARTITION BY 이름) AS 개인별평균점수 , SUM(점수) OVER (PARTITION BY 0) AS 전체점수합계 , COUNT(과목) OVER (PARTITION BY 이름) AS 개인별수강과목 , SUM(점수) OVER (PARTITION BY 성별, 과목) AS 성별과목별점수합계 , 과목 || '_' || ROW_NUMBER() OVER (PARTITION BY 과목 ORDER BY 점수 DESC) AS 과목별등수 , 성별 || '_' || RANK() OVER (PARTITION BY 성별 ORDER BY 점수 DESC) AS 성별등수 FROM ( SELECT 'A' AS 이름, 'F' AS 성별, 'KOR' AS 과목, 90 AS 점수 FROM DUAL UNION SELECT 'A', 'F', 'MAT', 95 FROM DUAL UNION SELECT 'B', 'M', 'KOR', 80 FROM DUAL UNION SELECT 'B', 'M', 'MAT', 100 FROM DUAL UNION SELECT 'C', 'F', 'KOR', 70 FROM DUAL UNION SELECT 'C', 'F', 'MAT', 85 FROM DUAL UNION SELECT 'D', 'M', 'KOR', 90 FROM DUAL ) A ORDER BY A.이름, A.과목
--------------------
PARTITION BY 안쓰고 GROUP BY 로 위와 동일한 결과값을 얻고싶은데
생각처럼 되지가 않네요...