kiyoja story

SQL에서 CAST함수를 이용해서 데이터 형식(타입)을 바꿀 수 있다. CAST함수의 문법은 아래와 같다. CAST(expression AS datatype) 예시를 통해 살펴보자. 아래와 같이 "30 / 4"를 계산하면 7.75가 아니라 7이 산출된다. 1 select 31 / 4 ; 숫자 30과 4의 data type이 정수(int)이기 때문에 7.75에서 데이터 타입을 정수로 맞추기 위해 소수점을 버려 7을 산출하게 되는 것이다. 쿼리를 아래로 바꿔보자. 1 select cast ( 31 as float) / cast ( 4 as float); 이번에는 7.75로 잘 산출된다. 나누기를 하기 전 cast()함수를 통해 숫자를 소수(float)로 바꿔줬기 때문이다. 즉  31.0 / 4.0을 계산한 것과 같다. cast( 31 as float)는 정수 31을 소수(float) 31.0으로 바꿔주는 쿼리이다. 바꿀 수 있는 데이터 타입은 float외에, int, char 등 모든 데이터 타입이 가능하다. 끝.

SQL의 percent_rank 함수를 사용해서 데이터의 상대 순위를 매길 수 있다. 순위를 매기는 방식은 기본적으로 rank 함수와 같다. rank 함수에 대한 설명은 "SQL RANK - 순위 매기기" 포스팅에서 자세히 확인할 수 있다. percent_rank는 1등을 0, 마지막 등수를 1로 출력한다는 점이 rank와 다르다. 아래의 샘플 테이블을 통해 살펴보자. 1 2 3 4 5 6 7 8 9 10 CREATE TABLE body (height float, _name text); INSERT INTO body VALUES ( 174 . 52 , 'Tom' ); INSERT INTO body VALUES ( 167 . 33 , 'Lucy' ); INSERT INTO body VALUES ( 174 . 52 , 'Frank' ); INSERT INTO body VALUES ( 168 . 89 , 'Jane' ); INSERT INTO body VALUES ( 177 . 80 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 170 . 40 , 'Robert' ); 상대 순위를 매기는 코드는 아래와 같다. 1 2 3 select percent_rank () over (partition by _name order by height desc ) as percent_ranking, _name, height from body ; 위 코드를 실행시키면 아래와 같은 결과를

SQL에서 LIMIT절을 사용하면 출력되는 행(row)의 수를 제한할 수 있다. 아래의 샘플 데이터를 통해 알아보자. 1 2 3 4 5 6 7 8 9 10 CREATE TABLE body (height float, _name text); INSERT INTO body VALUES ( 174 . 52 , 'Tom' ); INSERT INTO body VALUES ( 167 . 33 , 'Lucy' ); INSERT INTO body VALUES ( 174 . 52 , 'Frank' ); INSERT INTO body VALUES ( 168 . 89 , 'Jane' ); INSERT INTO body VALUES ( 177 . 80 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 170 . 40 , 'Robert' ); 다음과 같이 테이블이 생성된다. 만약 상위 3개의 데이터만 뽑고 싶다면 쿼리 마지막에 "LIMIT 3"을 추가하면 된다. 코드는 아래와 같다. 1 2 3 4 SELECT * FROM body LIMIT 3 ; 아래와 같이 상위 3개의 행만 출력한 것을 확인할 수 있다. LIMIT절을 모든 SQL에 사용할 수 없다. MS SQL은 SELECT TOP절을 사용하고 Oracle은 ROWNUM을 사용한다. MYSQL, SQLite에서는 LIMIT절을 사용할 수 있다. 끝.

SQL에서 특정 칼럼을 기준으로 번호를 매길 수 있다. 아래와 같은 예시 테이블을 만든다. 1 2 3 4 5 6 7 8 9 10 CREATE TABLE body (height float, _name text); INSERT INTO body VALUES ( 174 . 52 , 'Tom' ); INSERT INTO body VALUES ( 167 . 33 , 'Lucy' ); INSERT INTO body VALUES ( 174 . 52 , 'Frank' ); INSERT INTO body VALUES ( 168 . 89 , 'Jane' ); INSERT INTO body VALUES ( 177 . 80 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 170 . 40 , 'Robert' ); 번호를 매기는 코드는 아래와 같다. 1 2 3 select row_number() over (partition by _name order by _name) as _number, _name, height from body ; 위 코드를 실행시키면 아래와 같은 결과를 얻을 수 있다. 기본 문법은 row_number () over (order by columns)이다. 더 자세히 살펴보자. - order by는 번호를 매기는 기준을 나타낸다. 다시말해 order by의 칼럼을 기준으로 번호를 매긴다. Robert의 키가 큰 순서로 번호를 매기기 위해 desc를 붙여 내림차순을 해주었다. desc가

