종종 데이터 부트캠프에 강사로 참여하다 보면 알고리즘 강의를 해달라는 요청을 받는다. 알고리즘이란 무엇인지 그리고 분석가가 배우면 좋은 점은 어떤 이득이 있는지 작성해본다.
2. 예상독자
데이터 직무에 진입하려는 입문자
알고리즘을 간단하게 알아보고자 하는 사람
3. 본문
도대체 알고리즘이 뭐고 왜 쓰이는가?
알고리즘은 쉽게 말하면 개발자의 수능, 적성문제이다. 프로그래밍이라는 실력의 척도를 측정하기 위한 하나의 도구로 컴퓨터 과학에 등장하는 알고리즘이 발전되어왔다. 알고리즘을 잘한다고 프로그래밍 실력을 보장하진 않지만 어느정도 최소치를 만족하기에 입사시험으로 쓰고 있는 것이다.
그럼 분석가가 알고리즘을 왜 배워야 하는가?
1. 단적인 예!: 누군가에게 아쉬운 소리(특히 개발자에게) 하지 않아도 된다.
마치 특강을 위해 만든 자료가 있어서 대체!
2. 분석가는 프로그래밍을 업무에 활용할 일이 많다.
데이터 엔지니어는 개발자 트랙이니 말할 것도 없고 데이터 사이언티스트는 Python 프로그래밍으로 모델링을 하기 때문에 역시 필수적이다. 또한, 데이터 분석가는 업무에 이바지할 부분이 많다. 데이터 분석의 좁은 분야는 RDB(관계형 데이터베이스)기반에서 데이터를 뽑아내는 쿼리 작업부터 시작하고 그 부분에서 비즈니스적인 내용을 붙일지, A/B test를 포함한 통계를 붙일지 등등 뻗어나갈 분야는 많다. 어느 방향으로 주 분야를 뻗어 나가던 확실한 것은 Python과 같은 프로그래밍을 다룰 줄 아는 것은 업무 범위를 확장하기에 매우 좋다는 것은 사실이다.
분석의 범위는 매우 넓다구우
그럼 얼마나 배워야할까?
개인적인 기준으로는 프로그래머스와 같은 코딩 테스트의 기본 문제들만 풀어도 된다고 생각한다. 하지만 Lv3,4 혹은 카카오 입사시험 테스트를 본격적으로 푸는 것은 조금 길에서 벗어난 방법이라고 생각한다.
반면, 기본적인 알고리즘을 익히고 파이썬 라이브러리가 지원하는 함수와 메소드를 잘 이해하는 게 좋다.
오히려 기본적인 알고리즘을 배우고 이를 통해 API로 데이터 수집하기, Airflow로 자동화 하기와 같은 데이터 엔지니어링 부분을 확장하거나 분석모델링(간단한 선형회귀, 군집화)등을 활용할 정도만 되어도 된다. 데이터 분석의 본질은 문제를 인식하고 근거기반으로 판단하는 방법이 목적이며, 데이터 가공, 추출, 코딩 등은 이를 원활하게 하는 방법임을 잊으면 안 되겠다.
대표적인 데분에 활용할만한 내용 살펴보기
알고리즘에서 나오는 문제 중 하나의 개념을 알아야한다면, 당연히 배열이다. 정형데이터 분석에서 사용하는 자료는 배열이 대부분이며, Numpy의 array 자료형, Pandas의 Series 형 자료형도 모두 배열의 추상화 개념을 상속한 것이 때문에, 나는 가장 이 부분을 강조한다. 사실 가장 직관적이기도 하고..!
Python List와 Numpy Array 구조의 차이List vs Numpy의 자료형 성능 비교
또한, 배열자료형 하면 또 Pandas DataFrame 자료형을 빼놓을 수 가 없는데, 파이썬에서 대용량 데이터를 사용하다보면 메모리의 압박으로 문제가 생기는 경우가 있다. 이 경우 데이터의 메모리 효율을 고민하여 성능과 속도를 높히게 되는데, 이 부분에 대한 설명은 이수진님의 블로그글이 잘 정리되어 있어서 자주 참고하는 편이다
매년 이맘때쯤 부트캠프 & 데이터 사이언스 교육이 이뤄저서 그런지 딱 1년전에 L모기업 직원을 대상으로 교육했던 기억이 난다. 그때 공부했던 나의 기록링크를 남기며 이번에도 뉴비&초심자를 위한 자료를 한번 만들어 보면 좋겠다. 네? 데분 멘토링왔는데 알고리즘 2달 알려주라고요?
MySQL은 데이터베이스의 한 종류로, 가장 범용적인 데이터베이스입니다. 일반적으로 회사에 데이터베이스에 구축되어 있으면 따로 로컬컴퓨터에 설치하지 않고 SQL WorkBench, DBeaver등 어플리케이션 툴을 이용하여 접속하면 되기 때문에 설치할 일이 없습니다. 반면 개인 학습 혹은 서버를 띄운 상태에서 데이터베이스를 설치하고자 한다면 이 가이드를 통해 로컬에 MySQL 데이터베이스를 설치할 수 있습니다.
Last Updated
2024-06-26: Custom 설치방법 추가
2024-11-26: Initializing Database 오류 방법추가
2025-05-19: Trouble shooting 추가
1. MySQL 설치
MySQL을 설치하는 것은 설치파일을 실행 후 "다음", "다음"의연속입니다. 중요한 점은 중간에 mysql 접속을 위한 비밀번호 설정과 mysql 포트(지하철로 따지면 출구번호)가 3306으로 사전 지정이 잘 되어있는지 확인하면 됩니다. 이를 통해 MySQL을 로컬 컴퓨터에 설치할 수 있고 추후 Database에 접속할 수 있는 Command Line 혹은 DBeaver와 같은 Application을 통해서 테이블을 생성하고 SQL쿼리를 실행할 수 있게 됩니다.
연결할 수 있는 다양한 Database를 지원합니다. 우리는 MySQL을 설치했기 때문에 해당하는 것을 클릭합니다.
MySQL 설치시 설정한 Password를 넣어줍니다. Username은 기본적으로 root로 설정되어있어서 변경하지 않아도 됩니다.
3306 포트의 localhost가 연결된 것을 확인 할 수 있습니다.
4. 나의 데이터베이스 생성하기
localhost의 좌측 꺽쇄를 누르니 sys 라는 Database밖에 조회되지 않았습니다. 기본적으로 필수적이지 않은 Database는 숨겨진 채로 DBeaver에서 조회할 수 없네요. 또한, sys 이름으로 유추하건데 시스템 정보에 관한 데이터베이스므로, 테이블을 생성하는 등 작업에 적합하지 않아 보여 새로운 데이터베이스를 생성해보겠습니다.
머신러닝 딥러닝을 포함한 AI가 세상을 바꿀 것 같이 광고하고 있다. 하지만 실제로 산업에서 적용에 실패하고 AI는 허상이라고 느낄 수도 있을 것 같다. 여기엔 2가지 문제가 있다.
데이터 분석 등 의사결정에 영향을 끼치는 AI는 그 성과를 정량화하기 어려움
성과가 있더라도 그 노하우를 굳이 외부에 홍보할 필요가 없기 때문
성과 낼 수 있는 딥러닝은 현재 이미지와 자연어 처리에 국한되어 있어, 작고 좁은 분야에서 활약을 하기 때문
딥러닝이 잘 적용하는 예, 이미지 생성과 자연어 처리
세상의 모든 문제를 AI로 해결하지 못하는 이유
세상의 정보에는 2가지가 있다고 한다. 바로 암묵지와 형식지이다. 암묵지란 언어란 형식을 갖추어 표현될 수 없는 경험과 학습으로 머리에 쌓인 지식이며 흔히 노하우(know-how)라는 것이다. 반면, 형식지는 명시적으로 표현한 지식이다. 예를 들면 요리법은 레시피(형식지)에 써있지만, 그걸 그대로 재현한다고 똑같은 음식점 맛이 나타나는 것은 아니다.(암묵지)
지식의 두 갈래에 관해서 이야기하는 이유는, AI는 명시적인 형식지의 정보만 인식하기 때문이다. 다시 말해, 컴퓨터가 학습하지 못하는 정보는 AI에 학습시킬 수 없으며 당연히 예측하기도 어렵다. 이런 깨달음이 있기 전에 과거 50년대에는 기호주의와 연결주의 2가지 학파가 있었다는 것이 흥미롭다.
기호주의와 연결주의의 등장
1956년 다트머스 회의에서 처음으로 인공지능을 언급한 마빈 민스키는 인간의 지식을 기호화하고 그 기호화의 관계를 일일이 컴퓨터에 입력하면 비슷한 입력을 얻었을 때 출력도 비슷하게 낼 것이라는 주장을 하였고 이것이 바로 기호주의 인공지능의 시작이다. 조건과 액션으로 이루어진 규칙기반(Rule based) 인공지능은 80년대까지 약 30년 정도를 지배하였고, 전문가시스템(GPS)을 이용하여 성과를 이뤘지만, 전문가의 지식의 데이터의 수가 부족했고 암묵지의 한계로 후술할 연결주의에 자리를 내주게 되었다.
이거완전... ChatGPT?
반면 1957년 프랭크 로젠블랫은 뇌의 신경을 모방한 퍼셉트론을 발표했는데, 이는 연결주의 AI의 기반이 된다. 신경망 연구는 80년대에 들어와 메인 스트림으로 들어온 뒤 2010년이 되어서야 딥러닝으로 주목 받는다. 특히, 1986년 연결주의를 설명한 <병렬 분산처리> 논문을 발표한 러멜하트는 인간의 사고가 사람들이 컴퓨터가 똑똑하게 만드는 이유를 주장하며 연결주의 태동을 이끈다. 이후 2012년은 이미지 인식 기술 경진대회(ILSVRC)에서 100만 개의 이미지의 정확도를 딥러닝 알고리즘 알렉스넷(AlexNet)이 기존 26%에서 16%까지 획기적으로 낮추며 딥러닝 호황기가 시작되었다.
AI 발전의 겨울과 여름
현재에도 매스컴에서 AI만 붙이면 뭔가 세상을 바꿔버릴 것 같이 홍보하고 있다. 이는 1950년대 처음 인공지능을 제안한 시기에도 마찬가지였다. 기호주의가 성행하나 70년대까지 기업문제를 해결하지 못하자 첫 번째 AI 겨울(1974년 - 80년)이 일어나고, 전문가 시스템의 한계로 두 번째 AI 겨울(1987년 - 97년)이 일어났다. 그런 겨울 와중에도 인공신경망의 연결주의로 극복하며 역전파 알고리즘 발전 때문에 지금의 호황기를 누리고 있는 것이다.
인공시대에 대처하는 우리의 자세
늘 데이터 분야에 있으면서 인공지능을 필두로 발전한 데이터 직무분야가 허상으로 꺼지지 않을까 라는 생각과 의구심을 듣고 1년간 사람들에게 자문을 구했던 것 같다. 이렇게 역사를 짚어보니, AI에는 난관이 있었고 늘 이를 극복하여 다시 붐을 일으킨 것 같아 그런 고민은 접어도 될 것 같다.
우리가 할 일은 좁은 AI를 활용하며 어떻게 사업 문제를 "잘" 해결할 것 방법을 찾는 것인 것은 변함이 없다. 인공지능 전문가가 아니라 코드 활용만 가능해도 딥러닝 아키텍처를 적용할 수 있는 만큼, 일반인들에게 접근성 좋은 인터페이스를 가진 인공지능이 Chatgpt를 넘어 더 좋게 나올 시대가 기대된다.
도서관 선반에 있는 책을 아무거나 집어서 가져온 것인데 작가분이 너무 글을 잘 쓰고 이해가 잘되어서 완독예정 *이 글을 쓰는데 1시간 20분이 소요되었습니다.(독서는 대략 5시간)
"문서로 남기지 않는 사람은 사기꾼" 이라는 팀장님의 격언이 뇌리에 박힌 이후, 글또를 시작하게 되었다. 글또를 시작할 때는 패기넘치기게 2주에 1번 글 쓰는 거? 쉽지 라고 생각했지만, 생각보다 일정하게 글을 기고기는 쉽지 않았다. 앞으로 (1)매번 새 기수에 진행할 나에게 그리고 (2) 새롭게 글을 쓰고자 하는 독자들을 위해글 잘 쓰는 노하우를 남겨본다.
3. 본문
글쓰기 쉬운 주제를 정하자
개인적으로 제일+제일 중요하다고 생각하는 것이다. 공부를 따로 해서 글을 남기면 투자시간이 확 올라간다. 그보단 현실이나 업무에서 마주치는 문제와 경험들을 정리하는 연상선이 더 쉽게 작성이 된다. 새롭게 공부한다고 책 읽고 인강듣고.. 하다 보면 꾸준한 글쓰기 습관을 형성이 매우 어렵더라. 그래서 평소에 업무하는 부분에서 idea를 얻거나 간단한 개요를 상상하면서 글감을 모으면 훨씬 글쓰기가 쉽다. 주제와 별개로 글쓰기 쉬운 Editor를 선택하는 것도 매우 도움이 된다. 작성하기 편해야 부담 없이 쓸 마음이 드는데 에디터가 편하지 않는다면 자칫 현타올 수 가 있다. github 블로그 파서, vscode으로 작성한 다음에 커밋하고, 앗 그림 안 올렸네 다시 커밋하고.. 맞춤법 틀렸네 다시 커밋하고..
글 쓰는 시간을 정하자
글또를 시작하면서 예상은 평일 현생을 열심히 살고 밤에 글을 쓰는 갓생을 상상했다면, 그건 감히 불가능한 생각이다. 2주간 글쓰는 시간이 주면, 1주간은 현생에 치여서 집 -> 회사를 반복하다가 첫 번째 주말이 당도할 것이다.첫 번째 주말에 가장 먼저 드는 생각은 "다음 주말있으니까 놀아야지 ㄹㅇㅋㅋ" 그렇게 약 13일동안 걱정, 고민을 마음 한 쪽에 가지고 지낸다. 어차피 고통받고 마감직전에 작성할 것이면 처음부터 마감일을 비워 놓고 마음껏 자기 할 일을 하는 게 낫다.(물론 그전에 하면 좋겠지만..!!)
형식이 내용을 지배한다
여러분이 대량의 문서 검토를 상황이라면(ex 이력서 검토) 형식 없는 문서는 볼 마음 조차 안 들 것이다. 이렇듯 형식이 내용보다 우선순위가 있다. 또한, 모든 글에는 목적이 있고 예상되는 독자가 있다. 글의 목적과 독자는 글에 내용을 전개하는데 대들보 같은 역할을 해서 글이 중구난방이 되는 것을 방지해준다. 본인은 글을 구조화하는 것을 매우 못하는 사람이여서 작성 글 마다 템플릿을 차용해서 작성하니 일괄된 작성글이 나와서 좋았다. 추가로 회정 블로그를 보면 구조화를 참 잘하셔서 유심히 👀 배우고 있다.
웃긴 점: 본인 보드게임 글엔 진심임...
글쓰는 무드를 조성하자
꾸준히 성실하게 글을 쓰는 사람들이 있지만 때로는 글을 쓰기 싫을 때가 있을 수 있다. 그럴떈 글 쓰는 "멋진 모습"을 구현해본다. 예를 들어, 스타벅스에서 기술 글 쓰는 멋진 사람을 상상하며 행동한다던가, 아니면 아이패드-키보드를 이용해서 좀 더 트렌디한 연출을 해본다. 생각보다 내 행동이 멋지고 뿌듯한 건 중요하다. 그게 나의 삶을 좀 더 생산적이게 만든다면 Why Not? 다들 갓생 살려고 열심히 노력하는 것 아니겠음?
마무리는 시작만큼 중요하다 - 퇴고
입사 지원 전 날 밤새 작성한 자기소개서를 뿌듯하다고 생각하고 제출하고 다음에 일어나보면 그렇게 촌스러운 글이 있나 싶다. 글또 글 역시 "캬 명글이야 이러다 나 큐레이션 가는거 아님?" 이라고 생각하고 자고 일어나보면 부끄러운 글이 많다. 정작 큐레이션에 올라온 글은 퀄리티가 다르다(그냥 보법이 다름..!). 일정 시간을 두고 다시 읽어보고 수정하는 것은 퀄리티 향상에 아주 큰 도움이 된다. 특히...맞춤법!(않이 맞춤범 웨 안 쓺?)검사기를 한 번 꼭 돌려보도록 하자!
마무리는 엉망이여도 글을 사랑해 ~
나름 스스로 정리해보니 글에 작성에 대한 생각도 정리된 것 같아서 뿌듯하다. 이제 절대 시간내서 글써! *위 글은 작성에 1시간 10분이 소요됨
문제1) ORDER_TOTAL이 500 이상인 주문의 ORDER_ID와 ORDER_TOTAL 값을 구하세요.
SELECT ORDER_ID, ORDER_TOTAL
FROM OE.orders
WHERE ORDER_TOTAL >= 500;
문제2) ORDER_STATUS가 “Entered” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요. (힌트 Schema를 참조)
SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS = 1;
문제3) ORDER_STATUS가 “Cancled” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.
SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS in (2,3);
문제4) ORDER_STATUS가 “Shipped” 인 주문의 CUSTOMER_ID와 ORDER_DATE 값을 구하세요.
SELECT CUSTOMER_ID, ORDER_DATE
FROM OE.orders
WHERE ORDER_STATUS not in (0,1,2,3);
문제5)SALES_REP_ID가 153인 모든 주문의 ORDER_ID, ORDER_DATE, 및 ORDER_TOTAL 값을 구하세요.
SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL
FROM oe.orders
WHERE SALES_REP_ID = 5;
문제6) ORDER_MODE가 'online'인 주문 중, ORDER_TOTAL이 2000 이하인 주문의 ORDER_ID와 CUSTOMER_ID 값을 구하세요.
SELECT ORDER_ID, CUSTOMER_ID
FROM oe.orders
WHERE ORDER_MODE = 'online' AND ORDER_TOTAL <= 2000;
2. GROUP / HAVING 문제
문제1) OE.ORDERS 테이블에서 ORDER_STATUS 별로 주문 건수를 구하세요.
SELECT ORDER_STATUS, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.ORDERS
GROUP BY ORDER_STATUS;
문제2) ORDER_TOTAL의 합계가 10,000 이상인 CUSTOMER_ID를 구하세요.
SELECT CUSTOMER_ID, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OEorders
GROUP BY CUSTOMER_ID
HAVING SUM(ORDER_TOTAL) >= 10000;
문제3): 각 SALES_REP_ID별로 처리한 주문의 평균 ORDER_TOTAL 값을 구하세요.
SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID;
문제4) 3번의 결과에서 평균 ORDER_TOTAL이 500 이상인 SALES_REP_ID를 구하세요.
SELECT SALES_REP_ID, AVG(ORDER_TOTAL) as AVERAGE_ORDER_TOTAL
FROM OE.orders
GROUP BY SALES_REP_ID
HAVING AVG(ORDER_TOTAL) >= 500;
문제5) ORDER_MODE별로 주문된 ORDER_TOTAL의 합계를 구하세요.
SELECT ORDER_MODE, SUM(ORDER_TOTAL) as TOTAL_AMOUNT
FROM OE.orders
GROUP BY ORDER_MODE;
문제6) CUSTOMER_ID별로 주문한 횟수가 5회 이상인 고객들의 ID를 구하세요.
SELECT CUSTOMER_ID, COUNT(*)
FROM OE.orders
GROUP BY CUSTOMER_ID
HAVING COUNT(*) >= 5;
문제7) 각 SALES_REP_ID별로 처리한 주문 중 ORDER_TOTAL이 100 이상인 주문의 건수를 구하세요.
SELECT SALES_REP_ID, COUNT(*) as NUMBER_OF_ORDERS
FROM OE.orders
WHERE ORDER_TOTAL >= 100
GROUP BY SALES_REP_ID;
3. built-in 함수 문제 1
문제 1) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 50000 이상인 주문들에 대해 주문 모드(ORDER_MODE)를 기반으로 'direct'인 경우 '직접 주문', 그렇지 않은 경우 '일반 주문'으로 출력하세요.
SELECT ORDER_ID, DECODE(ORDER_MODE, 'direct', '직접 주문', '일반 주문') AS ORDER_TYPE
FROM OE.ORDERS
WHERE ORDER_TOTAL >= 50000;
문제 2) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)를 기반으로 상태 값이 0인 경우 '진행 중', 1인 경우 '완료', 2,3인 경우 '취소'로 출력하세요. 나머지는 ‘기타’로 출력하세요.
SELECT ORDER_ID, ORDER_STATUS, CASE ORDER_STATUS
WHEN 0 THEN '진행 중'
WHEN 1 THEN '완료'
WHEN 2 THEN '취소'
WHEN 3 THEN '취소'
ELSE '기타' END AS STATUS_DESC
FROM OE.ORDERS;
문제 3) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)의 절대값을 출력하세요.
SELECT ORDER_ID, ABS(ORDER_TOTAL) AS ABS_TOTAL
FROM OE.ORDERS;
문제 4) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)이 양수인지 음수인지를 판별하여 결과를 출력하세요.
SELECT ORDER_ID, SIGN(ORDER_TOTAL) AS SIGN_STATUS
FROM OE.ORDERS;
문제 5) OE.ORDERS 테이블에서 주문 총액(ORDER_TOTAL)을 100으로 나눈 나머지를 출력하세요.
SELECT ORDER_ID, ORDER_TOTAL, MOD(ORDER_TOTAL, 100) AS MOD_RESULT
FROM OE.ORDERS;
문제 6) OE.ORDERS 테이블에서 주문 날짜(ORDER_DATE)의 년도만을 출력하세요.
SELECT ORDER_ID, ORDER_DATE, TO_CHAR(ORDER_DATE, 'YYYY') AS ORDER_YEAR
FROM OE.ORDERS;
문제7) OE.ORDERS 테이블에서 ORDERS_STATUS 별로 그룹지은 후 ORDER_TOTAL 의 값을 평균을 내세요. 해당 테이블을 with 절을 이용하여 temp1 에 저장한 후 전체 temp1 테이블을 출력하세요.
WITH TEMP1 AS
(SELECT ORDER_STATUS, ROUND(AVG(ORDER_TOTAL))
FROM OE.ORDERS GROUP BY ORDER_STATUS)
SELECT * FROM TEMP1
문제8) 당신은 ORDE_STATUS 별로 전체 매출액을 알고 싶다. 다만, 현재 취소건 등 고객들의 클레임이 많은 것을 확인하였다. 얼마나 취소로 인한 손해가 많은지 알고 싶다. 해당 쿼리를 하단에 작성하라.
-- order_status 2,3 취소건 -> 취소건만 order_total
-- order_status 0 ~ 10 까지있는데, 카테고리화 / 퍼센트(분율) 로 보여주면 더 구체적으로 판단할수있다.
-- 0. 쓸 컬럼을 order_total, order_status
-- 1. case문을 이용해서 order_status 카테고리화 할예정.
-- 2. order_status 별로 그룹을 지어 sum(order_total)
with temp1 as(
select order_total,
case
when order_status = 0 then '주문미완료'
when order_status = 1 then '주문완료'
when order_status in (2,3) then '주문취소'
else '배송중'
end as os_cat
from oe.orders)
select os_cat, sum(order_total), round(sum(order_total)/3668054.7*100)
from temp1
group by os_cat
4. DML/DDL 문제
문제 1) 새로운 테이블 NEW_EMPLOYEES를 만들어 주세요. 이 테이블에는 EMP_ID, EMP_NAME, EMP_SALARY 세 개의 컬럼이 있어야 합니다.
EMP_ID는 숫자 타입이며, 기본 키로 설정해주세요.
EMP_NAME은 최대 50자의 문자열 타입으로 설정해주세요.
EMP_SALARY는 숫자 타입으로 설정해주세요.
CREATE TABLE NEW_EMPLOYEES (
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(50),
EMP_SALARY NUMBER
);
INSERT ALL
INTO orders VALUES (1, '김영희', '노트북', 1, TO_DATE('2023-10-01', 'YYYY-MM-DD'))
INTO orders VALUES (2, '박철수', '키보드', 2, TO_DATE('2023-10-02', 'YYYY-MM-DD'))
INTO orders VALUES (3, '이영수', '마우스', 1, TO_DATE('2023-10-03', 'YYYY-MM-DD'))
INTO orders VALUES (4, '최민영', '모니터', 2, TO_DATE('2023-10-04', 'YYYY-MM-DD'))
INTO orders VALUES (5, '황영준', '노트북', 1, TO_DATE('2023-10-05', 'YYYY-MM-DD'))
INTO orders VALUES (6, '김영미', '키보드', 1, TO_DATE('2023-10-06', 'YYYY-MM-DD'))
INTO orders VALUES (7, '박진수', '마우스', 3, TO_DATE('2023-10-07', 'YYYY-MM-DD'))ㄷㄷ
INTO orders VALUES (8, '이민철', '모니터', 1, TO_DATE('2023-10-08', 'YYYY-MM-DD'))
INTO orders VALUES (9, '최영주', '노트북', 1, TO_DATE('2023-10-09', 'YYYY-MM-DD'))
INTO orders VALUES (10, '황지수', '키보드', 2, TO_DATE('2023-10-10', 'YYYY-MM-DD'))
SELECT * FROM DUAL;
문제8-3) 모든 주문 정보를 조회하되, 수량이 3 이상인 경우 '대량 주문'이라는 문자열을, 그렇지 않은 경우 '일반 주문'이라는 문자열을 새로운 컬럼 order_type에 표시하여 출력하세요.
SELECT order_id, customer_name, product_name, quantity, order_date,
CASE WHEN quantity >= 3 THEN '대량 주문'
ELSE '일반 주문'
END AS order_type
FROM orders;
문제8-4): 제품별로 총 주문 수량을 계산하고, 주문 수량이 많은 순으로 정렬하여 출력하세요.
with temp1 as (SELECT order_id, customer_name, product_name, quantity, order_date,
CASE WHEN quantity >= 3 THEN '대량 주문'
ELSE '일반 주문'
END AS order_type
FROM orders)
SELECT order_type, SUM(quantity) as total_quantity
FROM temp1
GROUP BY order_type
ORDER BY total_quantity DESC;;
5. 조인문제 1차
문제 1) OE.ORDERS 테이블에서 주문 상태(ORDER_STATUS)가 '1'인 모든 주문을 조회하십시오.
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1;
문제 2) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 이를 기반으로 주문 상태(ORDER_STATUS)가 '0'인 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)과 주문 총액(ORDER_TOTAL)을 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_STATUS = 0;
문제 3) OE.CUSTOMERS 테이블을 기준으로 OE.ORDERS 테이블과 LEFT JOIN을 하여, 주문 정보가 없는 고객의 이름 (CUST_FIRST_NAME, CUST_LAST_NAME)을 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM OE.CUSTOMERS C
LEFT JOIN OE.ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL;
문제 4) OE.ORDERS와 OE.CUSTOMERS 테이블을 INNER JOIN을 사용하여 결합하고, 주문 총액(ORDER_TOTAL)이 10,000 이상이며, 고객의 성별(GENDER)이 'M'인 모든 주문을 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.ORDER_TOTAL >= 10000 AND C.GENDER = 'M';
문제 5) FULL OUTER JOIN 이용한 조회 OE.ORDERS와 OE.CUSTOMERS 테이블을 FULL OUTER JOIN을 사용하여 결합하고, 주문 정보나 고객 정보 중 하나라도 없는 모든 레코드를 조회하십시오.
SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, O.ORDER_TOTAL
FROM OE.ORDERS O
FULL OUTER JOIN OE.CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
WHERE O.CUSTOMER_ID IS NULL OR C.CUSTOMER_ID IS NULL;
6. 조인문제 2차
문제 1) ORDER_DATE와 관련된 JOIN OE.ORDERS 테이블과 OE.ORDER_ITEMS 테이블을 JOIN하여 ORDER_DATE, PRODUCT_ID 및 QUANTITY 정보를 가져와보세요.
SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID;
SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
natural JOIN OE.ORDER_ITEMS I ;
SELECT O.ORDER_DATE, I.PRODUCT_ID, I.QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I using(order_id)
문제 2) 특정 조건에 따른 집계 ORDER_STATUS가 1인 주문에 대하여 총 주문 금액(UNIT_PRICE * QUANTITY)을 계산하세요.
SELECT O.ORDER_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_ORDER_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_STATUS = 1
문제 3) ORDER_MODE가 'direct'이며 ORDER_STATUS가 0인 주문에 대해 총 주문된 제품 수(QUANTITY)를 계산하세요.
SELECT O.ORDER_ID, SUM(I.QUANTITY) AS TOTAL_QUANTITY
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE O.ORDER_MODE = 'direct' AND O.ORDER_STATUS = 0
문제 4) 각 SALES_REP_ID별로 주문된 제품의 총 가격(UNIT_PRICE * QUANTITY의 합)을 조회하고, 주문 총 가격이 높은 순으로 정렬하세요.
SELECT O.SALES_REP_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_SALES
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
GROUP BY O.SALES_REP_ID
ORDER BY TOTAL_SALES DESC;
문제 5) 2008년에 이루어진 주문과 관련된 모든 PRODUCT_ID와 해당 PRODUCT_ID의 총 판매 금액을 조회하세요.
SELECT O.ORDER_DATE, I.PRODUCT_ID, SUM(I.UNIT_PRICE * I.QUANTITY) AS TOTAL_AMOUNT
FROM OE.ORDERS O
JOIN OE.ORDER_ITEMS I ON O.ORDER_ID = I.ORDER_ID
WHERE TO_CHAR(O.ORDER_DATE,'YYYY-MM-DD') BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY O.ORDER_DATE,I.PRODUCT_ID;
7. 집합 문제
문제1) 주문 상태(ORDER_STATUS)가 1과 2인 주문을 합쳐서 출력해보세요. (UNION을 이용)
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
UNION
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 2;
문제2) 주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 주문을 모두 출력하세요. 단, 중복된 주문이 있을 수 있습니다. (UNION ALL을 이용)
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
UNION ALL
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;
문제3) 주문 상태(ORDER_STATUS)가 1인 주문과 ORDER_TOTAL이 50000을 초과하는 공통된 주문을 찾아 출력하세요. (INTERSECT를 이용)
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
INTERSECT
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;
문제4) 주문 상태(ORDER_STATUS)가 1인 주문 중에서 ORDER_TOTAL이 50000 초과하는 주문을 제외하고 출력하세요. (MINUS를 이용
SELECT * FROM OE.ORDERS WHERE ORDER_STATUS = 1
MINUS
SELECT * FROM OE.ORDERS WHERE ORDER_TOTAL > 50000;
8. SubQuery 문제
문제1)총 주문 금액이 가장 높은 주문의 금액을 조회하시오.
SELECT *
FROM OE.ORDERS WHERE ORDER_TOTAL = (SELECT MAX(ORDER_TOTAL) FROM OE.ORDERS);
문제2)2007년에 주문한 모든 고객의 평균 주문 금액을 조회하시오.
SELECT CUSTOMER_ID, AVG(ORDER_TOTAL) AS AVG_TOTAL
FROM (SELECT CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS WHERE TO_CHAR(ORDER_DATE, 'YYYY') = '2007') GROUP BY CUSTOMER_ID;
문제3)SALES_REP_ID가 155인 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.
SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS WHERE SALES_REP_ID = 155);
문제4) IN 사용: 주문 금액이 10,000 이상 50,000 이하인 CUSTOMER_ID의 모든 주문을 조회하시오.
SELECT ORDER_ID, CUSTOMER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM OE.ORDERS WHERE ORDER_TOTAL BETWEEN 10000 AND 50000);
문제5) ALL 사용: 모든 판매 대표의 평균 주문 금액보다 높은 주문들만 조회하시오.
SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE ORDER_TOTAL > ALL (SELECT AVG(ORDER_TOTAL) FROM OE.ORDERS GROUP BY SALES_REP_ID);
문제6)ANY 사용:주문 상태가 1 또는 3인 주문보다 주문 금액이 높은 주문들을 조회하시오.
SELECT ORDER_ID, ORDER_TOTAL FROM OE.ORDERS
WHERE ORDER_TOTAL > ANY (SELECT ORDER_TOTAL FROM OE.ORDERS WHERE ORDER_STATUS IN (1,3))
9. GROUPING 문제
문제1) OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 그룹짓고 모든 subtotal과 총합을 생성하세요.
SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY CUBE (ORDER_MODE, ORDER_STATUS);
문제2) Grouping sets 으로 OE.ORDERS 테이블을 이용하여 ORDER_MODE, ORDER_STATUS 과 전체 total을 생성하세요.
SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY GROUPING SETS ((ORDER_MODE), (ORDER_STATUS), ());
-- 두 컬럼을 모두 넣으면 모든행 출력
문제3) ORDER_MODE로 그룹화한 후, 각 ORDER_MODE 내에서 SALES_REP_ID로 추가 그룹화하여 주문 총액을 반환합니다. 또한 ORDER_MODE에 대한 subtotal와 Grand total도 반환하세요
SELECT ORDER_MODE, SALES_REP_ID, SUM(ORDER_TOTAL)
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, SALES_REP_ID);
문제4) 문제 3처럼 ORDER_MODE와 ORDER_STATUS의 각 조합에 대한 주문 총액을 반환하며, GROUPING 함수를 사용하여 어떤 열이 집계된 결과인지를 0과 1로 나타내세요.
SELECT ORDER_MODE, ORDER_STATUS, SUM(ORDER_TOTAL),
GROUPING(ORDER_MODE) AS GRP_MODE,
GROUPING(ORDER_STATUS) AS GRP_STATUS
FROM OE.ORDERS
GROUP BY ROLLUP (ORDER_MODE, ORDER_STATUS);
10. Window function 문제
문제1) 모든 주문에 대한 주문 총액(ORDER_TOTAL)의 합계를 출력하세요.
SELECT ORDER_ID, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER() AS TOTAL_SUM
FROM OE.ORDERS;
SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE) AS CUMULATIVE_TOTAL
FROM OE.ORDERS;
문제4) 주문 모드(ORDER_MODE)별로 그룹화하고, 주문 날짜(ORDER_DATE)에 따라 주문 총액과 해당 주문 모드 및 날짜까지의 누적 합계를 반환하세요.
SELECT ORDER_ID, ORDER_MODE, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(PARTITION BY ORDER_MODE ORDER BY ORDER_DATE) AS CUMULATIVE_MODE_TOTAL
FROM OE.ORDERS;
문제5) 문제: 주문 날짜(ORDER_DATE)에 따라 주문 총액 누적합계와 해당 주문 날짜를 포함한 최근 7일 동안의 누적 합계를 반환하세요.
Hint) BETWEEN INTERVAL ‘7’ DAY PRECEDING AND CURRENT ROW
SELECT ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUM(ORDER_TOTAL) OVER(ORDER BY ORDER_DATE RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS WEEKLY_CUMULATIVE_TOTAL
FROM OE.ORDERS;
문제6) 각 판매 대표(SALES_REP_ID)에 대한 주문 총액(ORDER_TOTAL)과 해당 판매 대표의 평균 주문 총액을 반환하세요.
SELECT ORDER_ID, SALES_REP_ID, ORDER_TOTAL,
AVG(ORDER_TOTAL) OVER(PARTITION BY SALES_REP_ID) AS AVG_SALES_BY_REP
FROM OE.ORDERS;
데이터 베이스를 조작할때 자주 나오는 단어가 DDL, DML 이다.Data Definition Language (DDL), Data Manipulatation Language(DML), Data Control Language(DCL)이다. 다양한 SQL 언어를 사용하다 보면 헷갈리니까 mysql 기준으로 한번 정리해보자.
1. Data Definition Language(DDL)
DDL 란 데이터베이스를 정의하는 언어이며 데이터가 들어오는 조건(제약조건), 데이터의 타입등을 정의하는 언어이다. 쉽게말하면 데이터를 수집하기 위한 껍데기 혹은 틀을 생성하는 것이며, Database 설계시 가장 먼저 수행한다. 다만, 사용하는 Tool(DBeaver 등)에 대해서 지원하는 파일(csv) 혹은 API 형식(Google Bigquery)에 따라 자동으로 데이터 type를 잡아주는 것들도 있다.
CREATE: 테이블을 만들기 위한 기본 statement로, 데이터 타입, 제약조건을 설정한다.
자주 헷갈리는 TRUNCATE, DROP, DELETE 비교. 사실상 TRUNCATE와 DELETE를 비교해보면 쉽다.
TRUNCATE
DROP
DELETE
범주
DDL
DDL
DML
where와 사용가능
X
X
O
성능
빠름 (transaction log 1번)
-
느림 (각 행마다 transaction log 기록)
ROLLBACK
불가
불가
가능
테이블 용량
초기화
-
용량 감소 없음
2. Data Manipulatation Language(DML)
DML 란 기존에 생성된 Database를 조작하는 것으로 기본적으로 조회하는 select이 가장 많이 쓰인다.
SELECT
INSERT
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ... }
-- 사용례
-- 전체 컬럼에 해당하는 모든 값을 넣을 때
INSERT INTO 테이블이름
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
-- 특정 컬럼의 값을 넣을 때
INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)
VALUES (데이터값1, 데이터값2, 데이터값3, ...)
UPDATE
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
-- 사용례
UPDATE 테이블이름
SET 컬럼명1=데이터값1
WHERE 컬럼명2=데이터값
DELETE
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
-- 사용례
DELETE FROM 테이블명 WHERE 조건;
3. Data Control Language(DCL)
DCL이란 데이터베이스의 권한 관리를 위한 명령어이다. 데이터베이스는 무결성과 정합성이 매우 중요하기 때문에, 데이터 변환,삭제 등에 대한 관리를 엄격하게 한다. 이를 위해서 특정 user의 수행권한을 관리하는 것이라고 이해하면 된다. mysql에서는Account Manger Statements라고 분류된다.
GRANT
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
REVOKE
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
