[TIL]SQLD: SQL의 활용 2(그룹 함수, 윈도우 함수)+ Speak + 매카시 논문

11. SQL의 활용 2

--11-1. 그룹 함수
  --!데이터 분석을 위한 3가지 함수: 집계(AGGREGATE)함수, 그룹(GROUP)함수, 윈도우 (WINDOW)함수 ⇒ 내장&다중행 함수
-- (1)집계 함수: COUNT(*/ORDER) , SUM, AVG, MAX, MIN 등 (2)윈도우 함수
-- (3)그룹 함수: 데이터에 대한 결산 연산 ex) 올해 총 매출량
--  ①ROLLUP 함수: 소그룹 간 소계(N+1개) 산출. 계층 구조이므로 순서 바뀌면 결과도 바뀜
SELECT 
	DNAME, 
	JOB, 
	COUNT(*) "Total Empl", 
	SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB); --> DNAME으로 먼저 묶이고 JOB으로, 마지막은 총합 → 두 칼럼 순서 바뀌면 결과도 바뀜, UNION ALL과 같은 결과(훨씬 간단)

--   •GROUPING 함수: ROLLUP 이나 CUBE에 의해서 그룹화 된 칼럼의 소계가 계산된 결과를 1로 표시하고 그 외의 결과는 0으로 표시
SELECT DNAME,
       GROUPING(DNAME),
       JOB,
       GROUPING(JOB),
       COUNT(*) "Total Empl",
       SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY DNAME, JOB;

--   •CASE문: ROLLUP에서 NULL로 표시되는 소계,합계의 값을 사용자 정의 값으로 명시
SELECT 
    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
    CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END            AS JOB,
    COUNT(*) "Total Empl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB);

--   •ROLLUP과 괄호: ROLLUP((A, B), C)와 (A, B, C)의 차이
SELECT 
	DNAME, 
	JOB, 
	MGR, 
	SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP((DNAME, JOB), MGR);

--  ②CUBE 함수: 결합 가능한 모든 값에 대해 집계를 생성하여 다차원적인 소계를 계산 ex) CUBE(A,B) = A, B, AB, all → 나열 순서 중요x
--  ex)
SELECT 
    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
    CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END            AS JOB,
    COUNT(*) "TotalEmpl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);

--  ③GROUPING SETS 함수: 표시된 모든 칼럼들에 대한 개별 집계 구할 수 있어 ROLLUP 과 CUBE 와 비슷한 결과를 얻을 수 있지만 좀 더 명시적으로 원하는 그룹 수준을 정할 수 있다 + 마지막에 () 넣으면 총계 구하기 가능
SELECT 
    DNAME,
    JOB,
    COUNT(*) "Total Empl",
    SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);

--10-2. 윈도우 함수
    --정의: 칼럼 간이 아닌 행과 행간의 관계를 정의. 분석함수, 순위 함수로도 알려짐.
-- (1) 기본 구조
SELECT WINDOW_FUNCTION(ARGUMENTS) 
			OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절]) 
FROM 테이블 명;
--  1)PARTITION BY: 전체 집합을 기준에 의해 소그룹으로 나눔. GROUP BY와 달리 분할만. 집약x
--  2)WINDOWING: 함수의 대상이 되는 행 기준의 범위를 지정 → 택1 ROWS(범위 지정 ex)BETWWEN 시작 AND 끝)) / RANGE(설정x 기본 값)
--   ROWS의 범위 종류 ①PRECEDING_N PRECEDING(N 이전 범위를 지정), UNBOUNDED PRECEDING(첫 번째 행)   
--                 ②FOLLOWING_N FOLLOWING(N 이전 범위를 지정), UNBOUNDED FOLLOWING(마지막 행까지)
--                 ③CURRENT ROW_현재 위치 의미-범위의 끝점 

-- (2) 그룹 내 순위 함수
--  1)RANK 함수: 동일한 값에 대해서는 동일한 순위를 부여
SELECT JOB,
       ENAME,
       SAL,
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK --> RANK를 매기려면 ORDER BY가 필요
FROM EMP;

--  2)DENSE_RANK: 순위 누적시키지 않고 순차대로 순위 부여 → 1, 22 후에 3
SELECT 
    JOB,
    ENAME,
    SAL,
    RANK() OVER (ORDER BY SAL DESC) RANK,
    DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

--  3)ROW_NUMBER함수: (각 벤더 별 기준으로)항상 순위를 부여

-- (3) 일반 집계 함수
--  1)SUM함수: 파티션 별 윈도우의 합을 구할 때 사
--   ①모든 합 함께 표현 시
SELECT 
    MGR, 
    ENAME, 
    SAL, 
    SUM(SAL) OVER ()
FROM EMP;

--   ②파티션 별 합을 함께 보고 싶을때
SELECT 
  MGR, 
	ENAME, 
	SAL, 
	SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

--   ③누적 합을 보고 싶을때 → '누적'이라면 안쪽에 ORDER BY 필요
SELECT 
    MGR, 
    ENAME, 
    SAL, 
    SUM(SAL) OVER (ORDER BY SAL)
FROM EMP
ORDER BY SAL;

--   ④누적 합에서 같은 데이터를 따로 각각의 ROW에 대해 처리하고 싶을때
-- ...
SUM(SAL) OVER (ORDER BY SAL ROWS 
							 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
						     AS SUM_SAL
-- ...

-- 2) MIN/MAX
-- 3) AVG 함수
-- BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행을 기준으로 파티션 내 앞,뒤 행을 지정하는 구문입니다.
SELECT 
    MGR,
    ENAME,
    HIREDATE,
    SAL,
    ROUND(AVG(SAL) OVER
       (PARTITION BY MGR ORDER BY HIREDATE
       ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) MGR_AVG
FROM EMP;

--  4) (참고) 그룹 내 행 순서 함수
--   1)FIRST_VALUE 함수: 파티션별 윈도우에서 가장 먼저 나온 값을 구할 때 사용 2)LAST_VALUE 함수
SELECT 
    DEPTNO,
    ENAME,
    SAL,
    FIRST_VALUE(ENAME) OVER
        (PARTITION BY DEPTNO 
				 ORDER BY SAL DESC 
				 ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP;

--   3)LAG 함수: 현재 값을 기준으로 이전 값들 중 지정한 위치의 값을 가져온다. 4)(4) LEAD 함수: 이후에 지정한 위치의 값 가져온다.
LAG(칼럼명[, 가져오고 싶은 위치, default])  -- 기본 default는 NULL

LAG(TEAM, 5, 0)    -- 5행 앞의 TEAM을 가져오고 가져올 값이 없으면 0으로 처리
LAG(SALARY)        -- 바로 직전 행의 SALARY 값    


--   (5) (참고) 그룹 내 비율 함수
--    1)RATIO_TO_REPORT 함수: 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 조회 
SELECT 
    ENAME, 
    SAL, 
    ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R_RESULT
FROM EMP
WHERE JOB = 'SALESMAN';

--    2)PERCENT_RANK 함수: 각 그룹의 순위를 퍼센트로 바꿔서 값을 표현
--    3)CUME_DIST 함수: 주어진 그룹에 대한 상대적인  누적 백분율을 반환하는 함수
--    4)NTITLE 함수: 입력되는 인자 값으로 N등분한 결과를 구할 때 사용
SELECT 
    ENAME, 
    SAL, 
    NTILE(4) OVER (ORDER BY SAL DESC) QUAR_TILE
FROM EMP;