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

概述我有一些像这样的数字表(状态是FREE或ASSIGNED) id_set number status -----------------------1 000001 ASSIGNED1 000002 FREE1 000003 ASSIGNED1 000004 FREE1 000005 FREE 我有一些像这样的数字表(状态是FREE或ASSIGNED)

ID_set  number  status         -----------------------1       000001  ASSIGNED1       000002  FREE1       000003  ASSIGNED1       000004  FREE1       000005  FREE1       000006  ASSIGNED1       000007  ASSIGNED1       000008  FREE1       000009  FREE1       000010  FREE1       000011  ASSIGNED1       000012  ASSIGNED1       000013  ASSIGNED1       000014  FREE1       000015  ASSIGNED

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

1       000008  FREE1       000009  FREE1       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_countFROM qGROUP BY ID_set,rnd - rn,statusORDER BY     first_number

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

SQL Fiddle

这是一个 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,numberFROM countedWHERE 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,numberFROM ranked WHERE rnk = 1;

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

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

RANK() OVER (@H_301_49@PARTITION BY ID_set ORDER BY @H_301_49@grp) AS rnk

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

RANK() OVER (ORDER BY @H_301_49@cnt, ID_set,grp) AS rnk

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

RANK() OVER (PARTITION BY ID_set ORDER BY @H_301_49@cnt, grp) AS rnk

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

总结

以上是内存溢出为你收集整理的postgresql – 从表中查找“n”个连续的免费号码全部内容，希望文章能够帮你解决postgresql – 从表中查找“n”个连续的免费号码所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。