[MSSQL] PARTITION BY를 이용한 그룹 함수 사용

[MSSQL] PARTITION BY를 이용한 그룹 함수 사용

PARTITION BY를 이용한 그룹 함수(SUM, AVG, MAX, MIN) 사용 쿼리

WITH TEMP_TABLE_1 AS
(
SELECT '1반' AS [반정보], '일월~' AS [이름], 90 AS [국어], 80 AS [영어], 70 AS [수학]
 UNION ALL
SELECT '1반' AS [반정보], 'Feb!' AS [이름], 70 AS [국어], 60 AS [영어], 100 AS [수학]
 UNION ALL
SELECT '1반' AS [반정보], '3월@' AS [이름], 80 AS [국어], 80 AS [영어], 50 AS [수학]
 UNION ALL
SELECT '1반' AS [반정보], '사월#' AS [이름], 50 AS [국어], 80 AS [영어], 75 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], 'May$' AS [이름], 70 AS [국어], 65 AS [영어], 45 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], '6월%' AS [이름], 85 AS [국어], 90 AS [영어], 75 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], '칠월~' AS [이름], 95 AS [국어], 65 AS [영어], 70 AS [수학]
 UNION ALL
SELECT '2반' AS [반정보], 'Aug!' AS [이름], 50 AS [국어], 70 AS [영어], 95 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], '9월@' AS [이름], 85 AS [국어], 85 AS [영어], 85 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], '십월*' AS [이름], 60 AS [국어], 85 AS [영어], 70 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], 'Nov&' AS [이름], 80 AS [국어], 60 AS [영어], 65 AS [수학]
 UNION ALL
SELECT '3반' AS [반정보], '12월^' AS [이름], 100 AS [국어], 60 AS [영어], 75 AS [수학]
)

SELECT [반정보]
     , [이름]
     , SUM([국어])OVER(PARTITION BY [반정보]) AS [반별 총 국어점수]
     , AVG([영어])OVER(PARTITION BY [반정보]) AS [반별 평균 영어점수]
     , MAX([수학])OVER(PARTITION BY [반정보]) AS [반별 수학점수 최댓값]
     , MIN([국어])OVER(PARTITION BY [반정보]) AS [반별 국어점수 최솟값]
  FROM TEMP_TABLE_1
 WHERE 1=1

 

※ 실행화면

위의 쿼리 실행화면