SQL(7)(data types, date format, data wrangling, using string functions to clean data)

-

DATA TYPES









string - 최대 필드 길이가 1024자인 모든 문자

date/time - 년, 월, 일, 시, 분, 초 값을 YYYY-MM-DD hh : mm : ss로 저장

number - 최대 17개의 유효 자릿수 십진 정밀도를 가진 숫자

boolean - TRUE 또는 FALSE 값

열의 데이터 유형 변경

데이터 유형을 쿼리에서 언제든지 변경할 수 있다.

연습문제

테이블의 funding_total_usd 및 founded_at_clean열을 각각 다른 형식 지정 함수를 사용하여 문자열로 변환

1
2
3
SELECT CAST(funding_total_usd AS varchar) AS funding_total_usd_string,
       founded_at_clean::varchar AS founded_at_string
  FROM tutorial.crunchbase_companies_clean_date
cs









DATE FORMAT

1
2
3
4
SELECT permalink,
       founded_at
  FROM tutorial.crunchbase_companies_clean_date
 ORDER BY founded_at
cs










1
2
3
4
5
SELECT permalink,
       founded_at,
       founded_at_clean
  FROM tutorial.crunchbase_companies_clean_date
 ORDER BY founded_at_clean
cs

다음은 동일한 테이블의 예지만 정리된 날짜가 있는 필드가 있다. 정리된 날짜 필드는 실제로 문자열로 저장되지만 시간순으로 정렬된다.








1
2
3
4
5
6
7
8
9
SELECT companies.permalink,
       companies.founded_at_clean,
       acquisitions.acquired_at_cleaned,
       acquisitions.acquired_at_cleaned -
         companies.founded_at_clean::timestamp AS time_to_acquisition
  FROM tutorial.crunchbase_companies_clean_date companies
  JOIN tutorial.crunchbase_acquisitions_clean_date acquisitions
    ON acquisitions.company_permalink = companies.permalink
 WHERE founded_at_clean IS NOT NULL
cs








1
2
3
4
5
6
SELECT companies.permalink,
       companies.founded_at_clean,
       companies.founded_at_clean::timestamp +
         INTERVAL '1 week' AS plus_one_week
  FROM tutorial.crunchbase_companies_clean_date companies
 WHERE founded_at_clean IS NOT NULL
cs

위의 예에서 time_to_acquisition열이 다른 날짜가 아니라 간격임을 알 수 있다.

interval함수를 사용하여 간격을 도입할 수 있다.









1
2
3
4
5
SELECT companies.permalink,
       companies.founded_at_clean,
       NOW() - companies.founded_at_clean::timestamp AS founded_time_ago
  FROM tutorial.crunchbase_companies_clean_date companies
 WHERE founded_at_clean IS NOT NULL
cs

간격은 '10초' 또는 '5개월'과 같은 일반 용어를 사용하여 정의된다. 또한 date열과 열을 더하거나 빼면 위의 쿼리에서와 같이 다른 열이 생성된다.

now()함수를 사용하여 현재시간을 코드에 추가할 수 있다.









연습문제

설립 3, 5, 10년 이내에 인수한 회사 수를 세는 쿼리를 작성, 범주별로 그룹화하고 설립 날짜가 있는 행으로만 제한

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT companies.category_code,
       COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '3 years'
                       THEN 1 ELSE NULL END) AS acquired_3_yrs,
       COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '5 years'
                       THEN 1 ELSE NULL END) AS acquired_5_yrs,
       COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '10 years'
                       THEN 1 ELSE NULL END) AS acquired_10_yrs,
       COUNT(companies.category_code) AS total
  FROM tutorial.crunchbase_companies_clean_date companies
  JOIN tutorial.crunchbase_acquisitions_clean_date acquisitions
    ON acquisitions.company_permalink = companies.permalink
 WHERE founded_at_clean IS NOT NULL
 GROUP BY companies.category_code
 ORDER BY total DESC
cs








DATA WRANGLING

데이터 병합 또는 반자동 도구를 사용하여 데이터를 보다 편리하게 사용할 수 있도록 한 원래 데이터를 다른 형식으로 수동으로 변환하거나 매핑하는 프로세스다.

즉, 데이터를 보다 쉽게 작업 할 수 있는 형식으로 프로그래밍 방식으로 변환하는 프로세스다.

USING STRING FUNCTIONS TO CLEAN DATA

LEFT, RIGHT, LENGTH, TRIM

left는 문자열 왼쪽에서 특정 수의 문자를 가져와 별도의 문자열로 표시할 수 있다.

