오늘도 데이터분석

<과제1> 현업이 요청한 레이아웃으로 아래와 같이 최종 결과를 추출하는 sql 작성

상세요건

[1] 현재 재직 중인 임직원 중 자녀가 1명 이상인 사람

[2] 자녀의 생년월일이 2012년 1월 1일 ~ 2015년 12월 31일 경우만 대상에 포함

[3] 사내부부이면서 [1],[2] 조건에 만족하는 경우 자녀의 아버지 “직원번호” 기준으로 데이터 추출하고, 어머니는 리스트에서 제거할 것, 이 때 사내부부 여부는 Y로 표시, 배우자성명은 어머니 기준으로 표시

[4] 쌍생아의 경우 2명 모두 지원이 아님, 따라서 쌍생아 중 1명만 포함 시킬 것

[5] 쌍생아 1명을 가져오는 조건은 이름의 오름차순의 상위자로 추출할 것

풀이과정

: WITH문으로 자녀정보를 담은 테이블과, 사내부부 테이블을 따로 만들어 

 각 테이블을 EMP_C에 조인해서 최종 쿼리를 만드는 방법으로 생각. 

CHILD 테이블 

- FAM_C 테이블에서 2012년 1월 1일 ~ 2015년 12월 31일 사이에 태어난 자녀(A27) 정보만 담은 테이블

- 쌍생아의 경우 1명만 포함, 1명의 기준은 쌍생아 이름의 오름차순 상위자가 자녀이름에 들어감

 SELECT ch.emp_no emp_no
     , ch.fam_nm fam_nm
     , ch.rel_type_cd
     , ch.gender_cd gender_cd
     , ch.birth_ymd birth_ymd
     , tw.emp_no twin_emp_no
     , tw.twin twin_nm
FROM (SELECT *
      FROM FAM_C 
      WHERE rel_type_cd = 'A27'
        AND birth_ymd BETWEEN '20120101' AND '20151231')ch
      LEFT JOIN
     (SELECT FAM1.EMP_NO AS emp_no
           , FAM1.FAM_NM AS twin
      FROM FAM_C FAM1
           INNER JOIN FAM_C FAM2
           ON FAM1.EMP_NO = FAM2.EMP_NO
          AND FAM1.FAM_NM <> FAM2.FAM_NM
          AND FAM1.REL_TYPE_CD = FAM2.REL_TYPE_CD
          AND FAM1.REL_TYPE_CD = 'A27'
          AND FAM1.BIRTH_YMD = FAM2.BIRTH_YMD) tw
       ON ch.emp_no = tw.emp_no
WHERE ch.fam_nm <> tw.twin
  AND fam_nm < tw.twin
   OR tw.twin IS NULL;

쿼리결과

COUPLE 테이블 

- FAM_REL_C 테이블과 EMP_C테이블로 현재 재직중인 사내부부 중 남편의 정보만 담은 테이블

- 배우자의 성명 컬럼추가 

SELECT  hus.emp_no AS h_no
     , hus.emp_nm AS h_nm
     , e2.emp_nm AS w_nm
FROM 
(SELECT e1.emp_no AS emp_no
      , e1.emp_nm AS emp_nm
      , frel.emp_rel_no AS emp_rel_no
FROM fam_rel_c frel
    LEFT JOIN emp_c e1 ON e1.emp_no = frel.emp_no 
WHERE SYSDATE BETWEEN frel.sta_ymd AND frel.end_ymd
     AND SYSDATE BETWEEN e1.hire_ymd AND e1.retire_ymd 
     AND rel_type_cd = 'A18') hus
LEFT JOIN emp_c e2 ON e2.emp_no = hus.emp_rel_no;

쿼리 결과

최종 쿼리

- 사내부부여부, 쌍생아 여부 CASE 조건문 이용

- 자녀 나이는 현재날짜 기준 만나이로 계산 

- '현재 재직중' WHERE 조건문 이용

SELECT em.emp_no AS 직원번호
     , em.emp_nm AS 직원성명 
     , em.gender_cd AS 직원성별
     , (CASE WHEN co.h_no IS NULL THEN 'N' ELSE 'Y' END) AS 사내부부여부
     , co.w_nm AS 배우자성명
     , ch.fam_nm AS 자녀성명
     , ch.gender_cd AS 자녀성별
     , ch.birth_ymd AS 자녀생년월일
     , FLOOR((TO_CHAR(SYSDATE, 'YYYYMMDD') - ch.birth_ymd)/10000) AS 자녀나이
     ,(CASE WHEN ch.twin_nm IS NULL THEN 'N' ELSE 'Y' END) AS 쌍생아여부
FROM emp_c em
 INNER JOIN child ch ON em.emp_no = ch.emp_no
  LEFT JOIN couple co ON em.emp_no = co.h_no 
WHERE SYSDATE BETWEEN em.hire_ymd AND em.retire_ymd
ORDER BY em.emp_no;

WITH문 포함 

WITH couple AS(
SELECT  hus.emp_no AS h_no
     , hus.emp_nm AS h_nm
     , e2.emp_nm AS w_nm
FROM 
(SELECT e1.emp_no AS emp_no
      , e1.emp_nm AS emp_nm
      , frel.emp_rel_no AS emp_rel_no
FROM fam_rel_c frel
    LEFT JOIN emp_c e1 ON e1.emp_no = frel.emp_no 
WHERE SYSDATE BETWEEN frel.sta_ymd AND frel.end_ymd
     AND SYSDATE BETWEEN e1.hire_ymd AND e1.retire_ymd 
     AND rel_type_cd = 'A18') hus
LEFT JOIN emp_c e2 ON e2.emp_no = hus.emp_rel_no)
, child AS (
SELECT ch.emp_no emp_no
     , ch.fam_nm fam_nm
     , ch.rel_type_cd
     , ch.gender_cd gender_cd
     , ch.birth_ymd birth_ymd
     , tw.emp_no twin_emp_no
     , tw.twin twin_nm
FROM (SELECT *
      FROM FAM_C 
      WHERE rel_type_cd = 'A27'
        AND birth_ymd BETWEEN '20120101' AND '20151231')ch
      LEFT JOIN
     (SELECT FAM1.EMP_NO AS emp_no
           , FAM1.FAM_NM AS twin
      FROM FAM_C FAM1
           INNER JOIN FAM_C FAM2
           ON FAM1.EMP_NO = FAM2.EMP_NO
          AND FAM1.FAM_NM <> FAM2.FAM_NM
          AND FAM1.REL_TYPE_CD = FAM2.REL_TYPE_CD
          AND FAM1.REL_TYPE_CD = 'A27'
          AND FAM1.BIRTH_YMD = FAM2.BIRTH_YMD) tw
       ON ch.emp_no = tw.emp_no
WHERE ch.fam_nm <> tw.twin
  AND fam_nm < tw.twin
   OR tw.twin IS NULL
)

 
SELECT em.emp_no AS 직원번호
     , em.emp_nm AS 직원성명 
     , em.gender_cd AS 직원성별
     , (CASE WHEN co.h_no IS NULL THEN 'N' ELSE 'Y' END) AS 사내부부여부
     , co.w_nm AS 배우자성명
     , ch.fam_nm AS 자녀성명
     , ch.gender_cd AS 자녀성별
     , ch.birth_ymd AS 자녀생년월일
     , FLOOR((TO_CHAR(SYSDATE, 'YYYYMMDD') - ch.birth_ymd)/10000) AS 자녀나이
     ,(CASE WHEN ch.twin_nm IS NULL THEN 'N' ELSE 'Y' END) AS 쌍생아여부
FROM emp_c em
 INNER JOIN child ch ON em.emp_no = ch.emp_no
  LEFT JOIN couple co ON em.emp_no = co.h_no 
WHERE SYSDATE BETWEEN em.hire_ymd AND em.retire_ymd
ORDER BY em.emp_no;

쿼리결과

<과제2>  LISTAGG를 활용하여 직원번호가 10001483 인 자녀의 성명이 한줄로 추출 되도록 SQL작성

SELECT EMP_NO AS 직원번호,
       LISTAGG(FAM_NM, ',') WITHIN GROUP(ORDER BY FAM_NM) AS 자녀성명
FROM FAM_C
WHERE EMP_NO = '10001483' 
  AND REL_TYPE_CD = 'A27'
GROUP BY EMP_NO;

쿼리결과

문제: 질의 결과처럼 데이터를 추출 할 수 있도록 sql 작성 

 

[SQL 코드]

SELECT  f1.emp_no AS 직원번호
      ,f1.fam_nm AS 쌍둥이1
      ,f2.fam_nm AS 쌍둥이2
FROM 
  (SELECT *
   FROM fam_c
   WHERE birth_ymd BETWEEN '20120101' AND '20151231'
   AND rel_type_cd = 'A27') f1
   ,fam_c f2
WHERE f1.emp_no = f2.emp_no
      AND f1.birth_ymd = f2.birth_ymd
      AND f1.fam_nm <> f2.fam_nm;

2) . 20210321 기준으로 재직중인 임직원이며. 생년월일이 20120101 에서 20151231인 자녀를 가진 임직원을 구하는 sql을 작성하세요.

[주어진 질의결과]

[SQL 코드]

SELECT  e.emp_no AS 직원번호
      ,f.fam_nm AS 자녀성명
      ,f.birth_ymd AS 자녀생년월일 
FROM emp_c e
   INNER JOIN fam_c f on e.emp_no = f.emp_no
WHERE e.retire_ymd >= '20210321' 
     AND f.birth_ymd BETWEEN '20120101' AND '20151231'
     AND f.rel_type_cd = 'A27';

[제가 한 질의결과]

3) . 20210321 기준으로 재직중인 임직원이며. 사내부부인 임직원의 직원번호를 구하는 sql을 작성하세요.

[주어진 질의결과]

[SQL 코드]

SELECT e.emp_no AS EMP_NO
FROM emp_c e
    INNER JOIN fam_rel_c fam_r ON e.emp_no = fam_r.emp_no
WHERE e.retire_ymd >= '20210321' 
     AND fam_r.end_ymd >= '20210321' 
     AND fam_r.rel_type_cd IN ('A02', 'A18')

[제가 한 질의결과]

3-2) . 현재 날짜 기준 재직중인 “이씨 성＂을 가진 직원의 자녀명수가 몇 명인지 리스트를 만드세요.

[SQL 코드]

SUBSTR  사용

SELECT emp.emp_no AS 직원번호 
     , emp.emp_nm AS 직원명
     , COUNT(*) AS 자녀명수
FROM emp_c emp
    INNER JOIN fam_c fam ON emp.emp_no = fam.emp_no
WHERE SUBSTR(emp.emp_nm, 1, 1) = '이'
      AND SYSDATE BETWEEN HIRE_YMD AND RETIRE_YMD
      AND rel_type_cd = 'A27'
GROUP BY emp.emp_no, emp.emp_nm ;

LIKE 사용 

SELECT emp.emp_no AS 직원번호 
     , emp.emp_nm AS 직원명
     , COUNT(*) AS 자녀명수
FROM emp_c emp
    INNER JOIN fam_c fam ON emp.emp_no = fam.emp_no
WHERE emp.emp_nm LIKE '이%'
      AND SYSDATE BETWEEN HIRE_YMD AND RETIRE_YMD
      AND rel_type_cd = 'A27'
GROUP BY emp.emp_no, emp.emp_nm ;

[제가 한 질의결과]

3-3) . 자녀 명수가 2명 이상인 직원의 리스트를 구하세요.  (직원번호 / 성명 / 자녀명수 ) 

[SQL 코드]

SELECT e.emp_no AS 직원번호
      ,e.emp_nm AS 직원성명
      ,COUNT(*) AS 자녀명수
FROM emp_c e
     INNER JOIN fam_c f ON e.emp_no = f.emp_no 
WHERE f.rel_type_cd = 'A27'
GROUP BY e.emp_no, e.emp_nm
HAVING  COUNT(*) >= 2;

[제가 한 질의결과]

3-4) . 현재 기준 조직의 부서별 직원이 몇 명인지 구하는 sql을 작성하세요. (조직코드, 조직명, 직원수)

[SQL 코드]

SELECT org.org_cd AS 조직코드 
     , org.org_nm AS 조직명
     , COUNT(emp.emp_no) AS 직원수
FROM org_c org
     INNER JOIN emp_c emp ON emp.org_cd = org.org_cd
WHERE SYSDATE BETWEEN STA_YMD AND END_YMD
GROUP BY org.org_cd, org.org_nm;

[제가 한 질의결과]

<과제 목표>

현업이 제출한 데이터 추출 요청서를 확인 후 요구사항의 애매모호성 제거하여 데이터 추출 작업 준비하기.

상황

