[교육&스터디]/[패스트 캠퍼스] 데이터 분석 부트캠프 9기

[데이터 분석 부트캠프] 6주차 진행 및 학습일지

icepack788 2023. 8. 4. 10:02

이미지 출처 : 인프런

[패스트 캠퍼스] 데이터 분석 부트캠프 6주차!! 

이번 6~7주차는 '데이터 분석을 위한 Tool, SQL'을 진행하고 있습니다!!
인프런과 유튜브 등등 인터넷에서 굉장히 유명하신 Dave lee, 이준희 강사님께서 진행해주시는 강의인데요~!! 
어떤 내용들을 배웠는지 저의 솔직한 리얼 후기&반성&회고를 통해서 만나보시죠!!

[7주차 : SQL 학습 및 정리 내용] 

1. SQL JOIN

Every table in the database needs to have a single theme.
모든 테이블은 하나의 테마 만 가지고 있어야 한다.

 

JOIN 쉽게 이해하기

INNER JOIN

LEFT JOIN

RIGHT JOIN

OUTER JOIN

SELECT * FROM TableA FULL OUTER JOIN TableB ON A.key=B.key;

(SELECT * FROM TABLEA LEFT JOIN TableB On A.key=B.key) UNION DISTINCT (SELECT * FROM TABLEA RIGHT JOIN TableB On A.key=B.key)

 

위의 이미지를 통해서 한번에 이해가 가능하다!!

 

2. group by, having, order by

1) GROUP BY 구

  • 데이터를 특정 컬럼 기준으로 그룹화시키는 명령어
    EX) 연령별 평균 매출액 → 연령별로 그룹화
  • 그룹을 나누고 다시 그 그룹 안에서 세부그룹으로 나눌 수 있다
  • GROUP BY COL1, COL2 ⇒ COL1안에서 다시 COL2로 나누기 가능
    EX) 연령별 성별 평균 매출액 → 연령별로 먼저 그룹, 다음 성별로 그룹
  • 각 그룹에 대한 연산 결과(합, 평균, 갯수 등)를 산출하기 위해서는 집계함수가 필요
  • GROUP BY 구에 있는 컬럼은 반드시 SELECT 절에도 존재해야 한다
  • GROUP BY 구에도 다양한 함수 사용 가능하다 (SUBSTR, INSTR 등등..)

📌 그룹 함수

  • 하나 이상의 행을 그룹으로 묶어 연산하기 위한 함수
  • GROUP BY 기준으로 나눠진 각 그룹을 연산하는 역할
  • COUNT 함수를 제외한 나머지 그룹함수는 NULL 값 제외하고 계산! (COUNT는 NULL을 포함)

2) HAVING 구

  • GROUP BY 절에 대한 조건을 걸고 싶을 때 사용하는 명령어
  • 그룹화 된 결과에 조건을 걸어주는 역할
  • HAVING 뒤에는 SELECT 구문에서 사용하는 AS 별칭 사용 불가

⭐ WHERE절과 헷갈리지 않게 조심하기 ⭐

  • 처음부터 테이블 자체에 조건을 걸고 싶다면? → WHERE 절
  • 그룹별로 묶인 컬럼에 조건을 걸고 싶다면? → HAVING 절

3) ORDER BY

  • 테이블을 특정 컬럼값을 기준으로 정렬하기 위한 명령어
  • 즉, '정렬'의 역할
  • 여러개 컬럼을 기준으로 정렬 가능
    ex) ORDER BY COL1, COL2...
  • 기본 정렬값은 ASC(오름차순)로 설정되어있으므로 오름차순 정렬시에는 입력 필요 X, 내림차순의 경우 DESC
  • SELECT절의 컬럼 순서 혹은 별칭으로도 정렬 가능
    ex) ORDER BY 1, 2,...

3. 함수 

1) 문자함수 

  • LOWER(), UPPER() : 대/소문자로 변환
