I have a platform that has a check-in function. I need to determine how many consecutive days this user has checked in on this platform and give him different points.
For example, today is April 26th, and I have signed in every day since April 15th. That means I have signed in for 12 consecutive days. I also signed in on April 13th, but did not sign in on April 14th.
mysql statement How to write it
PHP中文网2017-05-16 13:09:47
Generally, the number of days of continuous check-in is stored. If it is not stored, one way we have done before is to store the check-in with a timestamp and then get the timestamp of the check-in. Generally, we get it for one month (if this month When the sign-in is full, get the previous month's number), and then loop through the number of days in PHP to calculate it. Later, this method was slower, so we recorded the check-in days separately
大家讲道理2017-05-16 13:09:47
Database design issues.
Because it is necessary to count consecutive days, it is impossible to record one item every day (imagine a bank recording the user's balance for any number of days).
The accounting method is called the annual accumulation algorithm. That is, recording a certain point in time until another time. The balance at one point in time is unchanged (continuous)
The table design is roughly as follows:
End time-start time = number of days. Points conversion is just like tax, just calculate the bonus.
大家讲道理2017-05-16 13:09:47
In your business rules, there should be an upper limit on how many points will be given for x consecutive days. For example, how many points will be given if there are >= 20 consecutive days.
Then let’s remember this x, in db select * from tb where uid = 10086 order by dt desc limit x
Then judge the maximum number of consecutive days in the program and add the corresponding score.
