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

質問

初めまして、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でしょうか？
よろしくお願いします。

回答No.6

taka_tetsu · ベストアンサー

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

ということであれば、集計とは別に検索を行う必要があります。

その方法は、集計結果を1つの表とみなし、元のテーブルと時刻で結合を行います。
Access2000以上ですとfrom句の中にselect文を記述できるのですが、Access97以前だと記述できないので、
あらかじめorVBから集計結果を求めるクエリーを作成しておき、それと元のテーブルを時刻で結合してください。

Access2000以降であれば、次のSQLで表示可能です。

select 集計結果.*, 別名.状態フラグ from テーブル名 as 別名, (集計を求めるSQL文をそのまま記述) AS 集計結果
where 集計結果.時刻 = 別名.時刻

のようになります。(表の結合はINNER JOINを使って記述してもかまいません)

お礼

この度は、大変お世話になり有難うございました。
おかげさまで、実行可能となりました。
少し変わっている所がありましたが結果は正常です。
最初に何を使っているか記述していませんでしたMSSQL2000でした。
INNER JOINを使いましたので as 集計結果が 集計結果 ON
となりました。色々と親切にしていただき本当に有難うございました

回答No.5

taka_tetsu

>集計は、単位時間どうりに状態フラグ＝0のみ集計できておりますので思いとおりですが、単位時間毎の状態フラグの値を知りたい時はどこへどのように記述するのでしょうか？色々試してみたのですがはじかれてしまいます
よろしくお願い致します

