ERP 프로젝트 - 최저가 표

개요

이번 프로젝트에서 거래처에게 발주하는 시스템이라는 기능을 만들면서

거래처만의 각자 다른 가격을 가져 해당 가격을 비교할 수 있으면 좋겠다는 생각을 하게 되었고

그렇게 짜게된 테이블이번 프로젝트에서 거래처에게 발주하는 시스템이라는 기능을 만들면서

거래처만의 각자 다른 가격을 가져 해당 가격을 비교할 수 있으면 좋겠다는 생각을 하게 되었고

그렇게 짜게된 테이블이 아래와 같다.

허나 이렇게 테이블을 짜보니 생각보다 일자마다 조정된 가격을 찾는 데에 어려움이 있었다.

따져야할 상황은 만약에 단가 순번이 다음 번호가 있는 사업체의 경우 그 이전의 단가는 쓰지 못하기 때문에 그에 대한 사업체마다의 MAX인 조건을 따져봤어야 했고

그 MAX조건도 조정 일시에 따라 그 조정 일시 이전의 대해서 사업체들의 max값이 얼마인지 또 따로 따져봐야했기에 쿼리를 짜는데 있어 어려움을 겪었다.

---

1번째 방법은 변경된 조정일시마다 다른 사업체들도 그대로 가격데이터를 넣는 방법도 있었으나,

서버에 과부하를 주는 일이었고 해당 방법이 절대 옳은 방법이 아닐 것같다 생각하여 제외를 시키게 되었고

 

2 번째 방법은 최저가에 대한 테이블을 생성하는 방법이었다.

해당 테이블에 최저가인 가격을 넣거나 아니면 최저가인 사람이 가격 변동을 할때 가격을 변동한 값을 저장하는 식으로 생각을 해봤지만, 시스템상 구현은 가능하지만 이미 데이터로 넣은 값들이 있어 해당 방식은 현재 구현하는 방식과는 안맞겠다는 생각이 들어 현재 저장한 테이블을 통해서만 쿼리로 값을 가져오는 방식을 따로 찾게 되었다.

 

그리고 방법을 찾은 결과 해당 방식으로 구현된 쿼리이다

<select id="selectMinAmt" parameterType="String" resultType="gdsAmtVO">
	<![CDATA[
		-- 재고가 변경된 일자를 가져옴
		WITH DATE_LIST AS (
		    SELECT DISTINCT AJMT_DT
		    FROM GDS_AMT
		    WHERE GDS_CODE = #{gdsCode}
		),
		-- 해당 기간 이전의 BZENT_NO별 MAX(AMT_SEQ)의 단가들을 가져온다
		LATEST_AMT AS (
		    SELECT DT.AJMT_DT, G.GDS_CODE, G.BZENT_NO, G.AMT
		    FROM DATE_LIST DT
		    JOIN GDS_AMT G 
		      ON G.AJMT_DT <= DT.AJMT_DT
		     AND G.GDS_CODE = #{gdsCode}
		    JOIN (
		        SELECT DT_IN.AJMT_DT, GDS_CODE, BZENT_NO, MAX(AMT_SEQ) AS MAX_AMT_SEQ
		        FROM DATE_LIST DT_IN
		        JOIN GDS_AMT GA ON GA.GDS_CODE = #{gdsCode}
		                        AND GA.AJMT_DT <= DT_IN.AJMT_DT
		        GROUP BY DT_IN.AJMT_DT, GDS_CODE, BZENT_NO
		    ) MA ON G.GDS_CODE = MA.GDS_CODE
		         AND G.BZENT_NO = MA.BZENT_NO
		         AND G.AMT_SEQ = MA.MAX_AMT_SEQ
		         AND G.AJMT_DT <= DT.AJMT_DT
		         AND DT.AJMT_DT = MA.AJMT_DT
		),
		-- 해당 기간 이전의 BZENT_NO별 MAX(AMT_SEQ)의 단가들을 가져와 그 중의 최저가를 일자별로 가져온다.
		MIN_AMT_PER_DATE AS (
		    SELECT AJMT_DT, MIN(AMT) AS MIN_AMT
		    FROM LATEST_AMT
		    GROUP BY AJMT_DT
		),
		-- 만약에 전에 일자가 같은 최저가인 경우 이전행을 리턴한다
		-- LAG함수 : 이전 행의 값을 리턴
		-- ORDER BY AJMT_DT는 AJMT_DT를 기준으로 정렬하여 이전 값이 어떤 값인지 결정
		FILTERED_MIN_AMT AS (
		    SELECT AJMT_DT, MIN_AMT,
		           LAG(MIN_AMT) OVER (ORDER BY AJMT_DT) AS PREV_MIN_AMT
		    FROM MIN_AMT_PER_DATE
		)
		-- 필터된 값을 가져오면서 만약에 PREV_MIN_AMT가 이전값을 가지고있으면(LAG함수로인해) 보이지 않게하고
		-- 만약에 변경된 값에대한 경우 그 값은 PREV_MIN_AMT <> MIN_AMT이기 때문에 출력하게 한다
		SELECT AJMT_DT
			, MIN_AMT AS AMT
		FROM FILTERED_MIN_AMT
		WHERE PREV_MIN_AMT IS NULL OR PREV_MIN_AMT <> MIN_AMT
		ORDER BY AJMT_DT
		]]>
	</select>

