postgresql – 从表中查找“n”个连续的免费号码

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了postgresql – 从表中查找“n”个连续的免费号码，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
我有一些像这样的数字表(状态是FREE或ASSIGNED) 
id_set  number  status         
-----------------------
1       000001  ASSIGNED
1       000002  FREE
1       000003  ASSIGNED
1       000004  FREE
1       000005  FREE
1       000006  ASSIGNED
1       000007  ASSIGNED
1       000008  FREE
1       000009  FREE
1       000010  FREE
1       000011  ASSIGNED
1       000012  ASSIGNED
1       000013  ASSIGNED
1       000014  FREE
1       000015  ASSIGNED

我需要找到“n”个连续数字,所以对于n = 3,查询将返回

1       000008  FREE
1       000009  FREE
1       000010  FREE

它应该只返回每个id_set的第一个可能的组(事实上,它将仅针对每个查询的id_set执行)

我正在检查WINDOW函数,尝试了一些查询,如COUNT(id_number)OVER(PARTITION BY id_set ROWS UNBOUNDED PRECEDING),但这就是我得到的:)我想不到逻辑,如何在Postgres中做到这一点.

我正在考虑使用WINDOW函数创建虚拟列,对于status =’FREE’的每个数字计算前面的行,然后选择第一个数字,其中count等于我的“n”数.

或者可以按状态分组,但只能从一个ASSIGNED到另一个ASSIGNED,并且只选择包含至少“n”个数字的组

编辑

我找到了这个查询(并稍微改了一下)

WITH q AS
(
  SELECT *,ROW_NUMBER() OVER (PARTITION BY id_set,status ORDER BY number) AS rnd,ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
  FROM numbers
)
SELECT id_set,MIN(number) AS first_number,MAX(number) AS last_number,status,COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,rnd - rn,status
ORDER BY
     first_number

它产生了一组FREE / ASSIGNED数字,但我希望只有第一组符合条件的所有数字

SQL Fiddle

@H_404_26@
这是一个 gaps-and-islands问题.假设在同一个id_set集中没有间隙或重复： 
WITH partitioned AS (
  SELECT
    *,number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),counted AS (
  SELECT
    *,COUNT(*) OVER (PARTITION BY id_set,grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,number
FROM counted
WHERE cnt >= 3
;

这是查询的sql Fiddle demo *链接：http://sqlfiddle.com/#!1/a2633/1.

UPDATE

要只返回一组,您可以添加一轮排名：

WITH partitioned AS (
  SELECT
    *,grp) AS cnt
  FROM partitioned
),ranked AS ( SELECT *,RANK() OVER (ORDER BY id_set,grp) AS rnk FROM counted WHERE cnt >= 3 )
SELECT
  id_set,number
FROM ranked WHERE rnk = 1
;

这也是这个的演示：http://sqlfiddle.com/#!1/a2633/2.

如果您需要为每个id_set设置一个,请更改RANK()调用,如下所示：

RANK() OVER (PARTITION BY id_set ORDER BY grp) AS rnk

此外,您可以使查询返回最小的匹配集(即,如果存在则首先尝试返回正好三个连续数字的第一组,否则为四个,五个等),如下所示：

RANK() OVER (ORDER BY cnt, id_set,grp) AS rnk

或者像这样(每个id_set一个)：

RANK() OVER (PARTITION BY id_set ORDER BY cnt, grp) AS rnk

*本回答中链接的sql Fiddle演示使用9.1.8实例,因为9.2.1实际上似乎没有工作.

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1. 标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1. 数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1. 表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1. 读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问