查询以查找随时间增长的增长

Question

我有一个stats_by_dates表，其中包含每天为一首歌测量的分数。我怎样才能找到得分增长最快的歌曲呢？

这个表中的列是：id、song_id、date、score。

到目前为止，这就是我所拥有的，但它并不安静，对吧：

select song_id, date, score - coalesce(lag(score) over (partition by song_id order by date desc), 0) as delta
from stats_by_dates
group by song_id, date, score
order by score desc limit 100

这是返回前100首歌曲按乐谱，而不是前100首歌曲的最高增长的歌曲。一旦我做到了这一点，我还想应用这个查询来找到过去3天中上升最快的歌曲。谢谢!

Answer 1

如果我说得对，你需要得到一首歌的第一个分数和最后一个分数，并计算差值(delta)，这将代表随着时间的推移，这个分数是如何变化的。

试试这个：

SELECT DISTINCT 
    song_id, 
    -- the first score of a song
    first_value(score) OVER (PARTITION BY song_id ORDER BY date ASC) as first_score, 
    -- the last score of a song
    first_value(score) OVER (PARTITION BY song_id ORDER BY date DESC) as last_score,
    -- the difference (delta) between the first and the last scores
    (first_value(score) OVER (PARTITION BY song_id ORDER BY date DESC) - first_value(score) OVER (PARTITION BY song_id ORDER BY date ASC)) as delta
FROM stats_by_dates
WHERE date > now() - INTERVAL '3 day' -- get score for the last 3 days
ORDER BY delta DESC LIMIT 100