对数据库行中的匹配条目进行计数,除非该行为空
问题描述:
计算一行中的连续(特定)匹配的entrys,但是如果其他数据位于其间,则从头开始计数。对数据库行中的匹配条目进行计数,除非该行为空
我的想法是查看某人在某个时间段内进行了多少次登录尝试(在我的情况下为15分钟)并相应采取行动。例如(简化表格):
Row: User_id | Loginstatus | Timestamp
5 Attempt most recent
5 Attempt .
5 Logoff .
5 Login .
5 Attempt .
5 Attempt .
5 Attempt .
5 Attempt .
5 Logoff .
5 Login .
5 Logoff .
5 Login .
我选择现在应出示:2
我走到这一步:
select count(Loginstatus) Result from Userlog
where User_Id = 5
and Loginstatus = 'ATTEMPT'
and Timestamp > Systimestamp - Interval '15' Minute;
但不幸的是计算所有的尝试。我很高兴的任何帮助,并感谢您提前
答
您可以使用行号的差异来确定相邻序列:
select user_id, count(*) as num_consecutive
from (select ul.*,
row_number() over (partition by user_id order by timestamp) as seqnum_u,
row_number() over (partition by user_id, loginstatus order by timestamp) as seqnum_us
from Userlog ul
where User_Id = 5 and Timestamp > Systimestamp - Interval '15' Minute;
) ul
where Loginstatus = 'ATTEMPT'
group by user_id, (seqnum_u - seqnum_us);
如果你想要一个值,你可以添加:
order by num_consecutive desc
fetch first 1 row only
+0
完美!非常感谢你的亲切先生:) –
答
您需要使用GROUP BY:
SELECT [LoginStatus]=MAX(Loginstatus), [Count]=count(Loginstatus)
FROM Userlog
WHERE User_Id = 5
AND Loginstatus = 'ATTEMPT'
AND Timestamp > Systimestamp - Interval '15' Minute;
GROUP BY (Loginstatus)
用你正在使用的数据库标记你的问题。 –
对不起,我忘了提及它。感谢您的提示 –