SQL row_number在一个列上有一个条件

Question

我想用case条件配置row_number。查看"time_diffs"列一下，检查一下，如果1是一个接一个的，那么它是一组，数字应该在前面的1之后重复。如果有0，那么每个0都是一组，数字不会重复-在每一个0之后，它会以+1的方式增长。当迭代器遇到新的1时，在0之间进行操作后，它不会重置计数。它将继续计数，0之后+1，但使用上面描述的逻辑。

下面列出的查询和结果示例。

select session_id, 
player_id, 
country, 
start_time, 
end_time,       
case when timestampdiff(minute, 
lag(end_time, 1) over(partition by player_id order by end_time)
, start_time) < 5 then 1
when timestampdiff(minute, end_time
, lead(start_time, 1) over(partition by player_id order by start_time)) < 5 then 1
else 0
end as time_diffs
/* , here is a new code with an expected result */

from game_sessions
where 1=1
and player_id = 1
order by player_id, start_time

当前查询的结果:

<表类>session_idplayer_id国家start_timeend_timetime_diffstbody><<tr>11英国01.01.2021 00:0101.01.2021 00:10121英国01.01.2021 00:1201.01.2021 01:241131英国01.01.2021与01.01.2021 01:50131英国01.01.2021远15 01.01.20210161英国01.01.2021十七1001.01.2021十七201171英国01.01.2021 17:2201.01.2021 17:551541英国01.01.2021十八1501.01.2021 18:350321英国01.01.2021 18:5501.01.2021 19:350

不能让ROW_NUMBER不为ROW_NUMBER。但是您可以使用SUM() OVER ()来累计计数。

那么，首先为"this is start of a new group"创建一个标志。(距上一行超过5分钟)，并按顺序汇总。

(我删除了时间差列，因为它是过度设计的，并且在当前形式中不需要。)

WITH
diffs AS
(
select
session_id, 
player_id, 
country, 
start_time, 
end_time,
/* here is a new code with an expected result */
LAG(end_time)
OVER (
PARTITION BY player_id
ORDER BY start_time
)
AS prev_end_time
from
game_sessions
)
SELECT
*,
SUM(
IF(start_time < prev_end_time + INTERVAL '5' MINUTE, 0, 1)
)
OVER (
PARTITION BY player_id
ORDER BY start_time
)
AS expected_result
FROM
diffs
WHERE
1=1
AND player_id = 1
ORDER BY
player_id,
start_time

演示:https://dbfiddle.uk/sJcWInTN

(注意，对于将来的问题，请使用ISO-8601标准符号表示日期和时间，这使得将数据插入表中更容易，更可靠等)