- PR -

直近最大値の取得方法で、MAX関数。

1|2 次のページへ»
投稿者投稿内容
トビー
会議室デビュー日: 2009/02/07
投稿数: 2
お住まい・勤務地: 長野
投稿日時: 2009-02-07 20:28

SQL初心者です。皆様よろしくお願いします。

条件：
現在時刻から過去５分前の間での最大値とその時刻を取得する。
但し同一値があった場合は、現在時刻に近い値（直近値）を
採用する。

テーブルTBL_NEDAN
------------------------------
VALUE 値段 NUMBER()
TIME 時刻 CHAR(4)
：
：
：

上記の様な仕様があり、ストアドプロシージャで以下のように作成してみました。
CURSOR CURSOR_01 is
SELECT /*+ INDEX(インデックス) */
VALUE, r_VALUE
TIME r_TM
FROM TBL_NEDAN
WHERE TIME >= 5分前時刻 AND
TIME <= 現在時刻
ORDER BY TIME;
BEGIN
FOR out IN CURSOR_01 LOOP
IF( a1 <= out.r_VALUE ) THEN
a1 := out.r_VALUE;
a1_tm := out.r_TM;
END IF;
END LOOP;
END;
カーソルで該当時間内の全てのレコードを取得し、ループで回しながら
値を１件１件判断し最終的に最大値と時刻をa1とa1_tmに格納する。と言うものです。
上記で直近値を取得出来たのですが、該当レコード件数が1500件とかになると
物凄く遅くなります。(SQL*PLUSでset timing onで時間を表示させてみました。)
また、ORDER BYを外し、IF文を複数使い直近時間の値を取得する方法も試みましたが
遅いのです。

そこで、より早く処理結果を取得する為に、ループではなくMAX関数とかを使用し
上記条件を満たすSQLは書けるのでしょうか？
MAX関数は最大値取得ですし、同一値が複数あった場合は直近値を取得する様な事は
出来るのでしょうか？

皆様、ぜひ解決方法を教えてください。
よろしくお願いします。
King
ぬし
会議室デビュー日: 2008/06/20
投稿数: 284
投稿日時: 2009-02-07 20:48
> MAX関数は最大値取得ですし、同一値が複数あった場合は直近値を取得する様な事は
出来るのでしょうか？

MAX(VALUE) を持つレコードを取得（複数件）し
その中から MAX(TIME) を持つレコードを取得（１件）
すればいいのではないでしょうか。
MAX(VALUE) を取得する SQL をサブクエリにして。
トビー
会議室デビュー日: 2009/02/07
投稿数: 2
お住まい・勤務地: 長野
投稿日時: 2009-02-16 22:38
King様 ありがとうございました。
早速やってみたのですが、本当にこれで良いのか解らなくなってしまいました。
group by とmax関数を上手く使用出来ていないような感じがします。
同じような条件を２箇所に書かなければならないのでしょうか？
アドバイス等ありましたらよろしくお願いします。
select max(TIME),VALUE from(
select TIME,VALUE from TBL_NEDAN
where VALUE = ( select max(VALUE) from TBL_NEDAN
where TIME >= 5分前時刻 AND
TIME <= 現在時刻 )
AND
TIME >= 5分前時刻 AND
TIME <= 現在時刻 )
group by VALUE;

King
ぬし
会議室デビュー日: 2008/06/20
投稿数: 284
投稿日時: 2009-02-16 23:36
> 早速やってみたのですが、本当にこれで良いのか解らなくなってしまいました。
実際に実行しましたか？
テストデータを作って SQL を実行した結果が自分の思い通りのものか
一度試してみて下さい。
よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2009-02-16 23:43
トビーさん、こんにちは。
仕様を理解していないかも知れませんが、下記でどうでしょう？
コード:
SELECT
    VALUE
    , TIME
FROM
    TBL_NEDAN
WHERE
    TIME BETWEEN 5分前時刻 AND 現在時刻
ORDER BY
    VALUE DESC
    , TIME DESC

の１件目。
saki1208
ベテラン
会議室デビュー日: 2006/08/22
投稿数: 86
投稿日時: 2009-02-17 02:00
saki1208です。

手元に確認可能な環境がありませんが、分析関数を使用すれば
シンプルに作成できるのでないかと思います。

キーワードは、
Over
Partition By
Order By

等です。

文脈から、勝手にORACLEだと思ってますが、バージョンによ
っては使用できません。


King
ぬし
会議室デビュー日: 2008/06/20
投稿数: 284
投稿日時: 2009-02-17 10:51
コード:

SELECT
    VALUE,
    MAX(TIME)
FROM
    TBL_NEDAN
WHERE
    (VALUE, TIME) = (
                    SELECT
                        MAX(VALUE),
                        TIME
                    FROM
                        TBL_NEDAN
                    WHERE
                        TIME BETWEEN 5分前時刻 AND 現在時刻
                    )
GROUP BY
    VALUE


コード:

SELECT
    VALUE,
    TIME
FROM
    (
    SELECT
        VALUE,
        TIME,
        MAX(VALUE) OVER () MAX_VALUE,
        MAX(TIME) OVER (PARTITION BY VALUE) MAX_VALUES_TIME
    FROM
        TBL_NEDAN
    )
WHERE
    VALUE = MAX_VALUE
    AND
    TIME  = MAX_VALUES_TIME
King
ぬし
会議室デビュー日: 2008/06/20
投稿数: 284
投稿日時: 2009-02-17 11:10
すみません。
上の書き込みの下の SQL のサブクエリ部に
コード:

WHERE
    TIME BETWEEN 5分前時刻 AND 現在時刻

を追加して下さい。
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）