請問MySQL 怎麼實作類似LINE POINT規則的點數

mysql

biscuit 2022-11-02 20:19:19 ‧ 1335 瀏覽

分享至

請問各位大大，MySQL要怎麼實作類似LINE POINT規則的點數
原本我以為我已經成功實作了，但現在有發現有問題

規則:
點數購買，會有贈送點數
點數有效期，贈送點數也有

如果點數未過期前，有新購買點數
未過期的點數可以展期(跟新買點數的過期時間一樣)
但贈送點數不可展期

因為要考慮到購買的點數，可能會未結帳或是退貨不買
規則上就滿複雜的(好像搞得有點像股票庫存XD)

原本欄位上只設計了，
點數(point)，有效期開始(valid)、有效期結束(expiry)，狀態(status 只需考慮1，剩下都是不可用的)，是否為贈送點數(isGift)，此筆是否註銷(deleted_at)，其他不重要的欄位我就不列出了

id|user_id|point|memo|isGift|status|valid|expiry|deleted_at
------------- | -------------
17|1|-22|消費扣點|0|1|null|null|null
16|1|-22|消費扣點|0|1|null|null|null
15|1|25|購買點數|0|1|2022/10/10 00:00|2023/10/10 23:59|null
14|1|25|購買點數|0|0|null|null|null
13|1|25|購買點數|0|0|null|null|null
12|1|-11|消費扣點|0|1|null|null|null
11|1|-11|消費扣點|0|1|null|null|null
10|1|-11|消費扣點|0|1|null|null|null
9|1|-11|消費扣點|0|1|null|null|null
8|1|2|交易贈送|1|1|2019/8/2 00:00|2020/8/1 23:59|null
7|1|50|購買點數|0|1|2019/8/2 00:00|2020/8/1 23:59|null
6|1|-11|消費扣點|0|1|null|null|null
5|1|-11|消費扣點|0|1|null|null|null
4|1|-11|消費扣點|0|1|null|null|null
3|1|-11|消費扣點|0|1|null|null|null
2|1|2|舊點數轉移(贈送)|1|1|2018/10/10 00:00|2020/3/1 23:59|null
1|1|75|舊點數轉移|0|1|2018/10/10 00:00|2020/3/1 23:59|null

SELECT 
(SELECT sum(a.point) as point 
FROM points a
WHERE a.user_id = 1 and a.status = 1 and a.deleted_at is null and a.point > 0
      and (a.valid < now() or a.valid is null) 
      and (now() < (SELECT max(expiry) FROM points WHERE user_id = a.user_id and status = 1 and (valid < now() or valid is null) and deleted_at is null) or a.expiry is null)) as ValidPoint,
(SELECT sum( a.point ) AS point 
FROM points a
WHERE a.user_id = 1 and a.status = 1 and a.deleted_at is null and (a.valid < now() or a.valid is null)) as DiffPoint

原本我就是判斷ValidPoint與DiffPoint哪個數字小，哪個就是剩餘點數。

SELECT max(a.expiry) as MaxExpiry
FROM points a
WHERE a.user_id = 1 and a.status = 1 and (a.valid < now() or a.valid is null) and a.deleted_at is null

原本用這個SQL在頁面上呈現過期日。

原本想說DiffPoint等於不考慮是否過期，直接正負相加的結果，因為原本想說購買必定大於扣點，不然無法消費。
然後用ValidPoint直接無腦判斷剩餘沒過期的點數，如果手上的有效點數低於DiffPoint，代表就剩這些點數了。

但依據上面提供的資料，就發現這個算法有誤，無法正確算出剩餘點數，
已經導致user超額消費了最上面的22點那筆。
(理論上那個當下應該是剩3點，點數不足不能購買22點的商品)

小的才疏學淺，還請各位大大幫忙指點迷津，感謝
　
　
　
　
2022/11/21 更新
我使用資料庫的Stored Procedure與function
寫了
CalculatePoints　　　立即重算某個user的點數，結束後並return GetPoint 的結果
CheckAllUserPoint　　依據users表，依序重算每個人的點數
GetPoint　　　　　　　取的某個user的點數(不重算)
三隻

提供以後有人遇到該問題的方向給大家參考

https://dbfiddle.uk/UTEsI4TL

海綿寶寶 iT邦大神 1 級 ‧ 2022-11-03 10:35:23 檢舉

進銷存的進階版本(是否需依有效期結束FIFO?)
我不會，追蹤等看高手解答

窮嘶發發發 iT邦高手 1 級 ‧ 2022-11-03 14:35:51 檢舉

我印象中 LINE POINT
是上次獲點假如180天沒有在獲點會全部刪除

因此我建議你多一個表紀錄最後獲點的時間
然後建立一個SQL程序自動檢查這個時間進行刪除的動作，
但為了避免糾紛，最好刪除前幾天要有一個提醒的動作給USER會比較好

大致上這樣

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

純真的人

