database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 31227 게시물 읽기
No. 31227
마일리지테이블 막히는 부분좀 도와주세요..
작성자
오진솔(wlsthf77)
작성일
2019-04-02 17:18
조회수
2,223

제가 혼자 정리해보려고 끄적거려본건데 고수님들의 의견도 한번 들어보고 싶어서

이곳에 올려봅니다.

지금 방법보다 훨씬 좋은 방법을 제시해주신다면 매우 감사드리겠습니다.

mileage 테이블로 총합을 넘기는 구조는 변경하기 힘들 것 같습니다.

deldate는 입력받을때 regdate에 +7일을 한다음 비교해서 타입을 변경할 예정입니다.

----------------------------------------------------------------------------------------*/

 

 

select * from buypoint;

+-------------+--------+------------+------------+---------------+----------------+-----------------+---------------------+---------------------+

| buypoint_id | buy_id | member_num | mileage_id | buypoint_type | buypoint_price | buypoint_amount | buypoint_regdate    | buypoint_deldate    |

+-------------+--------+------------+------------+---------------+----------------+-----------------+---------------------+---------------------+

|          19 |      0 |          7 |          7 |           503 |              0 |               0 | 2019-03-28 17:07:55 | 0000-00-00 00:00:00 |

|          20 |      0 |          8 |          8 |           503 |              0 |               0 | 2019-03-28 17:09:28 | 0000-00-00 00:00:00 |

|          21 |      0 |          7 |          7 |           502 |              1 |               1 | 2019-03-28 17:16:13 | 0000-00-00 00:00:00 |

|          22 |      0 |          7 |          7 |           503 |              1 |               0 | 2019-03-28 17:19:37 | 0000-00-00 00:00:00 |

|          24 |     13 |          7 |          7 |           500 |          24950 |           24950 | 2019-03-28 17:25:49 | 0000-00-00 00:00:00 |

|          25 |      0 |          7 |          7 |           501 |          24000 |             950 | 2019-03-28 17:26:46 | 0000-00-00 00:00:00 |

|          26 |     13 |          7 |          7 |           500 |          12475 |           13425 | 2019-03-28 17:31:16 | 0000-00-00 00:00:00 |

|          27 |     11 |          7 |          7 |           500 |           1944 |           15369 | 2019-03-28 17:31:29 | 0000-00-00 00:00:00 |

|          28 |     12 |          7 |          7 |           500 |           8750 |           24119 | 2019-03-28 17:32:52 | 0000-00-00 00:00:00 |

|          30 |     14 |          7 |          7 |           500 |          17500 |           41619 | 2019-04-01 11:52:12 | 0000-00-00 00:00:00 |

|          32 |     15 |          7 |          7 |           500 |          25000 |           66619 | 2019-04-01 17:10:34 | 0000-00-00 00:00:00 |

+-------------+--------+------------+------------+---------------+----------------+-----------------+---------------------+---------------------+

 

 

select buypoint_amount from mileage;

 

/* mileage 테이블은 사용자 별로 여러 종류의 마일리지의 합을 모아놓은 테이블 

buypoint는 insert로 입력되면 트리거로 가장 최근의 amount 값을 buypoint_amount로 update시킴.*/

+-----------------+

| buypoint_amount |

+-----------------+

|           66619 |

+-----------------+

 

 

 

 

/****************************************** 구매v포인트 관련 type (500~599)************************************************/

 

 

500 - 구매포인트 적립

501 - 구매마일리지 전환

502 - 운영자 충전

503 - 운영자 차감

504 - 보유일(7일) 경과로 인한 삭제

 

/****************************************** 구매v포인트 관련 type (500~599)************************************************/

 

 

 

 

이슈 1:

 buypoint_id가 32인 포인트는 2019-04-01 17:10:34에 +25000원 되었음.

 그렇다면 4월 8일에 삭제가 되어야 함.

 

 현재 시스템은 각 포인트별 가격을 sum 하는게 아닌 가장 최신의 항목에 총합을 적어놓는 시스템(regdate를 내림차순으로 불러오고 limit 1 을 이용해 최신 값을 찾아내서 발생한 항목만큼 더하거나 빼줌).

 

 그렇다면 내가 7일이내에 사용한 포인트를 제외하고 얻은 포인트를 7일 후 삭제당하는 방법은...?

 

 

방안 1 

 buypoint_id별로 amount 값을 따로 주고 입력이 끝나면 sum(buypoint_amount)로 mileage  테이블에 업데이트 함.

 (예상문제 : 한번의 포인트 차감을 위해선 사용가능한 포인트를 모두 불러와서 컬럼마다 계산을 해야함.)

 

 

 

 

이 글에 대한 댓글이 총 2건 있습니다.

-- INSERT INTO buypoint
SELECT 0          buy_id
     , member_num member_num
     , member_num mileage_id
     , 504        buypoint_type
     , v3 - v2    buypoint_price
     , v1 - v3    buypoint_amount
     , CURDATE()  buypoint_regdate
     , DATE_ADD(CURDATE(), INTERVAL 7 DAY) buypoint_deldate
  FROM (SELECT member_num
             , SUM(CASE WHEN buypoint_type     IN (500, 502)
                        THEN buypoint_price END)     v1 -- 적립
             , IFNULL(
               SUM(CASE WHEN buypoint_type NOT IN (500, 502)
                        THEN buypoint_price END), 0) v2 -- 사용
             , SUM(CASE WHEN buypoint_type     IN (500, 502)
                         AND buypoint_deldate <= CURDATE()
                        THEN buypoint_price END)     v3 -- 적립(종료대상)
          FROM buypoint
         GROUP BY member_num
        ) a
 WHERE v3 > v2
;

마농(manon94)님이 2019-04-03 17:22에 작성한 댓글입니다.
이 댓글은 2019-04-03 17:25에 마지막으로 수정되었습니다.

 늦었지만 마농님 답변 너무나 감사드립니다 

오진솔(wlsthf77)님이 2019-04-09 10:31에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31230mysql 원단위 버림에 대해 문의 드립니다. [1]
최기철
2019-04-19
2395
31229쿼리좀 도와주세요. [1]
초봉임둥
2019-04-11
2218
31228mariadb varchar의 최대크기 [3]
DB초보
2019-04-03
4089
31227마일리지테이블 막히는 부분좀 도와주세요.. [2]
오진솔
2019-04-02
2223
31226쿼리가 index를 타지 않습니다.
최봉수
2019-04-02
2162
31225MySQL 클론 관련 문의 드립니다. [3]
지나가다
2019-03-22
2504
31224적립금(마일리지) 적립, 사용, 만료소멸 프로세스 도움 부탁드립니다. [1]
조현철
2019-03-21
2290
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다