[解決済み] postgresqlでクエリから最小値、中央値、最大値を取得するにはどうすればよいですか？

質問

私は、1つの列が月であるクエリを書きました。そこから、最小の月、最大の月、中央の月を取得する必要があります。以下は私のクエリです。

select ext.employee,
       pl.fromdate,
       ext.FULL_INC as full_inc,
       prevExt.FULL_INC as prevInc,
       (extract(year from age (pl.fromdate))*12 +extract(month from age (pl.fromdate))) as month,
       case
         when prevExt.FULL_INC is not null then (ext.FULL_INC -coalesce(prevExt.FULL_INC,0))
         else 0
       end as difference,
       (case when prevExt.FULL_INC is not null then (ext.FULL_INC - prevExt.FULL_INC) / prevExt.FULL_INC*100 else 0 end) as percent
from pl_payroll pl
  inner join pl_extpayfile ext
          on pl.cid = ext.payrollid
         and ext.FULL_INC is not null
  left outer join pl_extpayfile prevExt
               on prevExt.employee = ext.employee
              and prevExt.cid = (select max (cid) from pl_extpayfile
                                 where employee = prevExt.employee
                                 and   payrollid = (
                                   select max(p.cid)
                                   from pl_extpayfile,
                                        pl_payroll p
                                   where p.cid = payrollid
                                   and   pl_extpayfile.employee = prevExt.employee
                                   and   p.fromdate < pl.fromdate
                                 )) 
              and coalesce(prevExt.FULL_INC, 0) > 0 
where ext.employee = 17 
and (exists (
    select employee
    from pl_extpayfile preext
    where preext.employee = ext.employee
    and   preext.FULL_INC <> ext.FULL_INC
    and   payrollid in (
      select cid
      from pl_payroll
      where cid = (
        select max(p.cid)
        from pl_extpayfile,
             pl_payroll p
        where p.cid = payrollid
        and   pl_extpayfile.employee = preext.employee
        and   p.fromdate < pl.fromdate
      )
    )
  )
  or not exists (
    select employee
    from pl_extpayfile fext,
         pl_payroll p
    where fext.employee = ext.employee
    and   p.cid = fext.payrollid
    and   p.fromdate < pl.fromdate
    and   fext.FULL_INC > 0
  )
)
order by employee,
         ext.payrollid desc

もしそれが不可能なら、最大月と最小月を取得することは可能でしょうか？

どのように解決するのですか？

という名前の集約関数が必要です。 min と max . PostgreSQLのドキュメントとチュートリアルを参照してください。

• http://www.postgresql.org/docs/current/static/tutorial-agg.html
• http://www.postgresql.org/docs/current/static/functions-aggregate.html

PostgreSQLには組み込みのmedianはありませんが、実装され、wikiに寄稿されています。

http://wiki.postgresql.org/wiki/Aggregate_Median

と同じように使われます。 min と max を読み込むと PL/PgSQLで書かれているため、かなり遅くなりますが、C言語版もありますので、速度が重要な場合はそちらを適用することもできます。

アップデイト コメント後

統計的な集計を個々の結果と一緒に表示したいようですね。にないカラムを参照することができないので、単純な集約関数ではこれを行うことはできません。 GROUP BY を結果一覧に表示します。

サブクエリから統計情報を取得するか、ウィンドウ関数としてアグリゲートを使用する必要があります。

ダミーデータが与えられた

CREATE TABLE dummystats ( depname text, empno integer, salary integer );
INSERT INTO dummystats(depname,empno,salary) VALUES
('develop',11,5200),
('develop',7,4200),
('personell',2,5555),
('mgmt',1,9999999);

を追加し、さらに PG wikiにある中央値集計表 :

普通の集合体でもできます。

regress=# SELECT min(salary), max(salary), median(salary) FROM dummystats;
 min  |   max   |         median          
------+---------+----------------------
 4200 | 9999999 | 5377.5000000000000000
(1 row)

が、これは違う。

regress=# SELECT depname, empno, min(salary), max(salary), median(salary)
regress-# FROM dummystats;
ERROR:  column "dummystats.depname" must appear in the GROUP BY clause or be used in an aggregate function

というのも、平均値を個々の値と一緒に表示するのは、集計モデルとして意味がないからです。グループを表示することはできます。

regress=# SELECT depname, min(salary), max(salary), median(salary) 
regress-# FROM dummystats GROUP BY depname;
  depname  |   min   |   max   |          median          
-----------+---------+---------+-----------------------
 personell |    5555 |    5555 | 5555.0000000000000000
 develop   |    4200 |    5200 | 4700.0000000000000000
 mgmt      | 9999999 | 9999999 |  9999999.000000000000
(3 rows)

... しかし、あなたは個々の値が欲しいようですね。その場合は ウィンドウ これはPostgreSQL 8.4で追加された機能です。

regress=# SELECT depname, empno, 
                 min(salary) OVER (), 
                 max(salary) OVER (), 
                 median(salary) OVER () 
          FROM dummystats;

  depname  | empno | min  |   max   |        median         
-----------+-------+------+---------+-----------------------
 develop   |    11 | 4200 | 9999999 | 5377.5000000000000000
 develop   |     7 | 4200 | 9999999 | 5377.5000000000000000
 personell |     2 | 4200 | 9999999 | 5377.5000000000000000
 mgmt      |     1 | 4200 | 9999999 | 5377.5000000000000000
(4 rows)

こちらもご覧ください。

• http://www.postgresql.org/docs/current/static/tutorial-window.html
• http://www.postgresql.org/docs/current/static/functions-window.html