[pgsql-jp: 38932] Re: グループごとの最大値を持つデータを取得したい
kaigai @ kaigai.gr.jp
kaigai @ kaigai.gr.jp
2007年 11月 7日 (水) 12:03:13 JST
海外@SE-PostgreSQLです。
> こんにちは、中出と申します。
>
> しばらくSQLで悩んでいるのですが、良い方法が
> 思いつかなかったので投稿させていただきます。
似たような問題意識を持っているので、現在の私の解法を
ご紹介します。もっと良い方法があれば教えて頂けると助
かります。
> 以下のようなテーブルがあり、データがあるとします。
> categoryごとにcntが最大のデータを1回のSQL発行で
> 取得できないかと思っています。
> よい方法があれば教えてください。
>
> なお、category分のSQLを発行してcntが最大のデータ
> を取得するというのもありますが、categoryが増える
> 可能性がありますので、できれば避けたいと考えてい
> ます。
>
> 以上、よろしくお願いします。
>
> ●テーブル
> CREATE TABLE "test"
> (
> category int4 NOT NULL,
> name varchar(10) NOT NULL,
> cnt int4 DEFAULT 0
> )
>
> ●データ
> category | name | cnt
> ---------+------+-----
> 1 | hoge | 1
> 1 | fugo | 2
> 2 | fufu | 1
> 2 | hoho | 3
>
> ●取り出したいデータ
> category | name | cnt
> ---------+------+-----
> 1 | fugo | 2
> 2 | hoho | 3
SELECT * FROM test WHERE (category, cnt)
in (SELECT category, MAX(cnt) FROM test GROUP BY category);
で、どうでしょう?
以下、余談:
私が悩んでいる問題というのは、階層型データベースを実装する場合に
必要となる機能で、機密レベルが異なれば同一の主キーに対して複数の
タプルが存在し得るというテーブル(Polyinstantiationテーブル)を、
どの様に実装するかという問題です。
つまり、ユーザに見える主キーが (PK1, ... , PKn) であった場合、真の
主キーは (LV, PK1, ... , PKn) になるというもので、見かけの主キーが
同一のタプルは、ユーザが参照可能な最も高い機密レベルのものだけが見
える必要があります。
(*) LVは機密レベル。各タプルのシステム列に格納される。
これの実装は SQL の書換えによって行なおうと思っているのですが、
オリジナル構文:
SELECT * FROM t WHERE a > 0;
書換え後:
SELECT * FROM t WHERE a > 0 and (LV, PK1, ... , PKn)
in (SELECT MAX(LV), PK1, ... ,PKn FROM t GROUP BY PK1, ... ,PKn);
こういった場合に、もっとエレガントな書き方ってあるのでしょうか?
--
KaiGai Kohei
pgsql-jp メーリングリストの案内