COUNT(DISTINCT punch_time) AS consecutive_days FROM ( SELECT name, punch_time, SUM(is_start_of_group) OVER (PARTITION BY name ORDER BY punch_time) AS grp FROM GroupedData ) GroupedDataWithGroups GROUP BY name, grp ORDER BY name, start_date; 执行结果 分析: 当我们要查找每个人的连续打卡情...
在这个查询中,我们使用COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)来计算每个用户在当前行之前的3行是否存在,该窗口函数为每个用户分配了一个连续登录天数的计数值。结果如下: 然后我们将这个子查询作为内部表,并根据consecutive_days为3进行筛选,得到所有...
consecutive_day AS (SELECT user_id, diff, COUNT(diff) AS consecutive_day FROM login_days_diff GROUP BY user_id,diff ) user_id diff consecutive_day 224332 2022/12/11 2 224332 2022/12/19 3 224332 2022/12/21 1 224332 2022/12/24 1 224561 2022/12/1 1 224561 2022/12/6 1 224561 202...
WITH cte AS ( SELECT Date, ROW_NUMBER() OVER (ORDER BY Date) AS rn, DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY Date), Date) AS grp FROM YourTable ) SELECT MIN(Date) AS StartDate, MAX(Date) AS EndDate, COUNT(*) AS ConsecutiveDays FROM cte GROUP BY grp ORDER BY COUNT(*) DES...
Please start any new threads on our new site at . We've got lots of great SQL Server experts to answer whatever question you can come up with. All Forums SQL Server 2000 Forums Transact-SQL (2000) Consecutive Day Count
In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players. The query result format is in the following example: 三、参考SQL 方法一:内连接(统计连续...
selectmax(consecutive_day)from(selectcount(*)(consecutive_dayfrom(selectsum(rise_mark)over(order by trade_date)days_no_gainfrom(select trade_date,casewhen closing_price>lag(closing_price)over(order by trade_date)then0else1ENDrise_mark
但是，很不幸，SQL无法直接描述这个有过程的逻辑（除非用存储过程），于是只能更换思路实现：```select max (consecutive_day)from (select count(*) (consecutive_day from (select sum(rise_mark) over(order by trade_date) days_no_gain from (select trade_date,case when closing_price>lag(closing_price)...
from (select count(*) (consecutive_day from (select sum(rise_mark) over(order by trade_date) days_no_gain from (select trade_date, case when closing_price>lag(closing_price) over(order by trade_date) then 0 else 1 END rise_mark ...
Calculate the maximum number of trading days that a certain stock keeps rising: SELECT max(consecutive_day) FROM (SELECT count(*) consecutive_day FROM (SELECT sum(rise_or_fall) OVER(ORDER BY trade_date) day_no_gain FROM (SELECT trade_date, ...