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

发布时间：2020-12-13 16:22:25 所属栏目：百科来源：网络整理

导读：我有一些像这样的数字表(状态是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 00

我有一些像这样的数字表(状态是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

这是一个 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实际上似乎没有工作.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!