前回のBLOGにて「GENERATE_SERIES」という連番を返す関数を作成しました。
http://blogs.wankuma.com/kaya/archive/2006/09/25/39512.aspx
今回はこれとCROSS APPLYを使って遊びたいと思います♪
APPLYはテーブルとテーブル値集計関数をJOINしてしまうぞ！って命令です。
記述する場所はFROM句になります。
APPLYについてのマニュアルは以下のページにあります。
「SQL Server 2005 Books Online － APPLY の使用 」
http://msdn2.microsoft.com/ja-JP/library/ms175156.aspx
では、お遊び開始！！
まずベースのテーブルとして月初日を持ったapplyTestテーブルを作りました。
applyTable
| base |
| 2004-01-01 00:00:00.000 |
| 2004-02-01 00:00:00.000 |
| 2004-03-01 00:00:00.000 |
| 2004-04-01 00:00:00.000 |
ここから最終的に生成されるテーブルは以下のものになります。
#(中略)部分は前後にあるデータの合間にある日付がすべてあります。
| 2004-01-01 00:00:00.000 |
| 2004-01-02 00:00:00.000 |
| 2004-01-03 00:00:00.000 |
| (中略) |
| 2004-01-30 00:00:00.000 |
| 2004-01-31 00:00:00.000 |
| 2004-02-01 00:00:00.000 |
| 2004-02-02 00:00:00.000 |
| (中略) |
| 2004-02-28 00:00:00.000 |
| 2004-02-29 00:00:00.000 |
| 2004-03-01 00:00:00.000 |
| 2004-03-02 00:00:00.000 |
| (中略) |
| 2004-03-30 00:00:00.000 |
| 2004-03-31 00:00:00.000 |
| 2004-04-01 00:00:00.000 |
| 2004-04-02 00:00:00.000 |
| (中略) |
| 2004-04-28 00:00:00.000 |
| 2004-04-29 00:00:00.000 |
| 2004-04-30 00:00:00.000 |
元のapplyTableと作成したテーブル値関数generate_seriesを使用して
どのようなSQLを書いたかというと・・・・。
こんなでした！
SELECT base + num as days
FROM applyTest
cross apply
generate_series(0,DATEDIFF(DAY, base, DATEADD(MONTH,1, base))-1,default);
このSQLでしていることを説明しますと・・・・。
1) DATEADDでapplyTestのbaseに入っている日付の1ヶ月先日付を作っています。
applyTestの1行目を例にとると base = 2004-01-01なので
DATEADD(MONTH, 1, base) = DATEADD(MONTH, 1, 2004-01-01) = 2004-02-01
となります。
2) baseの日付と1ヵ月後の日付の間にある日数を取得します。
先ほどのデータですと、2004-01-01から2004-02-01の間の日数＝31 となります。
3) baseが1日なので、取得した日数を-1日しています。
(この数値は後でbaseに加算するので)
4) 日数分のデータをgenerate_seriesで作成しています。(1～月日の日数までを持った行)
0～30になるので、num = 0, 1, 2, 3, ・・・・ 29, 30という行が返されます。
5) その結果とapplyTableの対象行をJOINします。
applyTableの1行毎に対し、generate_seriesを実行してJOINが行われるので、
1行目の2004-01-01に対しては 0～30の計31行とJOINが行われます。
6) SELECT項目として baseの日付＋generate_seriesの戻りデータ(0～30)を加算した日付を指定しています。
初めて使ったAPPLYなんですが、
やってみて感じたAPPLYの良い点として、
行毎に、テーブル値関数を実行してJOINが出来る→データごとにJOINされる件数を変えることが出来る。
テーブル値関数の引数に行のデータを使用できる。
ってことかなぁ・・・と私は思いました。
とりあえず、今日のお遊びはこんな感じで♪