SQL의 rank 함수를 사용해서 데이터의 순위를 매길 수 있다. 아래의 샘플 테이블을 통해 살펴보자. 1 2 3 4 5 6 7 8 9 10 CREATE TABLE body (height float, _name text); INSERT INTO body VALUES ( 174 . 52 , 'Tom' ); INSERT INTO body VALUES ( 167 . 33 , 'Lucy' ); INSERT INTO body VALUES ( 174 . 52 , 'Frank' ); INSERT INTO body VALUES ( 168 . 89 , 'Jane' ); INSERT INTO body VALUES ( 177 . 80 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 175 . 77 , 'Robert' ); INSERT INTO body VALUES ( 170 . 40 , 'Robert' ); 랭킹을 매기는 코드는 아래와 같다. 1 2 3 select rank () over (partition by _name order by height desc ) as rankig, _name, height from body ; 위 코드를 실행시키면 아래와 같은 결과를 얻을 수 있다. 기본 문법은 rank () over (partition by columns order by columns)이다. 더 자세히 살펴보자. - order by는 랭킹을 매기는 기준을 나타낸다. 다시말해 order by의 칼럼을 기준으로 랭킹을 매긴다. Robert의 키가 큰 순서로 랭킹을 매기기 위해 d

SQL에서 두 날짜 사이의 간격을 계산하기 위해서는 DATEDIFF() 함수를 사용한다. 문법은 아래와 같다. DATEDIFF(date1, date2) 결과는 "date1 - date2"의 날짜 차이를 반환한다. 아래의 예시 코드를 살펴보자. 1 select datediff( '2018-01-05' , '2018-01-01' ); 위 코드를 실행하면 "2018-01-05"에서 "2018-01-01" 뺀 "4"를 얻을 수 있다. 다른 예시를 살펴보자. 1 select datediff( '2018-01-05' , '2018-01-09' ); 위 코드를 실행하면 "2018-01-05"에서 "2018-01-09" 뺀 "-4"를 얻을 수 있다. DATEDIFF()에서 첫 번째 날짜가 기준이라는 점을 헷갈리지 말자. 즉, DATEDIFF(기준날짜, 비교날짜) 끝.

SQL에서 IF조건문을 사용할 수 있다. 문법은 아래와 같다. IF(condition, value_if_true, value_if_false) IF문의 괄호 안에 "조건, True 값, False 값"을 순차적으로 적어주면 된다. 간단하다. 아래의 예시 코드를 통해 알아보자. 1 select if( 80 >= 70 , 'pass' , 'fail' ); if 조건인 "80 >= 70"은 True이다. 따라서 True 값인 "pass"가 산출된다. 아래와 같이 조건을 바꿔보자. 1 select if( 60 >= 70 , 'pass' , 'fail' ); if 조건인 "60 >=70"은 False이다. 따라서 False 값인 "fail"이 산출된다. 끝.

SQL에서 숫자의 자리 수를 맞춰서 버리기 위해서는 truncate 함수를 사용한다. 예시를 통해 살펴보자. 아래와 같이 height 테이블을 만든다. 1 2 3 4 5 6 7 8 9 CREATE TABLE HEIGHT(height float PRIMARY KEY , Name text); INSERT INTO HEIGHT VALUES ( 174 . 52 , 'Tom' ); INSERT INTO HEIGHT VALUES ( 167 . 33 , 'Lucy' ); INSERT INTO HEIGHT VALUES ( 178 . 27 , 'Frank' ); INSERT INTO HEIGHT VALUES ( 168 . 89 , 'Jane' ); INSERT INTO HEIGHT VALUES ( 177 . 80 , 'Robert' ); SELECT * FROM HEIGHT; 코드를 실행하면 아래와 같이 테이블이 생성되는 것을 확인할 수 있다. truncate(숫자, 자릿 수)를 사용하여 숫자를 바꿔보자. 아래와 같이 자릿 수에 "1"을 입력해보자. 1 2 3 SELECT truncate (height, 1 ) as height, _name FROM height ; 결과는 아래와 같다. 소수점 "1"번째 자리를 기준으로 그 아래의 숫자는 버림을 확인할 수 있다. 이번에는 자릿 수 부분에 "0"을 대입해보자. 1 2 3 SELECT truncate (height, 0 ) as height, _name FROM height ; 숫자 "0"을 대입하면 아래와 같이 소수점 "0"번째 자리, 즉 정수를 기준으로 그 아래는 버리게 된다. 이번에는 자릿 수 부

