如何设计“上周热门比赛”与 SQLite 数据库排名？
我有一个播放器软件。它有一个 SQLite 数据库支持。
我是 SQL 新手，我猜如何是进行“热门播放”排名的最佳方式。播放最多的可能来自所有时间、上周、上个月等。
我认为记录所有音乐跟着日期和时间一起玩，然后对范围内的日期进行选择，但看起来很重。
有更好的方法吗？
I have a player software. It have a SQLite database backing it.
I´m a SQL novice, and I´m guessing how could be the best way to do a "Top played" ranking.The top played could be from all time, last week, last month, etc..
I think in logging all music played along with the date and hour, and after do a Select with the date within the range, but it looks very heavy.
There´s a better way to do it ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你的方法是最简单的，所以我会尝试针对大日志测试它，看看它的性能如何。如果效果很好，那就没必要把事情复杂化。
如果它的性能不够好，那么这是一个更复杂的方法：
如果“上个月”是您将显示的最长时间范围（“所有时间”除外），那么您可以有一个日志表，其中仅包含最近的播放月。每次播放歌曲时，将其添加到日志中，然后定期删除一个月以上的记录（每次插入记录时，或每天一次）。这样您的查询将需要处理更少的记录。上周排名仍然需要选择日期范围，但应该更快。
如果这仍然不够好，您可以为每个时间范围（TopLastMonth、TopLastWeek、TopToday 等）创建一个日志表，而不仅仅是一个覆盖最长时间范围的日志表。这样，排名查询将永远不必使用 WHERE 子句，因为它们将始终汇总表中的所有记录。只有 DELETE 查询必须选择日期，并且这些查询的运行频率可以较低。
对于历史排名，您需要一个包含每首歌曲记录的表格。每次播放歌曲时，都会增加“播放次数”字段。您的所有时间排名将查看此表而不是日志。
Your approach is the simplest, so I would try testing it against a large log and see how it performs. If it does well, then no need to complicate things.
If it doesn't perform well enough, then this is a more complicated approach:
If the Last Month is the longest time frame you will display (other than All Time), then you could have a log table that only includes plays from the last month. Every time you play a song, add it to the log, and then regularly delete records older than a month (either every time you insert a record, or once a day). That way your queries will have fewer records to deal with. The Last Week ranking will still have to select on a date range, but it should be faster.
If that still doesn't perform well enough, you could have a log table for each time frame (TopLastMonth, TopLastWeek, TopToday, etc.) instead of just one that stretches over the longest time frame. That way, the ranking queries will never have to use a WHERE clause, because they will always be summarizing all the records in the table. Only the DELETE queries will have to select on the date, and these can be run less frequently.
For the All Time ranking, you will need a table with a record for each song. Every time a song is played, increment the Times Played field. Your ranking for All Time will look at this table instead of the log.