How Oracle Databases group statistics by TIME _oracle

Oracle group statistics by different time SQL
As shown in the following table table1:

Date (exportdate)        Quantity (amount) 
--------------          ----------- 
1 April-February -08 
October-March -08            2 
1 April-April -08            6 
1 April-June -08 
2 April-October -09 
April-August 1 April-November -09 
-10 5 April-September -10 
April-October -10           88 

Note: In order to display more intuitive, the following query has been sorted by the appropriate group

1. Grouped by year

Select To_char (exportdate, ' yyyy '), sum (amount) from Table1 Group by To_char (Exportdate, ' yyyy '); 

Number of years 
----------------------------- 
2009  137 
2008  103 

2. Grouped by month

Select To_char (exportdate, ' yyyy-mm '), sum (amount) from Table1 Group by To_char (Exportdate, ' yyyy-mm ') Order by
To_ char (exportdate, ' yyyy-mm '); 

Number of months 
----------------------------- 
2008-02 
2008-03  2 
2008-04  6 
2008-06 
2009-10 
2009-11 
2010-08  5  2010-09 
2010-10  88 

3. Grouped by quarter

Select To_char (exportdate, ' yyyy-q '), sum (amount) from Table1 Group by To_char (Exportdate, ' yyyy-q ') Order by 
To_ char (exportdate, ' yyyy-q '); 

Number of quarters 
------------------------------ 
2008-1 
2008-2  bayi 
2009-4  68 
2010-3 
2010-4  88 

4. GROUP BY week

Select To_char (exportdate, ' yyyy-iw '), sum (amount) from Table1 Group by To_char (Exportdate, ' yyyy-iw ') Order by
To_ char (exportdate, ' yyyy-iw '); 

Number of weeks 
------------------------------ 
2008-07 
2008-11  2 
2008-16  6 
2008-24 
2009-43 
2009-46 
2010-31  5  2010-35 
2010-40  88

ps:oracle Group statistics by time period
want to query by Time group, first of all to understand the Level,connect by,oracle time of addition and subtraction.
About level here is not much to say, I only write a query statement:

----level is a pseudo example 
 select level from dual connect by level <=10 
 ---results:
1   
2 
3 
4 
5 
6 
7 
8 
9 
10 

Oracle time Plus and minus take a look at the following SQL statement to know:

Select sysdate-1 from dual 
----result minus one day, also 24 hours 
select sysdate-(1/2) from dual 
-----result subtract half-day, also 12 hour 
Select sysdate-(1/24) from dual 
-----results minus 1 hours 
select sysdate-((1/24)/12) from dual  
----result minus 5 minutes 
Select sydate-(LEVEL-1) from dual connect by level<=10 
---result is 10 interval 1 days time

Here is the example:

Select DT, COUNT (satisfy_degree) as Num from T_demo i, 
(select Sysdate-(LEVEL-1) * 2 dt/ 
dual Connect by Level <= D 
where i.satisfy_degree= ' satisfy_1 ' and I.insert_time<dt and 
i.insert_time> d.dt-2 
GROUP BY D.dt  

The example of Sysdate-(LEVEL-1) * 2 Gets a time interval of 2 days
Group BY D.DT is a two-day interval grouping query

Your own implementation examples:

CREATE TABLE A_hy_locate1
(
 mobile_no     VARCHAR2), locate_type number    (4),
 area_no      VARCHAR2 (a),
 created_time    DATE,
 area_name     VARCHAR2 (
);

Select (sysdate-13)-(LEVEL-1)/4 from dual connect by level<=34--starting with the first time record (SYSDATE-13) as the earliest date in the table, "34" The number of groups that appear (grouped by six hours per day should be 4)

is grouped according to every 6 hours

Select Mobile_no,area_name,max (created_time), DT, COUNT (*) as num from a_hy_locate1 i,
(select (sysdate-13)-( LEVEL-1)/4 dt from
dual connect by level <= 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

Another method:

--Group Select trunc by six hours
(To_number (To_char (created_time, ' hh24 '))/6), COUNT (*) from
 t_test
 where created_ Time > Trunc (sysdate-40)
 GROUP by Trunc (To_number (To_char (created_time, ' hh24 '))/6


--group Select in 12 hours
Trunc (To_number (To_char (created_time, ' hh24 '))/6), COUNT (*) from
 t_test
 where Created_time > Trunc ( SYSDATE-40)
 GROUP by Trunc (To_number (To_char (created_time, ' hh24 '))/6)