下面的样本数据
id start end
a 1 3
a 5 6
a 8 9
b 2 4
b 6 7
b 9 10
c 2 4
c 6 7
c 9 10
我正在尝试提出一个查询，该查询将返回a，b和c之间的所有交叠起始端（包括扩展量）。因此，预期数据将如下所示
start end
2 3
6 6
9 9
我能描述的唯一方法是使用自定义聚合函数，该函数跟踪当前有效间隔，然后在迭代阶段计算新间隔。但是，在处理大型数据集时，我认为这种方法不可行。因此，如果有些聪明的人有一个查询或一些我不知道的先天函数，我将不胜感激。
这比Gordon的解决方案难看得多，也更复杂，但是我认为它可以更好地提供预期的答案，并且应该扩展以使用更多id：
WITH NUMS(N) AS ( --GENERATE NUMBERS N FROM THE SMALLEST START VALUE TO THE LARGEST END VALUE
SELECT MIN("START") N FROM T
UNION ALL
SELECT N+1 FROM NUMS WHERE N < (SELECT MAX("END") FROM T)
),
SEQS(N,START_RANK,END_RANK) AS (
SELECT N,
CASE WHEN IS_START=1 THEN ROW_NUMBER() OVER (PARTITION BY IS_START ORDER BY N) ELSE 0 END START_RANK, --ASSIGN A RANK TO EACH RANGE START
CASE WHEN IS_END=1 THEN ROW_NUMBER() OVER (PARTITION BY IS_END ORDER BY N) ELSE 0 END END_RANK --ASSIGN A RANK TO EACH RANGE END
FROM (
SELECT N,
CASE WHEN NVL(LAG(N) OVER (ORDER BY N),N) + 1 <> N THEN 1 ELSE 0 END IS_START, --MARK N AS A RANGE START
CASE WHEN NVL(LEAD(N) OVER (ORDER BY N),N) -1 <> N THEN 1 ELSE 0 END IS_END /* MARK N AS A RANGE END */
FROM (
SELECT DISTINCT N FROM ( --GET THE SET OF NUMBERS N THAT ARE INCLUDED IN ALL ID RANGES
SELECT NUMS.*,T.*,COUNT(*) OVER (PARTITION BY N) N_CNT,COUNT(DISTINCT "ID") OVER () ID_CNT
FROM NUMS
JOIN T ON (NUMS.N >= T."START" AND NUMS.N <= T."END")
) WHERE N_CNT=ID_CNT
)
) WHERE IS_START + IS_END > 0
)
SELECT STARTS.N "START",ENDS.N "END" FROM SEQS STARTS
JOIN SEQS ENDS ON (STARTS.START_RANK=ENDS.END_RANK AND STARTS.N <= ENDS.N) ORDER BY "START"; --MATCH CORRESPONDING RANGE START/END VALUES
首先，我们生成介于最小起始值和最大终止值之间的所有数字。
然后，通过将生成的数字加入范围，并为每个“ id”选择出现一次的每个数字“ n”，找到包含在所有提供的“ id”范围内的数字。
然后，我们确定这些值“ n”中的每一个是开始还是结束一个范围。为了确定这一点，对于每个N，我们都说：如果N的先前值不存在或比当前N小1，则当前N开始一个范围。如果N的下一个值不存在或不大于当前N的值1，则当前N结束范围。
接下来，我们为每个开始和结束值分配一个“等级”，以便我们可以将它们匹配。
最后，我们在等级匹配的地方（以及起点<=终点）进行自我联接以得到结果。
编辑：经过一些搜索，我遇到了这个问题，它显示了一种更好的方式来查找开始/结束并将查询重构为：
WITH NUMS(N) AS ( --GENERATE NUMBERS N FROM THE SMALLEST START VALUE TO THE LARGEST END VALUE
SELECT MIN("START") N FROM T
UNION ALL
SELECT N+1 FROM NUMS WHERE N < (SELECT MAX("END") FROM T)
)
SELECT MIN(N) "START",MAX(N) "END" FROM (
SELECT N,ROW_NUMBER() OVER (ORDER BY N)-N GRP_ID
FROM (
SELECT DISTINCT N FROM ( --GET THE SET OF NUMBERS N THAT ARE INCLUDED IN ALL ID RANGES
SELECT NUMS.*,T.*,COUNT(*) OVER (PARTITION BY N) N_CNT,COUNT(DISTINCT "ID") OVER () ID_CNT
FROM NUMS
JOIN T ON (NUMS.N >= T."START" AND NUMS.N <= T."END")
) WHERE N_CNT=ID_CNT
)
)
GROUP BY GRP_ID ORDER BY "START";
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句