SQL_Analysis_Day 2-2

data_hamster·2023년 5월 12일

학습내용
구글 Colab 사용

작업을 시작하기 전에 직접 레코드 눈으로 확인. 중복된 레코드들을 눈으로 보게 되면 이걸 실제로 쓸 수 있을지 의심의 감.
노가다로 중복된 레코드 체크 1000~10000개
최근 데이터 존재여부 체크 -> 지난 1년동안의 매출기록이 없다. 이러면 잘못된 것. timestamp로 확인해봐야 함.
Primary key uniqueness. 일전에 세션 ID가 PK였음. 이 세션 ID는 유일한 필드값을 가져야 함.
값이 비어있는 컬럼들이 있는지 체크 -> 데이터 익스플로레이션 통해 판다스에서 함수를 통해 체크 가능함. ex. 매출금액 필드에 20%가 비어있다. -> 어떤 문제가 있다는 뜻.
unit test 형태로 만들어 체크. 이 데이터를 가지고 계속 업데이트를 한다고 하면, 이 데이터 품질이 항상 유지된다는 보장은 없음. 이에 위의 조건들을 만족해서 데이터가 업데이트 되었는지 확인하는 pipe line을 만들어야 함. 일종의 자동화

데이터 기반 모델링을 하다보면 데이터가 업데이트 될수록 바뀌기 때문에, 처음 모델의 퍼포먼스는 좋게 시작되나 시간이 지나면서 데이터를 사용하는 패턴이 바뀌기 때문에 성능이 조금씩 내려감. ex. 검색 모델 퍼포먼스는 업데이트 안해주면 최대 30%까지 성능저하. -> 이것도 자동화 해주면 좋음.

데이터 과학자도 이래서 코딩을 할 줄 알면 좋음 -> 이렇다보면 남의 도움을 받아야 됨. 일이 분담이 되더라도 처음 속도를 낼려면 코딩을 할 줄 알아야함.
적어도 데이터에 관한 sql, pandas, spark를 사용해 대용량 데이터 조작, unittest 이해 후 데이터 퀄리티 체크.

팀원이 많아질수록 너무 많은 테이블들이 존재하기 때문에 예를들어 매출 테이블을 어떻게 찾아야하는지, 등 처음부터 규칙을 잘 만들어가는게 중요함.

raw data
analytics
adhoc
사람들을 analytics로 분석하게함. raw data는 데이터 관련직무만, 등으로 규칙을 생성했었음.
또 저 세 분류 안에 테이블이 많이 생성될 수도 있음.
Data Discovery 문제가 발생

다양한 오픈소스들이 이를 해결해줄 수 있음. 어떤 데이터 디스커버리 문제를 풀어주는 서비스. 각 테이블별로 그 테이블이 몇번이나 사용되었고, 최근에 사용한 사람이 누구인지 등 테이블에 대한 정보를 달아줌. -> 누구에게 물어봐야하는지 알게 됨.

데이터를 만들 때도 이미 비슷한 테이블이 있는지 확인
없을 경우, 데이터를 새로 생성한다. 이때 어떤 규칙이 있는지 확인한다.
생성 후 다큐먼트를 만들어 다른사람들이 접근할 수 있게 한다.

이를 가지고 실습을 해본다.

SELECT: 열을 선택, COUNT함수로 몇개 인지, DISTINCT로 중복 제거.
FROM 테이블이름
WHERE 조건, LIKE로 문자열 포함, IN으로 원하는 범주, SELECT로 다른 테이블에서 읽어온 값을 조건으로 제시 가능.
GROUP BY, HAVING과 함께 사용, 보통 SUM(필드이름) 등 생성한 필드에 제한을 줄때
ORDER BY 필드이름 뒤에 ASC(기본), DESC
LIMIT 3, 2 3번 인덱스(4번째) 2개

SELECT *
FROM raw_data.user_session_channel;

스키마.테이블 이름 으로 접근함.
notation(표기법)을 통함.

필드를 지정해 줄수도 있다. 또한 순서를 바꿔줄 수도 있다.

SELECT DISTINCT channel
FROM raw_data.user_session_channel;

SELECT channel, COUNT(1)
FROM raw_data.user_session_channel
GROUP BY 1;

