Part One
根据数字范围，判断下面的数字是否在数字范围内，难度不大
with origin AS (
SELECT row_number() over () as rn, line
FROM lance_input
), empty AS (
SELECT rn
FROM origin
WHERE line is null
), range AS (
SELECT split_part(line, '-', 1) :: BIGINT as min_num, split_part(line, '-', 2) :: BIGINT as max_num
FROM origin, empty
WHERE origin.rn < empty.rn
)
SELECT count(distinct case when origin.line :: BIGINT between range.min_num and range.max_num then origin.line end)
FROM origin, empty, range
WHERE origin.rn > empty.rn;
Part Two
判断所有的数字范围中，包含的数字个数。
generate_series
可以直接generate_series来生成min和max之间的个数，然后去重即可。
with origin AS (
SELECT row_number() over () as rn, line
FROM lance_input
), empty AS (
SELECT rn
FROM origin
WHERE line is null
), range AS (
SELECT split_part(line, '-', 1) :: BIGINT as min_num, split_part(line, '-', 2) :: BIGINT as max_num
FROM origin, empty
WHERE origin.rn < empty.rn
)
SELECT COUNT(distinct num)
FROM (
SELECT generate_series(min_num, max_num) as num
FROM range
) t;
但是例子中给的数字太大了，运行很久都没有得出结果。
merge_range
数字范围中有很多重合的，可以找出这些重合的范围进行合并
larger_range AS (
SELECT distinct least(a.min_num, b.min_num) as min_num, greatest(a.max_num, b.max_num) as max_num
FROM inner_tbl a
JOIN inner_tbl b
ON a.min_num <= b.max_num
AND a.max_num >= b.max_num
AND a.rn != b.rn
)
之前的数字范围，需要过滤掉被合并的小范围
SELECT step, min_num, max_num
FROM larger_range
UNION ALL
SELECT step, min_num, max_num
FROM (
SELECT s.step, s.min_num, s.max_num
FROM inner_tbl s
LEFT JOIN larger_range t
ON s.min_num >= t.min_num and s.max_num <= t.max_num
WHERE t.min_num is null
) s
最后的终止条件，都是再也找不到可以合并的数字范围即可。完整的SQL如下
with recursive origin AS (
SELECT row_number() over () as rn, line
FROM lance_input
), empty AS (
SELECT rn
FROM origin
WHERE line is null
), range AS (
SELECT row_number() over () as rn, split_part(line, '-', 1) :: BIGINT as min_num, split_part(line, '-', 2) :: BIGINT as max_num
FROM origin, empty
WHERE origin.rn < empty.rn
), merge_range AS (
SELECT 1 as step, rn, min_num, max_num
FROM range
UNION ALL
SELECT step + 1 as step, row_number() over () as rn, min_num, max_num
FROM (
WITH inner_tbl AS (select * from merge_range),
larger_range AS (
SELECT distinct a.step, least(a.min_num, b.min_num) as min_num, greatest(a.max_num, b.max_num) as max_num
FROM inner_tbl a
JOIN inner_tbl b
ON a.min_num <= b.max_num
AND a.max_num >= b.max_num
AND a.rn != b.rn
)
SELECT step, min_num, max_num
FROM (
SELECT step, min_num, max_num
FROM larger_range
UNION ALL
SELECT step, min_num, max_num
FROM (
SELECT s.step, s.min_num, s.max_num
FROM inner_tbl s
LEFT JOIN larger_range t
ON s.min_num >= t.min_num and s.max_num <= t.max_num
WHERE t.min_num is null
) s
) t
WHERE exists (
SELECT 1 FROM inner_tbl a
JOIN inner_tbl b
ON a.min_num <= b.max_num
AND a.max_num >= b.max_num
AND a.rn != b.rn
)
) t
)
select * from merge_range;