ねもぷらす

ふぁいんでぃんぐねもの日記。プログラミングとか育児とか

最大値を含むレコードの項目を取得する方法 例

課題

会社は Oracle しかないので、久々に PostgreSQL を使おうと思ったのが運のつき、以下の内容は PostgreSQL 8.4 以降じゃないと×でした。。


取得した集合の中から最大値を含むレコードを取得するには、分析関数とやらを使うとよいらしい。
http://www.atmarkit.co.jp/fdb/rensai/sqlclinic08/sqlclinic08_1.html

  • 集計関数
    • 複数レコードからひとつの集合データを取得
    • count, sum, min, max, average ...
  • 分析関数
    • 取得した集合データの中でさらに分析
    • rank() over(partition by XXX order by XXX)


できればこんなことをしないで済む設計にしたいところですが…嘆く時間がもったいない。
たとえば id:yamazaru_rengou のデータを集計せずに、取得した結果に年度別のランキングをつけてみる。

SELECT 氏名,年度,金額,収支理由,
       rank() over(partition by 氏名,年度 order by 金額 desc,収支理由 asc) 順位
  FROM 収入情報
 WHERE 氏名='id:yamazaru_rengou' and 年度=2010
ORDER BY 氏名,年度,収支理由

結果は↓。

氏名 年度 収支金額 収支理由 順位
id:yamazaru_rengou 2010 100,000 4月給与 2
id:yamazaru_rengou 2010 100,000 5月給与 3
id:yamazaru_rengou 2010 100,000 6月給与 4
id:yamazaru_rengou 2010 800,000 こども手当て 1

rank() 関数は over 以降の条件にしたがって順位をつけてくれる動き。
一度取得したデータの中で表示するときに動作してくれるので、ほかの方法で実装しようとするとかなりコストが高い（はず）。


コレだと結果が複数行返ってしまう。
結果は１レコードでほしい、金額は SUM したい、収支理由は別な条件にしたがって取得したい…そんな我侭に答えるための SQL は以下になる。

SELECT B.* FROM (
    SELECT 氏名,年度,収支理由,
           sum(金額) over(partition by 氏名,年度 ) 金額,
           rank()    over(partition by 氏名,年度 order by 金額 desc,収支理由 asc) 順位
      FROM 収入情報
    ORDER BY 氏名,年度,収支理由
) B WHERE B.順位 = 1

RANK() OVER も使えない DBMS の場合は、↓のような書き方になってしまうのだ。

SELECT
    A.*,
   (SELECT B.収支理由
      FROM 収入情報 B
     WHERE B.氏名=A.氏名
       AND B.年度=A.年度
       AND B.金額=A.最大額
    ORDER BY B.氏名,B.年度,B.収支理由
    LIMIT 1 OFFSET 0 ) as 最大収支事由
FROM
   (SELECT 氏名,年度,sum(金額) as 金額, max(金額) as 最大額
      FROM 収入情報
    GROUP BY 氏名,年度) A


今回の例があえて rank() だの使う必要ないじゃんと思えるのは、まだ単純な設計だったから。
そして個人的な意見ですが、分析関数が必要となるようなＤＢ設計は回避したいものだなと。
ちょろっと調査するときとかは良いけど、こんな SQL がプログラムで四六時中実行されてるとすると枕を低くすることになります。