結構むちゃなことを言いますね(^^;;
状態フラグの値って。
それだとフラグじゃないような・・・

で、何がほしいですか？
1.状態フラグが0または1のレコード数(単位時間ごと)
2.状態フラグが1であるレコードの割合(単位時間ごと)
3.その他

補足

本当に何度も無茶を言ってすみません。3のその他になると思います
例として下記データを頂いた集計方法で加工した場合
　時刻　　　　　　　　値1　値2　状態フラグ
2002/09/11　17:00:00　10　10　1 '集計されないデータ
2002/09/11　17:01:00　20　15　0
2002/09/11　17:02:00　30　20　0
2002/09/11　17:03:00　40　30　0
2002/09/11　17:04:00　50　35　1 '集計されないデータ
2002/09/11　17:05:00　60　40　0
2002/09/11　17:06:00　70　45　0
2002/09/11　17:07:00　80　50　0

加工後

　　時刻 値1 値2
2002/09/11 17:00:00 30 21.66 ' 17:00:00～17:04:00が集計されている
2002/09/11 17:05:00 70 45 ' 17:05:00～17:07:00が集計されている
となります。
単に単位時間毎の17:00:00や17:05:00の時の状態フラグが0か1かを知りたいので、
集計は必要ないです
2002/09/11 17:00:00のレコードの状態フラグは1だったよとか
2002/09/11 17:05:00のレコードの状態フラグは0でしたという具合なので
加工後データの値2の次に状態フラグの値を知ることが出来る
列が出来ればとても助かります。
よろしくお願い致します

回答No.4

taka_tetsu

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

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

お礼

お返事遅くなりまして申し訳ございませんでした。
おかげさまで、できました。
どうゆう動き方をするのか判りませんでしたので、大変感謝しております。
細かい所まで親切にしていただきまして本当に有難うございました。
ＰＳ　'n'が Dateinterval.minuteになっていました。
intを使うとエラーがでて出来ませんでしたので、外しました

補足

大変申し訳ございませんが、もう一点教えていただけませんでしょうか？
動作は思いどうりに動いております。
集計は、単位時間どうりに状態フラグ＝0のみ集計できておりますので思いとおりですが、単位時間毎の状態フラグの値を知りたい時はどこへどのように記述するのでしょうか？色々試してみたのですがはじかれてしまいます
よろしくお願い致します

回答No.3

taka_tetsu

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

そういうことであれば、そうですね。1度のSQL文の発行でまとめて済ますこともできなくはないですが、結構面倒くさいので、素直に
select min(時刻) from テーブル名
で時刻の最小値を取得することをお勧めします。

>DateAdd( "n", (Int(DateDiff("n", 時刻, 開始時間)/単位時間))*単位時間, 開始時間) でもとめた時刻を元に
>加工した時刻に置き換えてLoop文を書くのでしょうか？時刻は、テーブルの一番新しいデータレコードですか

いいえ、DateAdd～の式を、そのまま#1のSQL文の”加工した時刻”と置き換えるだけです。

つまり、SQL文の発行は、開始時刻の取得と、#1のselect文の発行の2回だけです。

あとは、DBが集計まで全部行います。
ループは、データを取り出すところだけです。

お礼

とても丁寧に教えていただきまして有難うございました。
こうゆうロジックはとても想像つきません。
実際に動かして見たくてパソコンにへばりついています。

補足

大変申し訳ございません。
データを検索できないのですがどこかが出来ていないのでしょうか
command as new sqlcliend.sqlcommand("select DateAdd( "n", (Int(DateDiff("n", 時刻, kaishijikan)/単位時間))*単位時間, kaishijikan),avg(値1),avg(値2) from テーブル名 where 状態フラグ=0 group by DateAdd( "n", (Int(DateDiff("n", 時刻, kaishijikan)/単位時間))*単位時間, kaishijikan)"
です。
"n"がコンパイルでエラーになるようですが
kaishijikanは、先に取得して変数へ代入しています

回答No.2

taka_tetsu

>再度ご質問させてください。
>((端数切捨て関数((時刻 - 開始時間)/単位時間))*単位時間) + 開始時間 ですが、時刻は、表の時刻のことですね、開始時間はこの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ではなくほかのものでも、このような演算を行う関数が用意されていると思うので適時読み替えてください。

補足

お礼が遅くなり申し訳ございません。本当に詳しく御説明いただき感謝いたしております。
私のほうの説明不足がありまして、テーブルは最大1000データレコードありまして
新しい時刻のデータが登録される度に一番古いデータが削除されてしまう構造になっていますので、一番古い時刻データを知るには、その都度SQLを発行して
求めないといけないように思うのですが
DateAdd( "n", (Int(DateDiff("n", 時刻, 開始時間)/単位時間))*単位時間, 開始時間) でもとめた時刻を元にSelect　加工した時刻に置き換えてLoop文を書くのでしょうか？時刻は、テーブルの一番新しいデータレコードですか
何回も申し訳ございません。

回答No.1

taka_tetsu

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

という感じになります。

で、問題なのは、「加工した時刻」のところです。
こんな感じになります。両方を以下のような式に置き換えてください。

((端数切捨て関数((時刻 - 開始時間)/単位時間))*単位時間) + 開始時間

1分ごとのときは、そのまま時刻フィールドでかまわないのですが、それ以外の単位時間の場合、集計時間ごとに一意になるように加工する必要が出てきます。

例：
12:01から12:05までを12:05に加工する
12:06から12:10までを12:10に加工する

こうすることにより、単位時間あたりで時刻が同じになるので、SQLの集計関数を使用することが出来るようになります。

補足

さっそくのお返事有難うございました。
再度ご質問させてください。((端数切捨て関数((時刻 - 開始時間)/単位時間))*単位時間) + 開始時間 ですが、時刻は、表の時刻のことですね、開始時間はこのSelect文を実行するシステム時間と判断すれば宜しいのでしょうか？
単位時間は、5分とか10分ですよね、少し書きもれていましたが時刻は、実際
2002/09/08 12:00:00 2002/09/08/ 12:01:00という具合に日付まで入っています。例えば100時間分（60×100）のレコードがあったとすると、この関数を使う事で1回のSelect分で5分毎に集計することが出来るのでしょうか？
端数切捨て関数は、時間を扱うのでどのような演算になるのでしょうか？
端数切捨てとは、日付型から数値型に変更するとかでの意味でしょうか？
よろしくお願い致します
最終的にはVBに組み込みたいと思っております