[MySQL]누적합계구하기

서비스 운영 중에 운전자의 주행거리를 가지고 일정 금액을 부과해야하는 처리가 필요했다.

그런데 부과해야하는 금액이 만약 100KM이상 주행할 경우에는 더 이상 부과하지 않는다는 비즈니스 사항을 포함하고 있었다.

즉, 'A'라는 사람이 130KM를 탔다면 30km에 대해서는 부과료를 매기지 않는 것이다.

실제로는 A라는 사람이 주행을 10번 했다고 가정하고, 그 중 5번째 주행을 80KM ~ 130KM를 탄다면 80KM부터 100KM까지의 20KM에는 부과료를 내야하고, 6번째부터 주행에는 금액이 부과되지 않는 방식이었다.

 

위 비즈니스 로직을 반영하기 위해선, 'A'라는 운전자의 주행 건별 누적주행거리와 부과료가 책정되는 거리를 각각 구했어야 했다.(Mission)

 

 

[테스트 데이터 생성]

주행데이터가 위와 같이 시간에 따라 저장되어 존재하고 있는 형태라고 가정하고 테스터 데이터를 생성하였다.

 

 

[세팅된 데이터]

ance는 각 주행 건 drving_id에 해당하는 이동거리(주행거리)이다.

accum_distance가 누적거리 필드이고, pay_distance가 해당 운행 건의 누적거리가 100KM를 초과하였을 때 금액을 부과해야하는 거리의 값의 필드이다.

 

 

개인누적거리 구하기

[개인순으로 나열하기 (선처리) ]

가장 먼저 각 인원들의 주행데이터를 그룹핑하여 누적거리를 먼저 구하고자 하였다.

누적거리를 구할 때 각각의 ROW가  이전 ROW데이터의 운전자 이름이 동일하면 누적거리 + ROW 거리의 방식으로 처리하기 때문에 이름 순으로 먼저 정렬을 해주어야했다.

SELECT * FROM driving WHERE driver_user order by asc;

 

 

[누적거리 계산]

여기서 지역변수를 이용하여 누적거리처리를 다음과 같이 구할 수 있다.

SELECT driving_id, driver_user, distance,
	(CASE WHEN driver_user = @driver_user THEN @distance := @distance + distance 
          ELSE @distance := distance END) as 'accum_distance' ,
	(@driver_user := driver_user) dName
FROM driving order by driver_user asc;

alias가 'dName' 인 부분 (@driver_user := driver_user) 에서 ROW의 driver_user값이 저장이 되고 그것이 CASE문에서 사용된다.

 

 

[위 쿼리 실행결과]

누적주행거리필드 accum_distance

 

 

100KM이하까지만 금액이 부과되어야할 거리 구하기

SELECT driving_id, driver_user, distance, accum_distance,
	(CASE WHEN accum_distance <= 100 THEN distance 
		  WHEN accum_distance - distance >= 100 then 0 
		  WHEN accum_distance - distance < 100 THEN 100 - (accum_distance - distance) 
		  END) 'pay_distance'
 FROM 
 (SELECT driving_id, driver_user, distance,
		(CASE when driver_user = @driver_user then @distance := @distance + distance ELSE @distance := distance END) as accum_distance ,
		(@driver_user := driver_user) dName
        FROM driving order by driver_user asc ) FINAL;

누적거리계산된 테이블 구하는 쿼리를 서브쿼리로 사용하여 조회된 데이터 테이블에서 누적거리 필드 값을 이용하여 

금액이 부과되는 거리를 따로 구한다. ( pay_distance)

 

 

[조회 결과]

 

 

누적거리 And 금액 부과거리 결과 업데이트

이제까지 누적거리와 부과료 거리에 대해서 조회가 가능하므로 해당 데이터를 기존의 주행테이블에 업데이트를 시켜주어야한다.

그러기 위해서 임시테이블을 생성해서 조회데이터를 잠시 저장하고 주행테이블에 다시 결과를 업데이트를 해줌으로써 모든 과정을 마칠 수 있게 된다.

 

[임시테이블에 조회데이터 저장]

CREATE TEMPORARY TABLE IF NOT EXISTS $T
(driving_id INT, driver_user VARCHAR(100), distance INT, accum_distance INT, pay_distance INT)
SELECT driving_id, driver_user, distance, accum_distance,
	(CASE WHEN accum_distance <= 100 THEN distance 
		  WHEN accum_distance - distance >= 100 then 0 
		  WHEN accum_distance - distance < 100 THEN 100 - (accum_distance - distance) 
		  END) 'pay_distance'
 FROM 
 (SELECT driving_id, driver_user, distance,
		(CASE when driver_user = @driver_user then @distance := @distance + distance ELSE @distance := distance END) as accum_distance ,
		(@driver_user := driver_user) dName
        FROM driving order by driver_user asc ) FINAL;

 

[임시테이블의 정보를 주행테이블(driving)에 업데이트]

UPDATE driving a, $T
	set a.accum_distance = $T.accum_distance,
	    a.pay_distance = $T.pay_distance
WHERE a.driving_id = $T.driving_id
AND a.driver_user = $T.driver_user;

 

[최종 정보 반영된 주행테이블]

 

이렇게 해서 완료.

 

 

 

출처

https://codingspooning.tistory.com/110

[MySQL] 그룹별 누적 합계 구하기

https://codingspooning.tistory.com/48