Wie bekomme ich min, median und max aus meiner Abfrage in postgresql

Question

Aug 22, 2012, 08:47 AM

Wie bekomme ich min, median und max aus meiner Abfrage in postgresql

Ich habe eine Abfrage geschrieben, in der eine Spalte einen Monat enthält. Daraus muss ich den minimalen Monat, den maximalen Monat und den mittleren Monat ableiten. Unten ist meine Frage.

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

Wenn es nicht möglich ist, ist es möglich, den maximalen Monat und den minimalen Monat zu erhalten.