with postgres如何按范围类型分组

axkjgtzd  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(238)

按独占范围类型分组的最佳方式是什么。考虑以下几点。

  1. CREATE EXTENSION IF NOT EXISTS btree_gist;
  2. create table if not exists ranges (
  3. "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
  4. "name" character varying NOT NULL,
  5. "range" numrange NOT NULL,
  6. EXCLUDE USING gist (range WITH &&)
  7. );
  8. create table if not exists customers (
  9. "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
  10. "name" character varying NOT NULL,
  11. "score" integer NOT NULL
  12. )
  13. insert into ranges (name, range) VALUES ('awesome', '[75,)'::numrange);
  14. insert into ranges (name, range) VALUES ('good', '[50,75)'::numrange);
  15. insert into ranges (name, range) VALUES ('ok', '[25,50)'::numrange);
  16. insert into ranges (name, range) VALUES ('bad', '[,25)'::numrange);
  17. insert into customers (name, score) VALUES ('A', 10);
  18. insert into customers (name, score) VALUES ('B', 10);
  19. insert into customers (name, score) VALUES ('C', 80);

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

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

我不知道如何做到这一点。希望能得到一些指导
谢谢!!
(注:第12页)

cdmah0mi

cdmah0mi1#

我觉得这很简单:

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

相关问题