MySQL 사용법, 전년비 성장률 구하기

MySQL 가용법의 하나로 전년비 성장률을 구해서 출력해주는 방법에 대래서 살펴보도록 하겠습니다.

제가 만든 테이블 및 컬럼 정보는 아래와 같습니다.

CREATE TABLE IF NOT EXISTS retail_m ( 
         date DATE not null,
         country CHAR(128) not null,
    	 retail FLOAT,	
         department FLOAT,
       	 supermarket FLOAT,	
          cvs FLOAT,	
          without_store FLOAT,	
          offline FLOAT,	
          online FLOAT,	
          online_portion FLOAT,
          online_products_portion FLOAT,
          intenet FLOAT,	
          mobile FLOAT,	
          internet_other FLOAT,
          mobile_other FLOAT ) ;Code language: PHP (php)

월별로 데이타를 입력한 상태에서 아래와 같이 전년 동기비 성률을 구하는 SQL 코드를 작성해 보았습니다.

WITH 함수 + LAG 함수 사용

여기서는 WITH 함수를 사용해 임시변수를 생성해 참조 데이타를 형성한다음, 이 임시 생성된 데이타를 불러와 LAG(컬럼명,참조할 아래로부터 위치 숫자)

LAG 함수는 입력된 데이타에서 위쪽으로(먼저 입력된) N번째 column1의 값을 가져오라는 함수이죠. 그리고 반대의 개념으로 사용되는 LEAD 함수는 입력된 데이타의 아래쪽으로(나중에 입력된) N번째 column1의 값을 가져오라는 함수 입니다.

이러함 수를 사용할때 중요한 내용은 이 함수 뒤에 나오는 over안의 partition by 와 order by 입니다.

(PARTITION by는 group by와 비슷한 개념으로 적용된 컬럼의 종류대로 LAD(LEAD) 한다는 의미입니다. 기업간 비교가 주로 이루어지는 주식 관련 데이타베이스에서는 기업코드를 사용하고, 구각별 비교를 한다면 국가명을 사용하면 됩니다.

이를 기업벼로 비교시, 국가간 비교 시 잘멋된 다른 데이타를 가져올 위험을 줄이기 위함입니다.

ORDER BY는 LAD(LEAD) 함수 사용 시 위쪽 또는 아래쪽 데이타를 가져올 때 일정한 순서로 정렬되어 있지 않으면 조회때마다 다른 데이타를 가져올 수 있기 때문에 날짜 데이터를 기준으로 정렬해서 시간별로 데이타를 가져올 수 있도록 합니다.

이 쿼리는 How to Compute Year-Over-Year Differences in SQL 설명 내용을 참조로 응용해 만든 SQL 쿼리입니다.

WITH retail_y AS (
    SELECT EXTRACT(year from date) as year,
	         EXTRACT(month from date) as month,
                 retail  as retail, 
                 online_portion AS online_portion,
                 online_products_portion AS online_products_portion,
                 offline AS offline, 
                 online AS online, 
                 sum(internet) AS internet, 
                sum(mobile) AS mobile          
    FROM retail_m 

    WHERE  
                retail_m.`country` = 'korea'
    AND retail_m.`date` > now() -INTERVAL 61 MONTH
    GROUP BY 1,2
)

SELECT 
       month AS '월', 
       retail AS '소매판매', 
       ((retail / LAG(retail,12) OVER (ORDER BY year, month))/ LAG(retail,12) OVER (ORDER BY year, month))*100 AS '소매판매 YoY',
       offline AS '오프라인', 
       ((offline / LAG(offline,12) OVER (ORDER BY year, month))/ LAG(offline,12) OVER (ORDER BY year, month))*100 AS '오프라인 YoY',
       online AS '온라인쇼핑', 
       ((online / LAG(online,12) OVER (ORDER BY year, month))/ LAG(online,12) OVER (ORDER BY year, month))*100 AS '온라인쇼핑 YoY',
       online_portion AS '온라인쇼핑 비중',
       online_products_portion AS '온라인쇼핑 상품 비중',             
       internet AS '인터넷쇼핑', 
       ((internet / LAG(internet,12) OVER (ORDER BY year, month))/ LAG(internet,12) OVER (ORDER BY year, month))*100 AS '인터넷쇼핑 YoY',
       mobile AS '모바일쇼핑', 
       ((mobile / LAG(mobile,12) OVER (ORDER BY year, month))/ LAG(mobile,12) OVER (ORDER BY year, month))*100 AS '모바일쇼핑 YoY'
          
FROM retail_y
ORDER BY 1,2;Code language: PHP (php)

위의 코드는 일반 MySQL에서는 잘 작동하며, MariaDB에서는 에러 표시를 보여(phpMyAdmin에서 사용 시)주기는 하지만 정상적으로 작동합니다.

하지만 php SQL 쿼리를 사용하는 wpDataTable 플러그인에서는 WITH와 같은 함수는 보안 이슈로 허용하지 않기 때문에 위 코도는 정상적으로 작동하지는 않습니다.

LAG 함수만을 사용

그래서 WITH 절을 사용하지 않고, LAG 함수만을 사용해서 코드를 만들었습니다. 작동하기는 합니다..

현재까지는 별 문제가 없기는 한데 지속 사용해 보면서 어떤 문제가 있다면 공유하도록 하겠습니다.

SELECT
    date AS '월', 

    retail AS '소매판매', 
    ((retail / LAG(retail,12) OVER (PARTITION by country ORDER by date))/ LAG(retail,12) OVER (PARTITION by country ORDER by date))*100 AS '소매판매 YoY',
    offline AS '오프라인', 
    ((offline / LAG(offline,12) OVER (PARTITION by country ORDER by date))/ LAG(offline,12) OVER (PARTITION by country ORDER by date))*100 AS '오프라인 YoY',
    online AS '온라인쇼핑', 
    ((online / LAG(online,12) OVER (PARTITION by country ORDER by date))/ LAG(online,12) OVER (PARTITION by country ORDER by date))*100 AS '온라인쇼핑 YoY',
    online_portion AS '온라인쇼핑 비중',
    online_products_portion AS '온라인쇼핑 상품 비중',             
    internet AS '인터넷쇼핑', 
    ((internet / LAG(internet,12) OVER (PARTITION by country ORDER by date))/ LAG(internet,12) OVER (PARTITION by country ORDER by date))*100 AS '인터넷쇼핑 YoY',
    mobile AS '모바일쇼핑', 
    ((mobile / LAG(mobile,12) OVER (PARTITION by country ORDER by date))/ LAG(mobile,12) OVER (PARTITION by country ORDER by date))*100 AS '모바일쇼핑 YoY'

FROM retail_m
WHERE  
     retail_m.`country` = 'korea'
 AND retail_m.`date` > now() -INTERVAL 61 MONTH;Code language: PHP (php)

삽질 사례…여러 값이 존재 시

아래는 SQL 공부하면서 삽질했던 사례를 적어 보았습니다. 기록차.. 정답을 찾은 경우도 있고 찾는 과정에 있는 것도 있습니다.

자동차 판매량 데이타 입력을 maker, brand별로 입력시 maker로선 여러 값이 존재하게 됩니다.

자동차 판매량을 입력하는 테이블을 아래와 같이 만들었습니다.

CREATE TABLE IF NOT EXISTS car_q ( 
    date DATE,
    year YEAR,
    quarter_cy  CHAR(32),
    country CHAR(128),
    maker CHAR(128),
    brand CHAR(128),
    sales int,
    sales_yoy FLOAT,
    sales_ms FLOAT);Code language: SQL (Structured Query Language) (sql)

데이터 입력 예

그러면 아래와 같은 데이타들이 입력됩니다.

현대기아이라는 maker아래에 제네시스, 현대, 기아와 같은 브랜드들 데이터가 따라오게 됩니다.

이렇게 maker를 기준으로 데이타를 뽑을 때 동일 시점에 여러 데이터가 존재하기 때문에 단순하게 판매량(sales 컬럼)을 뽑으라고 쿼리 명령을 주면 “#1242 – Subquery returns more than 1 row”와 같은 에러 메세지를 만나게 됩니다.

maker당 여러개 갑이 있기 때문이죠.

SELECT quarter_cy AS '분기', 
  ( SELECT sales
    FROM `car_q` AS s
    WHERE s.maker = 'Hyundai Kia'
      AND s.quarter_cy = t.quarter_cy
  ) AS '현대기아',

FROM car_q AS t
WHERE date > now() - INTERVAL 37 MONTH
GROUP BY quarter_cy, maker
ORDER BY quarter_cy ASC;Code language: PHP (php)

그렇게 때문에 sun(sales)와 같이 maker에 해당하는 모든 값을 해치라는 조치를 해야 합니다. 아래는 단지 sales를 sum(sales)로 변경했는데요. 그러면 정상적으로 결과값이 출력됩니다. sum(sales)로도 제대로된 결과를 보여주지 못하네요. 쩝.

참고

MySQL 사용법, 데이타베이스 & 테이블 & 컬럼 생성하기

MySQL 사용법, 최근 데이터 조회 방법, 최근 3개월 데이터 등등

MySQL 사용법, 데이터 추출 기간 설정하기, 날짜 범위 데이터 추출 방법

MySql 최적화로 빨라질 사이트 DB 튜닝 방법