SQLで一定時間周期毎の抽出方法を教えてください

Question

初めまして、SQL初心者です
知りたい内容は、

時刻 値1　値2　状態フラグ でデザインされた単表です
中身は1分毎の時刻と、値1,2には数値　状態フラグには0または1のデータが存在します。
こういった表からの抽出条件ですが、一番新しい時刻から
状態フラグは1や0に関わらず5分おきのデータで、値1、値2は、5分間内で状態フラグが0だけの物を値1、値2と別々に平均したSQL文の記述方法です。
5分というのは、ひとつの例で1分毎だったり10分毎の場合もあります。
5分毎というと5レコードづつ存在しますので平均を求められますが1分毎となると全レコードが対象になりまので。1分毎の平均値を求めても意味ないと思いますがそれでも値取得はOKでしょうか？
よろしくお願いします。

taka_tetsu · Accepted Answer

>単に単位時間毎の17:00:00や17:05:00の時の状態フラグが0か1かを知りたいので、 
集計は必要ないです 

ということであれば、集計とは別に検索を行う必要があります。


その方法は、集計結果を1つの表とみなし、元のテーブルと時刻で結合を行います。
Access2000以上ですとfrom句の中にselect文を記述できるのですが、Access97以前だと記述できないので、
あらかじめorVBから集計結果を求めるクエリーを作成しておき、それと元のテーブルを時刻で結合してください。

Access2000以降であれば、次のSQLで表示可能です。

select 集計結果.*, 別名.状態フラグ from テーブル名 as 別名, (集計を求めるSQL文をそのまま記述) AS 集計結果
where 集計結果.時刻 = 別名.時刻

のようになります。(表の結合はINNER JOINを使って記述してもかまいません)

taka_tetsu · Answer

>集計は、単位時間どうりに状態フラグ＝0のみ集計できておりますので思いとおりですが、単位時間毎の状態フラグの値を知りたい時はどこへどのように記述するのでしょうか？色々試してみたのですがはじかれてしまいます 
よろしくお願い致します

