按独占范围类型分组的最佳方式是什么。考虑以下几点。
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页)
1条答案
按热度按时间cdmah0mi1#
我觉得这很简单: