SQLおよびExcelで日付ツールを使用する｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2011.05.16

SQLおよびExcelで日付ツールを使用する

Nick Hampson 著

日付情報の優れた処理方法を新たに探る

どの業種でも、アプリケーション処理では日付を使用します。情報の提供側として、開発者やデータ・アナリストには日付を使用した情報を処理するツールが必要です。この記事では、こうした極めて重要な日付の処理に役立つ SQL や Excel を使用したツールを中心にお話します。

まず、日付処理を行うための SQL の組み込み関数 (BIF) のレビューから始めて、文字フィールドや数値フィールドの日付情報の処理に進みます。それから、SQL で日付期間に基づいてデータを要約するホットな方法をいくつか紹介し、最後に Excel でもそうした粋な要約ツールをお示しします。この記事は、5.4 以降で利用できる SQL BIF に基づいて掲載しています。5.4 の SQL 組み込み関数の優れた参考資料は publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmstabout.htm をご覧ください。

お示しする例のほとんどは、以下に示すデータベース・レイアウトを使用しています (通年のデータのスナップショットは図 1)。

列名	説明	タイプ/長さ/精度
WIDDATE	トランザクションの日付	日付
WIDSOLD	販売されたウィジェット (日単位)	数値/ 7 / 0
WIDSALES	ウィジェット売上値 (日単位)	数値/ 9 / 2

私のサンプル・ファイルには2008年から2009年の全データ、またこの記事を書いた時点の2010年7月19日までの年初来データが含まれています。

SQL の基本的な日付処理

日付処理をする場合、月、日、年の取り出し方法を知っていると便利です。これらは、図 2 に示すように、SQL の BIF である YEAR()、MONTH()、DAY(,) を使用して抽出できます。

似たような方法を使用して、基準日から日、月、または年を加減算するシンプルな日付計算を行います。フォーマットは <+ or -> です。次の例を見てください。

Select widdate, widdate + 1 day from widgets
Select widdate, widdate - 2 months from widgets
Select widdate, widdate + 3 years from widgets
Select widdate, widdate + 3 years - 1 day from widgets

最後の例には、年と日の節が有効になっている点に注目してください。返されたデータのサンプルの一部は次のようになります。

WIDDATE	Date expression
07/02/10	07/01/13
07/03/10	07/02/13
07/04/10	07/03/13

SQL は賢く、月末の処理方法がわかっており、無効な日付は返しません。この SQL では、1 月末の数日分に対して日付プラス 1ヶ月を選択しています。

select widdate, widdate + 1 month
from widgets
where widdate between '2010-01-27' and '2010-01-31'

結果は以下に示すとおりです。おわかりになると思いますが、1 月の 4 日分に対して 2 月の最終日を返しているだけです。

WIDDATE	WIDDATE + 1 MONTH
01/27/10	02/27/10
01/28/10	02/28/10
01/29/10	02/28/10
01/30/10	02/28/10
01/31/10	02/28/10

曜日を取得する

ほとんどのボリューム・アプリケーションでは、曜日のデータを分析することは非常に重要です。火曜日より月曜日の方が売上があるのでしょうか。日付を表示しても、これが必ずしもわかるわけではありません。トレンドを確認し分析するためには、曜日を文字形式で表示する必要があります。この SQL は、テーブルの全行について dayofweek() BIF を使用して、曜日に基づく売上の数を示しています。結果を図 3 に示します。

SQL 結果で曜日を表示する必要がある場合は、DAYNAME() BIF を使用できます。その使用方法とサンプル結果を図 4 に示します。

さまざまなフォーマットを使った SQL トリック

i5 サーバー上のすべての SQL テーブル (ファイルでいいでしょう) の日付形式がネイティブだったら素晴らしいと思います。おそらく、さまざまな数値や文字形式で保存されている旧ファイル・システムの日付データを処理する必要がない、素晴らしい世界になるでしょう。しかしそれでも我々のほとんどは、実日付フィールド・データと数値フィールドや文字フィールドに保存された旧型日付データが混じったデータを扱う必要があります。しかし、恐れることはありません。SQL の date BIF を使用できます。それらをまずある日付形式に変換する必要があります。