結構むちゃなことを言いますね(^^;;
状態フラグの値って。
それだとフラグじゃないような・・・

で、何がほしいですか？
1.状態フラグが0または1のレコード数(単位時間ごと)
2.状態フラグが1であるレコードの割合(単位時間ごと)
3.その他

taka_tetsu · Answer

>データを検索できないのですがどこかが出来ていないのでしょうか 

VBではSQL文を文字列として扱います。
VBでは文字列をあらわすのにダブルクォーテーションで囲みますが、文字列の中に"を入れたいときは、""と記述する必要があります。
つまり、"n"のところは、""n""となります。

もしくは、SQL文の中で文字列を指定するときは、'n'のようにシングルクォーテーションで囲ってもかまいません。


>kaishijikanは、先に取得して変数へ代入しています

これもまずいですね。
SQL文の中に変数の名前を入れても内容を参照してくれません。テーブル中のkaishijikanフィールドを検索しに行ってエラーになってしまいます。単位時間も同様です。

変数の値を、直接SQL中に記述するようにします。


ということで、下記のようにするのがいいのでは？

Dim intUnitTimeTime As Integer '単位時間
Dim dtStartTime As Date '開始時刻
Dim strEditTime As String '加工した時刻
Dim strSQL As String 'SQL文字列


'開始時刻の取得
dtStartTime = 取得した開始時刻

'5分間隔とする
intUnitTimeTime = 5

'加工した時刻のSQL文を作成
strEditTime = "DateAdd( 'n', (Int(DateDiff('n', 時刻,'" & dtStartTime & "')/" & intUnitTimeTime & "))*" & intUnitTimeTime & ", '" & dtStartTime & "')"

'SQL文を作成
strSQL = "select " & strEditTime & ", avg(値1),avg(値2) from テーブル名 where 状態フラグ=0 group by " & strEditTime

taka_tetsu · Answer

>新しい時刻のデータが登録される度に一番古いデータが削除されてしまう構造になっていますので、一番古い時刻データを知るには、その都度SQLを発行して 
求めないといけないように思うのですが 

そういうことであれば、そうですね。1度のSQL文の発行でまとめて済ますこともできなくはないですが、結構面倒くさいので、素直に
select min(時刻) from テーブル名
で時刻の最小値を取得することをお勧めします。

>DateAdd( "n", (Int(DateDiff("n", 時刻, 開始時間)/単位時間))*単位時間, 開始時間) でもとめた時刻を元に
>加工した時刻に置き換えてLoop文を書くのでしょうか？時刻は、テーブルの一番新しいデータレコードですか 

いいえ、DateAdd～の式を、そのまま#1のSQL文の”加工した時刻”と置き換えるだけです。

つまり、SQL文の発行は、開始時刻の取得と、#1のselect文の発行の2回だけです。

あとは、DBが集計まで全部行います。
ループは、データを取り出すところだけです。

taka_tetsu · Answer

>再度ご質問させてください。
>((端数切捨て関数((時刻 - 開始時間)/単位時間))*単位時間) + 開始時間 ですが、時刻は、表の時刻のことですね、開始時間はこのSelect文を実行するシステム時間と判断すれば宜しいのでしょうか？ 

ではなく、テーブル内の一番過去の時刻です。
ようは、時刻-開始時間は、一番古いデータから何分経過したかを求めたいのです。

>単位時間は、5分とか10分ですよね、少し書きもれていましたが時刻は、実際 
2002/09/08 12:00:00 2002/09/08/ 12:01:00という具合に日付まで入っています。例えば100時間分（60×100）のレコードがあったとすると、この関数を使う事で1回のSelect分で5分毎に集計することが出来るのでしょうか？ 

>最終的にはVBに組み込みたいと思っております

ということは時刻は日付型で、DBはAccessであると判断していいでしょうか？
以下は、日付型、Accessと勝手に決めて記述します。

日付、時刻の差を求めるにはDateDiff()を使用します。
これで、計測開始からの経過時間を求めます。

求められた経過時間を単位時間で割り、端数を丸めることで、何単位時間経過したかを求められます。

端数切捨て関数には、切捨てであればInt()、四捨五入であれば、Round()を使用します。
そして、求められた値に単位時間をかけることで、計測開始から何分経過したかが求まります。

そして、開始時刻に、求めたまるめ経過時間を足すと、集計するための単位時間毎に変換された時刻が求まります。
関数は、DateAdd()で求まります。

というのをまとめて１行で式にあらわすと、
DateAdd( "n", (Int(DateDiff("n", 時刻, 開始時間)/単位時間))*単位時間, 開始時間)

となります。

もし、DBがAccessではなくほかのものでも、このような演算を行う関数が用意されていると思うので適時読み替えてください。

taka_tetsu · Answer

select 加工した時刻, avg(値1), avg(値2) from テーブル名 where 状態フラグ = 取得したい状態フラグの値
group by 加工した時刻

という感じになります。

で、問題なのは、「加工した時刻」のところです。
こんな感じになります。両方を以下のような式に置き換えてください。

((端数切捨て関数((時刻 - 開始時間)/単位時間))*単位時間) + 開始時間

1分ごとのときは、そのまま時刻フィールドでかまわないのですが、それ以外の単位時間の場合、集計時間ごとに一意になるように加工する必要が出てきます。

例：
12:01から12:05までを12:05に加工する
12:06から12:10までを12:10に加工する

こうすることにより、単位時間あたりで時刻が同じになるので、SQLの集計関数を使用することが出来るようになります。

SQLで一定時間周期毎の抽出方法を教えてください

>単に単位時間毎の17:00:00や17:05:00の時の状態フラグが0か1かを知りたいので、

この回答への補足

>データを検索できないのですがどこかが出来ていないのでしょうか

この回答への補足

>新しい時刻のデータが登録される度に一番古いデータが削除されてしまう構造になっていますので、一番古い時刻データを知るには、その都度SQLを発行して

この回答への補足

>再度ご質問させてください。

この回答への補足

select 加工した時刻, avg(値1), avg(値2) from テーブル名 where 状態フラグ = 取得したい状態フラグの値

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング