トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
8-5 移動平均を求める
SQLパズル
終値推移テーブル
日付 終値
---------- -----
2006/03/08 10
2006/03/09 30
2006/03/10 50
2006/03/11 70
2006/03/14 70
2006/03/15 80
2006/03/16 90
2006/03/17 100
2006/03/18 110
2006/03/22 100
2006/03/23 90
5日移動平均を求める。
出力結果
日付 終値 移動平均
---------- ---- --------
2006/03/08 10 null
2006/03/09 30 null
2006/03/10 50 null
2006/03/11 70 null
2006/03/14 70 46
2006/03/15 80 60
2006/03/16 90 72
2006/03/17 100 82
2006/03/18 110 90
2006/03/22 100 96
2006/03/23 90 98
データ作成スクリプト
create table 終値推移(日付,終値) as
select date '2006-03-08', 10 from dual union
select date '2006-03-09', 30 from dual union
select date '2006-03-10', 50 from dual union
select date '2006-03-11', 70 from dual union
select date '2006-03-14', 70 from dual union
select date '2006-03-15', 80 from dual union
select date '2006-03-16', 90 from dual union
select date '2006-03-17',100 from dual union
select date '2006-03-18',110 from dual union
select date '2006-03-22',100 from dual union
select date '2006-03-23', 90 from dual;
SQL
--■■■case式と分析関数を組み合わせる方法■■■
select 日付,終値,
case when count(*) over(order by 日付) >= 5
then avg(終値) over(order by 日付 Rows (5-1) Preceding) end as 移動平均
from 終値推移
order by 日付;
--■■■相関サブクエリを使う方法■■■
select 日付,終値,
(select avg(b.終値) from 終値推移 b
where (select count(*) from 終値推移 c
where c.日付 between b.日付 and a.日付) between 1 and 5
having count(*) = 5) as 移動平均
from 終値推移 a
order by 日付;
--■■■結合させる方法■■■
select a.日付,a.終値,decode(count(*),5,avg(b.終値)) as 移動平均
from 終値推移 a,終値推移 b
where (select count(*) from 終値推移 c
where c.日付 between b.日付 and a.日付) between 1 and 5
group by a.日付,a.終値
order by a.日付;
--■■■逆ソートを使う方法■■■
select 日付,終値,
(select avg(b.終値)
from (select c.日付,c.終値
from 終値推移 c
order by c.日付 desc) b
where b.日付 <= a.日付
and RowNum <= 5
having count(*) = 5) as 移動平均
from 終値推移 a
order by 日付;
解説