［데이터분석 1주차］데이터 직무분석，ＳＱＬ기초문법

 

지난 일주일 동안 가장 인상 깊었던 배움에는 뭐가 있었지?

데이터 분석은 집중직무와 활용직무로 나뉘고 내가 관심있는 비즈니스 분석가는 데이터 활용직무라는 것.
SQL 기본 문법도 배움. 

그 배움까지 다가가는데 어떤 어려움이 있었지?

SQL 에서 Segmentation 부분부터 어려워지기 시작..

 

그 과정에서 나는 무엇을 깨달았고, 어떤 감정/생각이 들었었지?
결과적으로, 현재 나의 상태는?

어려운 문법은 주기적인 복습만이 살길. 첫 달, 첫 3개월 고비만 지나면 모든 업무에 적응할 수 있듯이..
결과적으로 나의 현재 상태는 Segmentation 부분 이후 는 아직 낯선 상태이다.

이 상태에서 다음 일주일을 더 잘 보내려면 어떻게 해야 할까?

1. 캠프 프로그램을 우선 잘 따라가기
2. 9:00 - 10:00 코드카타를 통해 SQL 반복복습 (다음 주 목표: Level 1 다 풀기)
3. 월~화 Python 강의듣고 정리
4. 수~ ADsP 책 통해 시험분석, 이론정리, 강의 배속

* ADsP(11/3), SQLD(11/17) 자격증 시험 예정   

 

 

 

1. 데이터분석 직무이해

 

데이터분석: 지저분한 raw 데이터를 정리, 변환, 조작 및 검사함으로써 유용한 인사이트를 도출하고 이를 차트, 그래프 또는 대시보드를 통해 시각화 형태로 표현하는 것.

• 1. 기술적 분석 (Descriptive Analysis): 첫 단계로, 수집된 데이터 요약하여 ‘무슨 일이 일어났지?’ 질문에 답, 데이터의 핵심포인트 살펴보고, 이미 벌어진 일에 집중
  2. 진단적 분석 (Diagnostic Analysis): 수집된 데이터 사용하여 당면한 문제의원인 이해하고 패턴을 찾아내는 단계 (왜 일어났는지?) 질문에 답
  3. 예측 분석 (Predictive Analysis): 미래 트랜드를 감지하고 예측, 미래성장을 위한 중요한 인사이트 ‘앞으로 어떤 일이 벌어질까?) 질문에 답
  4. 처방적 분석 (Prescriptive Anaylsis): 위 세 단계에서 얻은 모든 인사이트 하나로 모아 앞으로 다가올 일 대비한 대비책 세우고 실행 가능한 계획 수립. ‘무엇을 해야할까’ 질문에 답

 

[데이터집중직무]

1) 데이터 엔지니어 (개발자 느낌)

데이터 엔지니어는 기본적으로 데이터의 수집 및 관리를 담당한다. 기업 내 데이터 기초 공사를 하는거죠. 어떻게 데이터를 수집할 지, 어떤 방식으로 데이터를 쌓을지, 어떻게 관리할지를 고민하여 회사 내 데이터 흐름을 생성한다.

 

2) 데이터 사이언티스트 (연구원 느낌)

새로운 논문을 기반으로 자사 서비스의 시스템을 개선하는 역할을 하는 사람들이다. 데이터 사이언티스트는 일정 패턴을 찾아내면 해당 패턴이 유의미한 것인지까지 알아내는 역할까지도 통상 진행하기에 통계학적 지식 또한 많이 필요하다.

 

3) 데이터 분석가 (기획자 느낌)

'데이터를 분석하여 이해하기 쉬운 형태로 보고서나 시각화 자료 등을 만들어 의사결정을 지원하는 역할을 하는 사람.'으로 정의한다.

즉, 데이터 분석가는 기업이나 조직이 데이터를 효율적으로 활용하여 경영 전략 수립, 문제 해결, 예측 및 비즈니스 성과 향상을 이룰 수 있도록 (회사나 단체에 이익이 되는 방향으로의 결정) 도와주는 역할을 수행한다.

 

 

[데이터 활용직무]

1) CRM 마케터

Customer Relationship Management, 고객과 커뮤니케이션하고 분석하는 마케터로, 기존 고객들이 더 많은 가치를 창출하도록 관리하는 업무가 주이다. 고객 획득 비용이 증가하는 추세와 함께 퍼스트파티의 활용이 대두되며 CRM 마케터를 뽑는 기업이 많아졌다. 이러한 마케팅에는 고객데이터에 기반한 마케팅 전략을 세우고 결과를 분석하는 데이터 역량이 필요하다. CRM 마케터의 경우 회사의 데이터베이스에서 데이터를 꺼내오는 프로그램 언어인 SQL, 고객 데이터 분석을 진행하기 위한 웹/모바일 트래킹 툴(GA, 에이스카운터 등) 의 활용 가능 여부를 자격 요건으로 갖는 경우가 많다.

 

2) 퍼포먼스 마케터

광고 캠페인을 통한 광고 최적화를 진행하는 마케터를 의미하는데요. CRM 마케터와는 달리 보통 Paid 광고를 통해 효과적으로 새로운 고객을 데려오는 업무를 진행합니다. 가장 숫자와 연결된 마케터이기에 투자 수익률을 쉽게 가시적으로 나타내요. 그러다 보니 광고비용을 최적화하여 비효율적 광고를 줄일 수 있다는 장점이 있어 업계에서는 퍼포먼스 마케팅에 대한 관심이 늘고있다. 

퍼포먼스 마케터는 구글이나 페이스북 등 주요 플랫폼을 통한 고객 획득 마케팅을 주 업무로 갖는 경우가 많은데요. 보통 퍼포먼스 마케터 내에서는 디스플레이 광고(DA)와 검색 광고(SA)를 하는 업무로 나눠진다. 많은 경우 해당 직업은 미디어렙사나 광고대행사에서 해당 업무를 시작하는 경우가 많고, 자격 요건으로는 웹/모바일 트래킹 툴(GA, 에이스카운터 등) 활용 가능 여부 혹은 검색 광고 관련 자격증이나 활용 경험을 두는 경우가 많다.

 

3) 그로스해커

기업의 성장을 목표로 기업의 단계에 맞춰 다양한 액션을 진행하는 직업으로  다양한 비즈니스를 경험해보며 자신과 맞는 분야를 찾고 싶다면 그로스 해커를 첫 직장으로 갖는 것도 좋은 선택이 될 수 있다. 주로 그로스 마케터, 그로스 매니저 등으로 공고가 나오는 경우가 많다. 기업별로 원하는 자격 요건도 매우 상이하다

출처: Dighty Blog (https://blog.dighty.com/business/?idx=11164139&bmode=view)

 

 

4) 비즈니스 분석가

비즈니스 분석가는 회사의 경영진들이 사업을 한눈에 파악할 수 있도록 매출, 비용, 시장 점유율, 손익, 고객 프로파일 등 기업 내 주요 데이터를 리포팅하고 데이터에 따라 계획의 타당성 검증, 케이스 시뮬레이션 및 비용 산출하는 업무를 주로 진행한다. 그러기에 비즈니스에 대한 이해, 데이터에 대한 이해와 핸들링 스킬이 중요하다. 기업이 잘되고 잘되지 않는 상황은 인과가 명확하지 않을 때가 많다. 그러기에 산업 동향에 대한 이해가 풍부해야 하고 회사의 계획과 본인의 가설을 입증할 수 있는 데이터를 명확하게 뽑아내는 스킬이 중요하다. 그러기에 자격 요건으로는 주로 SQL 활용 능력, 데이터 시각화 툴(Tableau, Power BI) 등을 보는 경우가 많다.

 

비즈니스 분석가는 데이터 분석가, 프로덕트 분석가와 유사한 측면이 있다. 데이터 분석가와의 차이는 데이터 분석가는 데이터 분석 역량을 중요시하고 비즈니스 분석가는 인사이트 도출을 더 중요시한다는 차이가 있다. 그리고 프로덕트 분석가는 제품에 대한 행동 로그 데이터에 집중한다면 비즈니스 분석가는 매출이나 비용 등 사업 가치에 집중한다는 차이가 있다.

 

 

2. SQL 기초 문법

