我有一些像这样的数字表(状态是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数字,但我希望只有第一组符合条件的所有数字
这是一个
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
