[pgsql-jp: 39032] Re: 行番号を取得するユーザー関数について
Tomoaki Sato
sato ＠ sraoss.co.jp
2007年 12月 27日 (木) 01:55:28 JST
佐藤です。
From: <dai_sha_ring ＠ yahoo.co.jp>
Date: Fri, 21 Dec 2007 20:50:19 +0900 (JST)
> PostgreSQL8.2にてOracleのRownumのような、行番号を取得す
> る
> ユーザー関数を作りたいと思っています。
> 以下のような関数をつくり、行番号を取得しようと考えている
> のですが
> どうしても思ったような動作ができず投稿させていただきまし
> た。
>
> -- 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_datatable]
> -----------------------------------
> p_date(timestamp) p_value(integer)
> -----------------------------------
> 2007/12/01 10
> 2007/12/02 15
> 2007/12/04 30
> 2007/12/07 45
> 2007/12/08 50
> 2007/12/09 75
>
> このテーブルから１つ前のレコードからの増加量を求めようと
> しています。
> そこで、作成した関数を使用して下のようなビューを作成しま
> した。
>
> [view_datatable_offset]
> CREATE VIEW view_datatable_offset AS
> SELECT
> tbl1.p_date
> ,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 view_datatable ORDER BY p_date
> ) AS tbl1
> ,
> (SELECT
> getrownum('tmpseq2')+1 AS rownum,*
> FROM view_datatable ORDER BY p_date
> ) AS tbl2
>
> WHERE tbl1.rownum = tbl2.rownum;
>
> これで、
>
> SELECT * FROM view_datatable_offset
>
> というSQLを発行しますと
> --------------------------------------------------
> p_date p_value_tbl2 p_value_tbl1 p_value_offset
> --------------------------------------------------
> 2007/12/02 10 15 5
> 2007/12/04 15 30 15
> 2007/12/07 30 45 15
> 2007/12/08 45 50 5
> 2007/12/09 50 75 25
>
> と、予想どおりの結果が帰ってくるのですが、
>
> SELECT * FROM view_datatable_offset WHERE p_value_tbl1 =
> 15
>
> という風に条件をいれると結果がもどってきません。
これは、p_value_tbl1 = 15 (tbl1.p_value = 15) という条件によって tbl1
テーブルの行が 1 行に絞り込まれ、
tbl1
rownum | p_date | p_value
--------+---------------------+---------
1 | 2007-12-02 00:00:00 | 15
tbl2 テーブルと tbl1.rownum = tbl2.rownum という条件によって結合されま
すが、
tbl2
rownum | p_date | p_value
--------+---------------------+---------
2 | 2007-12-01 00:00:00 | 10
3 | 2007-12-02 00:00:00 | 15
4 | 2007-12-04 00:00:00 | 30
5 | 2007-12-07 00:00:00 | 45
6 | 2007-12-08 00:00:00 | 50
7 | 2007-12-09 00:00:00 | 75
条件に一致する行は存在しないので、結果は 1 件も取得することができませ
ん。
> また、
>
> SELECT * FROM view_datatable_offset WHERE p_date >
> '2007/12/07'
>
> という条件をいれると、
> --------------------------------------------------
> p_date p_value_tbl2 p_value_tbl1 p_value_offset
> --------------------------------------------------
> 2007/12/08 45 50 5
> 2007/12/09 50 75 25
> という結果が帰らず、
>
> --------------------------------------------------
> p_date p_value_tbl2 p_value_tbl1 p_value_offset
> --------------------------------------------------
> 2007/12/09 10 75 65
> という結果になってしまいます。
これは、p_date > '2007-12-07' という条件によって tbl1、tbl2 テーブルの
行が絞り込まれ、その結果に対して rownum 列の値が割り振られ、
tbl1
rownum | p_date | p_value
--------+---------------------+---------
1 | 2007-12-08 00:00:00 | 50
2 | 2007-12-09 00:00:00 | 75
tbl2
rownum | p_date | p_value
--------+---------------------+---------
2 | 2007-12-08 00:00:00 | 50
3 | 2007-12-09 00:00:00 | 75
tbl1.rownum = tbl2.rownum という条件によって結合されるので、p_date 列
の値が 2007-12-08 となる行は条件に一致せず、取得することができません。
これらの動作は PostgreSQL としては正しいものであり、期待するような結果
を取得できないのは、行番号 (rownum 列の値) を副問い合わせを取得した結
果に対して割り振っているためです。
> この問題について、他の掲示板にて質問をし、行番号を使用し
> ない
> 代替え案をアドバイスいただいたのですが、速度的に不安な面
> があ
> り、どうにかして、ユーザー関数で行番号を取得できないか悩
> んで
> います。
行番号を割り振って期待されるような結果を取得するよい方法は思い付きませ
んでしたが、以下のようなビューを定義すれば期待されるような結果を取得で
きます。ただし、
CREATE VIEW view_datatable_offset AS
SELECT DISTINCT ON (tbl2.p_date)
tbl2.p_date,
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 p_date, p_value FROM view_datatable ORDER BY p_date
) AS tbl2, (
SELECT p_date, p_value
FROM view_datatable
ORDER BY p_date
OFFSET 1
) AS tbl1
WHERE tbl2.p_date < tbl1.p_date;
行数の増加に伴って結合の回数も増加してしまい、性能に問題が発生する可能
性があるため、以下のようなビューも考えてみました。p_date 列にインデッ
クスを定義すれば、それなりの性能を発揮できるはずです。
CREATE INDEX view_datatable_p_date_idx ON view_datatable (p_date);
CREATE VIEW view_datatable_offset AS
SELECT p_date,
p_value_tbl2,
p_value_tbl1,
p_value_tbl1 - p_value_tbl2 AS p_value_offset
FROM (
SELECT p_date, (
SELECT p_value
FROM view_datatable
WHERE p_date = (
SELECT max(p_date)
FROM view_datatable
WHERE p_date < tbl1.p_date
)
) AS p_value_tbl2,
p_value AS p_value_tbl1
FROM view_datatable AS tbl1
) AS tbl2
WHERE p_value_tbl2 IS NOT NULL;
ちなみにどちらのビューも p_date 列の値は一意であることが前提となります。
----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan
pgsql-jp メーリングリストの案内