A表中有开始时间 sttime,结束时间 edtime 等字段,要求是,求用户接听时长的时间段:小于1分钟,1分钟到多少分钟(其中的多少分钟是用户自己选定的时间间隔),大于一个小时的,比如输入15分钟,通话时长:
小于1分钟的有多少个用户,
1分到15分的有多少个用户
15分到30分的有多少个用户
30分到45分的有多少个用户
45分到60分的有多少个用户
大于1小时的有多少个用户要是输入3分钟,时间间隔就是3
小于1分钟的有多少个用户,
1分到3分的有多少个用户
3分到6分的有多少个用户
...........
输入的数大于1分小于60分这样的sql 语句怎么写
小于1分钟的有多少个用户,
1分到15分的有多少个用户
15分到30分的有多少个用户
30分到45分的有多少个用户
45分到60分的有多少个用户
大于1小时的有多少个用户要是输入3分钟,时间间隔就是3
小于1分钟的有多少个用户,
1分到3分的有多少个用户
3分到6分的有多少个用户
...........
输入的数大于1分小于60分这样的sql 语句怎么写
select ((edtime-sttime)/(24*60)+3),count(*)
from tablename
group by ((edtime-sttime)/(24*60)+3)
(select '15' as jg,to_date('2011-01-27 12:20','yyyy-mm-dd hh24:mi')as sttime,to_Date('2011-01-27 12:28','yyyy-mm-dd hh24:mi')as edtime from dual )
select t.tim,
sum(case
when (select edtime - sttime from tab) < 15 then
1
else
0
end) as num
from (select 0.99 as tim
from dual
union all
select jg * rownum as tim
from tab
connect by jg * rownum <= 60
union all
select 60.01 as tim from dual) t
group by t.tim
order by t.tim
这个方法求和时有点问题,等有时间再帮你看 思路差不多了
with tab as (select '15' as jg from dual ),
tab1 as (select to_date('2011-01-27 20:20','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:30','yyyy-mm-dd hh24:mi')as edtime from dual
union select to_date('2011-01-27 20:23','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:50','yyyy-mm-dd hh24:mi')as edtime from dual )
select t.tim,
(select sum(case
when (edtime - sttime)*24*60 >t.tim-(select jg from tab) and (edtime - sttime)*24*60 <t.tim then
1
else
0
end)as temp from tab1) as num
from (select 0.99 as tim
from dual
union all
select jg * rownum as tim
from tab
connect by jg * rownum <= 60
union all
select 60.01 as tim from dual) t,mw_app.mwt_sm_datecontrol d
group by t.tim
order by t.tim
with tab as (select '15' as jg from dual ),
tab1 as (select to_date('2011-01-27 20:20','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:30','yyyy-mm-dd hh24:mi')as edtime from dual
union all select to_date('2011-01-27 20:13','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:59','yyyy-mm-dd hh24:mi')as edtime from dual )
select t.tim,
(select sum(case when t.tim>60 then (case when (edtime - sttime)*24*60>60 then 1 else 0 end)
else(case when (edtime - sttime)*24*60 >t.tim-(select jg from tab) and (edtime - sttime)*24*60 <t.tim then
1 else 0 end ) end)as temp from tab1) as num
from (select 0.99 as tim from dual
union all select jg * rownum as tim from tab connect by jg * rownum <= 60
union all select 60.01 as tim from dual) t
group by t.tim
order by t.tim
---这个是15 的列子 你自己改下就可以了
select decode((edtime-sttime)*24*60-1,1,
decode(ceil(((edtime-sttime)*24*60)/15),1,'1分到15分的有多少个用户',2,'15分到30分的有多少个用户',3,'30分到45分的有多少个用户',4,'45分到60分的有多少个用户','大于1小时的有多少个用户'),
'小于1分钟的有多少个用户') types,count(1)
from tb
group by types
CREATE TABLE linktime(
mobile varchar2(20),
starttime date,
endtime date);DECLARE
v_rand_mobile number(11,0); -- 随机生成手机号
v_rand_starttime date; -- 随机生成通话开始时间
v_rand_endtime date; -- 随机生成通话结束时间
BEGIN
FOR i in 1.. 100000 LOOP
select '13'||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10)))||
to_char(trunc(dbms_random.value(0,10))) as rand_mobile,
trunc(sysdate)-dbms_random.value(0.04,125)
into v_rand_mobile, v_rand_starttime
from dual;
select v_rand_starttime+dbms_random.value(1/1440,720/1440) into v_rand_endtime from dual;
insert into linktime(mobile,starttime,endtime) values(v_rand_mobile,v_rand_starttime,v_rand_endtime);
COMMIT;
END LOOP;
END;
/
select to_date('2011-01-27 20:20','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:30','yyyy-mm-dd hh24:mi')as edtime from dual
union all
select to_date('2011-01-27 20:13','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:59','yyyy-mm-dd hh24:mi')as edtime from dual
),temp1 as(
select level lv from dual connect by level < 61
)
select 1 ms, sum(case when ((edtime-sttime)*24*60) <= 15 then 1 else 0 end) splittime from temp
union all
select 15*lv ms,sum(case when ((edtime-sttime)*24*60) >15*lv and (edtime-sttime)*24*60 <=15*(lv+1) then 1 else 0 end) from temp,temp1
where 15*lv <60
group by 15*lv
union all
select 60 ms, sum(case when ((edtime-sttime)*24*60) > 60 then 1 else 0 end) splittime from temp
order by ms
以15分钟为列
第一行是1分钟以下的,最后一行是60分钟以上的
select level
,decode(&num * (level-1),0,1,&num * (level-1)) start_time
,&num * level end_time
from dual
connect by level <= 60 / &num
with temp as (temp1是什么意思没这样用过
temp1是用来构建变量的,因为你的要求是每几分钟分一段,
所以用level来构建一个变量
with tab as (select '15' as jg from dual )这是来构造间隔的 你可以用自己的时间间隔替换这一个
tab1 as (select to_date('2011-01-27 20:20','yyyy-mm-dd hh24:mi')as sttime,to_date('2011-01-27 20:30','yyyy-mm-dd hh2……这是来构造两个字段的 你也换成自己的就行啊
decode(ceil(((edtime-sttime)*24*60)/15),1,'1分到15分的有多少个用户',2,'15分到30分的有多少个用户',3,'30分到45分的有多少个用户',4,'45分到60分的有多少个用户','大于1小时的有多少个用户'),
'小于1分钟的有多少个用户') types,count(1)
from tb
group by types
--ceil(((edtime-sttime)*24*60)/15), 15改成你传人的参数即可
楼主需要多了解Oracle的函数
select trunc(((callettime-callsttime)*24*60),2) times,decode(sign((callettime-callsttime)*24*60-1),1,decode(ceil(((callettime-callsttime)*24*60)/15),1,'1分到15分的有多少个用户',2,'15分到30分的有多少个用户',3,'30分到45分的有多少个用户',4,'45分到60分的有多少个用户','大于1小时的有多少个用户'),
'小于1分钟的有多少个用户') typ,count(1)from telcdr group gy typ为什么一加上typ,count(1),group gy typ就出错
去看看group by 怎么用 唉 你trunc(((callettime-callsttime)*24*60),2) 这个也得放到GROUP BY 的后面select trunc(((callettime-callsttime)*24*60),2) times,decode(sign((callettime-callsttime)*24*60-1),1,decode(ceil(((callettime-callsttime)*24*60)/15),1,'1分到15分的有多少个用户',2,'15分到30分的有多少个用户',3,'30分到45分的有多少个用户',4,'45分到60分的有多少个用户','大于1小时的有多少个用户'),
'小于1分钟的有多少个用户') typ,count(1)from telcdr
group by times,typ