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

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

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

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

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

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

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

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

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

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

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

다음 매출 데이터를 보자.

이 매출 데이터를 위에서 소개한 쿼리를 이용해 날짜별 매출과 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 count(*) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) =7 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;

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

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

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

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

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

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

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

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

윈도우 함수 사용시 주목할 부분이 네 군데가 있다.

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

**1) <함수>**

SELECT 다음에 <함수> 부분을 살펴보자.
이때 <함수>에는 다양한 함수가 사용가능하다.

집계할때 많이 사용하는 SUM( )이나 COUNT( ),MAX( ),MIN( ) 함수 모두 가능하다.
특히 순서를 배열할때는 row_number( ) 함수나 rank() 함수, dense_rank( ) 함수가 주로 쓰인다.

• ROW_NUMBER(): 점수 순서로 유일한 순위를 붙임
  

• RANK() : 같은 순위를 허용해서 순위를 붙임
  

• DENSE_RANK() : 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 띄고 순위를 붙임
  
  

**2) PARTITION BY <칼럼명>**

두번째는 OVER( ) 다음에 바로 나오는 PARTITION BY <칼럼명> 이다.
PARTITION BY <칼럼명> 은 윈도함수 내부에서 데이터를 집약할 때 사용한다.

윈도함수 내부에서 GROUP BY를 해준다고 보면 되며
결과적으로 지정해준 <칼럼명> 필드 안에서 윈도우를 나눠서 집계한다고 생각하면 된다.

윈도 함수에서 특정 칼럼으로 집약하지 않고 순서 배열만 한다면 생략해도 무방하다.

**3) ORDER BY <칼럼명>**

세번째는 ORDER BY <칼럼명>이다. 윈도우를 세팅하는 과정이다.
윈도우를 어떤 필드를 기준으로 정렬(내림차순 or 오름차순)할지 정하는 것이다.
윈도우 내부를 어떤 순서로 구성할지 이해하면 편하다.

4) ROWS BETWEEN “start” AND “end”

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

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

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

두가지 예시를 통해 윈도함수 대해 더 알아보자.
다음은 인기 있는 상품이 기록된 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
;

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

✋🏾 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란 무엇일까?

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

1. AB Test의 정의
2. AB Test의 대상이 되는 요소
3. 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가지 프로세스를 통해 수행된다.

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

(SQL) 예제를 통해 서브쿼리를 어떻게 사용하는지 알아보자

✋🏾 <손에 잡히는 10분 SQL _ 인사이트> 교재를 참고해 작성한 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크

1. 서브쿼리란?

서브쿼리는 용어 그대로 쿼리 안에 쿼리를 넣는 것이다.
주로 WHERE절과 IN절에서 많이 사용된다.

서브쿼리를 이용하면 테이블끼리 동적으로 연계해서 작업이 가능하다.

다음 예제를 통해 더 자세히 알아보자.

이 DB에서 나는 RGAN01 이라는 제품을 구매한 고객의 목록을 가져오고 싶다.
이 정보를 가져오려면 다음 순서대로 진행해야 한다.

1) RGAN01을 주문한 주문 번호를 가져온다
2) 이전 단계에서 가져온 주문 번호로 고객 ID를 가져온다
3) 이전 단계에서 가져온 고객 ID로 고객의 상세 정보를 가져온다.

이 세 단계는 별도의 쿼리로 수행가능하다.
하지만 그렇게하면 세번의 작업이 필요하다.

SELECT order_num
FROM OrderItems
WHERE prod_id='RGAN01'; -- 1

SELECT cust_id
FROM Orders
WHERE order_num IN (200007,20008) --2

SELECT cust_name,cust_contact
FROM Customers
WHERE cust_id IN (1000000004,1000000005); --3

서브쿼리를 이용하면 이 세 단계를 하나의 쿼리로 합쳐서 진행할 수 있다.
다음과 같이 작성하면 된다.

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM ORders
                  WHERE order_num IN(SELECT order_num
                                     FROM OrderItems
                                     WHERE prod_id='RGAN01'))

이처럼 서브쿼리를 사용하면 강력하고 유연한 SQL문을 작성할 수 있다.

2. 서브쿼리를 사용할 때 주의할 점

서브쿼리를 사용할 때 주의할 점은 다음과 같다.

• 서브쿼리가 항상 효율적인 것은 아니다.
  
  • 사용할 수 있는 서브쿼리의 수에는 제한이 없다.
    
  • 너무 많은 쿼리를 사용하면 성능이 저하될 수 있다.
    
• 서브쿼리는 하나의 열만 검색할 수 있다.
  
• 서브쿼리는 항상 안에 있는 쿼리를 먼저 처리하고 , 이후 바깥쪽에 있는 쿼리를 처리한다.
  

3. 추가 문제 (서브 쿼리 이용)

1) 서브쿼리를 사용하여 10 또는 그 이상의 가격으로 제품을 구매한 고객 목록을 반환하라.
OrderItems 테이블에서 조건에 맞는 주문번호 (order_num)을 가져온 다음,
Orders 테이블에서 주문 번호와 일치하는 주문에 대한 고객 ID(cust_id)를 검색한다.

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                    FROM OrderItems
                    WHERE item_price>=10);

2) Products 테이블에서 모든 제품명(prod_name)과 quant_sold를 가져오는 SQL 문을 작성하라.
여기서 quant_sold는 판매된 항목의 총수량이 담긴 계산 필드다.

SELECT prod_name,
       (SELECT SUM(quantity)
        FROM OrderItems
        WHERE Products.prod_id=OrderItems.prod_id)
FROM Products;