比如某个用户在某一年录入了很多条血压数据, 先我想按周来求出平均数。 (周从星期天为开始 周六为结束)
显示效果 如2013-01-01到2013-01-05 88
2013-01-06到2013-01-12 99
2013-03-10到2013-03-16 120
.......急求啊! 大神们帮帮忙啊!!!!oracle数据库 周平均数
显示效果 如2013-01-01到2013-01-05 88
2013-01-06到2013-01-12 99
2013-03-10到2013-03-16 120
.......急求啊! 大神们帮帮忙啊!!!!oracle数据库 周平均数
解决方案 »
- oracle里面的char型问题
- 每次开机出现“STRTDB80.exe”遇到问题需要关闭,我们对此引起的不便表示抱歉,这是什么原因?
- oracle 11g cpu 100%
- Oracle SQL查询语句统计数据的问题
- 【数据库设计】-文章内容-不同的用户角色在前台查看的内容是不同的-【设计思路】-分不同的角色保存不同的内容-能行吗?
- Oracle两台服务器之间数据过滤问题
- 进入SQLPlus WorkSheet的服务老出错????
- 在存储过程中如何操作由参数传递过来的表名或对象名
- 怎么同时更新主从表
- 插入系统时间报文字和字符串不匹配的错误
- 相同查询条件,新增了关联的表,确提升了查询效率!?求大师
- 触发器update问题
with
a as --原始数据
(
select to_date('2012-12-30','YYYY-MM-DD') startdate,
to_date('2012-12-31','YYYY-MM-DD') enddate,
88 value1
from dual
union all
select to_date('2013-01-01','YYYY-MM-DD') startdate,
to_date('2013-01-05','YYYY-MM-DD') enddate,
88 value1
from dual
union all
select to_date('2013-01-06','YYYY-MM-DD') startdate,
to_date('2013-01-12','YYYY-MM-DD') enddate,
99 value1
from dual
union all
select to_date('2013-03-10','YYYY-MM-DD') startdate,
to_date('2013-03-16','YYYY-MM-DD') enddate,
120 value1
from dual
),
b as --根据原始数据构造周表
(select (rownum-1)*7+(select trunc(min(startdate),'day') from a) startdate,--一周起始日期
rownum*7-1+(select trunc(min(startdate),'day') from a) enddate --一周结束日期
from dual
connect by rownum <=(select max(enddate)-min(startdate) from a)/7+2
)
select b.startdate,
b.enddate,
sum((least(a.enddate,b.enddate)-greatest(a.startdate,b.startdate)+1 )*a.value1)/sum((least(a.enddate,b.enddate)-greatest(a.startdate,b.startdate)+1 )*1) avgvalue
from a,b
where a.startdate<=b.enddate and a.enddate>=b.startdate
group by b.startdate,b.enddate
结果如下:
1 2012-12-30 2013-01-05 88
2 2013-01-06 2013-01-12 99
3 2013-03-10 2013-03-16 120
1、首先你需要计算出各日期对应的各周归集日(可以用各周星期6)
2、按各周归集日分组并取血压平均值
将今天归集到周归集日上计算方法:
select next_day(trunc(sysdate),7) from dual;--7表示当前周最后一天(星期6)。
with tb_data as(
select '20130101' d, 88 v from dual union all
select '20130103' d, 87 v from dual union all
select '20130104' d, 82 v from dual union all
select '20130105' d, 92 v from dual union all
select '20130106' d, 102 v from dual union all
select '20130107' d, 102 v from dual union all
select '20130108' d, 98 v from dual union all
select '20130109' d, 92 v from dual union all
select '20130110' d, 94 v from dual union all
select '20130113' d, 88 v from dual union all
select '20130115' d, 92 v from dual union all
select '20130116' d, 94 v from dual )
select xd-6 "开始日期", xd "结束日期", trunc(avg(v), 2) "均值"
from (select t.*, next_day(to_date(d, 'YYYYMMDD'), 7) xd from tb_data t)
group by xd;
|| '到'
|| TO_CHAR (MAX (oxygentime), 'yyyy-mm-dd') "日期范围",
ROUND (AVG (eos),2) "平均血压"
FROM your_table
GROUP BY TO_CHAR (DECODE (TO_CHAR (oxygentime, 'D'), 1, oxygentime + 1, oxygentime), 'iw');
select '2013-01-12' oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select '2012-01-06' oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select '2013-01-02' oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select '2013-01-05' oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select '2013-03-08' oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select '2013-03-06' oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select '2013-03-07' oxygentime, 55 eos, 55 pulse, 125 userid from dual
)
select xd-6 "开始日期", xd "结束日期", trunc(avg(eos + pulse), 2) "均值"
from (select t.*, next_day(to_date(oxygentime, 'YYYY-MM-DD'), 7) xd from tb_data t)
group by xd;
with tb_data as (
select to_date('2013-01-12','YYYY-MM-DD') oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select to_date('2012-01-06','YYYY-MM-DD') oxygentime, 55 eos, 55 pulse, 125 userid from dual union all
select to_date('2013-01-02','YYYY-MM-DD') oxygentime, 44 eos, 33 pulse, 125 userid from dual union all
select to_date('2013-01-05','YYYY-MM-DD') oxygentime, 33 eos, 33 pulse, 125 userid from dual union all
select to_date('2013-03-08','YYYY-MM-DD') oxygentime, 63.25 eos, 63.25 pulse, 125 userid from dual union all
select to_date('2013-03-06','YYYY-MM-DD') oxygentime, 55 eos, 66 pulse, 125 userid from dual union all
select to_date('2013-03-07','YYYY-MM-DD') oxygentime, 44 eos, 55 pulse, 125 userid from dual
)
select trunc(oxygentime,'day'),
trunc(oxygentime,'day')+6,
userid,
avg(eos),
avg(pulse)
from tb_data
group by trunc(oxygentime,'day'),userid
1 2012-01-01 2012-01-07 125 55 55
2 2012-12-30 2013-01-05 125 38.5 33
3 2013-01-06 2013-01-12 125 55 55
4 2013-03-03 2013-03-09 125 54 61.4
select trunc(oxygentime,'day'),
trunc(oxygentime,'day')+6,
userid,
avg(eos),
avg(pulse)
from tb_data
group by trunc(oxygentime,'day'),userid