- PR -

PostgreSQL 関数による行番号の取得について

1
投稿者投稿内容
u2airsot
会議室デビュー日: 2004/05/13
投稿数: 13
投稿日時: 2007-12-17 18:14
OS:WindowsXP
DB:PostgreSQL 8.2

水道メーターのような積算アナログメーターの値を
以下のようなテーブルに格納しています。
※idが小さいレコードが古いレコードとなります。
コード:

-----------------------------------

id(integer)  p_value(integer)

-----------------------------------

1            10

3            15

7            30

10           45

11           50

15           75

日々の使用量（前のレコードの値との差）を算出するため、
以下のような行番号を取得する関数を作成し、

コード:

-- function:[getrownum(text)]

CREATE OR REPLACE FUNCTION getRownum(text) RETURNS INTEGER AS

'

DECLARE

	seq_tmpname ALIAS FOR ;

         seq_count INTEGER DEFAULT 0;

	ret_value INTEGER DEFAULT 0; 

	sql TEXT; 

BEGIN

	SELECT COUNT(*) INTO seq_count FROM pg_class WHERE relname=seq_tmpname ;

        IF seq_count=0 THEN

		sql := ''CREATE TEMP SEQUENCE ''||seq_tmpname;

		EXECUTE sql;

	END IF;

	SELECT NEXTVAL(seq_tmpname) INTO ret_value;

	RETURN ret_value;

END; 

' 

LANGUAGE 'plpgsql';


作成した関数を使用し、使用量を表示する下のようなビューを
作成しました。

コード:

-- view:[v_datatable]

CREATE VIEW v_datatable AS

	SELECT

	 tbl1.id

	,tbl2.p_value AS p_value_tbl2

	,tbl1.p_value AS p_value_tbl1

	,tbl1.p_value-tbl2.p_value AS p_value_offset

	 FROM

	(SELECT

	 getrownum('tmpseq1') AS rownum,*

	 FROM datatable ORDER BY id

	) AS tbl1

	,

	(SELECT

	 getrownum('tmpseq2')+1 AS rownum,*

	 FROM datatable ORDER BY id

	) AS tbl2

	

	WHERE tbl1.rownum = tbl2.rownum;


以上のような状況で、
SELECT * FROM v_datatable
というSQLを発行すると、
コード:

--------------------------------------------------

id  p_value_tbl2 p_value_tbl1 p_value_offset

--------------------------------------------------

3   10           15           5

7   15           30           15

10  30           45           15

11  45           50           5

15  50           75           25


という思ったとおりの結果が帰ってくるのですが、

SELECT * FROM v_datatable WHERE id=7

という風に条件付きのSQLを発行すると
結果が１行も帰ってきません。

色々試してみたのですが、なぜそうなってしまうのか
原因がわからず、アドバイスをいただきたく投稿させて
いただきました。
関数を作成したのが初めてで、関数の作り方が間違えて
いるのかと思っているのですが。。。

どなたかご教授よろしくお願いします。



[ メッセージ編集済み 編集者: u2airsot 編集日時 2007-12-17 21:11 ]
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2007-12-17 21:21
気になったので、試してみました。
下記のようなコードで実行してみてもらえば分かりますが、tbl1はrownumが1しか進んでいません。一方、tbl2はしっかり規定数進んでいます。
コード:
SELECT * FROM (
	SELECT
		getrownum('tmpseq1') AS rownum,*
	 FROM datatable
) AS tbl1, 
(
	SELECT
		getrownum('tmpseq2')+1 AS rownum,*
	FROM datatable ORDER BY id
) AS tbl2
where
    tbl1.id = 7


結果、tbl1.rownum = tbl2.rownumを満たすレコードを得られなかったものと思われます。

「おぷちまいざ」が気を利かせて、tbl1のレコードセットを減らしてくれたのでしょう。
余計なお世話ですね。

以上が原因でしょう。対策は分かりません。後はPostgres SQLのえらい人に丸投げ−。
上総
大ベテラン
会議室デビュー日: 2006/06/22
投稿数: 107
投稿日時: 2007-12-17 22:11
一応下記の関数を作成すれば問題ないかと思います。
（オプティマイザを一時的に無効にするのも面倒かと思います。）
引数にはIDを渡し、一個前のMAX値を取ります。
7を渡した場合は、一つ前のMAXである15を返します。
コード:
CREATE OR REPLACE FUNCTION getPrevValue ( integer ) RETURN integer AS
'
    SELECT      ALL
                MAX ( p_value )
    FROM        (
                SELECT      ALL
                            id,
                            p_value
                FROM        DATATABLE
                UNION       ALL
                SELECT      ALL
                            0,
                            1
                ) AS PrevValue
    WHERE       id < ;
