Postgres查询10个步骤中的一个范围

Question

我们有一个使用Postgres作为数据库的RAILS应用程序，并有一个用例用于在一系列值上绘制图形。不幸的是，范围是一个小数，所以我不能使用Postgres的generate_series函数。需要帮助找出查询这个的最佳方式，而不是将它分成10个不同的查询。以下是示例数据

• We have a table with score | students
• 给定一个查询，我会得到一组分数-学生元组，从中我得到范围(最小(分数)，最大(分数))。例如range(10.25, 16.80)
• 我们需要将上述范围分解为10个步骤，间隔为0.655，即(max-min)10 - 10.25,10.91,11.56,12.22,12.87
• 对于上面的每一步，显示该分数与前一个值之间的学生人数
• 结果将是一个数组[(10.25,11232)，(10.91,2434)，....]

在Postgres中进行单个查询或少于10+查询的任何方法/想法?

Answer 1

您的结果为set(对我来说更有意义):

WITH base AS (
   SELECT student, score
   FROM   tbl
   WHERE  <some_condition>
   )
, border AS (
   SELECT min(score) AS min_score, max(score) AS max_score
   FROM   base
   )
SELECT lower_bound, ct
FROM  (
   SELECT step
        , min_score + ((max_score - min_score) * (step-1)) / 10 AS lower_bound
   FROM   border, generate_series(1,10) step
   ) x
LEFT   JOIN (
   SELECT width_bucket(b.score, x.min_score, x.max_score, 10) AS step
        , count(*)::int AS ct
   FROM   border x, base b
   GROUP  BY step
   ) y USING (step)
ORDER  BY step;

具有两个cte, generate_series()(仍然有用)和经常被忽视的函数width_bucket()。

要生成复合类型的数组，如问题中概述的那样，首先创建一个匹配的类型(一次):

CREATE TYPE my_type AS (bound numeric, ct int);

由于缺乏信息，假设numeric值
然后将上述查询提供给数组构造函数:

SELECT ARRAY (
   <query from above>
   SELECT (lower_bound, ct::int)::my_type   -- only difference
   <query from above>
   );