해당 방식은 먼저 재고가 변경된 일자를 먼저 가져와서 그 변경된 일자별대로 max값을 가진 bzent들 만의 가격 값을 가져와 최소값을 일자마다 저장해주고 이때 일자가 변경되어도 최소값이 동일하면 출력하지 않게 하는 방식으로 LAG함수를 활용하는 조건문을 세워 최저가 값을 가져오는 형태로 만들게 되었다.

 

이러한 데이터를 보내 javascript에서 차트로 데이터를 보내 일자마다 단가 변동을 보여주는 그래프를 만들었다

///////////////////// 차트
var myChart2 = new Chart(
    document.getElementById('myChart2'),
    {
        type: 'line',
        data: {
            labels: res.min.map(item => {
                var date = new Date(item.ajmtDt);
                return date.toLocaleDateString('ko-KR', { year: '2-digit', month: '2-digit', day: '2-digit' });
            }),
            datasets: [{
                label: '변경일자',
                data: res.min.map(item => item.amt),
                fill: false,
                borderColor: 'rgb(0, 193, 87)',
                tension: 0.2
            }]
        },
        options: {
            scales: {
                xAxes: [{
                    type: 'category',
                    ticks: {
                        autoSkip: true,
                        maxTicksLimit: 4,
                        color: '#000000', // X축 폰트 색상
                        font: { // X축 폰트 스타일
                            family: 'NanumSquare, sans-serif',
                            size: 15,
                            weight: 'bold'
                        }
                    }
                }],
                yAxes: [{
                    ticks: {
                        suggestedMin: Math.min(...res.min.map(item => item.amt)) * 0.98,
                        suggestedMax: Math.max(...res.min.map(item => item.amt)) * 1.02,
                        color: '#999', // Y축 폰트 색상
                        font: { // Y축 폰트 스타일
                            family: 'NanumSquare, sans-serif',
                            size: 12,
                            weight: 'bold'
                        }
                    },
                    grid: { // Y축 그리드 선 스타일
                        drawBorder: false,
                        color: 'rgba(0, 116, 52, 0.2)'
                    }
                }]
            },
            legend: {
                display: true,
                labels: {
                    color: '#000', // legend 폰트 색상
                    font: {
                        family: 'NanumSquare, sans-serif',
                        size: 12,
                        weight: 'bold'
                    }
                }
            },
            tooltips: {
                callbacks: {
                    label: function (tooltipItem) {
                        return tooltipItem.yLabel;
                    }
                }
            },
            plugins: {
                datalabels: { // datalabels 플러그인 세팅
                    formatter: function (value, context) {
                        return value.toLocaleString() + '%'; // 출력 텍스트
                    },
                    align: 'top',
                    font: {
                        family: 'NanumSquare, sans-serif',
                        size: 12,
                        weight: 'bold'
                    },
                    color: '#000' // datalabels 텍스트 색상
                }
            }
        } // options 종료
    }
); // 차트2 끝

결과 차트