[Sql분석] Having / Window Function / Lead & Lag

Having

---

• Group by 된 집계값에 조건을 걸어줄 때 사용
• 예를 들어
  "동일한 Department 에 EducationField을 가진 사람들끼리 그룹군을 나눈다고 합니다.
  한 그룹군에 30명 이하인 그룹군만 보여주세요" 라는 요청이 있다면

SELECT 
	hc.Department  
	,hc.EducationField
	,count(DISTINCT hc.EmployeeNumber) AS HC
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn
ON hc.EmployeeNumber = hn.EmployeeNumber 
WHERE 1=1
	AND Attrition = 'No'
GROUP BY 1,2
HAVING HC >= 100
ORDER BY HC DESC 
;

• Having은 정렬 전에 Group by 된 테이블에 조건을 걸어주는 것이기 때문에
  Order by 전에 와야함

Window Function (집계함수)

---

• 집계된 결과값을 기존 데이터에 추가하여 보여줌

• 합계, 평균, 순위 매기기, 순서 조작 등

• 결과를 출력해주되, 결과 건수가 줄어들지 않음

• Group by 와 집계함수를 활용하여 특정 컬럼을 기준으로 집계된 결과를 산출한다는 공통점이 있음

• BUT! Group by는 요약된 집계결과만 보여주기 때문에 최종 결과 건수가 줄어듬

• Window Function을 이용하면 기존 데이터에 집계된 값을 추가하여 보여줌

JobLevel, PerformanceRating 별 인원 수 집계에서 추가적으로 같은 JobLevel 별 인원수의 합계와 PerformanceRating별 인원수의 합계도 각각 궁금해진다면?

1. JobLevel, PerformanceRating 별 인원 수 집계

SELECT JobLevel, PerformanceRating
	, count(DISTINCT hn.EmployeeNumber) AS hc
FROM hr.hr_cate hc 
LEFT JOIN hr.hr_number hn 
ON hc.EmployeeNumber = hn.EmployeeNumber 
WHERE 1=1
	AND Attrition = 'No'
GROUP BY 1,2
ORDER BY 1,2
;

<결과>

2. JobLevel 별 인원수의 합계와 PerformanceRating별 인원수의 합계 각각 표시

SELECT JobLevel, PerformanceRating
	, count(DISTINCT hn.EmployeeNumber) AS hc
	, sum(count(DISTINCT hn.EmployeeNumber)) OVER (PARTITION BY hn.JobLevel) AS hc_joblevel
	, sum(count(DISTINCT hn.EmployeeNumber)) OVER (PARTITION BY hn.PerformanceRating) AS hc_performance
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn 
ON hc.EmployeeNumber = hn.EmployeeNumber 
WHERE 1=1
	AND Attrition = 'No'
GROUP BY 1,2
ORDER BY 1,2
;

<결과>

• over로 창문을 열어주고
• patition by로 구하고 싶은 합계의 기준컬럼을 정해주면 됨

Lead & Lag

---

• 순서를 조작해주는 함수
• Lead는 다음에 있는 걸 당겨옴
• Lag는 뒤에 있는걸 끌고옴

SELECT JobLevel 
	,count(DISTINCT hn.EmployeeNumber) AS hc
	,lead(count(DISTINCT hn.EmployeeNumber)) OVER (ORDER BY JobLevel) lead_hc 
    --- lead(count(DISTINCT hn.EmployeeNumber),2) 이렇게 하면 두칸씩 당길수 있음
	,lag(count(DISTINCT hn.EmployeeNumber)) OVER (ORDER BY JobLevel) lag_hc
    --- lag(count(DISTINCT hn.EmployeeNumber),2) 이렇게 하면 두칸씩 밀수 있음
FROM hr.hr_cate hc 
LEFT JOIN hr.hr_number hn 
ON hc.EmployeeNumber = hn.EmployeeNumber 
GROUP BY 1
ORDER BY 1
;

<결과>

• 마찬가지로 over로 문을 열어주고
• order by로 순서를 정렬기준을 정해줘야 끌거나 당겨올 수 있음
• 만약 그룹핑된 값별로 순서를 조작해주고 싶다면?

SELECT Department , hn.JobLevel 
	, count(DISTINCT hc.EmployeeNumber) AS hc
	, lead(count(DISTINCT hc.EmployeeNumber)) OVER (PARTITION BY Department ORDER BY hn.JobLevel) AS lead_hc
	, lag(count(DISTINCT hc.EmployeeNumber)) OVER (PARTITION BY Department ORDER BY hn.JobLevel) AS lag_hc
FROM hr.hr_cate hc 
LEFT JOIN hr.hr_number hn 
ON hc.EmployeeNumber = hn.EmployeeNumber 
GROUP BY 1,2
ORDER BY 1,2
;

<결과>

• 그룹핑될 컬럼을 partition by 로 정해주면 됨