스터디노트 39일차

/* Table Create

• 1. CREATE TABLE -> INSERT INTO;
• 2. SOURCE TABLE -> CREATE (SELECT FROM );
     */

-- 1. Object (Text) Type
CREATE TABLE hr.hr_cate
(
EmployeeNumber int(11),
Attrition VARBINARY(50),
BusinessTravel VARBINARY(50),
Department VARBINARY(50),
EducationField VARBINARY(50),
Gender VARBINARY(50),
JobRole VARBINARY(50),
MaritalStatus VARBINARY(50),
OverTime VARBINARY(50)
);

INSERT INTO hr.hr_cate
(
SELECT EmployeeNumber,Attrition,BusinessTravel,
Department,EducationField,Gender,JobRole,MaritalStatus,OverTime
FROM hr.hr_employee_attrition hea
);

-- 2. Numeric Table

CREATE TABLE hr.hr_number
(SELECT
EmployeeNumber,Age,DailyRate,DistanceFromHome,Education,EmployeeCount,
EnvironmentSatisfaction,HourlyRate,JobInvolvement,JobLevel,JobSatisfaction,
MonthlyIncome,MonthlyRate,NumCompaniesWorked,PercentSalaryHike,PerformanceRating,
RelationshipSatisfaction,StandardHours,StockOptionLevel,TotalWorkingYears,TrainingTimesLastYear,WorkLifeBalance,
YearsAtCompany,YearsInCurrentRole,YearsSinceLastPromotion,YearsWithCurrManager
FROM hr.hr_employee_attrition hea
)
;
-- hr_cate & hr_number
-- JOIN KEY : EmployNumber
SELECT *
FROM hr.hr_cate hc
INNER JOIN hr.hr_number hn
ON hc.EmployeeNumber = hn.EmployeeNumber
;

SELECT hc.EmployeeNumber
,hc.Attrition
,hc.Gender
,hn.JobSatisfaction
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn
ON hc.EmployeeNumber = hn.EmployeeNumber
;

SELECT hc.EmployeeNumber
,hc.Attrition
,hc.Gender
,hn.JobSatisfaction
FROM hr.hr_cate hc
RIGHT JOIN hr.hr_number hn
ON hc.EmployeeNumber = hn.EmployeeNumber
;

-- USING JOIN KEY
-- JOIN KEY Column 이름이 완벽하게 동일

SELECT *
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn
USING (EmployeeNumber)
;

-- DATASET
SELECT Department , EducationField
,count(*) 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
;

-- LIMIT

SELECT Department , EducationField
,count(*) 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 hc DESC
LIMIT 5
;

-- HAVING

SELECT Department , EducationField
,count(*) 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

;
-- WINDOW 함수란?
-- 집계된 결과값을 기존 데이터에 추가하여 보여줌
-- 합계,평균,순위 메기기 , 순서 조작등등
-- 결과를 SHOW해주되 , 결과 건수가 줄어들지 않음

/* WINDOW FUCTION

• SELECT WINDOW_FUCTION( )

• 		OVER ( PARTITION BY <Column> 

• 			   ORDER BY             )

• */
  

-- JobLevel , PerformanceRating 별 HC(1,233 rows)
-- GROUP BY : 집계된 결과값으로 요약! < 요약이 주요
SELECT JobLevel
, count(DISTINCT hn.EmployeeNumber) hc
FROM hr.hr_number hn
LEFT JOIN hr.hr_cate hc ON hn.EmployeeNumber = hc.EmployeeNumber
WHERE 1=1
AND Attrition = 'No'
GROUP BY 1
ORDER BY 1
;

-- (1) JobLevel 별 인원수의 합계

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

-- (2) PerformanceRating 별 인원수의 합계도 궁금해진다면?

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

-- Window Function

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

-- LEAD & LAG
-- LEAD = 다음에 있는걸 당겨옴 , LAG = 뒤에 있는걸 대려옴
/*

• SELECT LEAD or LAG (col)

• 		OVER ( ORDER BY col)

• FROM TABLE
  */

-- JobLevel
SELECT JobLevel
, count(DISTINCT hn.EmployeeNumber) HC
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
GROUP BY 1
ORDER BY 1

-- LEAD & LAG

SELECT JobLevel
, count(DISTINCT hn.EmployeeNumber) HC
, lead(count(DISTINCT hn.EmployeeNumber)) OVER (ORDER BY JobLevel) lead_HC
, lag(count(DISTINCT hn.EmployeeNumber)) OVER (ORDER BY JobLevel) lag_HC
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
GROUP BY 1
ORDER BY 1

-- LEAD & LOG : N개의 ROW를 조작 -> N=2 면 2칸 간격으로 LEAD & LAG함

SELECT JobLevel
, count(DISTINCT hn.EmployeeNumber) HC
, lead(count(DISTINCT hn.EmployeeNumber),2) OVER (ORDER BY JobLevel) lead_HC
, lag(count(DISTINCT hn.EmployeeNumber),2) OVER (ORDER BY JobLevel) lag_HC
FROM hr.hr_cate hc
LEFT JOIN hr.hr_number hn ON hc.EmployeeNumber = hn.EmployeeNumber
GROUP BY 1
ORDER BY 1

/*

• SELECT LEAD or LAG (col)

• 		OVER (  PARTITION BY col

• 				ORDER BY col)

• FROM TABLE
  */

SELECT Department , JobLevel
, count(DISTINCT hn.EmployeeNumber) HC
, lead(count(DISTINCT hn.EmployeeNumber)) OVER (PARTITION BY Department ORDER BY JobLevel) lead_HC
, lag(count(DISTINCT hn.EmployeeNumber)) OVER (PARTITION BY Department ORDER BY JobLevel) 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