문법	내용
SELECT, FROM 문	select * (전체)  /   select  컬럼1, 컬럼2 (필요한 항목만)  /  select 컬럼 as " 한글, 특수문자 (-하이픈 포함)"  또는 select 컬럼 (한칸 띄어쓰기) 별명(영문, 언더바)  from  테이블
WHERE	(데이터 필터링) (* 반드시 from 다음에 기재)
BETWEEN	BETWEEN 숫자 and 숫자
IN	IN (A, B, C) 포함(숫자,'문자')
LIKE	LIKE ('문자%', '%문자%', '문자%')
AND, OR, NOT	ex) (select from 뒤) where age >=20 and gender = 'male' / ex) where pay_type='card' or vat <=0.2 ex) where  not gender = 'female'
계산하기	+ (더하기), - (빼기), * (곱하기), / (나누기), Sum (합계), Average (평균)
Count	COUNT(컬럼) * 컬럼명 대신 1 혹은 * 사용 가능 ) 몇개의 값을 가지고 있는지 구할 때 : DISTINCT select count (*)/ count (1)  - 전체 select count (distinct customer_id) 주문을 한 고객 갯수
최대, 최소값	max(컬럼), min(컬럼)
group by	group by 컬럼 (select 뒤 동일컬럼, 뽑고자하는 데이터) select cusine_type,           sum(price) sum_of_price  from food_orders group by cuisine_type
order by	order by (그대로는 오름차순) , order by 컬럼 desc (내림차순)
Replace	Replace(바꿀 컬럼, 현재 값, 바꿀 값)
Substr	Substr(조회 할 컬럼, 시작 위치, 글자 수)
Concat	Concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
if	"조건에 따라 다른 방법을 적용하고 싶을 때" if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
Case	"조건을 여러가지 지정하고 싶을 때" Case when~then else end
오류해결	문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경 -숫자로 변경 cast(if(rating='Not given', '1', rating) as decimal) --문자로 변경 concat(restaurant_name, '-', cast(order_id as char))
User Segmentation	ex) ★ ★ ★10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력) ★ ★ ★ select name, age, gender, case when (age between 10 and 19) and gender='male' then '10대 남자'         when (age between 10 and 19) and gender='female' then '10대 여자'         when (age between 20 and 29) and gender='male' then '20대 남자'         when (age between 20 and 29) and gender='female' then '20대 여자'         end '연령대' from customers where age between 10 and 29
Subquery	여러 번의 연산을 한 번의 SQL 문으로 수행 ex) Subquery 문 안을 수정해서, 음식 주문시간이 25분보다 초과한 시간을 가져오기 (주문 테이블에서 주문 번호, 음식점명, 음식 준비시간)  select order_id, restaurant_name, if(over_time>=0, over_time, 0) 'over_time' from ( select order_id, restaurant_name, food_preparation_time-25 'over_time' from food_orders ) a 종류 1) 중첩(일반) 서브쿼리 WHERE 절에서 사용 서브쿼리의 결과에 따라 달라지는 조건 # 중첩 서브쿼리 실습 # 문동은의 나이보다 나이가 많은 모든 데이터 반환하기 select * from basic.theglory  where 나이 > (select 나이 from basic.theglory where 이름='문동은') 2) 중첩(일반) 서브쿼리 SELECT 절에서 사용 하나의 컬럼처럼 사용 스칼라 서브쿼리 이용을 위해서는, 서로다른 테이블이 필요합니다. # 스칼라 서브쿼리 실습 # theglory 의 이름과 theglory2 테이블의 이름이 일치하는 경우를 count 하여 # same_name_cnt 컬럼으로 반환 # theglory 의 이름과 theglory 테이블의 이름이 일치하는 경우의 결제금액을 sum 하여 # same_name_sumamount 컬럼으로 반환 select 이름 , 나이 , (select count(*) from theglory2 where theglory2.이름=theglory.이름) as same_name_cnt , (select sum(결제금액) from theglory2 where theglory2.이름=theglory.이름)as same_name_sumamount from basic.theglory 3) 인라인 뷰 서브쿼리 (가장 많이 사용) FROM 절에서 사용 하나의 테이블처럼 사용 AS 구문을 사용하여 명칭을 반드시 기재해야 함 간단해 보이지만, 향후 JOIN 및 UNION 시 가장 유용하게 사용됩니다 ! # 인라인뷰 서브쿼리 실습 # 나이가 33세 이상인 모든 데이터 중 나이와 직업 컬럼 반환하기 select x.나이, x.직업 from ( select * from basic.theglory where 나이>=33          )as x 조건1) group by 를 활용하여 첫 접속일자별 게임캐릭터수를 중복값 없이 구하고, 조건2) 인라인 뷰 서브쿼리를 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요. select * from ( select first_login_date, count(distinct game_actor_id)as actor_cnt          from basic.users          group by first_login_date )as a where actor_cnt>10 (in 사용) select name from Employee where id in(                select managerId                from Employee                group by managerId                having count(*) >= 5                )
JOIN	필요한 데이터가 서로 다른 테이블에 있을 때 조회 LEFT JOIN : 공동 컬럼을 기준으로 다른 테이블에 값이 없더라도 모두 조회. INNER JOIN : 공통 컬럼을 기준으로 두 테이블에 모두 있는 값만 조회. ex) 한국 음식의 주문별 결제 수단과 수수료율을 조회하기 (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) *결제 정보가 없는 경우도 포함하여 조회 select a.order_id, a.restaurant_name, a.price, b.pay_type, b.vat from food_orders a left join payments b on a.order_id=b.order_id where cuisine_type='Korean' ex) 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기 (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회 select a.order_id, a.restaurant_name, a.price, b.vat, a.price*b.vat "수수료율" from food_orders a inner join payments b on a.order_id=b.order_id (join 사용) select e.name from employee e join employee m on e.id = m.managerId group by e.id having count(m.managerId) >= 5 (in 사용) select name from Employee where id in(                select managerId                from Employee                group by managerId                having count(*) >= 5                )
NULL 제거하기	where b.customer_id is not null
Coalesce	다른 값을 대신 사용하기 ex) coalesce(값이 없는 컬럽, 20) 값이 없는 컬럼이면, 20 을 줘라 select a.order_id, a.customer_id, a.restaurant_name, a.price, b.name, b.age, coalesce(b.age, 20) "null 제거", b.gender from food_orders a left join customers b on a.customer_id=b.customer_id where b.age is null
데이터가 상식 X	[방법] 조건문으로 값의 범위를 지정하기 case when age<15 then 15 when age>80 then 80 else age end "범위를 지정해준 age" 범위를 지정해준 결과, 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체된 것을 확인할 수 있음
Pivot Table	2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것 ex) 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순) select age, max(if(gender='male', order_count, 0)) male, max(if(gender='female', order_count, 0)) female from ( select b.gender, case when age between 10 and 19 then 10 when age between 20 and 29 then 20 when age between 30 and 39 then 30 when age between 40 and 49 then 40 when age between 50 and 59 then 50 end age, count(1) order_count from food_orders a inner join customers b on a.customer_id=b.customer_id where b.age between 10 and 59 group by 1, 2 ) t group by 1 order by age
< Window Function > Rank	1) N 번째까지의 대상을 조회하고 싶을 때, Rank ex) 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기 select cuisine_type, restaurant_name, order_count, rn "순위" from ( select cuisine_type, restaurant_name, rank() over (partition by cuisine_type order by order_count desc) rn, order_count from ( select cuisine_type, restaurant_name, count(1) order_count from food_orders group by 1, 2 ) a ) b where rn<=3 order by 1, 4
Sum	2) 전체에서 차지하는 비율, 누적합을 구할 때, Sum ex) 카테고리별 합, 카테고리별 누적합 구하기 select cuisine_type, restaurant_name, cnt_order, sum(cnt_order) over (partition by cuisine_type) sum_cuisine, sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine from ( select cuisine_type, restaurant_name, count(1) cnt_order from food_orders group by 1, 2 ) a order by cuisine_type , cnt_order
Sum 오류	***누적합 고쳐보기 - WINDOW 함수(SUM 등)를 사용할 때, SUM으로 동일한 cnt_order 값을 가진 여러 행이 있을 경우, SQL 엔진은 이 값을 한꺼번에 더하는 현상이 발생합니다. cnt_order의 순서를 결정할 명확한 기준이 없으므로 발생 - 해결 방법누적합을 순서대로 표기하기 위해 order by에 cum_cuisine 을 추가 - ORDER BY 절에 cnt_order 외에 추가적인 열에 순서를 부여할 수 있는 restaurant_name을 포함시켜야 함. 이렇게 하면 동일한 cnt_order 값을 가진 행들이 명확하게 순서가 정해져 누적합이 정상적으로 처리됩니다. ex) 카테고리별 합, 카테고리별 누적합 구하기 (오류수정) select cuisine_type, restaurant_name, cnt_order, sum(cnt_order) over (partition by cuisine_type) sum_cuisine, sum(cnt_order) over (partition by cuisine_type order by cnt_order, restaurant_name) cum_cuisine from ( select cuisine_type, restaurant_name, count(1) cnt_order from food_orders group by 1, 2 ) a order by cuisine_type , cnt_order, cum_cuisine
Date	date(date)     W요일은 1 일요일, 2 월요일, 3 화요일, 4 수요일 예시)  3월 조건으로 지정하고, 년도별로 정렬하기 select date_format(date(date), '%Y') "년", date_format(date(date), '%m') "월", date_format(date(date), '%Y%m') "년월", count(1) "주문건수" from food_orders a inner join payments b on a.order_id=b.order_id where date_format(date(date), '%m')='03' group by 1, 2, 3 order by 1