구문은 left(string, number of characters)이다.

right는 left랑 동일하지만 오른쪽에서 표시한다.

1
2
3
4
5
SELECT incidnt_num,
       date,
       LEFT(date, 10) AS cleaned_date,
       RIGHT(date, 17) AS cleaned_time
  FROM tutorial.sf_crime_incidents_2014_01
cs








length는 글자 수를 반환한다.

1
2
3
4
5
SELECT incidnt_num,
       date,
       LEFT(date, 10) AS cleaned_date,
       RIGHT(date, LENGTH(date) - 11) AS cleaned_time
  FROM tutorial.sf_crime_incidents_2014_01
cs








trim함수는 문자열의 시작과 끝에서 문자를 제거하는데 사용된다.

1
2
3
SELECT location,
       TRIM(both '()' FROM location)
  FROM tutorial.sf_crime_incidents_2014_01
cs

''에 포함된 모든 문자는 문자열의 시작, 끝 또는 양쪽에서 제거된다.








POSITION, STRPOS

position은 하위 문자열을 지정한 다음 해당 하위 문자열이 대상 문자열에 처음나타나는 문자번호와 동일한 숫자값을 반환한다. 대소문자 구분.

1
2
3
4
SELECT incidnt_num,
       descript,
       POSITION('A' IN descript) AS a_position
  FROM tutorial.sf_crime_incidents_2014_01
cs








strpos함수를 사용하여 동일한 결과를 얻을 수 있다. in을 ,로 바꾸고 문자열과 하위문자열의 순서를 전화하면 된다.

1
2
3
4
SELECT incidnt_num,
       descript,
       STRPOS(descript, 'A') AS a_position
  FROM tutorial.sf_crime_incidents_2014_01
cs








SUBSTR

문자열 중간에서 시작할 수 있다. substr(string, starting character position, of characters)

1
2
3
4
SELECT incidnt_num,
       date,
       SUBSTR(date, 4, 2) AS day
  FROM tutorial.sf_crime_incidents_2014_01
cs










연습문제

'location'필드를 위도와 경도에 대한 별도의 필드로 구분하는 쿼리

1
2
3
4
SELECT location,
       TRIM(leading '(' FROM LEFT(location, POSITION(',' IN location) - 1)) AS lattitude,
       TRIM(trailing ')' FROM RIGHT(location, LENGTH(location) - POSITION(',' IN location) ) ) AS longitude
  FROM tutorial.sf_crime_incidents_2014_01
cs









CONCAT

여러 열의 문자열을 함께 결합할 수 있다.

1
2
3
4
5
SELECT incidnt_num,
       day_of_week,
       LEFT(date, 10) AS cleaned_date,
       CONCAT(day_of_week, ', ', LEFT(date, 10)) AS day_and_date
  FROM tutorial.sf_crime_incidents_2014_01
cs










연습문제

lat및 lon필드를 연결하여 location필드와 동일한 필드를 형성(답은 소수 정밀도가 다름)

1
2
3
SELECT CONCAT('(', lat, ', ', lon, ')') AS concat_location,
       location
  FROM tutorial.sf_crime_incidents_2014_01
cs








또는 아래와 같이 (||)를 사용하여 동일한 연결을 수행할 수 있다.

1
2
3
4
5
SELECT incidnt_num,
       day_of_week,
       LEFT(date, 10) AS cleaned_date,
       day_of_week || ', ' || LEFT(date, 10) AS day_and_date
  FROM tutorial.sf_crime_incidents_2014_01
cs








연습문제

concat대신 ||를 사용하여 위의 연습문제와 같이 location필드 만들기

1
2
3
SELECT '(' || lat || ', ' || lon || ')' AS concat_location,
       location
  FROM tutorial.sf_crime_incidents_2014_01
cs








YYYY-MM-DD형식의 날짜 열을 만드는 쿼리

1
2
3
4
SELECT incidnt_num,
       date,
       SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) || '-' || SUBSTR(date, 4, 2) AS cleaned_date
  FROM tutorial.sf_crime_incidents_2014_01
cs










UPPER, LOWER

upper : 모든 문자열을 대문자로 표시

lower : 모든 문자열을 소문자로 표시

1
2
3
4
5
SELECT incidnt_num,
       address,
       UPPER(address) AS address_upper,
       LOWER(address) AS address_lower
  FROM tutorial.sf_crime_incidents_2014_01
cs








연습문제

