使用Postgres如何按范围类型分组

Question

按独占范围类型分组的最佳方法是什么？ 请考虑以下事项。

CREATE EXTENSION IF NOT EXISTS btree_gist;
create table if not exists ranges (
"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"name" character varying NOT NULL,
"range" numrange NOT NULL,
EXCLUDE USING gist (range WITH &&)
);
create table if not exists customers (
"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"name" character varying NOT NULL,
"score" integer NOT NULL
)
insert into ranges (name, range) VALUES ('awesome', '[75,)'::numrange);
insert into ranges (name, range) VALUES ('good', '[50,75)'::numrange);
insert into ranges (name, range) VALUES ('ok', '[25,50)'::numrange);
insert into ranges (name, range) VALUES ('bad', '[,25)'::numrange);
insert into customers (name, score) VALUES ('A', 10);
insert into customers (name, score) VALUES ('B', 10);
insert into customers (name, score) VALUES ('C', 80);

我想要一个将返回以下内容的查询...

| range name | count of customers in range |
| 'awesome'  | 1                           |
| 'bad'      | 2                           |

我不确定如何实现这一目标。 希望得到一些指导

谢谢！！

(注：第12页(

Answer 1

嗯，我认为这很简单：

select r.name,
count(c.*)
from ranges r
left join customers c on r.range::numrange @> c.score::numeric
group by 1