我如何跟踪“条纹”在数据库中？
在Stack Overflow上，您可以赚取一些基于条纹的徽章，从那些徽章中，您可以想象一些跟踪条纹的用例：
- 每天访问该网站30天。
- 连续7天对每天30个职位进行投票。
- 等等..
您如何在任何数据库中跟踪和实施它？ （哪一个，sql，
”难题/问题是，您是在cron/backindback工作/任务中还是在数据库（例如每个页面视图或投票）中执行此操作？
第二个困难问题是，根据先前的解决方案，您如何有效地查询数据库状态以确定是否满足条件，并且如何在新计算中不计算先前的“对象动作”？
因此，让我们专注于一票，因为这要复杂一些。说我们正在跟踪“ UTC日”。
如果我们使用“检查每个数据库更改”解决方案（第1部分），则在下次投票中，在保存到DB之后，请查询自该用户的UTC日期以来的所有票数。如果投票计数＆gt; = 30，请创建一个新的“连胜”记录，并且确实算作1。然后在下一次投票（同一天），我们以某种方式需要知道我们已经对其进行了计数，嗯，因此需要修改该方法。因此，也许我们在当天跟踪“ last_vote_id”，因此我们有（伪代码）：
table vote_streaks {
date last_date;
int last_vote_id;
int last_date_count;
int total_streak_count;
}
然后在下一个投票 /同一天，我们检查last_date < / code < / code>我们跟踪了投票（今天），然后，因此我们将其重置 last_date_count 在将其设置为1之前。那可能起作用吗？我不能说。last_date_count（假设我们目前有30票），总条纹为1，我们知道我们应该忽略当天的第31票。然后在第2天，last_date今天不匹配
如果我们采用执行CRON/背景作业的方法，那么我们将在UTC日开始的某个时候向每个用户查询所有投票（限制30）。如果我们可以从中构建一条连胜（这更简单地做），那么我们就可以获得条纹。但是，如果某个问题发生在工作中间，并且必须重新启动，该怎么办？我无法想象如何解决这个问题，但是它似乎比其他实时方法要复杂得多。
这通常如何解决？同样，不需要进入实际SQL/NOSQL表的杂草（除非您需要，伪代码很好）。或者，如果您知道特定的解决方案（例如如何堆栈;溢出实现它），则与其进行一般解决方案，这也可以。
On Stack Overflow, you can earn a few badges which are based on streaks, and from those you can imagine some other use cases of tracking streaks:
- Visit the site every day for 30 days.
- Vote on 30 posts per day, for 7 days in a row.
- Etc..
How would you keep track of and implement this in any kind of database? (It doesn't really matter which one, SQL, NoSQL, etc..)
The first hard question/problem is, do you do it in cron/background jobs/tasks, or on every change to the database (every page view or vote, for example)?
The second hard problem is, depending on the previous solution, how do you somewhat efficiently query the state of the database to figure out if the conditions are met, and how do you not count previous "object actions" in your new calculation?
So let's focus on the votes one, since that is a little more complicated. Say we are tracking "UTC days".
If we go with the "check on every database change" solution (to part 1), then on next vote, after save to DB, query all votes since past UTC date for that user. If vote count >= 30, create a new "streak" record and do count as 1. Then on next vote (same day), we somehow need to know we already counted it, hmm, so need to modify the approach. So maybe we track "last_vote_id" for the current day, so we have (pseudocode):
table vote_streaks {
date last_date;
int last_vote_id;
int last_date_count;
int total_streak_count;
}
Then on the next vote / same day, we check when the last_date we tracked the vote (today), and the last_date_count (say we had 30 votes at this point), and the total streak is 1, we know that we should ignore the 31st vote for that day. Then on day 2, last_date doesn't match today, so we reset last_date_count to 0 before we set it to 1. I think something like that might work? I can't quite tell.
If we went with the approach to do a cron/background job, then we would query all votes (limit 30) for each user, sometime after the start of the UTC day. If we can build a streak out of that (that is more straightforward to do), then we can get the streak. But what if some problem occurs in the middle of the job and it cuts out, and has to restart? I can't imagine how to solve this yet, but it seems way more complicated than the other real-time approach.
How is this generally solved? Again, don't need to get into the weeds of the actual SQL/NoSQL tables (unless you'd like, pseudocode is fine). Or instead of a general solution, if you know of a specific solution (like how Stack Overflow implements it), that would work too.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要跟踪此处的最小数据将是用户最新操作和当前条纹计数的日期。
当用户执行条纹操作时，您需要检查最新操作的日期。如果最近的动作发生在不到一天之内，但现在已经是不同的一天，那么您就会增加条纹计数。如果超过两天，您将条纹计数重置为零；否则，您在检查条纹时会更新最新的动作时间戳
，则需要检查最新的动作时间戳。如果时间戳是最后一天的最后更新，则条纹数量是有效的，否则实际条纹计数确实为零。
另外，您只需简单地执行文档数据库，然后根据事务日志进行完整的条纹计算。对于长条纹用户来说，这将变得昂贵，尽管这将是最简单的实施。取决于您期望人们长期条纹的频率，这可能是可以接受的，也可能是不可接受的。
The smallest data you need to keep track of here is going to be the date of the most recent action by the user and the current streak count.
When the user done the streaked action, you need to check the date of the most recent action. If the most recent action happens within less than a day but now is already a different day, you increment the streak count; if it's further than two days, you reset the streak count to zero; otherwise you update the most recent action timestamp
When checking for streaks, you need to check for the most recent action timestamp as well. If the timestamp was last updated within the last day, the streak count is valid, otherwise the real streak count is really zero.
Alternatively, you can just simply do a document database and do the full streak calculations based on transaction logs. This will get expensive for long streak users, though it will be the simplest to implement. Depending on how often you expect people to have long streaks, this might or might not be acceptable.