累积总和与SQLite中的条件

发布于 2025-02-13 08:14:38 字数 1935 浏览 1 评论 0原文

我有一个看起来像下面的数据集，我需要知道哪些歌曲是最顺序播放的（我正在使用SQLite）。

SELECT endtime, trackname FROM streaming_history AS sh
JOIN albums AS a ON a.albumid = sh.albumid
JOIN tracks AS t ON t.trackid = sh.trackid

末日	踪迹名称
2022-03-21 15:48:00	Walk
2022-03-21 15:50:00	Walk
2022-03-21 15:54:00	最后一口气
2022-03-21 15:57:00	突破
2022-- 03-21 16:02:00	外部
2022-03-21 16:07:00	起义
2022-03-21 16:12:00	步行
2022-03-21 16:16:00	最后一口气
2022-03-21 16:20:00	突破
2022-03-21 16:36:00	外部
2022-03-22 003-22 00:10:00	1022-03--03--
： 22 10:53:00	在外面

，因为我不得不知道哪些歌曲最多，而是播放了哪首歌，这是最简单的，与Group by to note by by by by by by by by by by by by by by by by by by by。从上面的数据提取物中，外部将以三次（而不是四次）排名第一，第二名是两次。

我设法创建了另一列的标记，上一首播放的歌曲是否与带有滞后和案例的当前行歌曲相同，但似乎无法弄清楚如何使用新列的最后一列的最后一个行信息。

原文

I have a dataset that looks like the one below and I need to know which songs were played in sequence the most (I am using SQLite).

SELECT endtime, trackname FROM streaming_history AS sh
JOIN albums AS a ON a.albumid = sh.albumid
JOIN tracks AS t ON t.trackid = sh.trackid

endtime	trackname
2022-03-21 15:48:00	Walk
2022-03-21 15:50:00	Walk
2022-03-21 15:54:00	One Last Breath
2022-03-21 15:57:00	Breakout
2022-03-21 16:02:00	Outside
2022-03-21 16:07:00	Uprising
2022-03-21 16:12:00	Walk
2022-03-21 16:16:00	One Last Breath
2022-03-21 16:20:00	Breakout
2022-03-21 16:36:00	Outside
2022-03-22 00:10:00	Outside
2022-03-22 10:53:00	Outside

Since I have to know not which songs were played the most, but which song were played the most in sequence, a simple count with group by won't do. From the data extract above, Outside would be first place with three times (not four) and Walk second place with two times.

I managed to create another column flagging whether or not the last played song was the same as the current row song with LAG and CASE, but can't seem to be able to figure out how to create a column to sum using the new column's last row info.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笨死的猪 2025-02-20 08:14:38

使用sum（）窗口函数以获取歌曲序列，通过将您使用lag（） and gentregate创建的标志求和。
然后使用rank（）窗口函数获取顶级排序歌曲：

WITH cte AS (
  SELECT trackid, count
  FROM (
    SELECT MAX(trackid) trackid, 
           COUNT(*) count,
           RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM (
      SELECT *, SUM(flag) OVER (ORDER BY endtime) grp
      FROM (
        SELECT *, trackid <> LAG(trackid, 1, 0) OVER (ORDER BY endtime) flag
        FROM streaming_history
      )
    )
    GROUP BY grp
  )
  WHERE rnk = 1
)
SELECT t.trackname, c.count
FROM tracks t INNER JOIN cte c
ON c.trackid = t.trackid;

请参阅 demo 。

Use SUM() window function to get the groups of sequences of songs, by summing the flag that you created with LAG() and aggregate.
Then use RANK() window function to get the top sequenced song(s):

WITH cte AS (
  SELECT trackid, count
  FROM (
    SELECT MAX(trackid) trackid, 
           COUNT(*) count,
           RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM (
      SELECT *, SUM(flag) OVER (ORDER BY endtime) grp
      FROM (
        SELECT *, trackid <> LAG(trackid, 1, 0) OVER (ORDER BY endtime) flag
        FROM streaming_history
      )
    )
    GROUP BY grp
  )
  WHERE rnk = 1
)
SELECT t.trackname, c.count
FROM tracks t INNER JOIN cte c
ON c.trackid = t.trackid;

See the demo.

回复收藏 0 原文

~没有更多了~