趋势 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)

每个方面的价值均为 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 - 如果您计划获得十亿以上
• Last7dPlays INT
• TotalPlays INT

在 aud_plays 表中创建一个 AFTER INSERT 触发器来增加 aud_song，请使用 BIGINT。 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 非常通用且未经优化，以说明该过程是如何工作的。