在一系列操作sql中找到最后的停止时间。

Question

有一个玩家将播放一个视频。用户可以启动或停止视频。

每次用户播放视频时，它都是一个video_click_id。我想找出第一站的第一站的系列结束的视频。如果video_click_id以一站结尾，则得到最后一站。如果video_click_id以两个停止结尾，则得到第二个最后一站。如果video_click_id以3停止结尾，则得到最后一站。

   id          video_click_id action_timestamp          action
   ----------- ------------- -----------------------   ------
   3           1             2016-05-19 13:59:07.437   play
   4           1             2016-05-19 13:59:13.413   stop
   6           1             2016-05-19 15:59:40.213   stop
   8           2             2016-05-19 16:29:11.950   play
   9           2             2016-05-19 16:29:27.090   stop
   10          2             2016-05-19 16:29:28.000   play
   12          2             2016-05-19 16:29:29.390   stop
   13          3             2016-05-19 20:59:07.437   play
   14          3             2016-05-19 20:59:13.413   stop
   15          3             2016-05-19 20:59:40.213   stop
   26          3             2016-05-19 20:59:40.213   stop

1. 在本例中，video_click_id 1的结果id为4。
2. video_click_id 2的结果id为12。
3. video_click_id 3的结果id为14。

有谁可以帮我？我正在使用mssql。

提前感谢！

Answer 1

在下面的查询中，子查询为每个stop记录分配一个行号。然后，外部查询保留每个video_click_id组的第一个停止记录。

SELECT
    t.id,
    t.video_click_id,
    t.action_timestamp,
    t.action
FROM
(
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY video_click_id ORDER BY action_timestamp) rn
    FROM yourTable
    WHERE action = 'stop'
) t
WHERE t.rn = 1

输出：

​

​

演示这里：

​

雷克斯试验器

​

Answer 2

您可以使用not exists检索没有播放的所有停止。然后，您可以使用一个group by来选择停止后没有播放的最早的id：

select min(id), video_click_id
from mytable t1
where action = 'stop'
and not exists ( -- subquery ensures that no 'play' occurs after 'stop'
    select 1 from mytable t2
    where t2.video_click_id = t1.video_click_id
    and t2.action = 'play'
    and t2.id > t1.id
) group by video_click_id

Answer 3

假设id是增量的，您可以对所需的输出使用以下简单查询-

SELECT MAX(id) AS id, video_click_id
FROM
(
    SELECT *
     , LAG(action) OVER(PARTITION BY video_click_id ORDER BY action_timestamp) AS prev_action
    FROM MyTable
) AS p
WHERE action = 'stop' AND prev_action = 'play'
GROUP BY video_click_id;

子查询查找每个操作的前一个操作。我们希望“停止”动作在最后一次“播放”动作之后立即停止。因此，选择id的最大值将得到视频实际停止的id。

SqlFiddle演示