1. 누적합계를 구해보자. 기준으로 정렬하고 기준이 증가함에 따라, 그 기준까지의 누적 합계를 구해보자 2. 샘플 데이터를 통해 알아보자. 온라인 매출 샘플 데이터를 아래와 같이 만든다. 1 2 3 4 5 6 7 CREATE TABLE sales_online(id integer, sales integer); INSERT INTO sales_online VALUES ( 1 , 100 ); INSERT INTO sales_online VALUES ( 5 , 170 ); INSERT INTO sales_online VALUES ( 4 , 250 ); INSERT INTO sales_online VALUES ( 2 , 170 ); INSERT INTO sales_online VALUES ( 3 , 250 ); 3. ID를 기준으로 누적합계를 구해보자. - 자신의 id보다 작은 id를 가지는 데이터를 JOIN해야 한다. SELF JOIN을 하되 자신의 id보다 작은 id를 가지는 행을 결합시킨다. 위 로직을 쿼리로 표현하면 아래와 같다. 1 2 3 4 5 6 SELECT * FROM sales_online a JOIN sales_online b ON a.id >= b.id ORDER BY a.id, b.id ; 아래와 같이 자신의 id보다 작은 id를 가지는 행들이 결합되어 있는 것을 확인할 수 있다. 4. 자신의 id를 기준으로 결합시킨 행의 sales를 모두 더한다. 아래와 같이 쿼리를 작성하면 된다. 1 2 3 4 5 6 7 SELECT a.id, a.sales, sum (b.sales) as cul_sum FROM sales_online a JOIN sales_online b ON a.id >= b.id GROUP BY 1 , 2 ORDER BY 1 ;

1. SQL은 Aggregation(집계)를 할 수 있다. SUM(), AVG(), MIN(), MAX()가 대표적이다. aggregation을 결합시켜 사용할 수 있다. 2.  aggregation을 결합해서 비율(ratio)을 구해보자. 예시로 전체 매출 대비 여성 유저의 매출 비율은 구해보자. 아래와 같이 샘플 테이블을 마련한다. 1 2 3 4 5 6 7 CREATE TABLE users(id integer, gender text, sales integer); INSERT INTO users VALUES ( 1 , 'female' , 160 ); INSERT INTO users VALUES ( 2 , 'male' , 170 ); INSERT INTO users VALUES ( 3 , 'female' , 160 ); INSERT INTO users VALUES ( 4 , 'male' , 190 ); INSERT INTO users VALUES ( 5 , 'female' , 200 ); 3. SUM(여성 매출) / SUM(전체 매출)을 구해야 한다. - 여성 매출을 구하기 위해 gender가 female인 케이스만 골라 sales를 그래도 두고 나머지 케이스는 sales를 0으로 만든다. 그러고 sum한다. - 전체 매출은 모든 sales 값을 sum한다. 4. 위 로직을 쿼리로 만들면 아래와 같다. 1 2 3 SELECT cast ( sum ( case gender when 'female' then sales else 0 end ) as float) / cast ( sum (sales) as float) * 100 as ratio_female_sales FROM users ; 아래와 같이 결과를 확인할 수 있다.

1. 2개 이상의 테이블의 column을 결합하는 방법은 여러가지가 있다. 기본적으로는 JOIN을 사용하지만, 복잡하게 결합해야 할 때에는 SUB QUERY(서브쿼리)와 WITH을 사용할 수 있다. 2. 아래와 같이 sales와 users의 샘플 테이블을 만들어 보자 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE sales_online(id integer, sales integer); INSERT INTO sales_online VALUES ( 1 , 100 ); INSERT INTO sales_online VALUES ( 2 , 170 ); INSERT INTO sales_online VALUES ( 3 , 250 ); CREATE TABLE users(id integer, last_name text); INSERT INTO users VALUES ( 1 , 'kim' ); INSERT INTO users VALUES ( 2 , 'lee' ); INSERT INTO users VALUES ( 3 , 'park' ); INSERT INTO users VALUES ( 4 , 'choi' ); INSERT INTO users VALUES ( 5 , 'han' ); 3. JOIN을 사용하여 결합해보자. JOIN의 기본이 되는 INNER JOIN을 사용해보자 1 2 3 4 5 SELECT s.id, u.last_name FROM sales_online s JOIN users u ON s.id = u.id ; 아래와 같이 잘 결합되었다. 4. SUB QUERY를 사용하여 결합해보자 1 2 3 4 5 SELECT s.id, u.last_name FROM sales_onlin