iT邦大師 1 級 ‧ 2022-11-02 22:30:05

最佳解答

恩..關於點數~
我都是每次異動就會update紀錄在一個欄位(目前點數)~
原因當歷史累積可觀的筆數後~查詢速度整個掉下來@@...

哈~先幫你建檔~

create table points(
	id int
	,user_id int
	,point int
	,memo VARCHAR(50)
	,isGift int
	,status int
	,valid date
	,expiry date
	,deleted_at datetime
	,created_time datetime
);

insert into points
values(1,1,75,'舊點數轉移',0,1,'2018/10/10','2020/3/1',null,'2018/10/10 13:50')
  ,(2,1,2,'舊點數轉移(贈送)',1,1,'2018/10/10','2020/3/1',null,'2018/10/10 13:50')
  ,(3,1,-11,'消費扣點',0,1,null,null,null,'2018/4/11 13:50')
  ,(4,1,-11,'消費扣點',0,1,null,null,null,'2018/5/12 13:50')
  ,(5,1,-11,'消費扣點',0,1,null,null,null,'2018/6/13 13:50')
  ,(6,1,-11,'消費扣點',0,1,null,null,null,'2018/7/14 13:50')
  ,(7,1,50,'購買點數',0,1,'2019/8/2','2020/8/1',null,'2019/8/1 13:50')
  ,(8,1,2,'交易贈送',1,1,'2019/8/2','2020/8/1',null,'2019/8/1 13:50')
  ,(9,1,-11,'消費扣點',0,1,null,null,null,'2019/9/11 13:50')
  ,(10,1,-11,'消費扣點',0,1,null,null,null,'2019/10/12 13:50')
  ,(11,1,-11,'消費扣點',0,1,null,null,null,'2019/11/13 13:50')
  ,(12,1,-11,'消費扣點',0,1,null,null,null,'2019/12/14 13:50')
  ,(13,1,25,'購買點數',0,0,null,null,null,'2022/8/1 13:50')
  ,(14,1,25,'購買點數',0,0,null,null,null,'2022/8/1 13:50')
  ,(15,1,25,'購買點數',0,1,'2022/10/10','2023/10/10',null,'2022/10/10 13:50')
  ,(16,1,-22,'消費扣點',0,1,null,null,null,'2022/10/14 13:50')
  ;

https://dbfiddle.uk/L6h43hS1

若採用查詢大概會是這樣~

select Minvalid
,Maxexpiry
,TotalPoints
,UsePoints
,TotalPoints + UsePoints RunPoints
from (
  select *
  ,(
    select sum(point)
    from points b
    WHERE b.user_id = 1
    and b.status = 1
    and created_time between Minvalid and Maxexpiry
    and b.deleted_at is null
    and b.point < 0
  ) UsePoints
  from (
    SELECT sum(point) TotalPoints
    ,min(a.valid) as Minvalid
    ,max(a.expiry) Maxexpiry
    FROM points a
    WHERE a.user_id = 1 
    and a.status = 1 
    and now() between a.valid and a.expiry
    and a.deleted_at is null
  ) k
) k

https://dbfiddle.uk/Y8A4BDc6

若加上展期資料表~會是這樣~

select Minvalid
,Maxexpiry
,TotalPoints
,UsePoints
,TotalPoints + UsePoints RunPoints
from (
  select *
  ,(
    select sum(point)
    from points b
    WHERE b.user_id = 1
    and b.status = 1
    and created_time between Minvalid and Maxexpiry
    and b.deleted_at is null
    and b.point < 0
  ) UsePoints
  from (
    SELECT sum(point) TotalPoints
    ,min(a.valid) as Minvalid
    ,max(if(new_date > a.expiry,new_date,a.expiry)) Maxexpiry
    FROM points a
    left join Extend_points on points_id = id
    WHERE a.user_id = 1 
    and a.status = 1 
    and now() between a.valid and a.expiry
    and a.deleted_at is null
  ) k
) k

https://dbfiddle.uk/qk75r1tl

回應 17
分享
檢舉

看更多先前的回應...收起先前的回應...

biscuit iT邦新手 5 級 ‧ 2022-11-02 23:08:14 檢舉

可是點數過期的部分，請問您怎麼處理呢?

純真的人 iT邦大師 1 級 ‧ 2022-11-02 23:22:11 檢舉

