오늘도 데이터분석

[문제]

https://leetcode.com/problems/nth-highest-salary/

[풀이]

- 서브쿼리 풀이 

   case 문 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
 RETURN (
      SELECT CASE WHEN COUNT(sub.salary) < N THEN NULL 
                  ELSE MIN(sub.salary) 
              END 
      FROM(
      SELECT DISTINCT salary
      FROM employee
      ORDER BY salary DESC 
      LIMIT N ) sub
 );
END

   if 함수 

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
 RETURN (
      SELECT IF( COUNT(sub.salary) < N , NULL , MIN(sub.salary))
      FROM(
      SELECT DISTINCT salary
      FROM employee
      ORDER BY salary DESC 
      LIMIT N ) sub
 );
END

- LIMIT OFFSET 풀이

CREATE FUNCTION getNthHighestSalary(N INT) 
RETURNS INT 
BEGIN 
     SET N = N-1;
 RETURN ( 
     SELECT DISTINCT salary 
     FROM employee
     ORDER BY salary DESC 
     LIMIT 1 OFFSET N
 );
END

[문제]

Weather Observation Station 9 문제 바로 가기 

 마지막 글자가 모음으로 끝나지 않는 도시이름 추출 

도시이름은 중복되면 안됨 

[풀이]

SELECT DISTINCT city 
FROM station
WHERE city NOT REGEXP ('^[a,e,i,o,u].*')

[문제]

weather observation station 8 문제 바로가기 

첫글자, 마지막 글자가 모음인 도시 이름을 추출

도시 이름은 중복 안됨

[풀이]

SELECT DISTINCT city 
FROM station 
WHERE city REGEXP ('^[a,e,i,o,u].*[a,e,i,o,u]$')

[문제]

rank scores 문제 바로 가기 

[풀이]

mssql 윈도우함수 이용해서 풀이 

SELECT score
     , DENSE_RANK() OVER (ORDER BY score DESC) rank
FROM scores
ORDER BY score DESC

[문제]

department top three salaries 문제 바로 가기 

[풀이]

- 윈도우함수 DENSE_RANK() 이용

* 리트코드 MySQL은 윈도우함수가 안돼서 MSSQL로 풀이

SELECT Department
      ,Employee
      ,Salary
FROM (
SELECT de.name AS Department
     , em.name AS Employee
     , em.salary AS Salary
     , DENSE_RANK() OVER (PARTITION BY departmentID ORDER BY salary DESC) drank
FROM employee em
    INNER JOIN department de ON de.id = em.departmentID) rank
WHERE rank.drank <= 3

[문제]

Consecutive Numbers 문제 바로 가기 

[풀이]

- 셀프조인 2번하는 방식으로 풀이 

(1)

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
     INNER JOIN logs l2 ON L1.id = l2.id - 1
     INNER JOIN logs l3 ON l2.id = l3.id - 1
WHERE l1.num = l2.num 
  AND l1.num = l3.num

(2)

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
     INNER JOIN logs l2 ON L1.id + 1 = l2.id 
     INNER JOIN logs l3 ON l1.id + 2 = l3.id 
WHERE l1.num = l2.num 
  AND l1.num = l3.num

- MS SQL 윈도우 함수 풀이

SELECT DISTINCT co.num AS ConsecutiveNums 
FROM(
    SELECT num
     , LEAD(num) OVER (ORDER BY id) AS lead 
     , LEAD(num, 2) OVER (ORDER BY id) AS lead2
    FROM Logs) co
WHERE co.num = co.lead 
AND co.num = co.lead2

<과제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;

쿼리결과

  캠프신청계기  

데이터리안을 처음 알게 된 건 올해 1월 웨비나였다. 

AI웹서비스 동아리를 할 때 운 좋게 데이터리안 세미나를 들을 수 있는 기회가 있었는데

실무에서 데이터분석툴로 SQL을 많이 사용한다는 걸 알 수 있었다.

이후 데이터리안 단톡방에 들어가서 계속 관심을 두다가 직무를 아예 '데이터분석'으로 잡고 바로 생각난 건 데이터리안이었다.

솔직히 취준생에게 가격이 많이 부담되었지만 한번 배울 때 제대로 배우고 싶어서 아예 입문+실전 패키지로 등록했다.

그렇게 5월부터 입문반 수업을 듣게 되었다. 

