5주차 화요일, 22일차 Today I Learned
SQL을 이용한 데이터 분석 (2)
: SELECT 배우기 - Redshift 설치, SQL 실습 환경, DDL/DML, SELECT
✏️ 학습 내용
1. Redshift 설치
AWS 콘솔을 통해 Redshift를 론치할 수 있다.
1) 우측 상단 '지역' 확인
2) 좌측 상단 'Services' 확인 or 검색창에서 'Redshift' 선택
3) 정보 입력 및 설정
4) Create cluster
클러스터 정보 :
1 x dc2.large instance (160GB)
Port number : 5439
Database name : dev
2. SQL 실습 환경
관계형 데이터베이스 예제 : 웹 서비스 사용자/세션 정보
- 사용자 ID table (user_session_channel)
- 세션 ID table (session_timestamp)
실습 전에 주의해야 할 점이 있다. 현업에서 깨끗한 데이터란 존재하지 않는다는 것! 그래서 항상 데이터를 믿을 수 있는지 의심을 해야 한다는 것이다. 실제 레코드를 몇 개 살펴보는 것 만한 것이 없는데, 이것은 단순 작업이 될 테지만 확실히 데이터를 확인할 수 있다. 이처럼 데이터 일을 한다면 항상 데이터의 품질을 의심하고 체크하는 버릇이 필요하다.
데이터 품질을 체크하는 방법은 중복된 레코드를 체크하기, 최근 데이터의 존재 여부 체크하기, PK 유일성이 지켜지는지 체크하기, 값이 비어있는 컬럼이 있는지 체크하기와 같은 일들을 수행하는 것이다. 일러한 체크는 코딩의 unit test 형태로 만들어 매번 쉽게 체크할 수 있다.
그리고 회사가 성장하고 어느 시점이 되면 너무나 많은 테이블이 존재하게 된다. 따라서 중요 테이블이 무엇이고 그것들의 메타 정보를 잘 관리하는 것이 중요해진다. 이 시점이 되면 Data Discovery 문제들이 생겨나므로, 애초에 규칙이 중요하다. 무슨 테이블에 원하고 신뢰할 수 있는 정보가 들어 있는지, 테이블에 대해 질문을 누구에게 해야 하는 지에 대한 의문이 생겨나기 나름이다. 이러한 문제들을 해결하기 위해서 다양한 오픈소소와 서비스들이 출현하기도 했다.
3. DDL/DML
SQL 기본 규칙은 아래와 같다.
- 다수의 SQL문을 실행한다면 세미콜론으로 분리해야 한다.
- 주석은 한 줄 짜리 주석 `--`와 여러 줄 짜리 주석 `/* */`이 있다.
- SQL 키워드는 나름의 포맷팅이 필요하다.
- 테이블 및 필드 이름의 명명 규칙을 정하는 것도 중요하다.
DDL은 테이블 구조 정의 언어이다. CREATE TABLE은 Primary key 속성을 지정할 수 있으나 데이터 웨어하우스에서는 지켜지지 않고 무시된다. CTAS (CREATE TABLE table_name AS SELECT) 를 이용할 수도 있다. DROP TABLE은 테이블을 삭제하는 명령어인데, 없는 테이블을 지우려고 하는 경우에 에러가 발생한다. DROP TABLE IF EXISTS을 이용하면 테이블이 존재하는 경우에만 실행되므로, 없는 테이블명을 작성해도 에러가 발생하지 않는다. ALTER TABLE은 ADD COLUMN으로 새로운 컬럼을 추가하거나, RENAME TO로 기존 컬럼이나 테이블 이름을 변경하거나, DROP COLUMN으로 기존 컬럼을 제거하는 기능을 수행한다.
DDL 구조
1. CREATE TABLE {테이블명} ( {필드 이름} {필드 타입}, .. ); : 테이블 생성
2. DROP TABLE {테이블명}; : 테이블 삭제
3. ALTER TABLE {테이블명} ~~ : 테이블 수정
DML은 테이블 데이터를 조작하는 언어이다. 레코드 질의 언어 SELECT를 기반으로 작동하는 구조이며, SELECT FROM으로 테이블에서 레코드와 필드를 읽어오고, WHERE 사용해서 레코드 선택 조건을 지정하며, GROUP BY 통해 정보를 그룹 레벨에서 뽑는데 사용하고, ORDER BY를 사용해 레코드 순서를 결정하기도 한다. 그리고 보통 다수의 테이블을 조인하여 사용한다.
레코드 수정 언어로는 테이블에 레코드를 추가하는데 사용하는 INSERT INTO, 테이블 레코드의 필드 값을 수정하는 UPDATE FROM, 테이블에서 레코드를 삭제하는 DELETE FROM가 있다.
DML 구조
1. SELECT - FROM, WHERE, GROUP BY, ORDER BY
2. INSERT INTO
3. UPDATE FROM
4. DELETE FROM
4. SELECT
SELECT 필드명1, 필드명2, ..
FROM 테이블명
WHERE 선택조건
GROUP BY 필드명1, ..
ORDER BY 필드명 [ASC|DESC]
LIMIT N;
SELECT는 테이블에서 레코드들을 읽어오는 데 사용된다. GROUP BY와 ORDER BY에서는 필드명 대신 숫자를 입력할 수 있고, ORDER BY는 기본적으로 ASC 오름차순으로 설정되어 있으므로 내림차순을 원할 때에만 뒤에 DESC를 입력하면 된다. LIMIT의 N을 지정하면 원하는 행의 갯수를 지정하여 볼 수 있다. 또한, SELECT DISTINCT를 이용하면 행의 중복을 제거할 수 있고, COUNT를 이용하면 설정한 필드별로 카운트를 할 수 있다.
CASE WHEN 조건 1 THEN 값 1
WHEN 조건 2 THEN 값 2
ELSE 값 3
END 필드명
필드 값의 변환을 위해서는 CASE WHEN을 이용할 수도 있다.
NULL 이란 값이 존재하지 않음을 나타내는 상수로, 0 혹은 공백과는 다르다. 필드 지정 시 값이 없는 경우, 테이블 정의 시 디폴트 값으로 NULL을 지정할 수 있다. 어떤 필드의 값이 NULL인지 아닌지 비교하는 방법은 특수한 문법을 이용하는 것이다. {필드명} 이후에 IS NULL 혹은 IS NOT NULL 을 입력하여 판별한다.
추가로 NULL은 사칙연산에 사용되면 그 결과 또한 NULL이 된다. ORDER BY에서 순서는 오름차순일 경우 마지막에, 내림차순일 경우 처음에 위치하고, 이를 바꾸고 싶다면 NULLS FIRST 혹은 NULLS LAST를 이용하면 된다.
1) COUNT 함수
CREATE TABLE raw_data.count_test (value int);
INSERT INTO raw_data.count_test VALUES (NULL), (1), (1), (0), (0), (4), (3)%%sql
SELECT
COUNT(1) as count_1
COUNT(0) count_0
COUNT(NULL) count_null
COUNT(value) count_value
COUNT(DISTINCT value) count_distinct_value
FROM| count_1 | count_2 | count_null | count_value | count_distinct_value |
| 7 | 7 | 0 | 6 | 4 |
2) WHERE
-- IN
WHERE channel IN ('Google', 'Youtube') -- channel이 Google이거나 Youtube인 것
WHERE channel NOT IN ('Google', 'Youtube') -- channel이 Google이거나 Youtube이 아닌 것
-- LIKE and ILIKE (대소문자 구분 여부)
WHERE channel LIKE '%o%' -- channel 가운데에 o가 들어가는 것
WHERE channel NOT LIKE 'G%' -- channel 처음이 G로 시작하지 않는 것
-- BETWEEN
WHERE @$% BETWEEN A AND B -- A이상 B미만의 것
3) STRING 함수
SELECT
LEN(channel),
UPPER(channel),
LOWER(channel),
LEFT(channel, 4)
FROM raw_data.user_session_channel;| len | upper | lower | left |
| 7 | ORGANIC | organic | Orga |
| 6 | Goog | ||
| 8 | Face | ||
| : | : | : | : |
이 외에도 REPLACE(str, exp1, exp2), LPAD, RPAD, SUBSTRING 등이 있다.
4) Type casting
- DATE Conversion : CONVERT_TIMEZONE('America/Los_Angeles', ts), DATE_TRUNC, EXTRACT orDATE_PART, DATEDIFF, DATEADD, GETDATE 등
- TO_CHAR
- TO_TIMESTAMP
- 숫자 : {필드명}::float, cast({필드명} as float
💡 배운 점
AWS에서 Redshift를 어떻게 설치하는 지에 대해 배웠다.
SQL 실습을 위해 주의할 점에 대해 배웠다.
DDL, DML에 대해 상세하게 배웠다.
SELECT문에 대해 기초적인 내용을 배웠다.
그 외에 CASE WHEN, DISTINCT, COUNT, STRING 함수, 타입 변환에 대해서도 배웠다.
📝 남아있는 의문과 개선점
COUNT, STRING 함수, 타입 변환에 대해 더 상세히 공부해서 익히면 좋을 것 같다.
☁️ 소감
DDL, DML은 알고 있어서 크게 어렵진 않았지만, 실제 활용할 때에는 헷갈릴 수 있으니 주의해야 한다. 특히 COUNT가 중요한 문법이니 이에 대해 정확한 동작 원리를 더 공부해야 할 것 같다고 생각했다.