SELECT 'DataBase', LOWER('DataBase')
	FROM dual; --데이터베이스에서 제공하는 가상의 테이블
  • SUBSTR() : 부분 문자열 추출
SELECT SUBSTR('abcde', 2, 4)
	FROM dual; --bcde
SELECT SUBSTR(first_name, 4)
	FROM employees; --인덱스 4부터 끝까지
  • LENGTH : 문자열 길이
SELECT job, SUBSTR(job, -LENGTH(job))
	FROM emp;
  • LPAD(), RPAD() : 데이터 빈 공간을 특정 문자로 채우기
SELECT 'Oracle',
	LPAD('Oracle', 10, '#'),
    RPAD('Oracle', 10, '*')
    FROM dual;

2) 숫자 연산 함수

  • MOD : 나머지 연산
SELECT MOD(10, 3) FROM dual;
  • ROUND : 소숫점 N번째에서 반올림
SELECT ROUND(3243.35543, 2) FROM dual;

 

3) 날짜 함수

  • SYSDATE : 오늘로부터의 날짜 구하기
SELECT SYSDATE -1 --어제
	FROM dual; --2021/02/17
SELECT first_name, round((sysdate-hire_date)/365, 1) as "근속년"
	FROM employees;

 

  • NEXT_DAY(날짜 데이터, 요일 문자)
SELECT NEXT_DAY(SYSDATE, '월요일')
	FROM dual;

4) 변환 함수

  • TO_CHAR() : 숫자, 날짜를 원하는 형식의 문자열로 변환
--오늘 날짜를 원하는 형식으로 변환
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
--숫자를 원하는 형식으로 변환
SELECT TO_CHAR(5000000, '$999,999,999') FROM dual;
SELECT * FROM employees
    WHERE TO_CHAR(hire_date, 'YY') = '07';
  • TO_DATE() : 문자열을 날짜 데이터로 변환
SELECT TO_DATE('2021-02-18', 'YYYY-MM-DD'),
	TO_DATE('20210219', 'YYYY-MM-DD')
    FROM dual;
  • CASE WHEN
SELECT job_id,
	CASE job_id
      WHEN 'SA_MAN' THEN 'Sales Dept'
      WHEN 'SH_CLERK' THEN 'Sales Dept'
      ELSE 'Another'
   	END "CASE"
    FROM employees;

 

3. 서브 쿼리

📌 사용되는 곳
1. WHERE, HAVING절
2. FROM절

  • 사용하는 이유?
    원하는 결과를 하나의 쿼리로 얻기 어려운 경우가 있음.
    사원의 평균 급여보다 많이 받는 직원의 목록을 알고 싶은 경우,
    1) 우선 사원의 평균 급여를 알아내기 위한 쿼리를 하고,
    2) 이를 이용해 평균 급여보다 많이 받는 직원을 알아내기 위해 쿼리를 해야한다.

 

cf. 서브 쿼리와 조인
사용 목적을 보면 알 수 있지만, 어떤 데이터를 얻어내는데 있어서 조인과 서브 쿼리가 모두 가능한 경우가 많다. 성능 면에서는 조인이 더 좋다고 하고, 보통은 조인을 통해서 해결이 다 되기 때문에 많이 쓰지는 않지만, 코딩하는 사람의 스타일에 따라서 서브쿼리만 쓰는 사람도 있고 그렇다고 한다! 

 

  • 서브 쿼리문 작성 순서
    1. 서브 쿼리문 먼저 작성 -> 다중 row, 다중 column을 처리해야할 경우, 서브 쿼리의 결과를 먼저 알아야 메인 쿼리를 짤 수 있다.
    2. 메인 쿼리문 작성
SELECT last_name, salary
	FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)

1) WHERE 절의 서브쿼리문 (중첩서브쿼리, nested subqueries)

--일반화학 수업을 듣는 관우의 학점
SELECT grade FROM student s, course c, score r, scgrade g
	WHERE s.sno = r.sno
	AND c.cno = r.cno
	AND cname = '일반화학'
	AND sname = '관우'
	AND result BETWEEN loscore AND hiscore

--해당 학점보다 낮은 학생들의 이름
  SELECT s.sno, s.sname, grade 
	FROM student s, course c, score r, scgrade g2
	WHERE s.sno = r.sno
	AND c.cno = r.cno
	AND cname = '일반화학'
	AND result BETWEEN loscore AND hiscore
	AND grade > (SELECT grade FROM student s, course c, score r, scgrade g
			WHERE s.sno = r.sno
			AND c.cno = r.cno
			AND cname = '일반화학'
			AND sname = '관우'
			AND result BETWEEN loscore AND hiscore)
select *
  from student
  where avr <= (select avg(avr)
                from student
                where major = '화학'
                and syear = 1)
  and major = '화학'
  and syear = 1;

2) Having 절의 서브쿼리문

서브 쿼리문은 where 절에 select 문이 다시 들어가는 형태를 말하는데,
만약 이 select 문에서 얻은 결과를 또 다시 그룹 함수와 비교해줘야하는 경우,
서브 쿼리문은 having 절에 수반된다.

  • 핵화학 과목보다 기말고사 평균 성적이 낮은 과목에 대한 정보 
    select c.cno, c.cname, p.pname, avg(sc.result) as "평균 성적"
	from course c, professor p, score sc
	where c.pno = p.pno
	and c.cno = sc.cno
	group by c.cno, c.cname, p.pname
	having avg(sc.result) > (select avg(s.result)
                           		from score s, course c
                           		where s.cno = c.cno
                           		and c.cname = '핵화학'
                           		group by c.cno) 
--이렇게 JOIN을 해서 구해도 되고 그냥 바로 SELECT FROM COURSE해줘도 됨
	order by avg(sc.result);

3) FROM 절의 서브쿼리

원래 테이블에서 일부를 뽑거나 가공한 임시 테이블(실행 중에 임시로 생성된 동적인 뷰)을 만들고, 그 테이블에 대해 where 조건절 등을 통해 데이터를 뽑아오는 형식의 서브쿼리이다.

SELECT t.team_name, p.player_name, p.back_no
	FROM (SELECT team_id, player_name, back_no
    		FROM player
            WHERE positon = 'MF') p, team t
    WHERE p.team_id = t.team_id
    OREDER BY 2;
--입사 순으로 5명
SELECT ROWNUM, alias.*
	FROM (SELECT employee_id, last_name, hire_date
    		FROM employees
            ORDER BY hire_date
         )alias
    WHERE ROWNUM <= 5;

 

 

  • ROWNUM을 사용할 때 주의할 점
    ROWNUM을 이용해서 ROWNUM 5~8과 같이 중간의 데이터를 바로 뽑아오는 것은 불가능하기 때문에 그럴 경우 아래와 같이 사용해주어야 한다.
SELECT * FROM(
		SELECT ROWNUM as ROW_NUM, temp.* FROM
        		(SELECT * FROM board
                			ORDER BY seq DESC
				)temp
        )
        WHERE ROW_NUM BETWEEN 5 AND 8;

4) SELECT 절의 서브쿼리 (스칼라 서브 쿼리)

  • SELECT 명령이 하나의 값만 반환하는 것을 '스칼라값을 반환한다'라고 한다. 이는 서브쿼리로서 사용하기 쉽다
  • SELECT구에서 하나의 열을 지정하고, GROUP BY를 지정하지 않은 채 집계함수를 사용하면 결과는 단일한 값이된다 또한, WHERE조건으로 하나의 행만 검색하면 단일값이 되므로 스칼라값을 반환하게 된다. 집계함수는 WHERE구 에서 사용할 수 없지만 스칼라 서브쿼리면 WHERE 구에 사용할 수 있다.
SELECT player_name, height,
	(SELECT AVG(height) FROM player L 
			WHERE p.team_id = l.team_id)
	FROM player p
    ORDER BY 1;

 

반응형