我如何从 postgresql 中的查询中获取最小值、中值和最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12067656/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do i get min, median and max from my query in postgresql
提问by Deepak Kumar
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.
如果不可能，则有可能获得最大月份和最小月份。
采纳答案by Craig Ringer
You want the aggregate functions named minand max. See the PostgreSQL documentation and tutorial:
您需要名为min和的聚合函数max。请参阅 PostgreSQL 文档和教程：
- 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
There's no built-in median in PostgreSQL, however one has been implemented and contributed to the wiki:
PostgreSQL 中没有内置中位数，但是已经实现并贡献给了 wiki：
http://wiki.postgresql.org/wiki/Aggregate_Median
http://wiki.postgresql.org/wiki/Aggregate_Median
It's used the same way as minand maxonce 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.
它的使用方式min与max加载后的方式相同。用 PL/PgSQL 编写它会慢一点，但如果速度至关重要，甚至还有一个 C 版本，你可以适应。
UPDATEAfter comment:
更新后评论：
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 BYin the result list.
听起来您想在单个结果旁边显示统计聚合。您无法使用普通聚合函数执行此操作，因为您无法引用不在GROUP BY结果列表中的列。
You will need to fetch the stats from subqueries, or use your aggregates as window functions.
您需要从子查询中获取统计信息，或者将聚合用作窗口函数。
Given dummy data:
给定虚拟数据：
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);
... and after adding the median aggregate from the PG wiki:
...并在添加来自 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)
but not this:
但不是这个：
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)
... but it sounds like you want the individual values. For that, you must use a window, a feature new in PostgreSQL 8.4.
...但听起来您想要单个值。为此，您必须使用window，这是 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)
See also:
也可以看看：
回答by Tobi Oetiker
To calculate the median in PostgreSQL, simply take the 50% percentile (no need to add extra functions or anything):
要计算PostgreSQL 中的中位数，只需取 50% 的百分位数（无需添加额外的函数或任何东西）：
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY x) FROM t;
回答by Aray Karjauv
One more option for median:
中位数的另一种选择：
SELECT x
FROM table
ORDER BY x
LIMIT 1 offset (select count(*) from x)/2
