MySQL, PHP 주기별 (주간 / 일간 / 월간) 합계, 매출 데이터 구하기

주간 / 월간 / 연간 데이터 합계를 구하고자 한다고 가정해 보자.

예를 들면 일간 매출, 원가를 품목별로 구하려고 한다.

 

체크할 점은 어느 날에는 판매 이력이 있는 품목이 어느 날에는 없기도 하고 둘 다 존재하기도 한다는 점이다.

 

아래와 같이 검색 되는 테이블 데이터를

 

SELECT * FROM sales WHRE R_DATE='2012-12-15';

r_date	goods_nm	sales	cost
2012-12-15	사과	50000	5000
2012-12-15	딸기	70000	6000
2012-12-15	레몬	5000	1600

 

SELECT * FROM sales WHRE R_DATE='2012-12-16';

r_date	goods_nm	sales	cost
2012-12-16	사과	15000	300
2012-12-16	레몬	80000	5600
2012-12-16	망고	4500	100

 

이렇게 출력하고 싶은 경우

goods_nm	sales_15	cost_15	sales_16	cost_16
사과	50000	5000	15000	300
딸기	70000	6000
레몬	5000	1600	80000	5600
망고			4500	100

 

여러 방법이 있겠지만 언어적으로 보면 두가지 방법이 있다.

MySQL 쿼리로 한번에 불러오는 방법과 PHP 코드 반복문으로 처리하는 방법.

두가지 경우를 모두 살펴 보자.

 

MySQL 쿼리를 사용하는 방법 (CASE WHEN)

SELECT tbl.goods_nm,
    CASE WHEN tbl.r_date='15' THEN sales END AS sales_15,
    CASE WHEN tbl.r_date='15' THEN cost END AS cost_15,
    CASE WHEN tbl.r_date='16' THEN sales END AS sales_16
    CASE WHEN tbl.r_date='16' THEN cost END AS cost_16
FROM (
    SELECT '15' AS r_date, goods_nm, sales, cost FROM sales WHERE r_date='2012-12-15'
    	UNION
    SELECT '16' AS r_date, goods_nm, sales, cost FROM sales WHERE r_date='2012-12-16'
) AS tbl

 

여기서 CASE WHEN 구문과 UNION 절에 사용된 구문을 늘리면 특정 기간, 주간 / 월간 단위로 데이터를 뽑아낼 수 있다.

 

PHP 코드로 구현하는 방법

DB SELECT 결과를 배열에 담았다고 가정하면 기간만큼 반복적으로 쿼리하는 방식으로 처리가 가능하다.

함수로 만든다면 아래와 같은 형태가 된다.

 

$result = array(); // 데이터를 합칠 변수 선언

for ($i = 15; $i < 17; $i++) {

    $row = dbfunction($i); // 변수 $row 에 쿼리 결과를 반복적으로 담는다. 이 함수는 상황에 따라 만들자.

    $result = data_monthly ($result, $row); // 이번 반복에서 $row 를 $result 에 추가한다

}

// 데이터를 배열에 집계하는 함수. 조금씩 수정하면 상황에 맞게 써먹을 수 있다.
function data_monthly ($target, $source) {

    for ($i = 0; $i < count($source); $i++) {

        $m = $source[$i]['r_date']; // 집계 결과물에서 column 명에 쓰일 날짜

        $key = array_search($source[$i]['goods_nm'], array_column($target, 'goods_nm')); // 합칠 배열에서 품목명을 찾는다

        if ($key !== false) {

            // 찾았다면 해당 순서(index) 에 붙인다
            $target[$key]['sales'.$m] = $source[$i]['sales'];
            $target[$key]['cost'.$m] = $source[$i]['cost'];

        } else {

            // 새로운 품목이라면 index 자체를 추가한다.
            $dummy = array(
                'goods_nm' => $source[$i]['goods_nm'],
                'sales'.$m => $source[$i]['sales'],
                'cost'.$m => $source[$i]['cost'],
            );
            $target[] = $dummy;

        }

    }
    
    return $target;

}

 

이 경우에 반복할 쿼리는 아래처럼 단순한 형태가 된다.

 

SELECT '15' AS r_date, goods_nm, sales, cost FROM tbl WHERE r_date='$date';

 

마치는 말

당연히 DB 쿼리로 한번에 데이터를 불러오는 방법이 성능적으로나 코드 간결화적으로나 우선시 되는 선택지이다.

하지만 아래와 같은 상황에 따라 일장일단이 있다.

 

• 어느 주기를 기준으로 데이터를 집계할 것인가 ex) 주간 / 월간 / 연간
• 하위 데이터를 산출하기에 테이블간 JOIN 이 얼마나 수월한가
• 중간 합계가 필요한가 GROUP BY ... WITH ROLLUP

 

WITH ROLLUP 으로 중간 합계를 구하고, 합계의 하위(자식 요소)로서 데이터를 구성하는 경우 PHP 코드로 구현한 방법이 더 수월하였다.

예시는 다른 포스팅에서 다루기로 하겠다.