人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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文が考えられるでしょうか?


●質問者: suzume_oyado
●カテゴリ:コンピュータ
✍キーワード:SQL データ 時系列
○ 状態 :終了
└ 回答数 : 2/4件

▽最新の回答へ

1 ● hu2
●70ポイント

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

(テーブル名を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) 
/

2 ● jin_jin74
●0ポイント

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


まず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を記載しなかった質問者様の自己責任ということで。

◎質問者からの返答

??

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