내가 들었던 1월 웨비나 링크

  수업방식 + 과제   

데이터리안 캠프는 4주동안 주 1회  2시간동안 실시간 온라인 세션이 있고 매주 과제가 있었다. 

참고로 이제 VOD로 바껴서 우리가 실시간으로 수업받는 마지막 기수라고 들었다. 

내가 들은 입문반 실시간 수업은 VOD로 제공되지 않아서 최대한 결석없이 수업시간동안 초집중하려 했다.

어차피 녹화본이 제공되어도 결석하지 않는 이상 다시 안볼 것 같긴 하다........ 

인강도 책도 2번이상 잘 보지 않기 때문에 한번 볼 때 최대한 집중해서 머릿 속에 넣으려고 하는 사람임...ㅎ

그리고 수업듣고 후기를 남기면 복습자료를 주는 데 덕분에 복습하기 용이했다.

매주 수업자료, 복습자료 다시 보면서 과제 외로 예습까지는 못해도 복습은 꾸준히 하려고 했다. 

과제는 인프런 초급, 중급강의와 sql 문제풀이, EDA 등이었는데 

인프런 강의를 과제로 들으니까 주1회 수업이 크게 부족하다고 느끼지는 않았다. 

  추천이유  

4주동안 일단 수업도 듣고 하라는 대로 과제도 했는데 이렇게 하면 정말 데이터분석가가 될 수 있나...? 

내가 지금 올바른 방향으로 가고 있는 건가? 생각이 안들었다면 거짓말이다.

데이터분석과 관련된 실무 경험을 해본 적이 없어서 직무를 준비하는 데 막연함이 항상 있었다. 

그런데  데이터리안 입문반 소개 페이지에 '4주 뒤에 이런 일을 할 수 있어요' 에 이렇게 나온다. 

내가 잘하고 있나 스스로 의심이 들었지만 4주가 지난 뒤 이걸 다시 보니 지금의 난 정말로 4가지를 다 하고 있다.

1. SQL로 간단한 데이터 추출 

나는 캠프를 듣기 전에는 SQL툴을 한번도 써본 적이 없는데 지금은 예전에 하던 R, 파이썬 보다 훨씬 편하고 익숙해졌다.

분석까지는 모르겠지만 적어도 질의문을 보고 쿼리를 어떻게 짤 지 고민하고 문법을 작성하는 걸 한달 내내 하다보니 재미도 있고 자신감도 붙었다. (지금 실전반들으면서 다시 자신감하락중이긴 하다...😂)

적어도 이력서에 SQL을 다룰 줄 안다고 쓸 수 있게 된 건 확실하다. 

2. 해커랭크, 리트코드 등 코딩 플랫폼에 익숙해짐

강의 실습으로 해커랭크, 리트코드,solvesql 코딩플랫폼을 이용한다. 뿐만 아니라 입문반 수업에서 원하는 사람들은 신청받아 스터디를 구성해줬는데 스터디를 하면서 해커랭크에 있는 대부분의 문제는 거의 다 푼 것 같다. 그 기록은 이 블로그에 'SQL문제풀이' 카테고리에도 있다.  이런 코딩플랫폼에 익숙해짐으로써 나중에 SQL 코딩테스트에 대비할 수 있다고 한다.

3. SQL을 실무에 활용할 수 있음

아직 취준중이라 실무에서 사용하고 있다고 말하기 어렵지만 배운 분석 내용을 바탕으로 프로젝트를 하려고 하고 있다. 그리고 입문반 3주정도 지났을 때 부터 다른 곳에서 직무부트캠프를 추가로 들었는데 실무자가 알려주는 분석을 위한  SQL문법 내용 중 90%이상은 이미 데이터리안 입문반수업으로 알고 있는 문법이었다. 

4. 남이 작성한 SQL쿼리를 읽고 이해/수정 할 수 있음

SQL스터디를 하면서 다른 사람의 쿼리를 읽을 수 있었고 특히 해커랭크 문제 풀 때 도저히 안 풀리면 풀이를 검색하거나 Discussion부분을 보면서 남이 작성한 쿼리를 자주 봤는데  내가 모르는 문법, 함수가 나와도 당황하지 않고, 구체적으로는 몰라도 전체적인 흐름, 왜 이사람이 이렇게 쿼리를 작성했는지는 알 수 있게 되었다.  

