PostgreSQLでは,OracleのようにROWNUMが使えない.仕方がないので,一時シーケンスを使ってROWNUMもどきをやってみた.

PostgreSQLでのROWNUMもどき

PostgreSQLでは,OracleのようにROWNUMが使えない(ROWNUMとは,クエリーの結果に対して通番を振る機能).

まぁPostgreSQLの場合は,Oracleと違ってlimit,offsetが使えるので ROWNUMが必要な状況は少ないはずだが,それでも必要な場合はある.というか,あった.

で,あれこれ考えたが,結局一時シーケンスを使って対応することにした.

SQL

drop sequence if exists seq_rownum;
create temporary sequence seq_rownum minvalue 1;

SELECT
	nextval('seq_rownum') AS rownum,
	s.*
FROM
	(
	SELECT
		data1
	FROM
		tab
	ORDER BY
		key1
	) s

実行結果

rownumdata1
1109.90
2109.50
3109.50
4109.60
5109.05

内側のサブクエリーでデータ本体をSELECTしてソートし, その結果に対して,外側のクエリーでシーケンスから取得したrownumもどきをくっつけている.

先頭でdrop sequenceするなら,一時シーケンスじゃなくても良さそうな気もするが, 一時シーケンスにしておかないと,他のセッションと同時実行されたときに rownumの奪い合いが生じてrownumが飛んでしまうので,駄目だ.

また逆に,一時シーケンスならコネクションが終了したときにシーケンスが削除されるので, dropしなくても良いような気もするが,今時はコネクションプーリングなどで, クエリーが終了してもコネクションが終了するとは限らないので, 念のためdropするようにしている.その代わり削除されている可能性があるので, if existsでエラーを回避している.

カモランドでは,為替のグラフを作る処理で,これを使っている.

具体的には,Google Chart API に渡すデータを作るときに大量の為替データを100個に減らしているのだが, この減らす時のグループ化で,今回のrownumもどきを使っている. v_countが全データ数,ttmがプロットする為替レート(仲値)だとすると, こういうイメージだ.

select avg(ttm) from ・・・
group by floor(rownum / (floor(v_count / 100) + 1))
rownumをプロットする解像度に応じた値で割った結果でグループ化して,グループ内でttmを平均(avg)している.GoogleChartAPIには,この平均値を簡易エンコードしたものを渡しているというわけだ.

ついでに言うと,この簡易エンコードもSQLというかPL/pgSQLでやっていて,こんな感じだ.

・・・
DECLARE
	v_base text default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
	v_py integer;
	v_encoded text default '';
BEGIN
	v_py = round(61 * (v_ttm - v_min) / (v_max - v_min));
	v_encoded := substring(v_base from v_py for 1);
・・・
  • v_min - プロットする値の最小値
  • v_max - プロットする値の最大値
  • v_ttm - 今回プロットする値

簡易エンコードの場合,0〜61の範囲に値を収める必要があるので,まずその計算をやってv_pyを求めてから, エンコード結果の文字をv_baseから選んでいる.

kamolandをフォローしましょう


© 2019 KMIソフトウェア