シリアル番号形式で保存された日付データを見つけるのは珍しいことではありません。ネイティブ日付フィールドにするまで、(050302 など) mmddyy 形式で保存された数字を簡単に日付計算することはできないでしょう。しかし、基準日があるシリアル形式で保存された日付ならば、日付計算はできるでしょう。例えば、数字 1 が 1/1/1980 を表している場合、数字 2 は 1/2/1980 を意味する、といった具合です。これらの数字にはシリアル計算が可能で、日を加減算できます。魅力的とは言えませんが、うまくいきました。

基底数がわかっていさえすれば、シリアル番号を日付に変換できます。例えば、Select date(722815) は 1980/01/01 を返します。したがって、この例では、SrlDate という数値フィールドがある場合 (1 は 1/1/1980、2 は 1/2/1980)、この SQL が返す有効な日付タイプは次のようになります。

Select date(SrlDate + 722814) from MyFile

その他の数値フィールドと文字フィールドには、mmddyy (6 ビット長の数値または文字フィールド)、yyyymmdd (8 ビット長の数値または文字フィールド)、年 (2 または 4 桁)、月、および日ごとにフィールドが異なるなど、日付データが入っています。

これからお示しする例では、SQL の日付タイプに変換する必要がある数値または文字の日付データの段階を設定しています。この例では、年 (4 桁)、月、および日ごとにフィールドが異なっています。SQL Digits() BIF を連結文字 || とともに使用して、文字列を作成します。この SQL ステートメントは、以下のエントリから日付を返します。

select
date(digits(ordyear) || '-' || digits(ordmonth) || '-' || digits(ordday))
from MyFile

最後の 2 つの例では、SQL date() BIF は賢く、数値が渡されている (シリアル日付を表す数字を使用した場合) また、文字値が yyyymmdd 形式で渡されているか認識しているのがわかると思います。

SQL ステートメント 1 つで日付期間を合計する SQL

「どうですか、景気は?」。年初来、過去1ヶ月、今日、関係なくビジネスでよく交わされる言葉ですね。今年の状況を去年の同じ時期と比べたがるのが人の常です。図 5 の SQL は条件付き合計と SQL dayofyear() BIF を使用しており、今年度と前年度の販売単位フィールドである widsold を合計しています。

dayofyear() BIF は、過去の日付の 1 から 365 (うるう年では 366) の数字を返しています。現在の日付 curdate() について dayofyear() を SQL BIF で使用すると、このステートメントは dayofyear() が 1 から widget テーブルの各行の現在の日付の通日までの値を返す場合に、販売単位を合計します。2 番目の条件付き合計節である B では、当年度 -1 を使用して、昨年の同じ日までの合計を算出しています。次のような結果になります。

Constant value	THISYEAR	LASTYEAR
Year to date:	9,821	9,451

同じ技法を使用して、当月のチェックを追加すると、図 6 に示すように、過去 1ヶ月の数字を簡単に算出できます。

dayofyear() BIF は dayofmonth() BIF と置き換えて、その月の日を返します。また、以下に示すように行のタイトルをより明確にするために、monthname() BIF を使用して現在の日付の月名を返しています。

Constant value	THISYEARMTD	LASTYEARMTD
Month to date for July	937	648

結果は、ここで示した内容とは異なるでしょう。この記事が掲載される頃には、2010 年 7月は過去の日付となっているでしょうから。

単純な数字を取得する複雑さ

人生は素晴らしい、でしょう? 毎日、終業時に、上司の前で年初来と過去 1ヶ月の数字を手にしています。そうではありません ― 少なくとも一部のビジネスでは、年度のある時期では曜日別の売上が重要な場合があります。感謝祭の後の金曜日は常に金曜日です (賢いでしょう) が、必ずしも同じ日番号ではありません。2010 年の感謝祭は 11 月 25 日でしたが、2009 年は 26 日でした。年初来と過去 1ヶ月については上記の SQL でうまくいきますが、2010 年 11 月 25 日に対して実行すると、2009 年 11 月 26 日からの売上高が欠ける可能性があります。問題は、火曜日は火曜日、金曜日は金曜日など、去年の同じ日について同じ曜日を見つけることです。これを理解するのに多少苦労しました。おそらく、賢い読者の中にはもっと簡単な方法を見つけることができた人もいると思いますが、私の場合はこうなりました。図 7 の SQL は、widget テーブルの毎日の曜日と、それに対応する昨年度の同じ曜日を示しています。結果を図 8 に示します。

