Oracle Database grouping statistics by time, oracle statistics

Oracle Database grouping statistics by time, oracle statistics

SQL statement used by Oracle to group statistics by Time
Table 1 is as follows:

Number of days (exportDate) (amount) -------------- ----------- 14-2 month-08 20-3 month-08 2 14-4 month-08 6-6 month-08 75 24-10 month-09 23-11 month-09 45 04-8 month-10 5 04-9 month- 10 44 months-10 88

Note: For more intuitive display, the following queries are sorted by corresponding groups.

1. Group by year

select to_char(exportDate,'yyyy'),sum(amount) from table1 group by to_char(exportDate,'yyyy'); 

Number of years ----------------------------- 2009 68 2010 137 2008

2. Group 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 20 2008-03 2 2008-04 6 2008-06 75 2009-10 23 2009-11 45 2010-08 5 2010-09 44 2010-10 88

3. Group 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'); 

Quarterly quantity ------------------------------ 2008-1 22 2008-2 81 2009-4 68 2010-3 49 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 20 2008-11 2 2008-16 6 2008-24 75 2009-43 23 2009-46 45 2010-31 5 2010-35 44 2010-40 88

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

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

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 dt from dual connect by level <= 10) d where i.satisfy_degree='satisfy_1' and i.insert_time<dt and i.insert_time> d.dt-2 group 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 ,(select (sysdate-13)-(level-1)/4 dtfrom dual connect by level <= 34) dwhere i.locate_type = 1 andi.created_time<dt and i.created_time> d.dt-1/4group by mobile_no,area_name,d.dt

 
Another method:

-- Grouping by six hours select 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) -- group by 12 hours select 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)