'category'필드를 반환하지만 첫번째 문자는 대문자로 나머지는 소문자로 표시하는 쿼리

1
2
3
4
SELECT incidnt_num,
       category,
       UPPER(LEFT(category, 1)) || LOWER(RIGHT(category, LENGTH(category) - 1)) AS category_cleaned
  FROM tutorial.sf_crime_incidents_2014_01
cs











문자열을 날짜로 바꾸기

1
2
3
4
5
SELECT incidnt_num,
       date,
       (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) ||
        '-' || SUBSTR(date, 4, 2))::date AS cleaned_date
  FROM tutorial.sf_crime_incidents_2014_01
cs









연습문제

date, time열을 사용하여 정확한 타임 스탬프를 만드는 쿼리 작성. 정확히 1주일 후의 필드도 포함

1
2
3
4
5
6
7
SELECT incidnt_num,
       (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) ||
        '-' || SUBSTR(date, 4, 2) || ' ' || time || ':00')::timestamp AS timestamp,
       (SUBSTR(date, 7, 4) || '-' || LEFT(date, 2) ||
        '-' || SUBSTR(date, 4, 2) || ' ' || time || ':00')::timestamp
        + INTERVAL '1 week' AS timestamp_plus_interval
  FROM tutorial.sf_crime_incidents_2014_01
cs










날짜필드를 분해하려면 extract를 사용하여 조각을 하나씩 분리할 수 있다.

1
2
3
4
5
6
7
8
9
10
SELECT cleaned_date,
       EXTRACT('year'   FROM cleaned_date) AS year,
       EXTRACT('month'  FROM cleaned_date) AS month,
       EXTRACT('day'    FROM cleaned_date) AS day,
       EXTRACT('hour'   FROM cleaned_date) AS hour,
       EXTRACT('minute' FROM cleaned_date) AS minute,
       EXTRACT('second' FROM cleaned_date) AS second,
       EXTRACT('decade' FROM cleaned_date) AS decade,
       EXTRACT('dow'    FROM cleaned_date) AS day_of_week
  FROM tutorial.sf_crime_incidents_cleandate
cs








date_trunc함수를 사용하여 가장 가까운 측정 단위로 날짜를 반올림 할 수 있다. 

1
2
3
4
5
6
7
8
9
10
SELECT cleaned_date,
       DATE_TRUNC('year'   , cleaned_date) AS year,
       DATE_TRUNC('month'  , cleaned_date) AS month,
       DATE_TRUNC('week'   , cleaned_date) AS week,
       DATE_TRUNC('day'    , cleaned_date) AS day,
       DATE_TRUNC('hour'   , cleaned_date) AS hour,
       DATE_TRUNC('minute' , cleaned_date) AS minute,
       DATE_TRUNC('second' , cleaned_date) AS second,
       DATE_TRUNC('decade' , cleaned_date) AS decade
  FROM tutorial.sf_crime_incidents_cleandate
cs








연습문제

주별로 보고된 사고 수를 계산하는 쿼리 작성.

1
2
3
4
5
SELECT DATE_TRUNC('week', cleaned_date)::date AS week_beginning,
       COUNT(*) AS incidents
  FROM tutorial.sf_crime_incidents_cleandate
 GROUP BY week_beginning
 ORDER BY week_beginning
cs












오늘 날짜 또는 시간을 포함하려면 다음과 같이 from절 없이 실행할 수 있다.

1
2
3
4
5
6
SELECT CURRENT_DATE AS date,
       CURRENT_TIME AS time,
       CURRENT_TIMESTAMP AS timestamp,
       LOCALTIME AS localtime,
       LOCALTIMESTAMP AS localtimestamp,
       NOW() AS now
cs





at time zone를 사용하여 시간을 다른 시간대로 표시할 수 있다.

1
2
SELECT CURRENT_TIME AS time,
       CURRENT_TIME AT TIME ZONE 'PST' AS time_pst
cs





연습문제

신고된지 얼마나 오래되었는지를 보여주는 쿼리를 작성. 태평양 표준시(UTC-8)로 가정.

1
2
3
4
5
SELECT incidnt_num,
       cleaned_date,
       NOW() AT TIME ZONE 'PST' AS now,
       NOW() AT TIME ZONE 'PST' - cleaned_date AS time_ago 
  FROM tutorial.sf_crime_incidents_cleandate
cs








COALESCE

null값을 대체하는데 사용할 수 있다.

1
2
3
4
5
SELECT incidnt_num,
       descript,
       COALESCE(descript, 'No Description')
  FROM tutorial.sf_crime_incidents_cleandate
 ORDER BY descript DESC
