如何基于另一列计算接下来的n行的平均值-SQL（Oracle）-先前回答的问题的更新版本

admin

如何基于另一列计算接下来的n行的平均值-SQL（Oracle）-先前回答的问题的更新版本

sql

我正在尝试按月计算每个POLICY_ID的PREMIUM的平均每月价值，如下面的屏幕截图所示。当客户将其每年的PAYMENT_FREQUENCY更新为不同于12的值时，我需要手动计算PREMIUM的平均每月值。此外，平均每月溢价金额可以及时更改。例如，对于POLICY_ID= 1，从“ 2015/11/01”开始，平均每月保费从120增加到240。

如何获得名为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 MYTABLE2 T
;

数据编码：

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); 
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-01-01',960,4,240);     
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-02-01',0,4,240);   
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-03-01',0,4,240);    
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-04-01',0,4,240);  
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-05-01',960,4,240);     
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-06-01',0,4,240);     
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-07-01',0,4,240);      
INSERT INTO MYTABLE2 VALUES (1, DATE '2016-08-01',0,4,240);    
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;

阅读 148

2021-07-01

共1个答案

admin

我认为您所追求的是：

select t.*, 
       last_value(nullif(premium * yearly_payment_freq / 12, 0) ignore nulls) 
                   over (partition by policy_id order by payment_date)
                   as monthly_premium_calculated
from   mytable2 t
;

对于非零付款的月份，它采用已支付的保费，乘以年度频率以获得等值的年度保费，然后将结果除以12得到每月的等值。对于保费为零的月份，假设该月的保费是在前一个月（频率小于“每月”）支付的，也就是说，该保费是整个季度或半年支付的，或者年）。

对您的数据运行此命令，发现您的“所需”数据有误。对于POLICY_ID = 1，对于“
2016-01-01”，您显示960的支付和4的年度频率，以及“期望的每月金额”240。这是不是不对？频率4表示“季度”，如果季度金额为960，则每月金额为320，而不是240。对吗？我的计算得到结果320。

您输入的内容也有错别字：您在2016年12月的政策ID
1行中有一行，当您表示2015年12月时。我本打算编辑您的帖子以更正此错误，但是后来我发现您发布的图片基于错误的内容数据（带有错字）。如果我仅在INSERT语句中更改它，那么它们将与图像不匹配…

2021-07-01