Oracle computing continuous login/working days

Oracle computing continuous login/working days

Now there is a report that calculates the number of working days of a user. It is found that the row_number analysis function can perfectly calculate this problem. This SQL statement can solve problems such as continuous login, sign-in, work, and absenteeism of computing users.

First, sort row_number by date.

Extract 1 from a date, such as 2016-7-1, and convert it into a number.

Calculate the value of this date number minus row_number and use it as the group number, because the group numbers calculated by the discontinuous values are inconsistent;

Perform group by based on the group number field to calculate the start time, end time, and number of days of each continuous shift.

Such as the original data
2016/7/1
2016/7/2
2016/7/4
2016/7/5
2016/7/6
2016/7/7
......

The calculated result is:
Group number start time end time days
0 2016/7/1 2016/7/2 2
1 2016/7/4 2016/7/9 6
......

The code below
With t1
(
Select date '2014-7-1 'd1 from dual
Union
Select date '2014-7-2 'd1 from dual
Union
Select date '2014-7-4 'd1 from dual
Union
Select date '2014-7-5 'd1 from dual
Union
Select date '2014-7-6 'd1 from dual
Union
Select date '2014-7-7' d1 from dual
Union
Select date '2014-7-8 'd1 from dual
Union
Select date '2014-7-9 'd1 from dual
Union
Select date '2014-7-11 'd1 from dual
Union
Select date '2014-7-12 'd1 from dual
Union
Select date '2014-7-13 'd1 from dual
Union
Select date '2014-7-14 'd1 from dual
Union
Select date '2014-7-18 'd1 from dual
Union
Select date '2014-7-19 'd1 from dual
)
Select gn, min (d1), max (d1), count (*)
From (
Select d1, to_number (to_char (d1, 'dd')-row_number () over (order by d1) gn from t1
) Group by gn order by 2