9-34 過去1時間の平均

SQLパズル

Samplesテーブル
sample_time       Load
----------------  ----
2007/12/31 10:00   100
2007/12/31 10:15   200
2007/12/31 10:45   400
2007/12/31 11:00   800
2007/12/31 11:30  2100
2007/12/31 11:45  4000
2007/12/31 12:15  6200
2007/12/31 14:15  8000

Loadの過去1時間のLoadの平均(小数点以下切り捨て)を求める

出力結果
sample_time       Load  AvgLoad
----------------  ----  -------
2007/12/31 10:00   100      100
2007/12/31 10:15   200      150  ← (200+100)/2
2007/12/31 10:45   400      233  ← (400+200+100)/3
2007/12/31 11:00   800      375  ← (800+400+200+100)/4
2007/12/31 11:30  2100     1100  ← (2100+800+400)/3
2007/12/31 11:45  4000     1825  ← (4000+2100+800+400)/4
2007/12/31 12:15  6200     4100  ← (6200+4000+2100)/3
2007/12/31 14:15  8000     8000

SQLパズル(日本語版)のパズル36 [移動平均] を参考にさせていただきました
SQLパズル 第2版のパズル37 [移動平均] を参考にさせていただきました

データ作成スクリプト

create table Samples(
sample_time date primary key,
Load number(4));

insert into Samples values(to_date('2007/12/31 10:00','YYYY/MM/DD HH24:MI'), 100);
insert into Samples values(to_date('2007/12/31 10:15','YYYY/MM/DD HH24:MI'), 200);
insert into Samples values(to_date('2007/12/31 10:45','YYYY/MM/DD HH24:MI'), 400);
insert into Samples values(to_date('2007/12/31 11:00','YYYY/MM/DD HH24:MI'), 800);
insert into Samples values(to_date('2007/12/31 11:30','YYYY/MM/DD HH24:MI'),2100);
insert into Samples values(to_date('2007/12/31 11:45','YYYY/MM/DD HH24:MI'),4000);
insert into Samples values(to_date('2007/12/31 12:15','YYYY/MM/DD HH24:MI'),6200);
insert into Samples values(to_date('2007/12/31 14:15','YYYY/MM/DD HH24:MI'),8000);
commit;

SQL

--■■■自己結合を使う方法■■■
select to_char(a.sample_time,'YYYY/MM/DD HH24:MI') as sample_time,
a.Load,
trunc(avg(b.Load)) as AvgLoad
  from Samples a,Samples b
 where b.sample_time between a.sample_time-60/24/60 and a.sample_time
group by to_char(a.sample_time,'YYYY/MM/DD HH24:MI'),a.Load
order by to_char(a.sample_time,'YYYY/MM/DD HH24:MI');

--■■■相関サブクエリを使う方法■■■
select to_char(sample_time,'YYYY/MM/DD HH24:MI') as sample_time,
Load,
(select trunc(avg(b.Load))
   from Samples b
  where b.sample_time between a.sample_time-60/24/60 and a.sample_time) as AvgLoad
  from Samples a
order by a.sample_time;

--■■■分析関数を使う方法1■■■
select to_char(sample_time,'YYYY/MM/DD HH24:MI') as sample_time,
Load,
trunc((Load+
case when Lag1sample_time between sample_time-60/24/60 and sample_time
     then Lag1Load else 0 end +
case when Lag2sample_time between sample_time-60/24/60 and sample_time
     then Lag2Load else 0 end +
case when Lag3sample_time between sample_time-60/24/60 and sample_time
     then Lag3Load else 0 end +
case when Lag4sample_time between sample_time-60/24/60 and sample_time
     then Lag4Load else 0 end +
case when Lag5sample_time between sample_time-60/24/60 and sample_time
     then Lag5Load else 0 end) / ( 1+
case when Lag1sample_time between sample_time-60/24/60 and sample_time
     then 1 else 0 end +
case when Lag2sample_time between sample_time-60/24/60 and sample_time
     then 1 else 0 end +
case when Lag3sample_time between sample_time-60/24/60 and sample_time
     then 1 else 0 end +
case when Lag4sample_time between sample_time-60/24/60 and sample_time
     then 1 else 0 end +
case when Lag5sample_time between sample_time-60/24/60 and sample_time
     then 1 else 0 end)) as AvgLoad
from (select sample_time,Load,
      Lag(sample_time)   over(order by sample_time) as Lag1sample_time,
      Lag(sample_time,2) over(order by sample_time) as Lag2sample_time,
      Lag(sample_time,3) over(order by sample_time) as Lag3sample_time,
      Lag(sample_time,4) over(order by sample_time) as Lag4sample_time,
      Lag(sample_time,5) over(order by sample_time) as Lag5sample_time,
      Lag(Load)   over(order by sample_time) as Lag1Load,
      Lag(Load,2) over(order by sample_time) as Lag2Load,
      Lag(Load,3) over(order by sample_time) as Lag3Load,
      Lag(Load,4) over(order by sample_time) as Lag4Load,
      Lag(Load,5) over(order by sample_time) as Lag5Load
      from Samples)
order by sample_time;

--■■■分析関数を使う方法2■■■
select to_char(sample_time,'YYYY/MM/DD HH24:MI') as sample_time,
Load,
trunc(avg(Load) over(order by sample_time Range 60/24/60 Preceding)) as AvgLoad
  from Samples
order by sample_time;

--■■■分析関数を使う方法3■■■
select to_char(sample_time,'YYYY/MM/DD HH24:MI') as sample_time,
Load,
trunc(avg(Load) over(order by sample_time Range interval '1' hour Preceding)) as AvgLoad
  from Samples
order by sample_time;

解説

分析関数を使う方法3が、
一番分かりやすいと思います

分析関数を使う方法3と、
似たようなSQL(RangeをRowsに変更)が
8-5 移動平均を求める
にあります。

CodeZine：分析関数の衝撃（後編）

---

マニュアル(分析関数)(英語)
マニュアル(分析関数)

マニュアル(Interval Literals)(英語)

---