Contents
데이터를 분석하기 위해서는 Database에서 데이터를 불러오고 이를 활용하여야 한다. DB를 사용하기 전에는 csv, xlsx 등의 파일에서 바로 불러와서 사용하곤 했으나 현업에서 일을하거나 협업을 하게 되는 경우에는 DB를 사용하는 일이 잦다. DBMS는 MySQL, PostgreSQL, MSSQL 등 다양한 SQL이 존재하지만, 본 글에서는 공통적으로 사용하는 문법을 위주로 다룬다.
SELECT
전체 데이터 조회
SELECT 문법은 데이터를 조회할 때 사용하는 SQL 문법이다. 가장 기본적인 것부터 알아보자. SELECT FROM은 아주 기본적인 문법이라고 볼 수 있다. 어떠한 열에서 원하는 값을 가지고 올 때 주로 사용한다.
SELECT * FROM `database`.table_name;* 는 모든 열을 의미한다. 만약에 `name`이라는 열에서 데이터를 불러오고 싶다면 * 대신 `name`을 입력하면 된다. 그 후 `database`를 지정하고, `database` 중 우리가 찾고자 하는 `table`을 지정하면 끝이다. SQL은 기본적으로 대소문자 구분하지 않고 입력해도 수행은 되지만, 내부적으로는 대문자와 소문자는 서로 다른 SQL로 취급한다. 일반적으로 키워드 부분을 대문자로 하고, 다른 컬럼 이름이나 조건 등은 소문자로 사용한다.
원하는 컬럼만 조회
위와 같이 전체 데이터를 조회하는 경우도 있지만, 원하는 컬럼에 대해서만 조회하는 경우도 생길 수 있다. 이때는 아래와 같은 형태로 작성하면 원하는 컬럼만 조회하는 것이 가능하다.
SELECT col1, col2 FROM 'database';원하는 컬럼만 SELECT 뒷 부분에 작성하고 끝난 후 FROM 을 작성하면 마무리된다.
WHERE
SELECT FROM 을 사용하면서 같이 사용되는 함수 중 WHERE이 있다. 이는 테이블 내에서 우리가 원하는 조건의 값만 불러오고 싶을 때 사용한다. 예를 들어, 'name'이라는 열에서 이름이 'iyou'인 행를 가지고 오고 싶다면 아래 같이 입력하면 된다.
SELECT name FROM `database`.table WHERE name = 'iyou';
CASE
CASE 구문은 CASE 뒤에 'WHEN <조건식> THEN <조건을 만족할 때의 값>'을 나열하고, 마지막으로 END를 사용해 끝내는 형태로 구성되어 있다. 만약 조건식에 해당하는 값이 없는 경우에는 NULL을 반환하게 되지만, 'ELSE <값>' 형태를 사용해 NULL 값이 반환되지 않도록 할 수도 있다.
SELECT
user_id
, CASE
WHEN register_name = 1 THEN 'desktop'
WHEN register_name = 2 THEN 'smartphone'
WHEN register_name = 3 THEN 'application'
END AS device_name
FROM mst_users
CAST
데이터 처리를 하다보면 지정한 날짜 혹은 시간에 대한 데이터를 추출하는 일이 생긴다. 그러나 데이터를 저장할 때 시간이 아니라 문자열로 지정한 경우가 있을 수 있다. 이때 사용하는 것이 바로 CAST 함수이다.
-- 문자열을 날짜/타임스탬프로 변환하기
SELECT
CAST('2016-01-30' AS date) AS dt
, CAST('2016-01-30 12:00:00' AS timestamp) AS stamp
;
EXTRACT
CAST 함수를 통해서 문자열을 시간 혹은 타임스탬프로 변환한 후 EXTRACT 함수를 사용하여 특정 년과 월 등의 값을 추출할 수 있다.
SELECT
stamp
, EXTRACT(YEAR FROM stamp) AS year
, EXTRACT(MONTH FROM stamp) AS month
, EXTRACT(DAY FROM stamp) AS day
, EXTRACT(HOUR FROM stamp) AS hour
FROM
(SELECT CAST('2016-01-30 12:00:00' AS timestamp) AS stamp) AS t
;
COALESCE
문자열 또는 숫자열을 다룰 때 NULL 값이 들어가 있는 경우 분석이 제대로 되지 않을 수 있다. 그렇기 때문에 NULL 값을 처리해주는 작업을 수행하여야 한다. 이때 사용하는 것이 바로 COALESCE 함수이다.
SELECT
purchase_id
, amount
, coupon
, amount - coupon AS discount_amount1
, amount - COALESCE(coupon, 0) AS discount_amount2
FROM
purchase_log_with_coupon
;위 수식에서 만약 coupon이 NULL인 경우 discount_amount1은 NULL 값을 반환하지만 discount_amount2는 coupon 중에서 NULL 값을 0으로 변환한 후 처리하기 때문에 NULL 값이 반환되지 않는다.
CONCAT
주소와 같은 데이터를 다룰 때 시군구가 서로 다른 column에 위치하는 경우 이를 합쳐서 사용해야 될 수 있다. 이때 문자열을 연결하는 데 사용되는 함수가 바로 CONCAT 함수다.
SELECT
user_id
, CONCAT(pref_name, city_name) AS pref_city
FROM
mst_user_location
;
-- PostgreSQL의 경우 || 연산자를 사용해도 된다.
SELECT
user_id
, pref_name || city_name AS pref_city
FROM
mst_user_location
;
NULLIF
0을 값을 처리할 때 사용하는 함수 중 하나다.
SELECT
dt
, ad_id
, 100.0 * clicks / NULLIF(impressions, 0) AS ctr_as_percent_by_null
FROM
advertising_stats
ORDER BY
dt, ad_id
;NULLIF(impressions, 0)은 impressions가 0인 경우 NULL을 반환한다. 분모가 0인 경우 연산이 불가능하기 때문에 0인 경우 NULL으로 반환하는 것이다.
Calculation
SIGN
연산을 할 때 간단하게 사용이 가능한 함수이다. SIGN 함수의 매개변수가 양수라면 1, 0이라면 0, 음수라면 -1을 리턴한다.
SELECT
year
, q1
, q2
, SIGN(q2-q1) AS sign_q2_q1
FROM
quartely_sales
ORDER BY
year
;
greatest, least
가장 큰 값을 찾는다거나 가장 작은 값을 찾는다거나 할 때 사용할 수 있는 함수다.
SELECT
year
-- Q1~Q4 최대 매출 구하기
, greatest(q1, q2, q3, q4) AS greatest_sales
-- Q1~Q4 최소 매출 구하기
, least(q1, q2, q3, q4) AS least_sales
FROM
quarterly_sales
ORDER BY
year
;
ABS
Absolute의 약자로 절대값을 의미하는 함수다.
SELECT
abs(x1 - x2) AS abs
, sqrt(power(x1-x2, 2)) AS rms
FROM location_1d
;sqrt와 power를 통해 동일한 값을 도출할 수 있다.
POINT
2차원 상의 거리를 구할 때는 유클리드 거리를 사용한다 이는 $\sqrt{(x_1 - x_2)^2 + (y_1 - y_2)^2}$를 통해 계산하는데, SQL에서 이를 작성하기 위해서는 위와 같은 방식으로 함수를 개별적으로 입력해주어야 한다. 이때 point 함수를 사용하면 쉽게 거리를 구할 수 있다.
SELECT
point(x1, y1) <-> point(x2, y2) AS dist
FROM location_2d
;
INTERVAL
SQL에서는 단순히 숫자에만 사칙연산을 수행할 수 있는 것이 아니라 날짜 개념에도 사칙연산이 가능하다. 예를 들어, 한 시간 후의 시간 혹은 한 달 전의 시간에 대해서 구하고 싶을 때 사용한다.
SELECT
user_id
, register_stamp::timestamp AS register_stamp
, register_stamp::timestamp + '1 hour'::interval AS atfer_1_hour
, register_stamp::timestamp - '30 minutes'::interval AS before_30_minutes
, register_stamp:::date AS regitser_date
, (regitser_stamp::date + '1 day'::interval)::date AS after_1_day
, (register_stamp::date - '1 month'::inverval)::date AS before_1_month
FROM mst_users_with_dates
;
AGE
age 함수를 통해 나이를 구할 수도 있다.
SELECT
user_id
, CURRENT_DATE AS today
, register_stamp::date AS register_date
, birth_date::date AS birth_date
, EXTARCT(YEAR FROM age(birth_date::date)) AS current_age
, EXTARCT(YEAR FROM age(register_stamp::date, birth_date::date)) AS register_age
FROM mst_users_with_dates
;
COUNT
COUNT 함수는 지정한 컬럼의 레코드 수를 리턴하는 함수다. 만약 COUNT 앞에 DISTINCT 구문이 온다면 이는 중복을 제거하고 수를 세어준다. 추가로 SUM, AVG, MAX, MIN 등의 함수도 존재한다.
SELECT
COUNT(*) AS total_count
, COUNT(DISTINCT user_id) AS user_count
, COUNT(DISTINCT product_id) AS product_count
, SUM(score) AS sum
, AVG(score) AS avg
, MAX(score) AS max
, MIN(score) AS min
FROM
review
;
GROUP BY
데이터 분석을 하다보면 그룹별로 혹은 특정 사용자를 대상으로 한 통계량을 보고 싶을 때가 있다. 이때 그룹별 혹은 사용자별로 지정한 그룹에 대해서 요약 통계를 보여주는 함수가 GROUP BY 함수다.
SELECT
user_id
, COUNT(*) AS total_count
, COUNT(DISTINCT product_id) AS product_count
, SUM(score) AS sum
, AVG(score) AS avg
, MAX(score) AS max
, MIN(score) AS min
FROM
review
GROUP BY
user_id
;
ORDER BY
ORDER BY 함수는 테이블 내부에서 어떠한 기준으로 정렬을 하고 싶을 때 사용하는 함수다. 이때 주로 사용하는 함수가 RANK와 DENSE_RANK가 있다. RANK는 1위가 2개 있을 때 1-1-3으로 출력해서 2라는 순위를 생략하는 반면에, DENSE_RANK는 1-1-2로 출력해서 2라는 순위를 생략하지 않는다.
SELECT
product_id
, score
, ROW_NUMBER() OVER(ORDER BY score DESC) AS row
, RANK() OVER(ORDER BY score DESC) AS rank
, DENSE_RANK() OVER(ORDER BY score DESC) AS dense_rank
FROM popular_products
ORDER BY row
;
이때 ORDER BY 내 인자에서 윈도 프레임을 지정해서 다양한 값들을 추출할 수 있다. 가장 대표적인 문법은 'ROWS BETWEEN A AND B'이며 A와 B는 start, end 즉, 시작지점과 끝지점을 의미한다. 'CURRENT ROW'는 현재의 행, 'UNBOUNDED PRECEDING'은 이전 행 전부, 'UNBOUNDED FOLLOWING'은 이후 행 전부를 의미한다.
SELECT
product_id
, score
, ROW_NUMBER() OVER(ORDER BY score DESC) AS row
, SUM(score)
OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_score
, AVG(score)
OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS local_avg
-- extract highest rank product
, FIRST_VALUE(product_id)
OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS first_value
-- extract least rank product
, LAST_VALUE(product_id)
OVER(ORDER BY socre DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS last_value
FROM popular_products
ORDER BY row
;
--- ROWS BETWEEN start AND end
--- start와 end에는 'CURRENT ROW'(현재의 행), 'n PECEDING'(n행 앞), 'n FOLLOWING'(n행 뒤)
--- 'UNBOUNDED PRECEDING'(이전 행 전부), 'UNBOUNDED FOLLOWING'(이후 행 전부)
SELECT
product_id
, ROW_NUMBER() OVER(ORDER BY score DESC) AS row
-- 가장 앞 순위부터 가장 뒷 순위까지 범위를 대상으로 상품 ID를 집약하기.
, array_agg(product_id)
, collect_list(product_id)
OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS whole_agg
-- 가장 앞 순위부터 현재 순위까지의 범위를 대상으로 상품 ID를 집약하기.
, array_agg(product_id)
, collect_list(product_id)
OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_agg
-- 순위 하나 앞과 하나 뒤까지 범위를 대상으로 상품 ID를 집약하기.
, array_agg(product_id)
, collect_list(product_id)
OVER(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS local_agg
'SQL' 카테고리의 다른 글
| [SQL] SQL의 종류 (0) | 2022.08.17 |
|---|