cs









댓글

댓글 쓰기

이 블로그의 인기 게시물

SQL(9)(performance tuning queries, pivoting data)

-
이미지
PERFORMANCE TUNING QUERIES 쿼리를 개선할 수 있는 개선방법을 식별하는 방법 먼저 수행해야하는 계산 수와 쿼리 런타임에 영향을 주는 몇가지 사항 - 테이블 크기 : 쿼리가 수백만개의 행이 있는 하나 이상의 테이블에 도달하면 성능에 영향을 미칠 수 있다. - 조인 : 쿼리가 결과 집합의 행 수를 크게 늘리는 방식으로 두 테이블을 조인하면 쿼리가 느려질 수 있다. - 집계 : 결과를 생성하기 위해 여러 행을 결합하려면 단순히 해당 행을 검색하는 것보다 더 많은 계산이 필요하다. - 쿼리를 실행하는 다른 사용자 : 데이터베이스에서 동시에 실행되는 쿼리가 많을수록 데이터베이스가 주어진 시간에 더 많은 처리해야하며 모든것이 느리게 실행된다. - 데이터베이스 소프트웨어 및 최적화 : 제어 할 수 없는 부분이지만 사용중인 시스템을 알고있다면 범위 내에서 작업하면 쿼리를 보다 효율적으로 만들 수 있다. 테이블 크기 줄이기 1 2 3 4 SELECT   *    FROM  benn.sample_event_table   WHERE  event_date  > =   '2014-03-01'    AND event_date  <    '2014-04-01' cs 1 2 3 SELECT  COUNT( * )    FROM  benn.sample_event_table   LIMIT   100 cs 1 2 3 4 5 6 SELECT  COUNT( * )    FROM  (      SELECT   *        FROM  benn.sample_event_table       LIMIT ...
자세한 내용 보기

데이터 크롤링(3주차)

-
이미지
 데이터를 시각화하고 분석을 진행함에 앞서 필요한 데이터들을 수집가능한지 어떤 문제점이 있는지 알아보기 위해 테스트를 진행해 보았다. 먼저 네이버 금융에 들어가 IT서비스 분야를 선택하면 다음과 같은 화면이 나타난다. 이 곳에서 필요한 데이터를 수집해 보기로 했다. 일단 종목명부터 수집해 보았다. import requests from pprint import pprint from bs4 import BeautifulSoup 먼저 웹 크롤링을 하기 위해 필요한 패키지들을 불러왔다. html = requests.get('https://finance.naver.com/sise/sise_group_detail.nhn?type=upjong&no=154') # 웹 페이지 요청 soup = BeautifulSoup(html.text, 'html.parser') # 다루기 좋게 파싱작업진행 data2 = soup.find_all('div', {'class':'name_area'}) # 회사 이름들을 추출한다. len(data2) #66 66개의 모든 종목명을 추출했다. name=[] for i in range(len(data2)):     name.append(soup.find_all('div', {'class':'name_area'})[i].get_text()) print(name) 리스트로 만들어 좀 더 보기 쉽게 만들었다. 결과는 다음과 같다. name1=[] for i in range(len(name)):     name1.append(name[i].replace('*', '').replace(' ','')) print(name1) 종목명에 특수문자와 공백을 제거했다. 결과는 다음과 같다. 이제 종목명 옆에 있는 다른 값들을 추출해 볼것이다. 이 곳에서 두가지의 문제점을 발견한다. 1. na...
자세한 내용 보기

SQL(8)(writing subqueries, window functions)

-
이미지
WRITING SUBQUERIES 하위쿼리는 쿼리내의 여러 위치에서 사용할 수 있지만 from문으로 시작하는 것이 가장 쉽다. 1 2 3 4 5 6 7 SELECT  sub. *    FROM  (          SELECT   *            FROM  tutorial.sf_crime_incidents_2014_01           WHERE  day_of_week  =   'Friday'        ) sub   WHERE  sub.resolution  =   'NONE' cs 1 2 3 SELECT   *    FROM  tutorial.sf_crime_incidents_2014_01   WHERE  day_of_week  =   'Friday' cs 연습문제 모든 Warrant Arrests를 선택하는 쿼리를 작성하고 해결되지 않은 사건만 표시하는 외부쿼리로 래핑 1 2 3 4 5 6 7 SELECT  sub. *    FROM  (          SELECT   *            FROM  tutorial.sf_crime_incidents_2014_01        ...
자세한 내용 보기