如何从 postgresql 中的查询中获取最小值、中值和最大值? [英] How do I get min, median and max from my query in postgresql?
问题描述
我写了一个查询，其中一列是一个月.从中我必须得到最小月份、最大月份和中位数月份.以下是我的查询.
I have written a query in which one column is a month. From that I have to get min month, max month, and median month. Below is my query.
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
如果不可能，那么有可能获得最大月份和最小月份吗?
If it is not possible, than is it possible to get max month and min month?
推荐答案
您需要名为 min 和 max 的聚合函数.请参阅 PostgreSQL 文档和教程:
You want the aggregate functions named min and max. See the PostgreSQL documentation and tutorial:
- http://www.postgresql.org/docs/current/static/tutorial-agg.html
- http://www.postgresql.org/docs/current/static/functions-aggregate.html
PostgreSQL 中没有内置中位数，但是已经实现并贡献给了 wiki:
There's no built-in median in PostgreSQL, however one has been implemented and contributed to the wiki:
http://wiki.postgresql.org/wiki/Aggregate_Median
一旦您加载它，它的使用方式与 min 和 max 相同.用 PL/PgSQL 编写它会慢一点，但如果速度至关重要，甚至还有一个 C 版本，你可以适应.
It's used the same way as min and max once you've loaded it. Being written in PL/PgSQL it'll be a fair bit slower, but there's even a C version there that you could adapt if speed was vital.
更新评论后:
听起来您想在单个结果旁边显示统计汇总.您无法使用普通聚合函数执行此操作，因为您无法引用不在结果列表中 GROUP BY 中的列.
It sounds like you want to show the statistical aggregates alongside the individual results. You can't do this with a plain aggregate function because you can't reference columns not in the GROUP BY in the result list.
您需要从子查询中获取统计信息，或将您的聚合用作窗口函数.
You will need to fetch the stats from subqueries, or use your aggregates as window functions.
给定的虚拟数据:
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的中位数聚合后:
你可以用一个普通的聚合来做到这一点:
You can do this with an ordinary aggregate:
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
因为在聚合模型中将平均值与单个值一起显示是没有意义的.您可以显示群组:
because it doesn't make sense in the aggregation model to show the averages alongside individual values. You can show groups:
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 中的新功能.>
... but it sounds like you want the individual values. For that, you must use a window, a feature new in 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
这篇关于如何从 postgresql 中的查询中获取最小值、中值和最大值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
