按年按月分组查询

这里以 Oracle 为例子。

to_char 方法

select 
to_char(tj_date, 'YYYY-MM'), count(*) tj,
count(case when o3>160 then 1 else null end) o3_160,
count(case when o3>160 then 1 else null end)/count(*)
from O3 
group by to_char(tj_date, 'YYYY-MM')
order by 1;

单独抽取每月的数据

select 
to_char(tj_date, 'MM'), count(*),
count(case when o3>160 then 1 else null end) o3_160
from O3 
group by to_char(tj_date, 'MM')
order by 1;

Extract 方法

以下例子，抽取月

select EXTRACT(month from DATE_CREATED), sum(Num_of_Pictures)
from pictures_table
group by EXTRACT(month from DATE_CREATED);

Ref

Oracle Extract Datetime
How to query group by month in a year