Oracle group statistics by time period

Select (sysdate-13)-(level-1) 4 fromdualconnectbylevellt; 34 -- starting from the first time record (sysdate-13) as the earliest date in the table

Select (sysdate-13)-(level-1)/4 from dual connect by levellt; = 34 -- starting from the first time record (sysdate-13) as the earliest date in the table

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:

--- 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:

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:

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

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 -- starting from the first time record (sysdate-13) as 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

D. dt-1/4
Group by mobile_no, area_name, d. dt