데.분.레 - 복습 2

Amazon Redshift의 무료 2개월 체험버전이 있어서 사용중이다.
원래는 PostgreSQL을 사용하려고 했지만, 알 수 없는 오류로 일단 보류하고 있다.

문자열 연결하기

다음과 같은 테이블에서 문자열을 연결하고자 한다

SELECT user_id
     , CONCAT(pref_name, city_name) AS pref_city
     , pref_name || city_name AS pref_city
  FROM mst_user_location

두번째, 세번째 행이 모두 동일한 값을 도출한다.
CONCAT : 여러 문자열을 하나로 합쳐주는 역할을 한다. PostgreSQL 과 Redshift에서는 ||를 이용해서도 동일한 값을 추출할 수 있다.

여러 개의 값 비교하기

하나의 레코드에 포함된 여러 개의 값을 비교하는 방법을 소개해보자.

SELECT year
     , q1
     , q2
     , CASE WHEN q1 < q2 THEN '+'
            WHEN q1 = q2 THEN ' '
            ELSE '-'
            END AS judge_q1_q2
     , q2-q1 AS diff_q2_q1
     , SIGN(q2-q1) AS sign_q2_q1
  FROM quarterly_sales
  ORDER BY 1

CASE 함수를 이용해, 1분기/2분기의 대소비교를 하였다.
SIGN 함수를 이용하면 CASE 식보다 간단하게 값의 증감 판정을 할 수 있다. SIGN 함수는 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴한다.

SELECT year
     , GREATEST(q1, q2, q3, q4) as greatest_sales
     , LEAST(q1, q2, q3, q4) as least_sales
  FROM quarterly_sales
  ORDER BY 1

GREATEST, LEAST 함수는 표준 SQL에 포함되지 않지만, 대부분의 SQL 쿼리 엔진에서 구현하고 있다.

SELECT year
     , (COALESCE(q1, 0) + COALESCE(q2, 0) + COALESCE(q3, 0) + COALESCE(q4, 0))
       / (SIGN(COALESCE(q1, 0)) + SIGN(COALESCE(q2, 0)) + SIGN(COALESCE(q3, 0)) + SIGN(COALESCE(q4, 0))) AS average
  FROM quarterly_sales
  ORDER BY year

각 연도의 평균 분기매출을 구하는 쿼리이다. COALESCE는 Null 값을 어떤 값으로 리턴할지 지정하는 함수이다.

2개의 값 비율 계산하기

SELECT dt
     , ad_id
     , CAST(clicks AS double precision)/impressions AS ctr
     , 100.0 * clicks / impressions AS ctr_as_percent
  FROM advertising_stats
  WHERE dt = '2017-04-01'
  ORDER BY dt, ad_id

CTR(Click Through Rate)를 구하는 쿼리이다.
CAST 함수를 이용해, 클릭수를 '더블 프리시전(double precision)' 자료형으로 변환하고 계산해야 결과도 소수점 둘째자리까지 나오게 된다.

SELECT dt
     , ad_id
     , CASE WHEN impressions >0 THEN 100.0 * clicks / impressions
            END AS ctr_as_percent_by_case
  FROM advertising_stats
  ORDER BY dt, ad_id

0으로 나누는 것을 방지하기 위해 CASE 함수를 사용하였다.