[5주차] SQL을 이용한 데이터 분석(2)

데이터엔지니어링

heecup 2024. 4. 23. 17:06

🙂 Redshift

사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
세션 ID: 세션마다 부여되는 ID
- 세션: 사용자의 방문을 논리적인 단위로 나눈 것
  - 사용자가 외부 링크를 타고 오거나 직접 방문할 경우 세션 생성
  - 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
- 하나의 사용자가 여러 세션을 가질 수 있음
- 보통 세션의 경우 세션을 만들어낸 접점을 "채널"이란 이름으로 기록
- 세션이 생긴 시간도 기록
위 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능.
- 마케팅 관련, 사용자 트래픽 관련
- DAU, WAU, MAU 등의 일주월별 Active User 차트
- Marketing Channel Attribution 분석
  - 어느 채널에 광고를 하는 것이 가장 효과적인가?

CREATE TABLE
- Primary key 속성을 지정할 수 있으나 무시됨
  - Primary key uniqueness
    - Big Data DW에서는 지켜지지 않음 (Redshift, Snowflake, BigQuery)
    - 대량의 레코드를 계속 검토하게 되면 성능이 나지 않기 때문
- CTAS: CREATE TABLE table_name AS SELECT
  - vs. CREATE TABLE and then INSERT

CREATE TABLE raw_data.user_session_channel (
    userid int,
    sessionid varchar(32) primary key,
    channel varchar(32)
);

DROP TABLE
- DROP TABLE table_name;
  - 없는 테이블을 지우려고 하는 경우 에러
- DROP TABLE IF EXISTS table_name;
- vs. DELETE FROM
  - DELETE FROM 은 조건에 맞는 레코드를 지움. (테이블은 존재)
ALTER TABLE
- 새로운 컬럼 추가
  - ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;
- 기존 컬럼 이름 변경
  - ALTER TABLE 테이블이름 RENAME 현재필드이름 TO 새필드이름;
- 기존 컬럼 제거
  - ALTER TABLE 테이블이름 DROP COLUMN 필드이름;
- 테이블 이름 변경
  - ALTER TABLE 현재테이블이름 RENAME TO 새테이블이름;

레코드 수정 언어:
- INSERT INTO: 테이블에 레코드를 추가하는데 사용
- UPDATE FROM: 테이블 레코드 수정
- DELETE FROM: 테이블에서 레코드 삭제 (트랜젝션에 사용가능)
  - VS. TRUNCATE: (트랜젝션에 사용 불가)

테이블에서 레코드를 읽어오는 데 사용

WHERE를 사용해 조건을 만족하는 레코드

SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름 [ASC|DESC] 
LIMIT N;

NULL값
- NULL 값은 오름차순일 경우 (ASC), 마지막에 위치
- NULL 값은 내림차순일 경우 (DESC), 처음에 위치
- 이를 바꾸고 싶다면 NULLS FIRST 혹은 NULLS LAST 사용

코드로 unit test로 구현해서 쉽게 체크하는 것을 추천