kiyoja story

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(기준날짜, 비교날짜) 끝.

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. 다른 테이블을 결합해야 할 때가 있다. 특정 조건을 기준으로 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일과 날짜와 유저를

두 개의 테이블을 결합할 때 JOIN(조인)을 사용한다. JOIN을 할 때 두 테이블의 결합 조건은 보통 ON을 사용한다. table_A에 colname_1, colname_2의 2개의 열이 있고, table_B에는 colname_1, colname_2의 2개의 열이 있다. 두 테이블을 colname_1의 값이 일치하는 조건으로 결합해야한다고 하자. 보통 아래와 같이 "JOIN ON" 구문으로 조인을 하게 된다. 1 2 3 4 5 SELECT  a.colname_1, a.colname_2, b.colname_3 FROM  table_A a INNER JOIN table_B b ON  a.colname_1  =  b.colname_1 ; Colored by Color Scripter cs 그런데 두 테이블 모두에 colname_1이 똑같은 이름으로 있다. 이 때 USING() 구문을 사용해서 더 편리하게 조인할 수 있다. 1 2 3 4 5 SELECT  a.colname_1, a.colname_2, b.colname_3 FROM  table_A a INNER JOIN table_B b USING (colname_1) ; Colored by Color Scripter cs 상기 2개의 쿼리는 결합 조건을 나타낸다. USING() 구문을 사용해서 더 간결한 쿼리를 작성할 수 있다. USING()은 두 테이블에 같은 이름으로 존재하는 칼럼을 연결하는 결합조건을 실행할 때 사용할 수 있다. 단, MS SQL과 SYBASE에서는 지원하지 않는다. 끝.

SQL에서 가상의 테이블을 만들 때 VIEW 구문을 사용한다. VIEW(뷰)는 한 번 생성해 놓으면, 일반 테이블이라 생각하고 동일하게 사용해도 될 정도로 테이블과 거의 동일한 개체로 여겨진다. VIEW의 쿼리 구조는 아래와 같다. 1 2 3 4 5 CREATE  VIEW view_name AS  -- 생성할 VIEW의 이름(view_name)을 지정한다 SELECT  column1, column2, ...  -- 쿼리의 결과가 view_name을 가지는 VIEW로 만들어진다 FROM  table_name WHERE  condition; Colored by Color Scripter cs "CREATE VIEW 뷰_이름 AS 일반 SQL 쿼리"의 형식으로 뷰(VIEW)를 생성한다. 뷰를 삭제하려면 아래와 같이 간단한 쿼리만 작성하면 된다. 1 DROP  VIEW view_name; cs 뷰는 크게 아래와 같은 2가지의 필요에 의해 사용된다. 1. 보안의 필요성 - table에 개인정보 등 민간함 정보가 포함되어 있어, table의 접근 권한을 다르게 주고 싶을 때 뷰를 사용하면 편리하다. table의 접근을 제한하고자 하는 유저에게는 table의 접근을 막고 view의 접근만 허용하면 간단하게 권한을 다르게 부여할 수 있다. 2. 쿼리의 단순화 - 복잡하거나 연산에 큰 시간이 필요한 쿼리를 자주 사용하는 경우, 매번 쿼리를 실행시키는 대신 뷰를 만들면 관리가 편리해진다. 뷰를 생성해 놓고 필요할 때 접근하면 간단하게 끝난다. VIEW를 남용하지는 말되 적절히 활용하자. 끝.