내년부터 우리 회사에 직원 복지항목으로 자녀 학자금에 대한 지원이 추가됨.

관련하여 인사팀에서 현황을 파악하고자 관련 데이터 추출을 요청함.

1. 현업의 요구사항을 모두 파악 후 추출하려는 데이터의 레이아웃을 그려보세요

*자녀순서는 몇 번째 자녀인지 의미 

데이터는 은퇴한 직원의 정보도 담고 있지만 재직 중인 임직원을 대상으로 하는 복지혜택이기 때문에 재직여부는 필요없을 것 같다.  

FEEDBACK 이후

2열: 테이블

3열: 컬럼

2. 애매모호한 부분에 밑줄을 긋고 어떻게 명확히 할 지 수정해 보세요.

- 기혼의 임직원 중 자녀가 1명 이상의 인 자

미혼모, 미혼부도 있을 수 있다 생각.

재작자중 자녀코드를 가지고 있고 자녀가 1명이상이면 기혼으로 간주

- 자녀 나이가 5~7세인자

나이의 기준이 만나이인지 세는 나이(한국식나이)인지 연나이인지 애매모호함

또한 만나이를 기준으로 삼을 경우 어느 날짜를 기준으로 만나이를 할 것인지도 부정확함

만나이로 할 경우에는 "2022년 01월 01일 기준으로 만5~7세 자녀"로 기준 세우는 것이 명확함.

그리고 나이보다 출생연도를 기준으로 세우는 것도 명확하다고 생각.

예를 들어 2022년 기준으로 한국식 나이 5세~7세에 해당되는 "2016년 01월 01일 ~ 2018년 12월 31일 사이 출생자"

- 해당자녀의 나이, 성별표시

마찬가지로 나이의 기준이 애매모호함.

만나이, 연나이, 세는나이(한국식나이) 기준 명확화 필요

나이, 생년월일, 성별 추출

- 쌍생아의 경우 대표 1명만 표시

쌍생아의 기준: 관계코드가 자녀이면서 생년월일이 같은 경우

쌍생아 대표의 기준이 애매모호함. 

쌍생아의 경우에도 자녀 순서가 나뉘어 있음. 쌍생아 중 첫째를 대표로 표시 

또는 이름으로 오름차순 기준, 상위자로 추출,

+ 추가사항

- 2명이상의 자녀가 나이기준에 충족될 경우에 혜택 중복 여부 -> 나이가 더 많은 자녀를 대표 1명으로 표시 

- 임직원의 연차기준에 따른 복지혜택 적용 여부 -> 입사연도 상관없이 모든 임직원 복지적용 or 입사한 지 2년이상된 직원부터 복지 적용 등 기준세우기

FEEDBACK 이후 추가

배우자가 사내부부인 경우는 남편, 아내 중 한쪽만 가능

사내커플인 경우 부를 대상자에 포함하고 모는 제외

 배우자가 사내부부인 경우 배우자 명을 표시

대상자 부 기준으로 모의  직원번호 및 배우자 명을 함께 추출

3. SQL을 사용하여 문제에 답하세요.

(1) 직원번호 10004141 의 이름은? 

SELECT emp_no, emp_nm
FROM emp_c
WHERE emp_no = 10004141;

결과

(2) 직원번호 10004141 는 몇 명의 가족이 있나요? 

SELECT COUNT(emp_no)
FROM fam_c
WHERE emp_no = 10004141;

결과

(3) 직원번호 10004141 의 소속 부서 코드는? 

SELECT org_cd
FROM emp_c
WHERE emp_no = 10004141;

결과

(4) 직원번호 10004141 의 소속 부서 명은?  

SELECT org_nm
FROM org_c
WHERE org_cd = (
  SELECT org_cd
  FROM emp_c
  WHERE emp_no = 10004141
);

결과

(5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql 작성

SELECT gender_cd, COUNT(*)
FROM emp_c
GROUP BY gender_cd
ORDER BY gender_cd

(6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql 작성

SELECT emp_no, COUNT(*) AS 자녀수
FROM fam_c
WHERE rel_type_cd = 'A27'
GROUP BY emp_no;

결과

(7) 생년월일이 1970년 1월 1일 이전인 직원의 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE birth_ymd < 19700101

결과

(8) 현재 재직 중인 직원의 전체 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd LIKE '9999%'

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd = '9999/12/31'

결과