COUNT(1)은 행을 셀 때 1개로 계산하여 세줌. 보편적으로 많이 사용. GROUP BY 1은 선택된 첫번째 열을 기준으로 정렬. 즉 GROUP BY channel과 동일.
채널을 기준으로 그룹을 형성. -> 각 그룹의 갯수를 COUNT로 출력함.
COUNT(*)도 동일

SELECT COUNT(1)
FROM raw_data.user_session_channel;

GROUP BY 없이 COUNT를 쓰면 레코드 1개가 리턴되고 -> 전체 행의 수

SELECT COUNT(1)
FROM raw_data.user_session_channel
WHERE channel = 'Facebook';

channel 이름이 Facebook경우만 고려해서 레코드 수 카운트.

CASE
WHEN 조건1 THEN 값1
WHEN 조건2 THEN 값2
ELSE 값3
END 필드이름

SELECT CASE
WHEN channel in ('Facebook', 'Instagram') THEN 'Social-Media'
WHEN channel in ('Google', 'Naver') THEN 'Search-Engine'
ELSE 'Something-Else'
END channel_type
FROM raw_data.user_session_channel;

새로운 필드를 만들어냄 END 뒤에 새로운 필드의 이름
하나의 필드를 조건을 사용해 또다른 필드. 또는 여러 조건을 사용해 필드값 생성 가능.

값이 존재하지 않음. 0 ""과 다름 -> 존재하는 값이 지정.
NULL 은 무에 해당.
필드가 NULL인지 확인하는 방법은 아래와 같은 특수한 방법으로 확인.
field1 is NULL 또는 field1 is not NULL
NULL을 사칙연산에 사용하면

SELECT 0 + NULL,
0 - NULL,
0 * NULL,
0 / NULL

이 모든 결과는 NULL이 됨. 사칙연산에 들어가는 순간 결과가 이상하게 바뀌어버림.

생각보다 제대로 이해하고 있는 사람들이 많지가 않음.
value필드의 타입은 INT.
SELECT COUNT(1) FROM count_test -> 7

SELECT COUNT(0) FROM count_test -> 7 NULL만 아니면 1씩 세감

SELECT COUNT(NULL) FROM count_test -> 0 count함수라는건 주어진 인자의 값이 NULL이면 0, 0이 아닌게 들어오면 1씩 더해감.

SELECT COUNT(value) FROM count_test -> 6 value의 값을 보면 NULL이 아닌 값들만 카운트함

SELECT COUNT(DISTINCT value) FROM count_test -> 4
DISTINCT 후 COUNT가 실행.
DISTINCT를 하면 5개로 줄어듬. 유니크한 값만 남음. NULL, 1, 0, 4, 3
COUNT 시 NULL 빼고 셈.
필드를 직접 지정하게되면 NULL을 빼고 세는것을 알 수 있다.

IN
WHERE channel in ('Google','Youtube')

LIKE(대소문자 구분) and ILIKE(대소문자 미구분)
WHERE channel LIKE 'G%'
WHERE channel ILIKE '%o%'

BETWEEN
보통 날짜 사이의 값을 추출할 때 사용

LEFT(str, N) 첫번째가 스트링 필드, 두번째 인자로 주어진 숫자만큼 추출
SELECT LEFT(channel, 4) 채널이라는 필드에서 처음 4자리까지 추출한 필드
REPLACE(str, exp1, exp2) 스트링 필드에서, 첫번째로 주어진 인자값을 두번째로 주어진 인자값으로 교체 -> 그 값이 리턴됨
UPPER(str) 주어진 문자열 필드를 대문자로 리턴
LOWER(str) 주어진 문자열 필드를 소문자로 리턴
LEN(str) 주어진 문자열 필드의 각 레코드마다 길이를 리턴
LPAD, RPAD 문자의 왼쪽에다가 string을 붙여줌. RPAD는 오른쪾
SUBSTRING - 시작점을 부여해서 3번째부터 8개의 문자열을 리턴

필드이름, 숫자로 지정 가능 (SELECT문의 x 번째)
ASC가 기본
ORDER BY 1 DESC
여러개를 정렬할 수 있음
ORDER BY 1 DESC, 2, 3 만일 값이 없는 필드가 있을 경우(NULL 필드). 오름차순일 경우 NULL이 뒤로가고, 내림차순일 경우 NULL이 앞으로 옴.
이를 바꾸고 싶으면 NULLS FIRST, NULLS LAST를 사용해 기본동작을 바꿀 수 있음.

