表结构及内容如下: id date1 srcid
1 2010-1-1 100
1 2010-1-2 100
1 2010-1-1 101
2 2010-1-1 100
2 2010-1-1 200按id分组,然后统计该id下唯一srcid和date1的个数及该id下的记录数,统计结果应该如下:id date1 srcid
1 2 2
2 1 2
1 2010-1-1 100
1 2010-1-2 100
1 2010-1-1 101
2 2010-1-1 100
2 2010-1-1 200按id分组,然后统计该id下唯一srcid和date1的个数及该id下的记录数,统计结果应该如下:id date1 srcid
1 2 2
2 1 2
select id,
count(distinct date1) date1,
count(distinct scrid) srcid
from tb group by id;
from table_test t
group by t.id没测试~~ 手写~~
SQL> with tab as
2 (
3 select 1 id , date '2010-1-1' date1, 100 srcid from dual union all
4 select 1 id , date '2010-1-2' date1, 100 srcid from dual union all
5 select 1 id , date '2010-1-1' date1, 101 srcid from dual union all
6 select 2 id , date '2010-1-1' date1, 100 srcid from dual union all
7 select 2 id , date '2010-1-1' date1, 200 srcid from dual
8 )
9 select id, max(date1count), max(srcidcount)
10 from (select id,
11 dense_rank() over(partition by id order by date1) date1count,
12 dense_rank() over(partition by id order by srcid) srcidcount
13 from tab)
14 group by id
15 ; ID MAX(DATE1COUNT) MAX(SRCIDCOUNT)
---------- --------------- ---------------
1 2 2
2 1 2SQL>
from tb
group by id
1 2010-1-1 102
1 2010-1-2 100
1 2010-1-1 101
1 2010-2-10 101
1 2010-2-10 100
2 2010-1-1 100
2 2010-1-1 200
2 2010-2-10 100按id分组,然后统计该id下日期在1周内的唯一srcid和date1的个数,还有该id下的记录总数,最小日期;统计结果应该如下:id mindate totalcount totalsrcid date1 srcid
1 2010-1-1 5 3 1 2
2 2010-1-1 3 2 1 1
id mindate totalcount totalsrcid date1 srcid
1 2010-1-1 5 3 1 22 2010-1-1 3 2 1 1
select t1.*,t2.date1,t2.srcid
from (select id,min(date1) as mindate ,count(date1) as totalcount,count(distinct srcid) as totalsrcid from tb group by id) t1
inner join (select id, count(distinct date1) as date1, count(distinct srcid) as srcid from tb where date1>=(sysdate - interval '7') group by id) t2 on t1.id=t2.id
from (select id,min(date1) as mindate ,count(date1) as totalcount,count(distinct srcid) as totalsrcid from tb group by id) t1
inner join (select id, count(distinct date1) as date1, count(distinct srcid) as srcid from tb where floor(sysdate - to_date(to_char(date1,'yyyymmdd')<=7 ,'yyyymmdd')) group by id) t2 on t1.id=t2.id
select id,mindate,count(1) totalcount,
count(distinct srcid) totalsrcid,
count(distinct case when sysdate-date1<7 then date1 end) date1,
count(distinct case when sysdate-date1<7 then srcid end) srcid
from (select id,min(date1) over (partition by id) mindate,date1,srcid from t)
group by id,mindate
/
inner join是求交集吧,会不会漏掉数据