(SQL) 이동평균 구하기

09 Dec 2021 in 가이던스 on SQL

(SQL) 이동평균 구하기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

1.이동평균이란?

이동평균이란 추세의 변동을 알 수 있도록 구간을 옮겨 가면서 구하는 평균을 말한다.

예를 들어 7일간 이동평균은 1~7일 평균, 2~8일 평균, 3~9일 평균 … 처럼
가장 오래된 데이터 대신에 최신의 데이터를 넣어 평균을 구하는 것을 말한다.

즉, 이동평균은 정해진 기간에서의 데이터의 평균이라고 할 수 있다.

2. 이동평균 구하는 구문

이동평균을 구하기 위해서, 윈도 함수인 OVER( ORDER BY ~) 구문을 활용해주어야 한다.
윈도함수를 통해 7일씩 데이터를 쪼개서 계산하는것이 원리이다.

이동평균을 구하는 구문은 다음과 같다.

SELECT 
    <날짜 칼럼>,
    AVG(SUM(구하고자 하는 칼럼)) OVER (ORDER BY <날짜 칼럼> ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) ,
    -- 최근 최대 7일동안의 이동평균
    CASE
        WHEN 7=count(*)
        OVER(ORDER BY <날짜 칼럼> ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    THEN
        AVG(SUM(구하고자 하는 칼럼))
        OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    END AS  -- 최근 7일 동안의 평균을 확실하게 계산하기
FROM <테이블명>
GROUP BY <날짜 칼럼>

;

위의 구문을 보면 이동평균 구하는 것을 2가지 방법으로 나누어 놓은 것을 볼 수 있다.

최근 최대 7일 동안의 이동평균
무조건 7일 동안의 평균

첫번째 방법은 7일의 기간이 충족되지 않는 1~6일도 있는 기간까지의 평균이 구해지는 방법이다.

반면 두번째 방법인 CASE 구문을 이용한 이동평균 구하기 방법은
무조건 기간이 7일이 되었을때 평균을 구하는 것이다.

3. 예제

다음 매출 데이터를 보자.

이 매출 데이터를 위에서 소개한 쿼리를 이용해 날짜별 매출과 7일 이동평균을 집계하는 결과물을 출력해보자

SELECT
    dt,
    SUM(purchase_amount) AS total_amount,
    AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS seven_day_avg, -- 최근 최대 7일 동안의 평균 계산하기.
    CASE
        WHEN 7=count(*)
        OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    THEN
        AVG(SUM(purchase_amount))
        OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
    END AS seven_day_avg_strict -- 최근 7일 동안의 평균을 확실하게 계산하기
FROM purchase_log
GROUP BY dt
ORDER BY dt;

아래와 같이 출력 됨을 알 수 있다.

(SQL) 테이블 임시로 만들기

02 Dec 2021 in 가이던스 on SQL

(SQL) 테이블 임시로 만들기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

1. 테이블 임시로 만들어야하는 상황

데이터 분석 작업을 하다보면, DB에 주어진 테이블 이외에
임시로 테이블을 만들어야 하는 상황이 생긴다.

DB에 없는 테이블을 별도로 만들어 with구문을 이용해 작업하거나 ,
CROSS JOIN을 원활히 하기위해 사용하는 경우이다.

대표적인 케이스가 가로 기반 데이터를 세로 기반으로 변환할 때이다.
행으로 전개할 데이터 수가 고정되어있으면, 그 수와 같은 수의 일련번호를
가진 피벗 테이블을 만들고 CROSS JOIN을 하여 이 작업을 할 수 있다.

이 때 일련번호를 가진 피벗테이블을 임시로 만들때가 바로 이 포스팅에서 설명할 내용이다.

2. 임시 테이블 만드는 구문

테이블을 임시로 만드는 구문은 다음과 같다.

SELECT <값> AS <칼럼명>
UNION ALL SELECT <값> AS <칼럼명>
UNION ALL SELECT <값> AS <칼럼명> -- 반복하면 됨.
;

또 다른 방법은 WITH구문과 VALUES 구문을 사용해 테이블을 임시로 만드는 것이다.

WITH <테이블명> (칼럼명1,칼럼명2,....) AS(
    VALUES
        (값1, 값2)
        (값3, 값4) -- 칼럼순서대로 값이 넣어짐. ex)값1,3은 칼럼1에 속한 값 
) 
<추가 구문>
;

3. 예제

칼럼명이 number이고 1,2,3,4를 레코드로 갖는 테이블을 만들어보자

-- 방법1
SELECT 1 AS NUMBER
UNION ALL SELECT 2 AS NUMBER
UNION ALL SELECT 3 AS NUMBER -- 반복하면 됨.
UNION ALL SELECT 4 AS NUMBER;