(당황하지 않고 천천히 쿼리를 뜯어보려고 하는 습관은 데 정말로 데이터리안의 강의력이 뛰어난 덕분이라고 생각한다.)

추가로 캠프를 들으면서 비슷한 직무를 준비하는 사람들을 만날 수 있어서 좋았다.

나는 지금 데이터리안에서 다른 수강생분들과 SQL스터디, 기초 통계학 스터디를 같이 하고 있다.

그리고 캠프 수강생은 월간세미나가 무료인데 데이터리안 블로그+ 유튜브+ 세미나까지 양질의 콘텐츠를 제공받을 수 있는 점이 정말 좋다.  

또한 슬랙으로 언제든지 질문을 올릴 수 있는 게 편리하다. 나는 사실 구글링으로 대부분 해결하는 편이라 질문답변 채널을 잘 이용하지는 않았지만 그래도 언제든 편하게 질문할 수 있는 창구가 있냐 없냐는 다르다고 생각한다.   

심지어 이 슬랙은 캠프가 종료되어도 이용할 수 있다. 혼자서 공부할 때는 질문할 곳이 막막한데 슬랙에서 질문하거나 프로젝트 피드백도 받을 수 있다. 

아쉬웠던 점 

나는 '데이터분석'을 경험하고 싶었는데 아무래도 입문반이다 보니까 SQL문법 위주의 수업이었다.  RFM분석, 매출분석은 3,4주차 세션이 거의 끝나갈 때쯤 배웠어서 아쉬움이 있다. 

입문반을 듣고 SQL에는 익숙해졌지만 막상 프로젝트를 하고 포트폴리오 작성을 하는데는 여전히 막막하다.

다른 분들한테도 물어보니 아직 다들 막막하다 하는 걸 보니 나만 그렇게 느끼는 것 아닌 것 같다. 

반면에 실전반은 언어보다 분석위주라고 해서 기대 중이다.

내가 처음 등록할 때 고민했던 부분은 인프런에 데이터리안 강의가 있는데 굳이 데이터분석 캠프를 들어야 하는 이유를 제대로 파악하기 어려웠다. 이번에 캠프가 전부 VOD로 바껴서 더욱이 나와 비슷한 고민을 하는 사람이 있을 것 같다.

만일 패키지를 들을 지, 실전반만 들을 지 고민 중이라면,

금전적으로 여유가 있다면 패키지를 추천한다. 

캠프를 들으면 매주 규칙적으로 공부할 수 있고 진도, 공부분량 등 다 정해주고 그에 따른 적절한 콘텐츠도 큐레이팅 해주기 때문에 캠프로 듣는 것이 확실히 편하고 꾸준히 공부할 수 있다. 특히나 나같이 인강만으로는 규칙적으로 공부하기 어려운 사람이라면 말이다. 

그러나 혼자서 인강도 계획적으로 잘 듣고, 문제도 추가로 찾아서 풀 수 있는 사람이라면 

인프런에 있는 데이터리안 초급, 중급 강의로 SQL 언어를 독학하고 실전반 캠프를 들으며 분석을 배우고 프로젝트를 하는 것도 좋지 않을까 생각이 든다.

마지막으로 데이터리안이 어떤 곳인지 아직 잘 모르겠다면 데이터리안 블로그 글을 읽는 걸 강추한다.

좋은 글들이 많고 나도 블로그 글을 읽으면서 캠프에 대한 신뢰성(?)이 생겨 등록할 수 있었다. 뿐만 아니라 유튜브도 있으니 충분히 찾아보고 고민해보시길 바란다. 

https://www.datarian.io/blog

https://youtu.be/l4_axJ8KKos

친구추천 이벤트로 데이터리안 데이터분석캠프 등록할 때 추천인을 '5기 티나'라고 써주시면 

추천받은 사람은 5만원 환급, 추천한 사람은 데이터리안 강의 1개를 무료로 제공받을 수 있습니다!

참여 방법

추천 받으신 분
1. 데이터리안 웹사이트를 통해 SQL 데이터 분석 캠프를 등록해주세요.
2. 신청 폼의 [기존 수강생 추천을 받으신 경우, 추천하신 분의 수강 기수와 성함(혹은 닉네임)을 적어주세요.] 문항을 적어주세요.

이벤트출처: 데이터리안 

마지막으로 수료증으로 인증하며 포스팅 마무리하겠습니다 :)