gpt4 book ai didi

sql - Informix 窗口函数范围子句的行为类似于行子句

转载 作者:行者123 更新时间:2023-12-01 14:59:36 24 4
gpt4 key购买 nike

考虑 Informix 中的这个语句:

SELECT 
a,
b,
sum(a) over (order by b) "no frame",
sum(a) over (order by b range between unbounded preceding and current row) "range",
sum(a) over (order by b rows between unbounded preceding and current row) "rows"
FROM TABLE(SET{row(1, 1), row(2, 1), row(3, 2), row(4, 2)}) AS t(a, b);

令人惊讶的是,它对 rangerows 产生了相同的结果:

a |b |no frame |range |rows |
--|--|---------|------|-----|
1 |1 |3 |1 |1 |
2 |1 |3 |3 |3 |
3 |2 |10 |6 |6 |
4 |2 |10 |10 |10 |

这与我习惯的任何 RDBMS(包括 PostgreSQL)的工作方式完全不同:

SELECT 
a,
b,
sum(a) over (order by b) "no frame",
sum(a) over (order by b range between unbounded preceding and current row) "range",
sum(a) over (order by b rows between unbounded preceding and current row) "rows"
FROM (values(1, 1), (2, 1), (3, 2), (4, 2)) AS t(a, b);

...产生预期的

a |b |no frame |range |rows |
--|--|---------|------|-----|
1 |1 |3 |3 |1 |
2 |1 |3 |3 |3 |
3 |2 |10 |10 |6 |
4 |2 |10 |10 |10 |

我遗漏了 Informix 和标准 SQL 之间的细微差别,还是一个错误?我正在使用 official docker image ,对应于目前的 Informix 12.10。

最佳答案

这是 documented行为:

If you specify an ORDER clause but no window frame clause for a window aggregation function, then by default, all rows that precede the current row and the current row are returned, which is equivalent to the following window frame specification:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

我相信当不存在窗口子句时,标准是 RANGE。我很确定这是大多数其他数据库的默认设置。

关于sql - Informix 窗口函数范围子句的行为类似于行子句,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55188186/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com