Oracle ____oracle statistics by year, month, day, week, etc.

In many projects, there will be statistical functions, such as the number of people submitted by the year, month, week and day, or the number of new additions to the specified year, month, week, or day. Recently I contacted a project where the customer asked for monthly and weekly statistics on the amount submitted per unit price increase. statistics by year

Select 
  o.id,
  o.name,
  to_char (a.create_date, ' yyyy '),
  count (*) from the
news a left
join Sys_ User U on u.id = a.create_by left 
join Sys_office o on o.id = u.office_id
GROUP by O.id,o.name,to_char (A.create_da Te, ' yyyy ')

This is to format the creation time into years, grouped by unit ID, and the number of submissions is queried.

Monthly Statistics

The date format above is changed directly to yyyy-mm form.

Select 
  o.id,
  o.name,
  to_char (a.create_date, ' yyyy-mm '),
  count (*) from
News a
left Join Sys_user u on u.id = a.create_by left 
join Sys_office o to O.id = u.office_id
GROUP BY O.id,o.name,to_char ( A.create_date, ' yyyy-mm ')

statistics by week

Weekly statistics can show two effects, one is to show the date of the Monday date, that is, the natural week of the date, the other is the weekly natural week statistics. by Natural Week statistics

Select 
  o.id,
  o.name,
  to_char (a.create_date, ' ww '),
  count (*) from
t_dzjg_news a left
join Sys_user u on u.id = a.create_by left 
join Sys_office o to O.id = u.office_id
GROUP by O.id,o.name,to_char (a.creat E_date, ' ww ')

Here's WW is getting that date in the first weeks of the year.

statistics by date of natural week

This can be very cumbersome to handle, first of all to get the date of the week of the Monday date, and then according to the Week 1st group query:

---Get the date of Monday of the specified date
select Trunc (sysdate, ' WW ') from dual;

Select To_char (trunc (sysdate, ' ww '), ' YYYY-MM-DD ') from dual;

---will create the time format in the form of Monday, and then look for
select 
  o.id,
  to_char (trunc (a.create_date, ' ww '), ' yyyy-mm-dd ') as Monday,
  Count (*) from
t_dzjg_news A-left
join Sys_user u on u.id = a.create_by left 
join Sys_office o on o.id = U.O ffice_id
GROUP BY O.id,to_char (Trunc (a.create_date, ' ww '), ' yyyy-mm-dd ')

Here you use a function trunc (): This function is like an intercept function that intercepts the input data in the specified format. This intercepts the time as a display of the week's form. Statistics by Quarter

In Oracle, Q is the quarter of the time:

Select To_char (sysdate, ' Q ') from dual;--to get the quarter of the current time

So the above SQL can be changed to:

Select 
  o.id,
  to_char (a.create_date, ' Q ') as quarter,
  count (*) from
t_dzjg_news a left
join Sys_user u on U.id = a.create_by left 
join Sys_office o on o.id = u.office_id
GROUP by O.id, To_char (a.create_date, ' Q ')

statistics by day

There's almost nothing to say about this:

Select 
  o.id,
  to_char (a.create_date, ' yyyy-mm-dd ') as date,
  count (*) from
t_dzjg_news a
left Join Sys_user u on u.id = a.create_by left 
join Sys_office o to O.id = u.office_id
GROUP by O.id, To_char (A.crea Te_date, ' Yyyy-mm-dd ')

Summary

For the statistics of database data, these are just simple basics. These days by writing those statistical functions, although the head is large, but the knowledge of the database more and more deep. For writing complex SQL statements, to subdivide the problem, step-by-step query, and finally a step-by-step analysis of the simple query combination into a complex query, can be said to be small to large, avoid from large to smaller. last

About time-used operations in Oracle databases you can view one of my previous blogs:

Oeacle Common Date processing