뒤에서 실습예쩡
시간이 서울시간 -> 미국 서부시간으로 CONVERT_TIMEZONE(내가 바꾸려는 지역의 타임존 이름,UTC 기준 타임스탬프)
select pg_timezone_names(); 이걸로 타임존 지역명 목록 확인

DATE, TRUCNCATE 연도 월 날짜 리턴
DATE TRUNC 특정 파트 추출
GET_CURRENT 현재시간 UTC로 리턴

TO_CHAR 시간을 문자열,
TO_TIMESTAMP 문자열 -> 날짜 시간타입으로 바꾸어 줌

잘 안쓰임. 숫자 계산할 때.
정수간 연산은 0으로 나옴
분자나 분모를 float로 캐스팅해야함.

필드이름::float
category::float
INT 타입 category필드를 float의 새로운 타입으로 변경

cast 함수 사용하면
cast(category as float)
동일한 기능을 한다.

구글 콜랩으로 실습 해본다. 파이썬 노트북을 sql 에디터처럼 쓸 수 있다.
파이썬 코딩과 섞어서 할 수 있다는 점에서 장점이 있다.
%load_ext sql

실행
SQLAlchemy 설치함.
red shift와 연결함. 이미 만들어져 있음.

aws 계정과 연결하려니 다음과 같은 오류 발생
ChatGPT 4로 물어보니 해당 라이브러리를 설치하라고 함.
pip install psycopg2-binary
설치 후 실행 -> 실패함
콜랩 주피터 노트북 환경에서 다시 설치해봄.
-> 실패함.
!pip install sqlalchemy==1.3.23
!pip install --upgrade ipython-sql
sqlalchemy 다운그레이드 시키고, ipython-sql을 최신버전으로 올림.
실패.

!pip install sqlalchemy==1.3.9
!pip install ipython-sql==0.3.9
버전 두개를 맞추고

%sql postgresql+psycopg2://guest:Guest1!*@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
postgresql://guest:Guest1!*@learnde.cduaw970ssvt.ap-northeast-2.redshift.amazonaws.com:5439/dev
명령어 실행 -> 둘다 성공함.

결론. ipython-sql의 버전 호환 문제로 보임.
-> 주피터 노트북 환경에서 라이브러리를 설치해야함.

%sql: %sql은 주피터 노트북에서 SQL 쿼리를 실행하기 위한 매직 명령어입니다. 이를 사용하면 SQL 쿼리를 직접 작성하고 데이터베이스와 상호작용할 수 있습니다. %sql을 사용하면 SQL 문법 강조 표시와 실행 결과를 테이블 형식으로 출력하는 등 SQL 작업을 더 편리하게 수행할 수 있습니다.
%%sql: %%sql은 셀(Cell) 단위로 SQL 쿼리를 실행하는 매직 명령어입니다. 이를 사용하면 셀에 작성된 모든 SQL 쿼리를 한 번에 실행할 수 있습니다. %%sql을 사용하면 여러 개의 SQL 문을 순차적으로 실행하거나 한 번에 실행 결과를 비교하는 등 다양한 작업을 수행할 수 있습니다.
!pip: !pip는 주피터 노트북에서 외부 패키지를 설치하기 위한 명령어입니다. 파이썬에서는 패키지 관리를 위해 pip를 사용하는데, !pip를 사용하면 주피터 노트북 셀에서 직접 외부 패키지를 설치할 수 있습니다. 이를 통해 데이터 분석이나 머신러닝 등 다양한 작업에 필요한 패키지를 쉽게 설치하고 사용할 수 있습니다.

10만개 정도의 테이블을 불러온다.

혹은 SELECT에 필드 이름을 명명할 수 있다.
%%sql 후 한 줄 비우고 sql 문장을 쓰면 해당 셀의 전체 sql이 실행된다.

LIMIT 10으로 값을 줄인 결과, 빨리 뜨는 것을 볼 수 있다.

데이터 퀄리티 체크는 다음 수업때 보여줄 예정.

channel_type 필드 새로 생성.

필드 이름이 생성되는 걸 확인.
DISTINCT는 필드 1개에 대해서만 중복 제거를 허용, 위의 출력될 select 문은 모두 channel을 기준으로 새롭게 생성한 필드. channel의 고유한 값만 사용하게 할 수 있다.

사전에 테이블과 레코드를 정의하였다.