php - 趋势 SQL 查询

Question

所以我想做的是制作一个趋势算法，我需要有关 SQL 代码的帮助，因为我无法让它运行。

该算法包含三个方面:(我对更好的趋势算法的想法完全开放)

1.Plays during 24h / Total plays of the song
2.Plays during 7d / Total plays of the song 
3.Plays during 24h / The value of plays of the most played item over 24h (whatever item leads the play count over 24h)

每个方面的值(value)为 0.33，最大值为 1.0 是可能的。

第三个方面是必要的，因为新上传的项目将自动位于顶部，除非他们有办法将它们下拉。

该表名为 aud_plays，列为:

PlayID: Just an auto-incrementing ID for the table
AID: The id of the song
IP: ip address of the user listening
time: UNIX time code

我已经尝试了一些 sql 代码，但我非常卡住无法让它工作。

Answer 1

在你的？aud_songs？ (AID 指向的那个)表添加以下列

• Last24hrPlays INT——如果您计划获得十亿以上，请使用 BIGINT
• Last7dPlays INT
• TotalPlays INT

在您的 aud_plays 表中创建一个 AFTER INSERT 触发器，它将增加 aud_song.TotalPlays。

UPDATE aud_song SET TotalPlays = TotalPlays + 1 WHERE id = INSERTED.aid

实时计算每个请求的趋势会对您的服务器造成负担，因此最好每 5 分钟运行一次作业来更新数据。因此，创建一个 SQL 代理作业以每 X 分钟运行一次以更新 Last7dPlays 和 Last24hrPlays。

UPDATE aud_songs SET Last7dPlays = (SELECT COUNT(*) FROM aud_plays WHERE aud_plays.aid = aud_songs.id AND aud_plays.time BETWEEN GetDate()-7 AND GetDate()), 
    Last24hrPlays = (SELECT COUNT(*) FROM aud_plays WHERE aud_plays.aid = aud_songs.id AND aud_plays.time BETWEEN GetDate()-1 AND GetDate())

我还建议从 aud_plays 中删除旧记录(可能早于 7 天，因为您将拥有 TotalPlays 触发器。

应该很容易弄清楚如何计算 1 和 2(根据问题)。这是 3 的 SQL。

SELECT cast(Last24hrPlays as float) / (SELECT MAX(Last24hrPlays) FROM aud_songs) FROM aud_songs WHERE aud_songs.id = @ID

注意我使 T-SQL 非常通用且未优化，以说明该过程的工作原理。