--방법 2
WITH NUMBER_TABLE (NUMBER) AS (
    VALUES
        (1),
        (2),
        (3),
        (4)   
)
SELECT *
FROM NUMBER_TABLE;

아래와 같이 출력 됨을 알 수 있다.

(SQL) 윈도 함수를 이용해 데이터 가공하기

29 Nov 2021 in 가이던스 on SQL

(SQL) 윈도 함수를 이용해 데이터 가공하기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.

1. 윈도함수란?

윈도함수란 테이블 내부에 ‘윈도’라는 범위 를 정의하고 ,
해당 범위 내부에 포함된 값을 자유롭게 사용하려고 도입한 것이다.
즉, 자체적으로 범위를 정해 그 안에서 작업하기 위한 함수이다.

윈도함수를 통해 데이터를 집약하거나 그룹 내부의 순서를 배열할 수 있다.

윈도 함수를 사용하기 위해서는 윈도 함수 내부에
참조하고자하는 값의 위치를 명확하게 지정해야 한다.

OVER 구문 내부에 ORDER BY 구문을 사용하여 윈도 내부에 있는 데이터의 순서를 정의한다.

일반적인 윈도 함수 , 특히 OVER 구문은 다음과 같다.

SELECT <칼럼명> 
        <함수> () OVER(PARTITION BY <칼럼명> ORDER BY <칼럼명> DESC ROWS BETWEEN "start" AND "end" )
FROM <테이블명>
    

2. 윈도함수 주의할 점

주목할 부분이 세 군데가 있다.

첫번째로, **SELECT 다음에 <함수>** 부분을 살펴보자.
이때 <함수>에는 다양한 함수가 사용가능하며 특히 순서를 배열할때는
row_number() 함수나, rank() 함수, dense_rank 함수가 주로 쓰인다.

ROW_NUMBER(): 점수 순서로 유일한 순위를 붙임
RANK() : 같은 순위를 허용해서 순위를 붙임
DENSE_RANK() : 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 띄고 순위를 붙임

두번째는 OVER( )구문에 바로 나오는 PARTITION BY <칼럼명>이다.

**PARTITION BY <칼럼명>**은 윈도함수 내부에서 데이터를 집약할 때 사용한다.

즉 윈도함수 내부에서 ,GROUP BY를 해준다고 보면 된다.
윈도함수에서 특정 칼럼으로 집약하지 않고 순서배열만 한다면 생략해도 무방하다.

세번째로, OVER( )구문 안에 ROWS BETWEEN “start” AND “end”를 보자.
이것을 프레임 지정 구문이라고 한다.

프레임 지정이란 현재 레코드 위치를 기반으로 상대적인 윈도를 정의하는 구문이다.
“start”와 “end”에는 다음 키워드가 들어갈 수 있다.

CURRENT ROW : 현재의 행
n PRECEDING : n행 앞
n FOLLOWING : n행 뒤
UNBOUNDED PRECEDING : 이전 행 전부
UNBOUNDED FOLLOWING : 이후 행 전부

3. 윈도함수의 실제 사용 예시

두가지 예시를 통해 윈도함수 대해 더 알아보자.

다음은 인기 있는 상품이 기록된 popular_products 테이블이다.

나는 윈도함수를 사용해 카테고리들의 순위를 계산하고 싶다.
다음 네가지를 한 테이블에 추출하고싶다.

1) 카테고리별로 점수 순서로 정렬하고 유일한 순위를 붙임
2) 카테고리별로 같은 순위를 허가하고 순위를 붙임
3) 카테고리별로 같은 순위가 있을 때 같은 순위
4) 다음에 있는 순위를 건너 띄고 순위를 붙임

이럴 경우, 윈도함수를 사용하면 추출할 수 있다.

SELECT 
 category
 , product_id
 , score
 , ROW_NUMBER() over (PARTITION BY category ORDER BY score DESC) AS row
 , RANK() OVER(PARTITION BY category ORDER BY score DESC) AS rank
 , DENSE_RANK() OVER(PARTITION BY category ORDER BY score DESC) AS dense_rank
FROM popular_products
ORDER BY category,row;

결과가 다음과 같이 나온다.

두번째 예시이다.
윈도 함수를 이용해 카테고리별 주요 수치를 추출하고 싶다.

