我如何从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
- 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.
更新发表评论后：
听起来您想显示统计汇总以及各个结果。您不能使用简单的聚合函数来执行此操作，因为您不能引用结果列表中
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
$ b中使用$ b
，因为在聚合模型中没有显示平均值和各个值的意义。您可以显示组：
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
- http://www.postgresql.org/docs/current/static/tutorial-window.html
- http://www.postgresql.org/docs/current/static/functions-window.html
这篇关于我如何从PostgreSQL中的查询中获取最小值，中位数和最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
