반응형
/*
-- Title : [SQL2017] 그룹함수, 집계함수, 순위함수, 윈도우함수, 분석함수
-- Reference : 디비랑
-- Tag : group function aggregation aggregate function rank function window function analysis analytic function
*/
■ Data Preparation
--▶ 임시 테이블 삭제
DROP TABLE #ttt;
--▶ 임시 테이블 생성
CREATE TABLE #ttt
( id INT NOT NULL IDENTITY(1,2)
, cid VARCHAR(1) -- 기업코드
, cnm VARCHAR(100) NULL -- 기업명
, kcd VARCHAR(100) NULL -- 산업코드
, knm VARCHAR(100) NULL -- 산업명
, num INT NULL -- 계산값1
, num2 NUMERIC(10,1) NULL -- 계산값2
);
--▶ 데이터 입력
INSERT INTO #ttt
SELECT 'A', '삼성전자', 'C20001', '기타 무선 통신장비 제조업', 10, 15.8 UNION ALL
SELECT 'A', '삼성전자', 'C20001', '기타 무선 통신장비 제조업', 20, 45.5 UNION ALL
SELECT 'A', '삼성전자', 'C20002', '유선 통신장비 제조업', 30, 32.4 UNION ALL
SELECT 'A', '삼성전자', 'C20002', '유선 통신장비 제조업', 40, 57.7 UNION ALL
SELECT 'A', '삼성전자', 'C20002', '유선 통신장비 제조업', 50, 12.3 UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 'B', '엘지전자', 'C20003', '광학 장비 제조업', 60, 22.6 UNION ALL
SELECT 'B', '엘지전자', 'C20004', '기타 무선 통신장비 제조업', NULL, 21.4 UNION ALL
SELECT 'B', '엘지전자', 'C20004', '기타 무선 통신장비 제조업', 60, 22.6 UNION ALL
SELECT 'B', '엘지전자', 'C20004', '기타 무선 통신장비 제조업', NULL, 21.4 UNION ALL
SELECT 'B', '엘지전자', 'C20004', '기타 무선 통신장비 제조업', 70, 10.1 UNION ALL
SELECT 'B', '엘지전자', 'C20004', '기타 무선 통신장비 제조업', NULL, 94.5 UNION ALL
SELECT 'B', '엘지전자', 'C20004', '기타 무선 통신장비 제조업', NULL, 94.5 UNION ALL
SELECT 'B', '엘지전자', 'C20006', '방송장비 제조업', 80, 24.2;
--▶ 입력 확인 NSrimSum
SELECT *
FROM #ttt;
■ 그룹함수(Group Function)
ㅁ GROUPING SET
ㅁ ROLLUP & CUBE
■ 순위함수(RANK Function, 랭크함수)
ㅁ RANK, DENSE_RANK, ROW_NUMBER, PERCENT_RANK, NTILE
■ 집계함수(Aggregate Function)
SELECT COUNT(*) "건수", COUNT(num) "num건수", SUM(num) "합계", MAX(num) "최대"
, MIN(num) "최소", AVG(num) "평균"
FROM #ttt;
SELECT cid, cnm
, COUNT(*) "건수", COUNT(num2) "num건수", SUM(num2) "합계", MAX(num2) "최대"
, MIN(num2) "최소", AVG(num2) "평균"
FROM #ttt
GROUP BY cid, cnm;
■ 윈도우 함수(Window Function)
ㅁ OVER() 및 파티션/윈도우별 조회
--▶ OVER() : 각 ROW별 출력
SELECT *
, SUM(num) OVER() "전체 합계"
, SUM(num) OVER(PARTITION BY cid) "기업별 합계"
, SUM(num) OVER(PARTITION BY cid, kcd) "기업-KCD별 합계"
FROM #ttt
ORDER BY id;
--▶ 파티션/윈도우별 (누적/전체)합계
SELECT id, cid, cnm, kcd, num, '●' "X"
, "전체합계" = SUM(num) OVER()
, "id별 누적합계" = SUM(num) OVER(ORDER BY id)
, "cid별 누적합계" = SUM(num) OVER(ORDER BY cid)
, "kcd별 누적합계" = SUM(num) OVER(ORDER BY kcd)
, "파티션간 전체합계" = SUM(num) OVER(PARTITION BY cid)
, "파티션간 id별 누적합계" = SUM(num) OVER(PARTITION BY cid ORDER BY id)
FROM #ttt
ORDER BY id;
ㅁ ROW 처리
--▶ ROW 처리 (1)
-- UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫 번째 행(시작행)
-- CURRENT ROW : 윈도우의 시작 위치가 현재 행(현재행)
-- UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 행(마지막행)
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 기본값
-- 1 PRECEDING : 현재행으로부터 이전 1행
-- 1 FOLLOWING : 현재행으로부터 다음 1행
SELECT id, cid, cnm, kcd, num, '●' "X"
, "ORD id wUPCR" = SUM(num) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- 시작행부터 현재행까지 누적합계
, "ORD kcd wUPCR" = SUM(num) OVER(ORDER BY kcd ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- 시작행부터 현재행간 누적합계(kcd null은 정렬에서 첫행이라 null)
, '●' "Y"
, "ORD id w1PCR" = SUM(num) OVER(ORDER BY id ROWS BETWEEN 1 preceding and current row)
-- 이전 1행부터 현재행간 SUM(여기서 null은 0처리), 현재행 + 이전행 합계
, "ORD id wCR1F" = SUM(num) OVER(ORDER BY id ROWS BETWEEN current row and 1 following)
-- 현재행부터 다음 1행간 SUM(여기서 null은 0처리), 현재행 + 다음행 합계
FROM #ttt
ORDER BY id;
--▶ ROW 처리 (2)
SELECT id, cid, cnm, kcd, num, '●' "X"
, "ORD kcd wUPUF" = sum(num) OVER(ORDER BY kcd ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-- 시작행부터 마지막행까지 전체합계
, "ORD kcd wUPCR" = sum(num) OVER(ORDER BY kcd ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- 시작행부터 현재행까지 누적합계
, "ORD kcd wCRUF" = sum(num) OVER(ORDER BY kcd ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
-- 현재행부터 마지막행까지 누적합계
FROM #ttt
ORDER BY kcd;
--▶ ROW 처리 (3)
SELECT id, cid, cnm, kcd, num, '●' "X"
, "윈도우(kci)별 합계" = SUM(num) OVER(ORDER BY kcd)
, "ORD kcd w1P" = SUM(num) OVER(ORDER BY kcd ASC ROWS 1 PRECEDING)
, "ORD kcd w1PCR" = SUM(num) OVER(ORDER BY kcd ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
, "ORD kcd w1P1F" = SUM(num) OVER(ORDER BY kcd ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM #ttt
ORDER BY id;
ㅁ RANGE 처리
--▶ RANGE 처리
SELECT id, cid, cnm, kcd, num, '●' "X"
, "ORD kcd wUPCR" = sum(num) OVER(ORDER BY kcd ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
, "ORD kcd gUPCR" = sum(num) OVER(ORDER BY kcd RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #ttt
ORDER BY id;
■ 분석함수(Analytic Function)
ㅁ 누적 분산 분포(Cumulative Distribution Function, CUME_DIST())
--▶ 누적 분산 분포(Cumulative Distribution Function, CUME_DIST())
SELECT *, cume_dist() OVER(ORDER BY kcd_cnt) "비율누적"
FROM
(
SELECT *, '●' "X"
, COUNT(*) OVER (PARTITION BY kcd) "kcd_cnt"
FROM #ttt
WHERE kcd IS NOT NULL
) a
ORDER BY kcd_cnt DESC;
/* 계산 과정 */
SELECT *, SUM(비율) OVER(ORDER BY 개수) "비율누적"
FROM (
SELECT *, cast(개수 AS FLOAT)/총합 "비율"
FROM (
SELECT *, SUM(개수) OVER() "총합"
FROM (
SELECT kcd, COUNT(*) "개수"
FROM #ttt
WHERE kcd IS NOT NULL
GROUP BY kcd
) a
) b
) c
ORDER BY 개수 DESC;
ㅁ 윈도우의 첫/마지막 번째 값 반환 : FIRST_VALUE(), LAST_VALUE()
--▶ 윈도우의 첫/마지막 번째 값 반환 : FIRST_VALUE(), LAST_VALUE()
SELECT *, '●' "X"
, first_value(num) OVER(ORDER BY kcd) "fv_ord_kcd1"
, first_value(num) OVER(ORDER BY kcd ROWS UNBOUNDED PRECEDING) "fv_ord_kcd2"
, first_value(num) OVER(ORDER BY kcd RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "fv_ord_kcd3"
, first_value(num) OVER(ORDER BY kcd ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "fv_ord_kcd4"
, '●' "Y"
, last_value(num) OVER(ORDER BY kcd) "lv_ord_kcd1"
, last_value(num) OVER(ORDER BY kcd ROWS UNBOUNDED PRECEDING) "lv_ord_kcd2"
, last_value(num) OVER(ORDER BY kcd RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "lv_ord_kcd3"
, last_value(num) OVER(ORDER BY kcd ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "lv_ord_kcd4"
FROM #ttt
WHERE cid IS NOT NULL
ORDER BY id
ㅁ 윈도우의 이전/이후 값 반환 : LAG(), LEAD()
--▶ 윈도우의 이전/이후 값 반환 : LAG(), LEAD()
SELECT *, '●' "X"
, LAG(num, 1, null) OVER(PARTITION BY cid ORDER BY id) "LAG_1_NL"
, LAG(num, 1, 9999) OVER(PARTITION BY cid ORDER BY id) "LAG_1_NN"
, LAG(num, 2, null) OVER(PARTITION BY cid ORDER BY id) "LAG_2_NL"
, '●' "Y"
, LEAD(num, 1, null) OVER(PARTITION BY cid ORDER BY id) "LEAD_1_NL"
, LEAD(num, 1, 9999) OVER(PARTITION BY cid ORDER BY id) "LEAD_1_NN"
, LEAD(num, 2, null) OVER(PARTITION BY cid ORDER BY id) "LEAD_2_NL"
FROM #ttt
WHERE cid IS NOT NULL
ORDER BY id
반응형