sql - 如何计算各种百分位数的count(*)

假设，我有一个表，其中包含从0到9999的整数值，我想绘制每个百分位数中值的总体分布图。
下面是我想到的。有更好的办法吗？

CREATE TABLE A(x INTEGER);

SELECT
(SELECT COUNT(*) FROM A WHERE x>=0  AND x<10) AS prcntl_01,
(SELECT COUNT(*) FROM A WHERE x>=10 AND x<20) AS prcntl_02,
(SELECT COUNT(*) FROM A WHERE x>=20 AND x<30) AS prcntl_03,
(SELECT COUNT(*) FROM A WHERE x>=30 AND x<40) AS prcntl_04,
(SELECT COUNT(*) FROM A WHERE x>=40 AND x<50) AS prcntl_05,
...
(SELECT COUNT(*) FROM A WHERE x>=990 AND x<1000) AS prcntl_100,

SQL语句的大小不是一个考虑因素，因为我可以动态生成它。我只是想知道是否有一种惯用的方法来计算每百分位数的人口数量。

最佳答案

使用条件聚合而不是多个查询：

SELECT sum(case when x >= 0  AND x < 10 then 1 else 0 end) as prcntl_01,
       sum(case when x >= 10  AND x < 20 then 1 else 0 end) as prcntl_02,
       . . .
       sum(case when x >= 990  AND x < 1000 then 1 else 0 end) as prcntl_100
FROM A;

如果希望将值放在单独的行而不是列中，只需执行以下操作：

select n as which,
       sum(case when x >= (n - 1)*10 and x < n*10 - 1 then 1 else 0 end) as percentile
from A cross join
     generate_series(1, 100) as n
group by n;

这限制了您必须编写的代码量。

关于sql - 如何计算各种百分位数的count(*)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21332277/