[SQL] Redshift 중심의 SQL(기초문법1)

SQL in Redshift

  - Redshift에서의 SQL은 다르 SQL과 다르지 않다

  - 특히, Posgresql과는 거의 대동소이할 만큼 유사하다

 

1. SQL 기본

  - 다수의 SQL문을 실행한다면 세미콜론으로 분리 필요 (SQL문1;SQL문2;SQL문3;)

  - 주석처리

    ① --: 한주짜리 주석(C, Java에서 //와 같다)

    ② /* -- */: 여러 줄에 걸쳐 사용 가능한 주석

  - SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요하며 특히, 팀 프로젝트라면 팀에서 사용하는

    공통 포맷이 필요하다

  - 테이블/필드이름의 명명규칭을 정하는 것이 중요하다

    ① 단수형 vs 복수형: User vs Users

    ② _ vs CamelCaing: user_session_channel vs UserSessionChannel

  ※ 공통 포맷이나 명명 규칙과 같은 것은 처음부터 잘 잡아두는 것이 좋다

 

2. SQL DDL - 테이블 구조 정의 언어

1) CREATE TABLE

  - Primary Key 속성을 지정할 수 있으나 Uniqueness는 보장하지 않는다(→ 속도 때문)

  - CTAS: CREATE TABEL table_name AS SELECT

• 테이블을 생성하면서 다른 테이블의 데이터를 선택하여 복사하는 명령어
• 새로운 테이블을 만들면서 기존의 테이블 데이터를 복사하여 저장할 수 있다

※ CTAS vs CTTI(Create Table Then Insert)

  - CTTI와 CTAS는 기존 테이블에서 데이터를 선택하여 새로운 테이블을 만드는 것으로 비슷한 개념이지만,

    약간의 차이가 있다

   ① CTAS

      - 기존 테이블에서 데이터를 선택하여 새로운 테이블을 만드는 데 사용

      - 보다 빠르고 효율적인 방식이지만, 새로운 테이블이 원본 테이블과 동일한 구조를 가져야한다

      - 기존 데이터를 모두 선택해야 한다는 제약이 있다

  ② CTTI

      - 기존 테이블에서 데이터를 선택하여 새로운 테이블을 만드는 것

      - 새로운 테이블은 기존 테이블의 열 구조와 데이터 유형 등을 상속받는다

      - 이터를 삽입할 필요 없이, SELECT 구문에서 지정한 데이터를 자동으로 새로운 테이블에 저장함

 

2) DROP TABLE

DROP TABLE table_name;

  - 없는 테이블을 지우려고 하는 경우 에러가 발생하므로 다음과 같이 우회한다

DROP TABLE IF EXISTS table_name;

  - vs DELETE FROM

    → DELETE FROM은 조건에 맞는 레코드들을 지우는 것이며, 테이블 자체를 지우는 것은 아니다

 

3) ALTER TABLE

  - 이미 생성된 테이블의 구조를 변경하는 데 사용되는 명령어

  - 새로운 컬럼 추가

ALTER TABLE table_name ADD COLUMN field_name field_type;

  - 기존 컬럼 이름변경:

 ALTER TABLE table_name RENAME current_field_name to new_field_name;

  - 기존 컬럼 제거:

ALTER TABLE table_name DROP COLUMN field_name;

  - 테이블 이름변경:

ALTER TABLE current_table_name RENAME to new_table_name;

 

 

3. SQL DML - 테이블 구조 정의 언어

1) SELECT

  - SELECT FROM: 테이블에서 레코드와 필드를 읽어오는데 사용

  - WHERE를 사용해서 레코드 선택 조건을 지정

  - GROUP BY를 통해 정보를 그룹 레벨에서 뽑는데 사용하기도 함

    → DAU, WAU, MAU 계산은 GROUP BY를 필요로 함

  - ORDER BY를 사용해서 레코드 순서를 결정하기도 함

  - 보통 다수의 테이블의 조인해서 사용하기도 함

SELECT DISTINCT channel			-- 유일한 채널 이름을 알고 싶은 경우
FROM raw_data.user_session_channel;

SELECT channel, COUNT(1)		-- 채널별 카운트를 하고 싶은 경우. COUNT 함수 사용
FROM raw_data.user_session_channel
GROUP BY 1;

SELECT COUNT(1)				-- 테이블의 모든 레코드 수 카운트
FROM raw_data.user_session_channel;

SELECT COUNT(1)				-- channel 이름이 Facebook경우만 고려해서 레코드 수 카운트
FROM raw_data.user_session_channel
WHERE channel = 'Facebook';

 

2) CASE WHEN

  - CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일 때 값 END 필드이름

    → 필드 값의 변환을 위해 사용 가능

  - 여러 조건을 사용하여 변환하는 것도 가능

SELECT
  channel,
  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
LIMIT 100;