1. 다른 테이블을 결합해야 할 때가 있다. 특정 조건을 기준으로 column(열)을 결합시킬 때는 JOIN을 사용한다. 하지만 row(행)을 결합시킬 때는 UNION 또는 UNION ALL을 사용하면 된다. 두 개의 SELECT문을 합치는 것이라 생각하면 된다. 2. UNION과 UNION ALL은 차이가 있다. UNION은 중복되는 row를 제거하고 결합 시키지만, UNION ALL은 중복도 포함하여 결합시킨다. 3. UNION과 UNION ALL을 사용할 때의 주의점은 아래와 같다. - SELECT문의 column 수가 같아야 한다. - column의 자료형이 같거나 호환되어야 한다. - column 이름은 최상위 SQL문의 이름을 따른다. - column 이름이 아니라 column 순서를 기준으로 결합된다. 이름이 같아도 순서가 다르면 다른 순서대로 결합된다. 4. 예제를 통해 살펴보자 아래와 같이 온라인, 오프라인 세일즈를 기록하는 테이블이 각각 있다고 하자. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE sales_online(id integer, sales integer); INSERT INTO sales_online VALUES ( 1 , 100 ); INSERT INTO sales_online VALUES ( 2 , 170 ); INSERT INTO sales_online VALUES ( 3 , 250 ); INSERT INTO sales_online VALUES ( 4 , 300 ); INSERT INTO sales_online VALUES ( 5 , 120 ); CREATE TABLE sales_offline(id integer, sales integer); INSERT INTO sales_offline VALUES ( 1 , 185 ); INSERT INTO sales_o

1. 데이터를 카테고리로 나눠 분류해야 할 때가 있다. 이럴 때 CASE WHEN 구문을 사용한다. CASE WHEN의 기본 문법은 아래와 같다. 1 2 3 4 5 CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE resultN END AS new_column_name CASE WHEN ~ END로 끝난다. END 다음에 AS new_column_name을 사용하여 이름을 부여할 수도 있다. 2. 유저들의 키 정보를 가지는 샘플 DB를 아래와 같이 만들자 1 2 3 4 5 6 7 CREATE TABLE USERS(id integer, height integer); INSERT INTO USERS VALUES ( 1 , 185 ); INSERT INTO USERS VALUES ( 2 , 170 ); INSERT INTO USERS VALUES ( 3 , 168 ); INSERT INTO USERS VALUES ( 4 , 177 ); INSERT INTO USERS VALUES ( 5 , 160 ); 3. 키를 구분해보자 키가 175 이상은 High, 165 이하는 Small, 나머지는 Middle로 구부시키려고 한다. 아래와 같이 쿼리를 작성하면 된다. 1 2 3 4 5 6 7 8 SELECT id, CASE WHEN height >= 175 then 'High' WHEN height <= 165 then 'Small' ELSE 'Middle' END AS 'category' FROM USERS ; 4. 아래와 같이 id별로 키의 category가 분류되었음을 확인할 수 있다. 끝.

0. Retention(잔존율)이란 웹 또는 앱에서 특정 시점을 기준으로 일정 시간이 흐른 뒤에도 남아있는, 잔존해 있는 비율을 의미한다. 보통 1 day retention(1일 리텐션)이라 함은 1일 후에도 남아있는 유저의 비율을 의미한다. 실무에서 가입일을 기준으로 유저들의 주(week) 단위 리텐션을 아래의 매트릭스와 같이 관리할 수 있다. 이번 포스팅에는 1일 리텐션을 구해본다. 1. 아래와 같이 게임을 플레이한 유저의 정보를 담고 있는 PLAYS라는 샘플 테이블을 만들자 1 2 3 4 5 6 7 CREATE TABLE PLAYS(id integer, created_at date); INSERT INTO PLAYS VALUES ( 1 , '2019-01-03' ); INSERT INTO PLAYS VALUES ( 2 , '2019-01-03' ); INSERT INTO PLAYS VALUES ( 3 , '2019-01-03' ); INSERT INTO PLAYS VALUES ( 1 , '2019-01-04' ); INSERT INTO PLAYS VALUES ( 2 , '2019-01-04' ); " SELECT * FROM PLAYS; " 쿼리를 통해 plays 테이블을 아래와 같이 확인할 수 있다. 2. 리텐션을 구하는 방법을 알아보자. 1월 3일의 dau는 3명, 1월 4일의 dau는 2명이다. 따라서 1월 3일 기준 1일 리텐션은 (2/3)*100 = 66%이다. 이 계산 방법을 조건으로 다시 풀어보자 - 일별 dau를 계산해야 한다. dau를 산출하는 쿼리는 " SQL - DAU 산출 " 포스팅에서 자세히 확인할 수 있다. - 1월 3일과 1월 4일을 매칭시킨다. 1월 4일에서 1일을 뺀 1월 3일을 원래 테이블의 1월 3일과 날짜와 유저를