如何基于另一列计算接下来的n行的平均值-SQL(Oracle) [英] How to calculate the average value of following n rows based on another column - SQL (Oracle)
本文介绍了如何基于另一列计算接下来的n行的平均值-SQL(Oracle)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试按月计算每个POLICY_ID的保费平均每月价值，如下所示.当客户将他/她的年度付款频率更新为不同于12的值时，我需要手动计算PREMIUM的平均每月值.如何获得MONTHLY _PREMIUM_DESIRED中显示的值?预先感谢.
I am trying to calculate average monthly value of premiums for each POLICY_ID in monthly basis as shown below. When a customer updates his/her yearly payment frequency to a value different than 12, I need to manually calculate the average monthly value for the PREMIUM. How can I achieve the values shown in MONTHLY _PREMIUM_DESIRED? Thanks in advance.
注意:Oracle 12c版
Note: Oracle version 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.还删除了屏幕截图，以使问题更易于理解.
Regardless from payment frequency PREMIUM amount can also be changed by customer. Below, for the POLICY_ID = 1, I have added new records starting from "2015/11/01" to demonstrate this situation. In this case, average monthly premium increased from 120 to 240. Also removed the screenshot to make the question more readable.
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;
推荐答案
我认为计算方法是:
select t.*,
premium / (12 / yearly_payment_freq)) as monthly_premium_calculated
from mytable t;
我知道，您还需要在中间月份进行此分配.因此，您可以通过计算非零付款的数量来分配组.然后:
I see, you also need this spread over the intermediate months. So you can assign the groups by counting the number of non-zero payments. Then:
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
Here is a db<>fiddle (it uses Postgres because that is easier to set up than Oracle).
这篇关于如何基于另一列计算接下来的n行的平均值-SQL(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
