问题描述
- 这项挑战基于一个涉及 IP 范围的真实用例.
- 我提供的解决方案基于 堆栈跟踪 我之前提出的挑战.每个范围的开始都被视为一个 PUSH 操作,每个范围的结束 + 1 被视为一个 POP 操作.
- This challenge is based on a real life use-case involving IP ranges.
- The solution I came with is based on the stack trace challenge I've presented previously. Each range start is treated as a PUSH operation and each range end + 1 is treated as a POP operation.
我们有一个范围数据集,其中每个范围都有一个起点、终点和一个值.
We have a data set of ranges where each range has a starting point, ending point and a value.
一个范围可以包含另一个范围或跟随另一个范围,但不能等于另一个范围或与另一个范围相交.
A range can contain another range or follow another range, but cannot be equal to another range or intersect with another range.
这些是范围之间的有效关系:
These are valid relations between ranges:
这些关系无效:
当以图形方式呈现时,我们的初始范围可能如下所示(字母代表 range_val):
Our initial ranges, when presented graphically, might look something like this (The letter represents range_val):
目标是采用初始范围集并根据以下规则创建新集:
The goal is to take the initial set of ranges and create a new set under the following rule:
包含的范围将覆盖包含范围的相应子范围.
请求的结果,当以图形方式呈现时,可能看起来像这样
The requested result, when presented graphically, might look something like this
要求
- 解决方案应该是单个 SQL 查询(子查询很好).
- 使用 T-SQL、PL/SQL 等.不允许.
- 不允许使用 UDF(用户定义函数).
- 该解决方案基于 堆栈跟踪 我之前提出过的挑战.每个范围的开始都被视为一个 PUSH 操作,每个范围的结束 + 1 被视为一个 POP 操作.
- 在性能方面,您可能会注意到 2 个内部分析函数如何使用相同的窗口,因此可以在一个步骤中执行.
- The solution is based on the stack trace challenge I've presented previously. Each range start is treated as a PUSH operation and each range end + 1 is treated as a POP operation.
- Performence wise, you may notice how the 2 internal analytic functions use the same windowing, therefore being executed in a single step.
请求的结果
(空值在此处显示为空格)
Requested Results
(Nulls are presented here as empty spaces)
可选添加最后一行:
推荐答案
甲骨文
SQL Server/PostgreSQL/Hive
这篇关于SQL 挑战/谜题:如何合并嵌套范围?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!