如果 x 是日期，那麼 (ORDER BY x RANGE BETWEEN n PRECEDING...) 是什麼意思？

在另一個執行緒中：

https://stackoverflow.com/questions/37759659/db2-query-to-find-average-sale-for-each-item-1-year-previous

OP 想要過去 365 天的滑動平均值。ROWS BETWEEN ...如果它保證每天恰好發生一次，那麼使用會很好，但這裡不是這種情況。RANGE BETWEEN ...看起來很合適，但我不清楚它在 DB2 中的含義。不確定 db2 沒有INTERVAL類型是否重要，但用標記的持續時間來模仿它。

文件說：（https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html）

無符號常數 PRECEDING

指定目前行之前的範圍或行數。如果指定了 ROWS，則無符號常量必須為零或表示行數的正整數。如果指定了 RANGE，那麼 unsigned-constant 的數據類型必須與 window-order-clause 的 sort-key-expression 的類型相當。只能有一個排序鍵表達式，並且排序鍵表達式的數據類型必須允許減法。如果 group-bound1 是 CURRENT ROW 或 unsigned-constant FOLLOWING，則不能在 group-bound2 中指定此子句。

無符號常數以下

指定目前行之後的範圍或行數。如果指定了 ROWS，則無符號常量必須為零或表示行數的正整數。如果指定了 RANGE，那麼 unsigned-constant 的數據類型必須與 window-order-clause 的 sort-key-expression 的類型相當。排序鍵表達式只能有一個，排序鍵表達式的數據類型必須允許加法。

DB2 允許以下結構：

values current_date - 1

預設單位日期是天，所以這意味著：

values current_date - 1 day

鑑於此，我希望這個範例能夠工作：

create table test 
( d date not null
, x decimal(3,0) not null);

insert into test (d,x) 
values ('2016-01-01',10),('2016-01-07',20),('2016-01-12',30);

我希望查詢：

select d, avg(x) over (order by d 
                      range between 30 preceding 
                                and current row) 
from test 
order by d;

返回：

2016-01-01-00.00.00  10
2016-01-07-00.00.00  15
2016-01-12-00.00.00  20

或可能產生錯誤，但結果是：

2016-01-01-00.00.00  10
2016-01-07-00.00.00  20
2016-01-12-00.00.00  25

我還嘗試在查詢中添加一天：

select d, avg(x) over (order by d 
                      range between 30 days preceding

select d, avg(x) over (order by d 
                      range between (cast 30 as day) preceding

以防萬一，但這兩種嘗試都會導致：

SQL0104N  An unexpected token "day" was found following "y ...
SQL0104N  An unexpected token "cast(30 as day)" was found following "r ...

首先懷疑我的單位比天小，但是將前面的部分增加到 300 會返回相同的結果。也許更令人驚訝的是，將其增加到 500 會將結果更改為：

select d, avg(x) over (order by d 
                       range between 500 preceding 
                                 and current row) 
from test 
order by d

2016-01-01-00.00.00  10
2016-01-07-00.00.00  20
2016-01-12-00.00.00  30

給定查詢：

select d, avg(x) over (order by d 
                       range between n preceding 
                                 and current row) 

對於 300 <= n <= 399，最後一行的結果是 25，對於 n<300 或 n>399，最後一行的結果是 30。

我無法弄清楚 avg 函式看到了哪些行，我最好的猜測是框架子句中有一些隱含的日期轉換為其他內容，但不知道如何證明或反駁這個假設。有人可以對此有所了解嗎？

首先，該表達式values current_date - 1僅在 Oracle 兼容模式生效時才有效——它模仿了 Oracle 的日期時間算法，其中預設間隔以（可能是小數）天數表示。

我認為無論 Oracle 兼容性如何，範圍界限都應該與整數相當，並且將DATE值與整數進行比較可能會產生意想不到的結果。如果將DATEs 轉換為自過去某個時刻以來的天數，則可以使用整數比較。您可以使用JULIAN_DAY()，例如：

select d, avg(x) over (order by julian_day(d) 
                  range between 30 preceding 
                            and current row) 
from test 
order by d

這會產生您期望的結果：

D          2                                
---------- ---------------------------------
01/01/2016   10.0000000000000000000000000000
01/07/2016   15.0000000000000000000000000000
01/12/2016   20.0000000000000000000000000000

 3 record(s) selected.

在 10.5 的第一個修訂包中，允許使用日期範圍，但結果無法預測。在最近的修訂包中，不再允許這樣做，因此可以通過使用最近的修訂包來避免問題中的大部分混淆。

引用自：https://dba.stackexchange.com/questions/141263