有张表 temp 如下:
time_id phone
20110101 134
20110101 135
20110101 134
20110101 137
20110102 134
20110102 135
20110102 136
20110102 136要求:
(1)
时间 不重复的个数
20110101 3
20110102 3
(2)
时间 累计不重复的个数
20110101 3
20110102 4(从1号开始到20110102累计不重复的个数)谁能帮我看下!
time_id phone
20110101 134
20110101 135
20110101 134
20110101 137
20110102 134
20110102 135
20110102 136
20110102 136要求:
(1)
时间 不重复的个数
20110101 3
20110102 3
(2)
时间 累计不重复的个数
20110101 3
20110102 4(从1号开始到20110102累计不重复的个数)谁能帮我看下!
select time_id,count(distinct phone) from temp where time_id='20110101'
union all
select time_id,count(distinct phone) from temp where time_id='20110102'
这种方式的就不要回复了,
'20110101','20110102'这两个参数是变的!
(
select '20110101' time_id , '134' phone from dual
union
select '20110101' time_id , '135' phone from dual
union
select '20110101' time_id , '134' phone from dual
union
select '20110101' time_id , '137' phone from dual
union
select '20110102' time_id , '134' phone from dual
union
select '20110102' time_id , '135' phone from dual
union
select '20110102' time_id , '136' phone from dual
union
select '20110102' time_id , '136' phone from dual
)select t.time_id,count(1)
from temp_table t
group by t.time_id ;
count(distinct phone)
from tempselect time_id,
count(distinct phone) over(order by time_id)
from
with t as
(
select '20110101' d , '134' s from dual
union
select '20110101' d , '135' s from dual
union
select '20110101' d , '134' s from dual
union
select '20110101' d , '137' s from dual
union
select '20110102' d , '134' s from dual
union
select '20110102' d , '135' s from dual
union
select '20110102' d , '136' s from dual
union
select '20110102' d , '136' s from dual
)
--第一种情况
select d,count(distinct s) from t group by d;
20110101 3
20110102 3
--第二种情况
select d,sum(count(distinct s)) over (order by d) from t group by d
20110101 3
20110102 6
select time_id,
count(distinct phone)
from temp
group by time_id
select time_id,
count(distinct phone) over(order by time_id)
from
--换个思路就可以了嘛:--1:
select time_id,count(distinct phone) cnt from tab group by time_id--2:
select a.time_id,(select count( distinct phone) from tab where time_id<=a.time_id) cnt
from tab a
group by a.time_id
order by a.time_id
select a.time_id,(select count( distinct phone) from tab where time_id<=a.time_id) cnt
from tab a
group by a.time_id
order by a.time_id
这个写的好,结贴时加分!