[pgsql-jp: 36898] Re: 項目毎に件数指定をしたい

[Yasuhiro Shibutani]

初めまして、渋谷といいます。

パフォーマンスを気にされるなら plpgsql でループを使った方が
いいのではないかと思います。

CREATE OR REPLACE FUNCTION "rank_table1_f1" (
  "_top_n" integer
)
RETURNS SETOF "table1" AS
$body$
DECLARE
  "_id" record;
  "_ret" "table1"%ROWTYPE;
BEGIN

  FOR "_id" IN
    SELECT "ID" FROM "table1"
    GROUP BY "ID" ORDER BY "ID"
  LOOP

    FOR "_ret" IN
      SELECT * FROM "table1"
      WHERE "ID" = "_id"."ID"
      ORDER BY "f1" DESC LIMIT "_top_n"
    LOOP
      RETURN NEXT "_ret";
    END LOOP;

  END LOOP;

  RETURN;

END;
$body$
LANGUAGE 'plpgsql'
STABLE CALLED ON NULL INPUT
SECURITY INVOKER;


SELECT * FROM rank_table1_f1(2);


ID のマスタテーブルがあるなら外側のループのクエリは書き換え、
同順 N 位を出力する必要があるなら内側のクエリを書き換えで。

インデックス ID, f1 があれば 50万件でも数秒じゃないかと。
（試してはないですが・・・）


-- 
渋谷 泰宏


On Sat, 18 Mar 2006 14:38:05 +0900
西村　篤史 <a2c ＠ lets-heart.co.jp> wrote:

> 西村＠和歌山市です。
> 
> >> つまり、左端のカラム「ID」ごとにカラム「f1」の値が大きい順に
> >> 上位2位までを表示したいのですが、Select文ではどう書けば良いので
> >> しょうか？
> 
> odagiri atsushi wrote:
> > select *
> > from ex1 e
> > where seq in (select seq from ex1 f where f.id = e.id order by
> > f1 limit 2)
> > order by e.id , e.f1 desc
> 
> 相関クエリというのを知りました。
> レコード数を増やしてやってみましたら、欲しい形の表を得られる事が
> できました。
> 
> >相関クエリなので、パフォーマンスは保障できませんが
> 50万レコードある本番用（といってもまだテスト）のテーブルを使い、
> ビューを作ってから相関クエリを実行させたんですが、マシンスペックの
> 低さも相まって、2時間経っても答えが返ってきませんでした（w
> 
> 「各部門毎に売上金額の上位 n位の商品を降順で出力」といった処理に
> つかうには、スペックアップや夜間バッチ等で作っておく表などの
> チューニングも必要みたいですね。
> 
> from table1 t1 ( from table1 as t1）という文法も知りました。
> 
> rish さん、小田切さん、中司さん、ありがとうございました。

-- 
Yasuhiro Shibutani <shibutani-yxa ＠ necst.nec.co.jp>