'
LANGUAGE SQL
u2airsot
会議室デビュー日: 2004/05/13
投稿数: 13
投稿日時: 2007-12-18 15:45
上総さん かずさん

ご教授ありがとうございました。
上総さんの、やり方でうまくいきました。
Rownumにこだわりすぎて視野が狭くなっていた気がします。

ただ、今回作成しているアプリケーションでは問題ない速度なのですが、
getRownum を使って作成したViewと
getPrevValue を使って作成したViewと比べますと、
後者のほうが処理が重たくなった感じがします。

今後のために、PostgreSQLでRownumを実現する方法を知りたい
のですが、みなさまはどのような方法を使っていますか？

独学で勉強していまして、セオリーというものをわかっていません。
「そもそもPostgreSQLでRownumを使おうとしてること自体がおかしい！」
という意見でも結構ですので、引き続きご教授お願いします。
上総
大ベテラン
会議室デビュー日: 2006/06/22
投稿数: 107
投稿日時: 2007-12-18 18:00
引用:

u2airsotさんの書き込み（投稿日時: 2007-12-18 15:45）より
ただ、今回作成しているアプリケーションでは問題ない速度なのですが、
getRownum を使って作成したViewと
getPrevValue を使って作成したViewと比べますと、
後者のほうが処理が重たくなった感じがします。


少なくとも私が掲載した関数で言えば、全件取得は実行速度的にNGでしょうね。
意図的にオプティマイザをオフにする方法を模索するのが良いかもしれません。
（但し、オプティマイザをオフにするのは、今回のSQLの実行時のみに絞るのが
　良いでしょう。）

最初からテーブルに行番号を振れば丸く収まりそうですがどうですかね？
u2airsot
会議室デビュー日: 2004/05/13
投稿数: 13
投稿日時: 2007-12-19 09:16

> 最初からテーブルに行番号を振れば丸く収まりそうですがどうですかね？
そうですね。。。できればそうしたいのですが。

実際のテーブルは、

id:シーケンス(行番号)
point_id:測定計器のID
pvalues_date:測定日時
pvalues_value:測定値

という風につくっています。
測定計器ごとにテーブルを作るのは大変だと判断して、
今回は、１つのテーブルに全ての計器の測定値を詰め込み、
測定計器のID(point_id)ごとにビューを作成して対応しよう
と考え、このようにしました。
行番号を振りたいのは、測定計器(ビュー)単位ですので、最初から
行番号は振れないと考えています。

コード:
id        point_id  pvalues_date            pvalues_value
------------------------------------------------------------
  :       :              ：                 ：                    
263330    1         2007/12/18 19:00:00     9.9
263331    2         2007/12/18 19:00:00     50
263332    3         2007/12/18 19:00:00     0.37
263333    4         2007/12/18 19:00:00     0.43
263334    5         2007/12/18 19:00:00     0.17
263335    6         2007/12/18 19:00:00     0.18
263336    7         2007/12/18 19:00:00     407
  :       :              ：                 ：                    
  :       :              ：                 ：                    
263897    32        2007/12/18 21:00:00     0.0000
263898    115       2007/12/18 21:00:00     7.1
263902    31        2007/12/18 22:00:00     0.00
263903    32        2007/12/18 22:00:00     0.0000
263904    115       2007/12/18 22:00:00     7.2
263905    116       2007/12/18 22:00:00     6.5
263906    29        2007/12/18 23:00:00     0.36
263907    30        2007/12/18 23:00:00     0.0258
263908    31        2007/12/18 23:00:00     0.00
263909    32        2007/12/18 23:00:00     0.0000
263910    115       2007/12/18 23:00:00     7.2
263911    116       2007/12/18 23:00:00     7.1



> 意図的にオプティマイザをオフにする方法を模索するのが良いかもしれません。
オプティマイザという言葉を今回初めて聞きました。
オプティマイザのＯＮ・ＯＦＦについて、調べてみましたが、なかなか。。。
参考になるようなおすすめのサイトはございますか？



今回、行番号をどうやって付加するのか調べているうちに、
Oracleには、大分前からRownumという関数が存在して、
SQLServerにも最近（？）行番号を付加できる機能がついたとわかりました。
PostgreSQLについては、「できない」という情報が圧倒的です。
なぜ、PostgreSQLは行番号をつける関数を用意してないのか、考えているうちに
「そもそも、自動で行番号をつける機能が必要なこと自体、データベースの設計がよくないのでは？」
と疑問に思っています。トホホ。。

1

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