[SQL독학] <SQL로 시작하는 데이터 분석> 1-3장

1. SQL 활용한 분석

• structured data : 열과 행에 저장되는 데이터
• unstructured data : 미리 지정된 구조, 데이터 모델, 데이터 타입이 없음 (문서, 이메일, 웹 페이지 등)
• quantitative data : 특정 수치로 정량화한 데이터 (가격, 양, 방문기간, 숫자 타입 정보 등)
• qualitative data : 텍스트 형태로 적힌 느낌, 의견 등 수치로 측정할 수 없는 데이터 (설문조사, 소셜미디어 내용 등) - 텍스트 분석, 감성 분석, 자연어 처리 기술을 활용 시 수준 높은 분석 가능
• first-party data : 기관이 직접 수집한 데이터 (로그, 고객 정보 등)
• second-party data : 서비스 제공 업체에서 수집하는 데이터 (이메일, 소프트웨어 등)
• third-party data : 업체에 돈 지불/ 정부에서 무료로 공개한 데이터 등

 

2. 데이터 준비

SELECT country,
‘1980’ as year,
year_1980 as population

 

프로파일링

: 데이터가 어떻게 저장되어 있는지, 열 이름, 관계 파악

: EDA(Exploratory data analysis와 연관있음- 데이터 분포를 파악하는 데 box plot, histogram 등 유용하다

: 출현빈도 확인이 가장 효과적인 프로파일링 방식

• 프로파일링 하려는 필드 : GROUP BY 로 묶는다
• COUNT() , COUNT DISTINCT()
• 구간화 (연속값을 프로파일링할 때 유용)

SELECT 
	CASE WHEN D≤100 THEN ‘K’
		WHEN D>100 THEN ‘J’
		ELSE ‘M’ END AS ‘KJM’,
	CASE WHEN~~~~~ END AS ‘DDD’
FROM ~

 

• 이런 식으로 설정 CASE문 SELECT안에 여러개 쓸 수 있다 ROUND(값, 소수점자리) → ROUND(sales, 2) 이런 식으로

 

- 하나의 case문 안의 모든 then 반환값은 데이터 타입이 같아야한다 (문자/순자/논리 등)

*오류발생 시, 반환값 데이터 타입을 cast로 변경하여 해결한다

CAST(1234 as varchar) 혹은 1234::varchar

 

윈도우 함수 (여러 행에 걸친 수행을 수행한다)

-COUNT, SUM, AVG, MIN, MAX ,RANK, FIRST_VALUE 등

- ORDER BY 는 정렬 기준

- PARTITION BY 에 해당하는 필드로 구분해 연산 수행

function (필드명) over (partition by 필드명 order by 필드명)

 

REPLACE(’$19’,’$’,’’)
→ 19

 

 

NULL  처리하는 방법

CASE WHEN num_orders IS NULL then 0 else num_orders END

 

 

 

UNION ALL

여러 쿼리 결과를 조합해 하나의 데이터셋으로 만든다

(각 쿼리의 열갯수가 서로 동일해야 한다, 데이터 타입이 호환되어야 한다)

 

FROM country_pop

UNION ALL

SELECT country,
‘1990’ as year,
year_1990 as population
FROM country_pop

 

NULLIF

같으면 null, 다르면 첫번째 숫자 반환

nullif(date,  ‘2015-03-03') 같으면 null, 다르면 date를 반환한다

 

date

데이터타입 바꾸기 → cast(—- as date)

 

 

3. 시계열 분석

시간순으로 정렬된 데이터 배열 (일일 최고 기온, S&P장 마감 지수, 일일 걸음 수 등)

시계열 분석을 통해 시간에 따른 데이터 변화를 이해하고 정량화 할 수 있다

예측은 특히, 시계열 분석의 주요 목표 중 하나이다

과거의 값으로 미래의 값을 예측하며, 시장상황, 대중트렌드, 제품도입 시기 등

정확한 예측은 어려워도, 계획 수립의 좋은 근거를 얻거나, 인사이트를 얻을 수 있다

1. 깃허브 페이지에서 csv 파일 다운로드 받기
2. PgAdmin에서 테이블 만들기!! (아래와 같이 각 열의 데이터타입을 넣어준다)

DROP table if exists retail_sales;
CREATE table retail_sales
(
sales_month date
,naics_code varchar
,kind_of_business varchar
,reason_for_null varchar
,sales decimal
)
;

1. CSV파일을 임포트 해준다.

COPY retail_sales FROM 'C:\sql_data\us_retail_sales.csv' DELIMITER ',' CSV HEADER;

 

현재 날짜 반환

SELECT current_date;

#그 외에도 현재 날짜/ 시간 반환하는 함수
current_timestamp
localtimestamp
get_date()
now()

#오늘 일자가 나온다
SELECT date_part('day', current_timestamp)
(day, month, hour 등을 넣을 수 있다)

SELECT extract('day' from current_timestamp)

#1변수에서 2변수의 이름을 가져올 때
SELECT to_char(current_timestamp, 'Day')
> Wednesday

 

 

날짜 계산

datediff('day', start_timestamp, end_timestamp) #PostGre에서는 사용 불가
age(date('2020-02-02'), date('2021-02-03'))  #PostGre에서는 이거 사용

date_add('month', 1,' 기존날짜)
date('2020-02-02') + interval '7 days' 
interval '3 months' 이런 식으로 기간을 더해주는 연산이 가능하다

 

 

시간 계산

SELECT time '05:00' + interval '3 hours' as new_time;
SELECT interval '1 second' * 2000 as interval_multiplied;

 

 

* 만든 서버의 postgre 우클릭 - Query Tool 로 새로운 쿼리 생성