Oracle11g分析函数和

Question

在我的交易表中，我使用一个解析函数来计算每个客户24小时滚动的花费金额。但是，trx_datetime字段最近从date更改为timestamp(9)。

    select sum(th.amount) 
        over(partition by th.customer_id 
        order by th.trx_datetime
        range between 1 preceding and 0 following) as rolling_trx_amt 
from transactions th;

现在，当我运行查询时，会得到以下错误。

ORA-00902: invalid datatype
00902. 00000 -  "invalid datatype"

为了找到解决方案，我已经搜索了几个小时，并在th.trx_datetime上尝试了无数次转换，但一直未能找到纠正错误的方法。如果您知道如何使分析函数按语句顺序使用时间戳，请告诉我。

Answer 1

您会得到这个错误，因为您的范围使用整数(这与日期算法很好，因为这是以天数计算的)，而时间戳算法使用间隔。

因此，您需要将您的范围转换为间隔，您可以使用numtodsinterval这样做，如下所示：

select sum(th.amount) 
        over(partition by th.customer_id 
        order by th.trx_datetime
        range between numtodsinterval(1, 'DAY') preceding
                  and numtodsinterval(0, 'DAY') following) as rolling_trx_amt 
from transactions th;

您还可以将其重写为：

select sum(th.amount) 
        over(partition by th.customer_id 
        order by th.trx_datetime
        range between numtodsinterval(1, 'DAY') preceding
                  and current row) as rolling_trx_amt 
from transactions th;

因为当您使用包含范围的窗口子句时，"current row“等同于”具有与当前行相同值的行“。