分析函数窗口子句

Question

列 VAL 是从 1 到 3 的数字列表，其他列应该显示：

• A( 所有低于 VAL 的值的最小值
• B( 所有低于 VAL 的值的最大值
• C( 所有大于 VAL 的值的最小值
• D( 所有更大的最大值 比 VAL 的值

我期待这个结果：

V   A   B   C   D
-------------------
1 |   |   | 2 | 3
2 | 1 | 1 | 3 | 3
3 | 1 | 2 |   | 

但我得到的结果是：

V   A   B   C   D
-------------------
1 |   |   | 2 | 3
2 |   |   |   |  
3 |   |   |   | 

(*( 所有空白单元格均为空结果

我写的查询：

WITH T AS
(SELECT     CAST(LEVEL AS NUMBER) val
FROM       DUAL
CONNECT BY LEVEL < 4)
SELECT   val
,MIN(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND val PRECEDING) A --MIN_PRECEDING
,MAX(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND val PRECEDING) B --MAX_PRECEDING
,MIN(val) OVER(ORDER BY val RANGE BETWEEN val FOLLOWING AND UNBOUNDED FOLLOWING) C --MIN_FOLLOWING
,MAX(val) OVER(ORDER BY val RANGE BETWEEN val FOLLOWING AND UNBOUNDED FOLLOWING) D --MAX_FOLLOWING
FROM     T
WHERE    val IS NOT NULL
ORDER BY 1
/

有人看到这个查询有什么问题吗？

提前感谢！

Answer 1

错误在val preceding和val following中。它应该是1 preceding和1 following.

您在那里指定的数字相对于当前记录，对应于 val 的记录(按给定的窗口顺序(，因此如果您在那里指定val，您将后退(或前进(太远。您应该需要在当前记录之前(或之后(获取最多一条记录的最小值/最大值。

所以：

WITH T AS
(SELECT     CAST(LEVEL AS NUMBER) val
FROM       DUAL
CONNECT BY LEVEL < 4)
SELECT   val
,MIN(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) A
,MAX(val) OVER(ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) B
,MIN(val) OVER(ORDER BY val RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) C
,MAX(val) OVER(ORDER BY val RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) D
FROM     T
WHERE    val IS NOT NULL
ORDER BY 1
/