如题。数据大致如下:
日期 数值
20121129 2
20121129 1
20121129 3
20121130 4
20121130 3
20121130 1
20121130 2
20121201 3
20121201 2
20121201 1
20121202 3
20121202 4
20121202 2
20121202 1
20121211 3
20121211 2
20121211 1
20121212 1
20121212 2
20121212 3
20121212 4
使用窗口函数写法如下:
SELECT tef.日期,AVG(SUM(tef.数值)) OVER (PARTITION BY tef.日期 ORDER BY TRUNC(TO_DATE(tef.日期,'yyyy-mm-dd'))
RANGE BETWEEN INTERVAL '30' DAY preceding AND INTERVAL '0' DAY following ) tstst
FROM 表 tef
GROUP BY tef.日期
;
使用这种写法向前30天由于日期不连续,只能获取连续日期的数据,比如20121212只能汇总20121211+20121212的数据无法汇总到20121202的数据,请教如何修改语句获取连续日期的数据汇总满足需求?
日期 数值
20121129 2
20121129 1
20121129 3
20121130 4
20121130 3
20121130 1
20121130 2
20121201 3
20121201 2
20121201 1
20121202 3
20121202 4
20121202 2
20121202 1
20121211 3
20121211 2
20121211 1
20121212 1
20121212 2
20121212 3
20121212 4
使用窗口函数写法如下:
SELECT tef.日期,AVG(SUM(tef.数值)) OVER (PARTITION BY tef.日期 ORDER BY TRUNC(TO_DATE(tef.日期,'yyyy-mm-dd'))
RANGE BETWEEN INTERVAL '30' DAY preceding AND INTERVAL '0' DAY following ) tstst
FROM 表 tef
GROUP BY tef.日期
;
使用这种写法向前30天由于日期不连续,只能获取连续日期的数据,比如20121212只能汇总20121211+20121212的数据无法汇总到20121202的数据,请教如何修改语句获取连续日期的数据汇总满足需求?
(
MyDate DATE,
MyNum NUMBER(4)
);
INSERT INTO T168 VALUES(to_date('20121129', 'YYYYMMDD'), 1);
INSERT INTO T168 VALUES(to_date('20121129', 'YYYYMMDD'), 2);
INSERT INTO T168 VALUES(to_date('20121129', 'YYYYMMDD'), 3);
INSERT INTO T168 VALUES(to_date('20121130', 'YYYYMMDD'), 4);
INSERT INTO T168 VALUES(to_date('20121130', 'YYYYMMDD'), 5);
INSERT INTO T168 VALUES(to_date('20121130', 'YYYYMMDD'), 6);
INSERT INTO T168 VALUES(to_date('20121130', 'YYYYMMDD'), 7);
INSERT INTO T168 VALUES(to_date('20121201', 'YYYYMMDD'), 8);
INSERT INTO T168 VALUES(to_date('20121201', 'YYYYMMDD'), 9);
INSERT INTO T168 VALUES(to_date('20121201', 'YYYYMMDD'), 10);
INSERT INTO T168 VALUES(to_date('20121202', 'YYYYMMDD'), 11);
INSERT INTO T168 VALUES(to_date('20121202', 'YYYYMMDD'), 12);
INSERT INTO T168 VALUES(to_date('20121202', 'YYYYMMDD'), 13);
INSERT INTO T168 VALUES(to_date('20121202', 'YYYYMMDD'), 14);
INSERT INTO T168 VALUES(to_date('20121211', 'YYYYMMDD'), 15);
INSERT INTO T168 VALUES(to_date('20121211', 'YYYYMMDD'), 16);
INSERT INTO T168 VALUES(to_date('20121211', 'YYYYMMDD'), 17);
INSERT INTO T168 VALUES(to_date('20121212', 'YYYYMMDD'), 18);
INSERT INTO T168 VALUES(to_date('20121212', 'YYYYMMDD'), 19);
INSERT INTO T168 VALUES(to_date('20121212', 'YYYYMMDD'), 20);
INSERT INTO T168 VALUES(to_date('20121212', 'YYYYMMDD'), 21);
测试结果
with t1 as
(
select '1' 编号,'20121127' 日期,'2' 数值 from dual union all
select '2', '20121128','1' from dual union all
select '3', '20121129','3' from dual union all
select '4', '20121130','4' from dual union all
select '5', '20121130','3' from dual union all
select '6', '20121130','1' from dual union all
select '7', '20121130','2' from dual union all
select '8', '20121201','3' from dual union all
select '9', '20121201','2' from dual union all
select '10', '20121202','2' from dual union all
select '11', '20121203','2' from dual union all
select '12', '20121204','2' from dual union all
select '13', '20121205','2' from dual union all
select '14', '20121206','4' from dual union all
select '15', '20121207','3' from dual union all
select '16', '20121208','6' from dual union all
select '17', '20121209','7' from dual union all
select '18', '20121210','3' from dual union all
select '19', '20121211','5' from dual union all
select '20', '20121212','2' from dual union all
select '21', '20121213','2' from dual union all
select '22', '20121214','2' from dual union all
select '23', '20121215','2' from dual union all
select '24', '20121216','2' from dual union all
select '25', '20121217','2' from dual union all
select '26', '20121218','2' from dual union all
select '27', '20121219','2' from dual union all
select '28', '20121220','2' from dual union all
select '29', '20121221','2' from dual union all
select '30', '20121222','2' from dual union all
select '31', '20121223','2' from dual union all
select '32', '20121224','2' from dual union all
select '33', '20121225','2' from dual union all
select '34', '20121226','2' from dual union all
select '35', '20121227','2' from dual union all
select '36', '20121228','2' from dual union all
select '37', '20121228','2' from dual union all
select '38', '20121228','2' from dual union all
select '39', '20121228','2' from dual
)select t1.日期,avg(sum(t1.数值)/count(t1.数值)) over(partition by t1.日期 order by t1.日期) a_count from t1,
(
select 日期,rownum from
(
select distinct 日期
from t1
order by 日期
)
where rownum<=30
) t2
where t1.日期 in t2.日期
group by t1.日期
order by t1.日期 日期 a_count
-------------------------------
1 20121127 2
2 20121128 1
3 20121129 3
4 20121130 2.5
5 20121201 2.5
6 20121202 2
7 20121203 2
8 20121204 2
9 20121205 2
10 20121206 4
11 20121207 3
12 20121208 6
13 20121209 7
14 20121210 3
15 20121211 5
16 20121212 2
17 20121213 2
18 20121214 2
19 20121215 2
20 20121216 2
21 20121217 2
22 20121218 2
23 20121219 2
24 20121220 2
25 20121221 2
26 20121222 2
27 20121223 2
28 20121224 2
29 20121225 2
30 20121226 2
from tab tef
where tef.日期
in (
select 日期 from (select distinct 日期 from tef where 日期<=统计日期 order by 日期 desc) where rownum<=30
)1、查询出统计日期之前所有的日期,并且排序最大日期在 然后获取30日期
左连过去 搞定
你写的SQL不是很明白,不过经过测试还是不能满足需求!