Oracle group statistics by time period

To group queries by time period, you must first understand the addition and subtraction of level, connect by, and Oracle time.
I will write only one query statement about level:

1.  --- Level is a pseudo-Sample
2.  Select Level FromDualConnect By Level<= 10
3.  --- Result: 1
4. 2
5. 3
6. 4
7. 5
8. 6
9. 7
10. 8
11. 9
12. 10

--- Level is a pseudo-sample select level from dual connect by level <= 10 --- result: 12345678910

For more information about connect by, see

Add or subtract the Oracle time and try the following SQL statement:

1. SelectSysdate-1FromDual
2. ---- The result is reduced by one day, that is, 24 hours.
3. SelectSysdate-(1/2)FromDual
4. ----- The result is less than half a day, that is, 12 hours.
5. SelectSysdate-(1/24)FromDual
6. ----- Result minus 1 hour
7. SelectSysdate-(1/24)/12)FromDual
8. ---- Result minus 5 minutes
9. SelectSydate -(Level-1)FromDualConnect By Level<= 10
10. --- The result is a 10-day interval.

Select sysdate-1 from dual ---- result minus one day, that is, 24 hours select sysdate-(1/2) from dual ----- result minus half day, that is, 12 hours select sysdate-(1/24) from dual ----- result minus 1 hour select sysdate-(1/24)/12) from dual ---- result minus 5 minutes select sydate-(level-1) from dual connect by level <= 10 --- the result is a 10-day interval.

The following is an example:

1. SelectDt,Count(Satisfy_degree)AsNumFromT_DEMO I,
2. (SelectSysdate -(Level-1) * 2 dt
3. FromDualConnect By Level<= 10) d
4. WhereI. satisfy_degree ='Satisfy _ 1' And
5. I. insert_time <dtAndI. insert_time> d. dt-2
6. Group ByD. dt

select dt, count(satisfy_degree) as num from T_DEMO  i ,(select sysdate - (level-1) * 2 dtfrom dual connect by level <= 10) dwhere i.satisfy_degree='satisfy_1' andi.insert_time<dt and i.insert_time> d.dt-2group by d.dt  

In this example, sysdate-(level-1) * 2 returns a two-day interval.
Group by d. dt is the two-day interval group query.

Example:

Create table A_HY_LOCATE1
(
MOBILE_NO VARCHAR2 (32 ),
LOCATE_TYPE NUMBER (4 ),
AREA_NO VARCHAR2 (32 ),
CREATED_TIME DATE,
AREA_NAME VARCHAR2 (512 ),
);

Select (sysdate-13)-(level-1)/4 from dual connect by level <= 34 -- since the first time record (sysdate-13) is the earliest date in the table, number of groups displayed in "34" (the number of groups should be 4 every six hours)

It is grouped every 6 hours.
Select mobile_no, area_name, max (created_time), dt, count (*) as num from a_hy_locate1 I,
(Sysdate-13)-(level-1)/4 dt
From dual connect by level <= 34) d
Where I. locate_type = 1 and
I. created_time <dt and I. created_time> d. dt-1/4
Group by mobile_no, area_name, d. dt