※NULL??

  - 값이 존재하지 않음을 나타내는 상수. NULL != 0, NULL != ""

  - 필드 지정시 값이 없는 경우 NULL로 지정 가능

    → 테이블 정의시 default 값으로도 지정 가능

  - 어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함

    → field1 is NULL or field1 is not NULL

  - NULL이 사칙연산에 사용되면 결과는 → 전 "NULL"

3) COUNT

  - COUNT함수는 인자가 NULL이면 0, NULL이 아니면 1씩 더함

4) WHERE

  - 데이터를 선택하고 검색하는 데 사용되는 조건절

  - SELECT, UPDATE, DELETE 문에서 모두 사용됨

  ① IN

    - WHERE 절에서 여러 값 중 하나에 해당하는 데이터를 검색하는 데 사용

  ② LIKE and  ILIKE

• LIKE

SELECT *
FROM products
WHERE product_name LIKE 'Apple%';

    - WHERE 절에서 특정 패턴을 가진 문자열을 검색하는 데 사용

    - 패턴은 "%N", "N%", "%N%"로 정의할 수 있

• ILIKE

SELECT *
FROM products
WHERE product_name ILIKE 'apple%';

    - ILIKE 연산자는 LIKE 연산자와 유사하지만, 대소문자를 구분하지 않음 → 대소문자에 관계없이 패턴을 검색

③ BETWEEN

SELECT *
FROM products
WHERE price BETWEEN 10 AND 20;

  - WHERE 절에서 지정된 범위 내의 값을 검색하는 데 사용

  - BETWEEN 연산자는 시작 값과 끝 값 사이의 값을 모두 선택

※ 위의 오퍼레이터들은 CASE WHEN 사이에서도 사용 가능

 

5) ORDER BY

  - SELECT 문에서 결과 데이터를 정렬하는 데 사용

  - 내림차순 : DESC / 오름차순 : ASC

  - 컬럼을 겹쳐서 사용할 수 있다

SELECT *
FROM products
ORDER BY product_name ASC, price DESC;

 

6) 타입 변환

  - DATE Conversion

  ① 타임존 관련 변환

    - CONVERT_TIMEZONE('America/Los_Angeles', ts)

    - select pg_timezone_names() → PostgreSQL 데이터베이스에서 사용 가능한 모든 타임존(timezone) 목록을 반환

  ② DATE, TRUNCATE

    - TRUNCATE는 일부 데이터 유형에서만 작동

    - 날짜와 시간 값을 반올림하거나 버리는 데 사용되는 함수

    - 날짜 및 시간 값을 가진 열의 데이터 유형이 DATE나 TIMESTAMP가 아닌 경우, TRUNCATE가 작동하지 않습니다.

SELECT TRUNCATE(sale_date, 'MONTH') FROM sales;
-- 위의 쿼리는 sales 테이블에서 sale_date 열의 월(month) 부분만 남겨두고 나머지를 버리는 작업을 수행

  ③ DATE_TRUNC

    - 특정 기간 단위(년, 월, 일, 시간 등)에서 날짜를 잘라내는 함수

    - 예를 들어, DATE_TRUNC('month', date_column)은 날짜 열(date_column)에서 월(month) 값만 추출

  ④ EXTRACT or DATE_PART

    - 날짜, 시간에서 특정 부분의 값을 추출

  ⑤ DATEDIFF: 두 날짜 값 간의 차이를 계산하는 함수

  ⑥ DATEADD: 특정 날짜 값에 일정한 기간을 더하거나 빼는 함수

  - TO_CHAR: char타입으로 형변환

  - TO_TIMESTAMP: timestamp타입('YYYY-MM-DD HH24:MI:SS')으로 형변환

 

7) 레코드 수정 언어

  - INSERT INTO: 테이블에 레코드를 추가하는데 사용

  - UPDATE FROM: 테이블 레코드의 필드 값 수정

  - DELETE FROM: 테이블에서 레코드를 삭제

 

※ DELETE vs TRUNCATE

  - DELETE 와 TRUNCATE 모두 테이블에서 데이터를 삭제하는 명령어로 비슷한 개념이지만, 동작 방식과 결과

    에서 약간의 차이가 있다

    ① DELETE 

      - 선택한 데이터만 삭제하는 명령어

      - DELETE 문은 WHERE 절을 사용하여 삭제할 행을 지정할 수 있다

      - 테이블의 데이터는 삭제되지만, 테이블의 구조나 고유 식별자는 유지된다

    ② TRUNCATE

      - 테이블에서 모든 데이터를 한 번에 삭제하는 명령어이며, 이 때, 테이블의 구조는 그대로 유지된

      - 테이블의 데이터를 모두 삭제하고 테이블의 고유 식별자(예: 시퀀스 번호)도 초기화된다

      - 초기화 작업은 테이블의 자동 증가 열을 다시 1부터 시작하도록 설정하는 데 유용

      - TRUNCATE는 DELETE보다 더 빠르지만, 복구할 수 없다