按itemcc分组计算数量,并且要获取每组的最小时间。
create table aa (datetime date,itemcc)insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:01','YYYY-MM-DD HH24:MI:SS'),1);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:02','YYYY-MM-DD HH24:MI:SS'),1);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:03','YYYY-MM-DD HH24:MI:SS'),1);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:11','YYYY-MM-DD HH24:MI:SS'),2);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:12','YYYY-MM-DD HH24:MI:SS'),2);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:40','YYYY-MM-DD HH24:MI:SS'),3);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:41','YYYY-MM-DD HH24:MI:SS'),3);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:42','YYYY-MM-DD HH24:MI:SS'),3);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:43','YYYY-MM-DD HH24:MI:SS'),3);
期望结果:
count(*),itcc,time
3 1 2011-3-22 12:00:01
2 2 2011-3-22 12:00:11
4 3 2011-3-22 12:00:40
create table aa (datetime date,itemcc)insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:01','YYYY-MM-DD HH24:MI:SS'),1);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:02','YYYY-MM-DD HH24:MI:SS'),1);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:03','YYYY-MM-DD HH24:MI:SS'),1);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:11','YYYY-MM-DD HH24:MI:SS'),2);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:12','YYYY-MM-DD HH24:MI:SS'),2);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:40','YYYY-MM-DD HH24:MI:SS'),3);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:41','YYYY-MM-DD HH24:MI:SS'),3);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:42','YYYY-MM-DD HH24:MI:SS'),3);
insert into aa(datetime,itemcc) values(to_date('2011-3-22 12:00:43','YYYY-MM-DD HH24:MI:SS'),3);
期望结果:
count(*),itcc,time
3 1 2011-3-22 12:00:01
2 2 2011-3-22 12:00:11
4 3 2011-3-22 12:00:40
select count(*),itemcc itcc,min(datetime) from aa group by itemcc;
select t.time,t.itcc,count(*)
from
(select
itemcc itcc,
first_value(datetime) over (partition by itemcc order by datetime asc) time
from aaaa ) t
group by t.time,t.itcc
order by t.time;