按年按月分组查询
这里以 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