前年度の同じ曜日を見つけるには、基準日を設けることがカギになります。next_day() BIF が私のソリューションです。これは、将来の日付の曜日のタイムスタンプを返します。この場合、去年の同じ週の次の日曜日になります。ここで、去年の日曜日という基準日があります。dayofweek() BIF を使用して、テーブルの各行についてどの曜日かを判断し、去年の同じ日を見つけます。

そのプロセスを使用して、図 9 に示すように、年初来の同じ曜日の売上データを取得できます。これは、ビジネスが実際どのように行われたか、より正確に映し出しています。

Excel 計算式で日付を合計する

すべてのことを i5 サーバーで出来るだけ済ませたいのですが、忌々しい Microsoft 製品を扱わなければならない場合があるのが現実です。Excel で上記 SQL で行った内容と同じようなことをしなければならない場合、前年度の同じ期間と比較して、1 年の同じ日と年初来の数字を取得する方法が 1 つあります。スプレッドシートは、図 10 のように要約したいと考えています。

これを更新するためには、当日の売上高を取得しさえすれば、すべて再計算され、同じ日と年初来の売上高を表示してくれます。

ウィジェットテーブルを i5 から Excel へダウンロードしました。次に、図 11 に示すように、同じ曜日に合わせるように情報を移動しました。マクロを作成せずに、同じ曜日に合わせるための Excel の VLOOKUP 関数を使用するために、これを行いました。

繰り返しますが、その基準日を設けることがカギになります。私の場合、基準日は現在の日付であり、計算式 =today() を使用するセル M3 に常に存在します。昨年度の同じ曜日を取得するには、VLOOKUP 計算式 =VLOOKUP(M3,B2:J500,4,) を使用します。VLOOKUP は、ある列の値を検索し、同じ行の別の列からの値を返す Excel 計算式 (i5 サーバーで SQL の組み込み関数を使用することに似ています) です。この場合、Excel は列ラベル 2010 年にある M3 中の現在の日付を見つけ、4 番目の列を返します。これは、同じ曜日の 2009 年の日付です。同じ計算式が 2008 年の日付を取得する場合にも使用できます。

(著者のメモ: 最初にこのスプレッドシート [下記のダウンロード・リンク] を開いたとき、2009 年と 2008 年は「#N/A」と表示されるかもしれません。これは、データ・セットが 2010 年 7 月 19 日に掛けて実行されているためで、おそらくこの記事をお読みの時点ではその日付を過ぎていると思います。セル M3 がカギです。本日の日付を基準日としたかったので、セルには計算式 = today() が入っています。結果を表示するには、M3 を 7/19/2010 に変更してください。)

年ごとに同じ日の売上を取得するため、VLOOKUP が使用されています。ここでは、検索値が各年の日付でセル M2、N2、および O2 に入っています。2010 年、2009 年、2008 年の同じ日の売上を取得するため、VLOOKUP は M2、N2、O2 に日付を入れて使用され、計算式で売上数字を返しています。2010 年の場合、この計算式は =VLOOKUP(M3,B2:D400,3,1)、2009 年の場合、=VLOOKUP(N3,E2:G400,3,1)、2008 年の場合は =VLOOKUP(O3,H2:J400,3,1) になります。

同じ日付の年初来の売上数字、条件付合計を取得するには、SUMIF 計算式を使用します。2010 年の YTD 売上高を取得するには、セル M5 の計算式は =SUMIF(B2:B400,&Prime<=&Prime&M3,D2:D400) になります。日本語で言うとこうなります。「B2 から B400 (今回の場合、これらは 2010 年の日付です) までの各セルをチェックします。 M3 (この場合は、現在の日付) に格納されている値以下の値が含まれている場合、D2 から D400 (この場合、各日の 2010 年の売上) の対応するセルを加算します」同じ計算式による技法を 2009 年と 2008 年にも使用しますが、チェックと合計の範囲が異なります。

これらの計算式では、その日まだ継続中の売上数字によりスプレッドシートが更新されるため、M3 の基準日が変わり、見栄えが良い表中のすべての数字は、当日の業績を正確に反映するため変更され、さらに年初来と同日の数字も更新されます。

Excel スプレッドシートはここからダウンロード.