SQL文を教えて下さい。下記のような時系列のデータを格納するテーブルがあったとします。


時刻 データ
--------------
10:10 100
10:12 200
10:13 300
10:15 500
10:19 600
10:21 300

これを下記のように5分毎に抜粋したテーブルを出力したいですが

時刻 データ
--------------
10:10 100
10:15 500
10:19 600

必ずしも毎分データがあるわけではありません。(その場合は直前の値を出す)
どのようなSQL文が考えられるでしょうか?

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/04/25 17:50:02
  • 終了:2010/05/02 17:50:02

回答(4件)

id:hu2 No.1

hu2回答回数12ベストアンサー獲得回数02010/04/25 19:26:12

ポイント70pt

以下ではどうでしょうか。

(テーブル名をtimeline、時間をtime、データをdataとしています)

SELECT *
FROM
	timeline r1
WHERE
	EXISTS (SELECT * FROM 
		(SELECT
			MAX(time) AS time
		FROM
			(SELECT
				time
				,SUBSTR(time, 0, 2) AS hour
				,CASE
					WHEN SUBSTR(time, 4, 2) <= '05' THEN 1
					WHEN SUBSTR(time, 4, 2) <= '10' THEN 2
					WHEN SUBSTR(time, 4, 2) <= '15' THEN 3
					WHEN SUBSTR(time, 4, 2) <= '20' THEN 4
					WHEN SUBSTR(time, 4, 2) <= '25' THEN 5
					WHEN SUBSTR(time, 4, 2) <= '30' THEN 6
					WHEN SUBSTR(time, 4, 2) <= '35' THEN 7
					WHEN SUBSTR(time, 4, 2) <= '40' THEN 8
					WHEN SUBSTR(time, 4, 2) <= '45' THEN 9
					WHEN SUBSTR(time, 4, 2) <= '50' THEN 10
					WHEN SUBSTR(time, 4, 2) <= '55' THEN 11
					ELSE 12
				END AS group_no
			FROM
				timeline)
		GROUP BY
			hour
			,group_no) r2
	WHERE
		r1.time = r2.time)  
/
id:jin_jin74 No.2

jin_jin74回答回数29ベストアンサー獲得回数12010/04/26 00:42:07

手元でテストをしていないのでその点は悪しからず。


まず3ステップで考えます。

1.5分ごとに集計するベースになるマスタデータ?を用意する。

2.集計ベースに応じ、集計対象を決定する。

3.決定した集計対象に対し、最大時間の取得と合計の取得を行う。


2.は簡単ですね。データに対して集計ベースをLEFT JOINして、集計ベースの時間でGroup Byすればいいんですから。

3.も然り。2.の際にMAX(時刻), SUM(データ)を用意すれば良いんですから。

集計ベースをB(時刻)、質問のデータテーブルをTとすると以下のクエリが書けます。

  SELECT MAX(T.時刻), sum(T.データ) FROM T LEFT JOIN B ON T.時刻>B.時刻 - 5 minutes AND T.時刻<=B.時刻

  GROUP BY B.時刻

これでテーブルBに格納されている時刻より前5分間と時間が一致するデータがまとめて集約され、

その中で最大値の時刻とデータの合計値が取得できます。


問題は1.ですね。前出のクエリにおけるテーブルBの作成。

動的に作れれば一番なんでしょうが・・・5分ステップの時刻を生成するイメージが湧きませんでした。

安易な対応としてはテーブルBを静的に作ってしまうことでしょうか。


テーブルB

時刻

------

10:00

10:05

10:10

10:15

10:20

10:25

10:30

10:35

10:40

10:45

10:50

10:55

11:00

11:05

・・・・・・こんな感じで。格好悪いけど。


※この回答者はdate型とテキストのCASTなどを考慮していません。

※同様に時刻計算でPostgreSQLの記法を参考にしているので、別のPlatformには対応しない可能性があります。

 まあその点はPlatformを記載しなかった質問者様の自己責任ということで。

id:suzume_oyado

??

すみません。質問は、SQL文を要求したのですが・・・。

2010/04/26 09:30:48

質問者が未読の回答一覧

 回答者回答受取ベストアンサー回答時間
1 hashizo 9 7 0 2010-04-26 20:18:32
2 yamayamada 2 1 0 2010-04-27 14:07:32

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません