タイムゾーン de 自動調整のおまけ Tweet
では、まず最初は階層問合せから。
カレンダーを生成する前に階層と合わせで一連番号を生成するには、level疑似列を用いて必要なlevelまで階層を作り出せばOK。
（但し、Oracle9iまではこの方法は想定通りに動作しないのでご注意を。10g以降であれば想定通りの結果を返してくれます。）
以下の例では階層問合せを利用し１〜２７までの一連番号を生成したものです。
SCOTT> select level
2 from dual
3 connect by level <= 27;
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
27行が選択されました。
SCOTT>
上記の一連番号を利用し、実行同日を含む２７日前までの日付を求めてみます。
SCOTT> l
1 select trunc(sysdate) - (level-1)
2 from dual
3 connect by level <= 27
4* order by 1
SCOTT> /
TRUNC(SY
--------
08-03-05
08-03-06
08-03-07
08-03-08
08-03-09
08-03-10
08-03-11
08-03-12
08-03-13
08-03-14
08-03-15
08-03-16
08-03-17
08-03-18
08-03-19
08-03-20
08-03-21
08-03-22
08-03-23
08-03-24
08-03-25
08-03-26
08-03-27
08-03-28
08-03-29
08-03-30
08-03-31
27行が選択されました。
SCOTT>
できましたよね。簡単です。
次に集計関数cubeを利用する方法です。
cubeに関するマニュアルも事前に読んでおくといいですよ。
一連版を生成するために、cube関数の以下のような性質を利用しています。
SCOTT> select 1
2 from dual
3 group by cube(1);
1
----------
1
1
SCOTT> select 1
2 from dual
3 group by cube(1,1);
1
----------
1
1
1
1
SCOTT> select 1
2 from dual
3 group by cube(1,1,1);
1
----------
1
1
1
1
1
1
1
1
8行が選択されました。
SCOTT>
階層問合せのような柔軟性はないのですが、３２件のデータを生成するには cube(1,1,1,1,1)とすればいいわけです。
SCOTT> select 1
2 from dual
3 group by cube(1,1,1,1,1);
1
----------
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
32行が選択されました。
SCOTT>
ただ一連番号にはなっていません。単に３２行の結果を得たたけです。そこでronum疑似列を利用して１〜２７までの一連番号を求めます。
SCOTT> select rownum
2 from (select 1 from dual group by cube(1,1,1,1,1))
3 where rownum <= 27;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
27行が選択されました。
SCOTT>
ここまでできればあとは簡単。階層問合せと同じように日付を生成してみましょう。
SCOTT> select trunc(sysdate) - (rownum-1)
2 from (select 1 from dual group by cube(1,1,1,1,1))
3 where rownum <= 27
4 order by 1;
TRUNC(SY
--------
08-03-05
08-03-06
08-03-07
08-03-08
08-03-09
08-03-10
08-03-11
08-03-12
08-03-13
08-03-14
08-03-15
08-03-16
08-03-17
08-03-18
08-03-19
08-03-20
08-03-21
08-03-22
08-03-23
08-03-24
08-03-25
08-03-26
08-03-27
08-03-28
08-03-29
08-03-30
08-03-31
27行が選択されました。
SCOTT>
cube関数はOracle9iから利用できる関数なのでOracle9iでこんなことを行いたい場合には前述した階層問い合せの代役にもなりますね。
Enjoy SQL!
| 固定リンク | 0
コメント