[👩‍💻TIL 45일차 ] 유데미 스타터스 취업 부트캠프 4기

 

이번 SQL 수업은 Postgresql과 dbeaver로 진행된다.

• 프로그램 설치
• 샘플 DB 설치

 

select count(*) 
from northwind.customers;

 

• 분석 목적 설정 → 지표 설정 및 분석 계획 → 데이터 추출/정제/가공/분석 → (BI 도구, python, R, Excel과 연동하여 작업) → 리포트 작성 및 발표
  • 조직마다 다르는 데이터 및 분석 목표가 다르다. 도메인 지식, 데이터 특성을 이해하는 것이 중요하다.
  • 분석 업무는 질문에서 시작한다 → 질문에 답하기 위한 분석 지표 설정
    ex) 월별 판매 추이가 어떠한가? → 월별 매출액, 판매 건수 변화
  • 질문에 답하기 위한 데이터는 어디에서 나오고 어디에 저장되는가?
    기업에는 여러 데이터 베이스가 있고 이것을 통합해서 관리하는 것이 데이터 웨어하우스이다. 데이터 웨어하우스 (data warehouse)는 있을 수도 있고 없을 수도 있다. 
  • 데이터의 종류에 따른 특성
    • 업무 데이터: 갱신형, 정합성↑, 정확도↑, 정규화(조인, 서브쿼리 사용 多)
      - 기업의 비즈니스 결과로 생성된 데이터
      - 마스터 데이터 (정보): 고객, 상품, 카테고리 정보 등 (코드성)
      - 트랜잭션 데이터(행동): 구매, 배송, 리뷰작성 등 = 마스터 데이터와 조인이 일어남
    • 로그 데이터: 누적형 (쌓이기만 하고 변경X)
      - 사용자의 접속시간, 클릭이벤트, ip, 기기, 세션 등의 정보를 저장한 데이터
      - 사용자 행동 분석을 통해 웹/앱 ui, ux 개선 시 필요
      - 정확도는 업무데이터에 비해 낮음 (크롤러의 로그 포함 고려 필요)
• 데이터 → ETL → Data Warehouse → SQL쿼리(데이터 추출) → BI, python 등 사용 → 발표
  
  • 탐색(테이블) → 프로파일링 → 정제(=전처리) → 셰이핑(분석할 결과 테이블) → 분석

---

northwind 데이터셋 개요: 전 세계에 식품을 수출하는 가상의 식품회사의 샘플 데이터

 

• ERD 보기

• 테이블 관계

 

비식별관계

• products: 0~n
• category_id: 0~1
• 하나의 카테고리에는 여러 개의 상품이 존재하는데, 제품이 없는 카테고리는 존재할 수 있다. 카테고리가 없는 제품은 존재할 수 있다.

식별관계

• orders: 1, order_details: 多
• 하나의 주문에는 여러 개의 주문 내역이 포함된다.
• 주문내역이 없는 order_details는 존재할 수 있다.
• 주문이 없는 주문내역은 존재할 수 없다.

자기참조관계

 

• 속성
  • 기본속성: ex) 판매 제품명, 단가, 날짜
  • 설계속성
  • 파생속성
    
    
• 식별자: 각각의 개체를 구분할 수 있는 결정자
  • 대표성 여부: 주식별자, 보조식별자
  • 속성의 수: 단일식별자, 복합식별자
  • 스스로 생성 여부: 내부식별자, 외부식별자
  • 대체 여부: 본질 식별자, 인조 식별자 (일련번호)
    
    
• 정규화: 중복 제거, 정합성
  • 제1정규화: 데이터의 중복 제거
  • 제2정규화: 주식별자 항목 중 코드화 할 수 있는 속성 분리
  • 제3정규화: 일반 속성 항목 중 코드화 할 수 있는 속성 분리
    
    
• 테이블 구조 설계
  → 테이블 2개: 개인정보(1),  학력(多)
  
  

-- 테이블 목록 조회 --
select *
from pg_tables
where schemaname = 'northwind'
order by 2;

 📍 pg_table를 이용하면 database에 있는 table의 종류와 각 table의 owner를 알 수 있습니다.

 

select *
from orders  ;

➡️ 행의 개수가 200개를 넘어 표시되지 않는 경우 아래 코드 사용

select count(*)
from orders ;

-- 테이블 상세 정보 --
select table_name as 테이블명,
	column_name as 컬럼명,
	column_default as 디폴트값,
	is_nullable as null가능여부,
	data_type as 자료형,
	character_maximum_length as 문자열최대자리수
from information_schema.columns
where table_schema = 'northwind'
order by table_name , ordinal_position ;

📍 information_schema는 각 MySQL 인스턴스 내의 데이터베이스이며, MySQL Server가 보유하는 다른 모든 데이터베이스에 대한 정보를 저장하는 장소입니다.

📍 information_schema.columns는 열에 대한 모든 정보를 제공한다.

-- 제약조건 정보 --
select table_name, column_name, constraint_name
from information_schema.constraint_column_usage
where table_schema = 'northwind'
order by 1, 3 desc;

 

• count: null이 아닌 것만 개수를 세준다.

select count(*) - count(region) as region
from customers;

 

• 단순 반복 작업 엑셀로 쉽게 하기

-- 컬럼 별 null 개수 --
select count(*) - count(	customer_id	) as	customer_id
, count(*) - count(	company_name	) as	company_name
, count(*) - count(	contact_name	) as	contact_name
, count(*) - count(	contact_title	) as	contact_title
, count(*) - count(	address	) as	address
, count(*) - count(	city	) as	city
, count(*) - count(	region	) as	region
, count(*) - count(	postal_code	) as	postal_code
, count(*) - count(	country	) as	country
, count(*) - count(	phone	) as	phone
, count(*) - count(	fax	) as	fax
from customers;

--디폴트값--
select column_default 
from information_schema.columns
where table_schema='northwind'
order by table_name,ordinal_position ;

• pk는 erd보고 파악하기
• orders: 기간 파악 등

---

기본 데이터 타입

• 숫자형
  • 정수: smallint, integer, bigint
  • 실수
    고정소수점: 

엑셀파일, PPT (간단한 사항), 고객이 누군지, 특이사항

 

쿼리와 파이썬 연결방법도 배웠다.

---

-- 고객 직함별 데이터 수
select contact_title , count(*)
from customers c 
group by 1
order by 2 desc;


-- 국가별 고객 수
select country, count(*) cnt
from customers c
group by 1
order by 2 desc;


-- 국가-도시별 고객 수
select country, city, count(*) cnt
from customers c
group by 1,2
order by 3 desc;

---

팀 과제

테이블을 하나하나 뜯어보자!

테이블 목록

 

테이블 상세정보

 

 

 

이해관계자들에 대해 순서가 있다고 생각하고 테이블 관계 및 세부사항을 정리해보았다. 테이블을 정리해놓고 보니 northwind의 특징을 쉽게 이해할 수 있었다. 앞으로 추가적인 분석을 통해 northwind에 대한 많은 정보를 알아보고 싶다.

---

* 유데미 큐레이션 바로가기 : https://bit.ly/3HRWeVL

* STARTERS 취업 부트캠프 공식 블로그 : https://blog.naver.com/udemy-wjtb

📌 본 후기는 유데미-웅진씽크빅 취업 부트캠프 4기 데이터분석/시각화 학습 일지 리뷰로 작성되었습니다.