如何基于另一列计算接下来的n行的平均值-SQL（Oracle）

admin

如何基于另一列计算接下来的n行的平均值-SQL（Oracle）

sql

我正在尝试按月计算每个POLICY_ID的保费平均每月价值，如下所示。当客户将他/她的年度付款频率更新为不同于12的值时，我需要手动计算PREMIUM的平均每月值。如何获得MONTHLY
_PREMIUM_DESIRED中显示的值？提前致谢。

注意：Oracle版本12c

我尝试过的

SELECT 
    T.*,
    SUM(PREMIUM) OVER(PARTITION BY T.POLICY_ID ORDER BY T.POLICY_ID, T.PAYMENT_DATE ROWS BETWEEN CURRENT ROW AND 12/T.YEARLY_PAYMENT_FREQ-1 FOLLOWING ) / (12/T.YEARLY_PAYMENT_FREQ) MONTLY_PREMIUM_CALCULATED
FROM MYTABLE T
;

数据代码：

DROP TABLE MYTABLE;
CREATE TABLE MYTABLE (POLICY_ID NUMBER(11), PAYMENT_DATE DATE, PREMIUM NUMBER(5), YEARLY_PAYMENT_FREQ NUMBER(2),MONTHLY_PREMIUM_DESIRED NUMBER(5));                                  
INSERT INTO MYTABLE VALUES (1, DATE '2014-10-01',120,12,120);
INSERT INTO MYTABLE VALUES (1, DATE  '2014-11-01',360,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2014-12-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-01-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-02-01',360,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-03-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-04-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-05-01',720,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-06-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-07-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-08-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-09-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-10-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-11-01',120,12,120);
INSERT INTO MYTABLE VALUES (2, DATE '2015-01-01',60,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-02-01',0,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-03-01',0,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-04-01',0,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-05-01',180,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-06-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-07-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-08-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-09-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-10-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-11-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-12-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-01-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-02-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-03-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-04-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-05-01',15,12,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-06-01',15,12,15);
SELECT * FROM MYTABLE;

编辑：

无论付款频率如何，客户都可以更改PREMIUM金额。下面，对于POLICY_ID = 1，我添加了从“
2015/11/01”开始的新记录来演示这种情况。在这种情况下，平均每月保费从120增加到240。还删除了屏幕截图，以使问题更易读。

DROP TABLE MYTABLE2;
CREATE TABLE MYTABLE2 (POLICY_ID NUMBER(11), PAYMENT_DATE DATE, PREMIUM NUMBER(5), YEARLY_PAYMENT_FREQ NUMBER(2),MONTHLY_PREMIUM_DESIRED NUMBER(5));
INSERT INTO MYTABLE2 VALUES (1, DATE '2014-10-01',120,12,120);
INSERT INTO MYTABLE2 VALUES (1, DATE  '2014-11-01',360,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2014-12-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-01-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-02-01',360,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-03-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-04-01',0,4,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-05-01',720,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-06-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-07-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-08-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-09-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-10-01',0,2,120);
INSERT INTO MYTABLE2 VALUES (1, DATE '2015-11-01',240,12,240);
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-12-01',240,12,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-01-01',960,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-02-01',0,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-03-01',0,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-04-01',0,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-05-01',960,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-06-01',0,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-07-01',0,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-08-01',0,4,240);      --newly added records
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-01-01',60,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-02-01',0,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-03-01',0,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-04-01',0,3,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-05-01',180,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-06-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-07-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-08-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-09-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-10-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-11-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2015-12-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-01-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-02-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-03-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-04-01',0,1,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-05-01',15,12,15);
INSERT INTO MYTABLE2 VALUES (2, DATE '2016-06-01',15,12,15);
SELECT * FROM MYTABLE2;

阅读 521

2021-07-01

共1个答案

admin

我认为计算是：

select t.*,
       premium / (12 / yearly_payment_freq)) as monthly_premium_calculated
from mytable t;

编辑：

我知道，您还需要在中间几个月内进行这种分配。因此，您可以通过计算非零付款的数量来分配组。然后：

select t.*,
       ( max(premium) over (partition by policy_id, grp) /
         (12 / yearly_payment_freq)
       ) as monthly_premium_calculated
from (select t.*,
             sum(case when premium > 0 then 1 else 0 end) over (partition by policy_id order by payment_date) as grp
      from mytable t
     ) t;

这是一个db
<> fiddle（它使用Postgres，因为它比Oracle更容易设置）。

2021-07-01