1) 점수 순서로 유일한 순위
2) 순위 상위부터의 누계 점수 계산
3) 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
4) 순위가 높은 상품 ID 추출하기

 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 -- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
    ,FIRST_VALUE(product_id) OVER(ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value -- 순위가 높은 상품 ID 추출하기
    ,LAST_VALUE(product_id) OVER(ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value -- 순위가 낮은 상품 ID 추출하기
FROM popular_products
;

결과가 다음과 같이 나온다.

(SQL) CASE 문을 이용하여 데이터 전처리를 하자

25 Aug 2021 in 가이던스 on SQL

(SQL) CASE 문을 이용하여 데이터 전처리 하기

✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

1. CASE문이란?

CASE문은 데이터 전처리를 할때 많이 사용된다.
CASE문을 이용하면 데이터를 조건에 따라 원하는 값으로 바꿀 수 있다.

기본 구문은 다음과 같다.

CASE  
    WHEN <조건식> THEN <조건을 만족할 때의 값>
    END

WHEN 뒤에 조건식을 만족하면 데이터를 THEN 다음에 나오는 값으로 바꿔준다.

2. CASE문의 실제 사용 예시

예시를 통해 CASE문에 대해 더 알아보자.

다음은 Vendors테이블이다.

나는 vend_country에 기록된 국가명을 각 국가가 속한 대륙명으로 바꾸고 싶다.
(USA –> America , England –> Europe, France –> Europe)

이럴 경우, CASE문을 사용하면 간단히 데이터를 원하는대로 바꿀 수 있다.

  SELECT vend_id,
       CASE
        WHEN vend_country= 'USA' THEN 'America'
        WHEN vend_country='England' Then 'Europe'
        WHEN vend_country='France' Then 'Europe'
        END AS vend_continent -- 칼럼명을 vend_continent로 변경
FROM tysql.vendors;

결과가 다음과 같이 나온다.

SQL내에서 데이터 전처리를 할 때
CASE문은 많이 사용되니 잘 알아두도록 하자.

AB Test란 무엇일까?

01 Aug 2021 in 가이던스 on 데이터 기초

AB Test란 무엇일까?

이 포스팅은 3개의 목차로 구성되어 있습니다.

AB Test의 정의
AB Test의 대상이 되는 요소
AB Test 프로세스

1. AB Test의 정의

AB Test란 온라인으로 접속한 사용자를 임의로 여러개의 그룹으로 분리하고
각 사용자 그룹에게 서로 다른 UI나 알고리즘을 노출시키고 비교함으로써
여러 안 중 가장 효과가 좋은 안을 찾아내는 것을 말한다.

쉽게말하자면, A안과 B안을 만들어 비교하고 더 나은 것을 찾는 방법이라 할 수 있다.

구글, 페이스북, 디스커버리와 같은 많은 유명기업들은
AB Test를 사용하여 페이지를 개선하고 목표를 달성한다고 한다.

2. AB Test의 대상이 되는 요소

AB Test의 대상이 되는 요소는 대표적으로 Headline Text와 CTA Text가 있다.

Headline Text
- 페이지 방문자가 처음으로 읽게 될 문구
CTA Text ( Call-to-action )
- 페이지 방문자에게 목표 행동을 유도할 문구
- ex) 목표행동 : 회원가입, 구매, 사이트 방문

3. AB Test 프로세스

AB Test는 크게 6가지 프로세스를 통해 수행된다.

데이터 수집
- 전환율이 낮은 트래픽 구역 찾고 , 데이터 수집
목표 설정
- ex) 버튼 클릭, 구매링크 클릭, 회원 가입
가설 수립
- 현재(기존) 버전보다 나을 것 같은 아이디어를 기준으로 가설 수립
비교 버전 만들기
- AB Test 소프트웨어 이용하여 비교 버전 만들기
- ex) CTA 버튼 색깔 변경, 문구 변경
실험 수행
- 페이지 방문자들에게 랜덤으로 페이지 배정 (A or B) 하여 테스트
결과 분석
- 두 버전의 퍼포먼스 비교
- 이 분석 결과가 통계적으로 유의미한지
- 일반적으로 P-value, AA Test를 통해 통계적 유의미성 확인함

(SQL) 이동평균 구하기

1.이동평균이란?

2. 이동평균 구하는 구문

3. 예제

(SQL) 테이블 임시로 만들기

1. 테이블 임시로 만들어야하는 상황

2. 임시 테이블 만드는 구문

3. 예제

(SQL) 윈도 함수를 이용해 데이터 가공하기

1. 윈도함수란?

2. 윈도함수 주의할 점

3. 윈도함수의 실제 사용 예시

(SQL) CASE 문을 이용하여 데이터 전처리를 하자

1. CASE문이란?

2. CASE문의 실제 사용 예시

AB Test란 무엇일까?

1. AB Test의 정의

2. AB Test의 대상이 되는 요소

3. AB Test 프로세스

Datata29

Error

1.이동평균이란?

2. 이동평균 구하는 구문

3. 예제

1. 테이블 임시로 만들어야하는 상황

2. 임시 테이블 만드는 구문

3. 예제

1. 윈도함수란?

2. 윈도함수 주의할 점

3. 윈도함수의 실제 사용 예시

1. CASE문이란?

2. CASE문의 실제 사용 예시

1. AB Test의 정의

2. AB Test의 대상이 되는 요소

3. AB Test 프로세스

Pagination

Templates (for web app):

Error