我只有點數異動(更新
當日點數第一次查詢(更新，標記今日已執行計算
所以點數過期~會在第一次查詢點數時，計算處理掉~
但看你的方式啦~如果你覺得應該只有幾萬筆的話就用查詢~
否則資料表超過10萬筆就整個掉速了(指的是所有人的點數明細資料)
+
其實銀行的紅利點數~是每個月計算一次~異動也會計算~
會計算扣掉逾期點數~

powerc iT邦研究生 5 級 ‧ 2022-11-03 10:00:24 檢舉

過來人經歷，我做過某個系統的點數也是利用紀錄統計方式，user一多時間一拉長，整個速度就會受明顯影響

純真的人 iT邦大師 1 級 ‧ 2022-11-03 11:30:57 檢舉

你的消費扣點~怎麼沒扣點日期?這樣怎麼知道什麼時候扣掉的?

若沒有日期問題~採先進先出扣點~會是以下變化
75 > 75 / +75
2 > 77 / +75,+2
-11 > 66 / +64,+2
-11 > 55 / +53,+2
-11 > 44 / +42,+2
-11 > 33 / +31,+2
+50 > 83 / +31,+2,+50
+2 > 85 / +31,+2,+50,+2
-11 > 74 / +20,+2,+50,+2
-11 > 63 / +9,+2,+50,+2
-11 > 52 / +50,+2
-11 > 41 / +39,+2
+25 > 66 / +39,+2,+25
+25 > 91 / +39,+2,+25,+25
-22 > 69 / +17,+2,+25,+25
-22 > 47 / +22,+25

biscuit iT邦新手 5 級 ‧ 2022-11-03 15:08:27 檢舉

每個資料都有，created time、updated time，異動者是誰等...資料，因為都列上來太冗長了，所以我就沒列出了請見諒XD

問題就在於這個先進先出的程式化問題很複雜XD
所以詢問大大們，怎麼處理這塊
這複雜的計算，小的還是太菜了
自己沒法想到比較聰明的寫法XD

提醒ID 13、14 因為status=0，所以未生效

純真的人 iT邦大師 1 級 ‧ 2022-11-04 08:05:37 檢舉

恩~因為我不知道你還會需要加什麼的判斷~
我大致寫了一下~我的查詢~你看一下吧
SQL如更新在問答~

johncoc iT邦新手 3 級 ‧ 2022-11-04 10:04:17 檢舉

這個查詢看起來沒考慮展期

純真的人 iT邦大師 1 級 ‧ 2022-11-04 10:31:34 檢舉

johncoc哈~本來就是就事論事了~
我也說過還有什麼條件~你自己加囉
你要加展期就加進去咩~

johncoc iT邦新手 3 級 ‧ 2022-11-04 10:40:13 檢舉

純真的人他題目有寫展期

純真的人 iT邦大師 1 級 ‧ 2022-11-04 11:20:42 檢舉

johncoc
展期~我在看了一下~的確有寫展期吧
展期我會這樣寫~(更新如回答)

select Minvalid
,Maxexpiry
,TotalPoints
,UsePoints
,TotalPoints + UsePoints RunPoints
from (
  select *
  ,(
    select sum(point)
    from points b
    WHERE b.user_id = 1
    and b.status = 1
    and created_time between Minvalid and Maxexpiry
    and b.deleted_at is null
    and b.point < 0
  ) UsePoints
  from (
    SELECT sum(point) TotalPoints
    ,min(a.valid) as Minvalid
    ,max(if(new_date > a.expiry,new_date,a.expiry)) Maxexpiry
    FROM points a
    left join Extend_points on points_id = id
    WHERE a.user_id = 1 
    and a.status = 1 
    and now() between a.valid and a.expiry
    and a.deleted_at is null
  ) k
) k

johncoc iT邦新手 3 級 ‧ 2022-11-04 11:33:48 檢舉

純真的人 a.expire 可能要換成 points.expire

純真的人 iT邦大師 1 級 ‧ 2022-11-04 11:36:30 檢舉

johncoc
這個就要看版主的資料表是如何設計了@@..
我這個只是個SQL大概示意而已~

johncoc iT邦新手 3 級 ‧ 2022-11-04 11:41:01 檢舉

如果是先扣贈送點數的話,這查詢會有問題
購買 60 2022/10/23-2022/11/06
消費 -33 2022/10/24
贈送 3 2022/10/22-2022/10/25
購買 70 2022/10/22-2022/10/25
照上面的查尋會變成剩餘97,正解應該是100

biscuit iT邦新手 5 級 ‧ 2022-11-04 14:55:00 檢舉

純真的人
抱歉~~
原本是直接用這段

SELECT max(a.expiry) as MaxExpiry
FROM points a
WHERE a.user_id = 1 and a.status = 1 and (a.valid < now() or a.valid is null) and a.deleted_at is null

直接判斷是否，已經過期沒購買
但看來不是一個好解...

真的很感謝您幫我補上這麼多東西(跪謝

johncoc大大真是太神了!
的確還有一條要優先消耗[購買點數] 才消耗[贈送點數]
[購買點數] 有買新點數可自動展期
[贈送點數] 不展期
不過這規則我忘了KEY出來，不過我是直接忽視這規則，規則太多實在太複雜了XD
而且好像也不太會影響，原本想說只要在使用當下，計算出來的餘額點數不要錯，每次消費都是正確的(沒有消費到透支)，那麼這條規則應該不會影響到整體，因為使用點數時，代表這些點數都沒過期，不過目前的狀況是計算點數出現BUG了...
原本想出來的解不夠細...