sql - 为唯一保单编号创建的案例编号之间至少间隔三个月
问题描述
我有一个包含三列的表,policy_no、casenumber、created_date;可以为相同的唯一保单编号创建多个不同的案例编号。我需要从表中提取所有数据,除非最新创建的案例编号与之前创建的任何案例编号之间的差距小于三个月。因此,例如,如果在 7 月 1 日为保单编号创建了案例编号,但在 6 月 15 日和 5 月 1 日之前也为同一保单编号创建了案例编号,我只想提取数据7 月 1 日创建的案例编号,因为我只想计算一次唯一的保单编号。但是,当间隔大于三个月时,例如,当一个案例编号是在 7 月 1 日创建的,而在此之前创建的最后一个是在 4 月 30 日创建的,
我希望这一切都有意义!不知道从哪里开始!
解决方案
首先,您应该知道月份不是一个精确的时间单位。这里我使用了 Oracle 函数months_between
,但您也可以减去日期并与 30 进行比较。Months_between
可能会给出不直观但正确的结果。例如:
select months_between(date '2019-03-29', date '2019-02-28') from dual;
select months_between(date '2019-03-31', date '2019-02-28') from dual;
第一次选择给1.03
,第二次给1
。奇怪但合乎逻辑。这是因为月份不是精确的单位。
警告您:) 现在解决方案。首先是我的样本数据,3 个不同案例的不同保单编号:
create table policies(policy_no, casenumber, created_date) as (
select 1, 101, date '2007-01-01' from dual union all
select 1, 102, date '2007-02-01' from dual union all
select 1, 103, date '2007-06-01' from dual union all
select 1, 104, date '2007-09-15' from dual union all
select 1, 105, date '2007-11-01' from dual union all
select 1, 106, date '2007-12-01' from dual union all
select 2, 201, date '1992-08-30' from dual union all
select 3, 301, date '1995-07-12' from dual union all
select 3, 302, date '1995-08-30' from dual union all
select 3, 303, date '1997-02-25' from dual );
我的查询:
with
t(pn, cn, cdt, rn) as (
select policy_no, casenumber, created_date,
row_number() over (partition by policy_no order by created_date desc)
from policies),
c(pn, cn, cdt, rn, diff, ldt, info) as (
select pn, cn, cdt, 1, 0, cdt, 'last' from t where rn = 1
union all
select t.pn, t.cn, t.cdt, t.rn, round(months_between(c.ldt, t.cdt), 2),
case when months_between(c.ldt, t.cdt) >= 3 then t.cdt else c.ldt end,
case when months_between(c.ldt, t.cdt) >= 3 then 'inlcuded' else 'excluded' end
from c join t on t.pn = c.pn and t.rn = c.rn + 1)
select * from c order by pn, rn
结果:
PN CN CDT RN DIFF LDT INFO
---------- ---------- ----------- ---------- ---------- ----------- --------
1 106 2007-12-01 1 0 2007-12-01 last
1 105 2007-11-01 2 1 2007-12-01 excluded
1 104 2007-09-15 3 2,55 2007-12-01 excluded
1 103 2007-06-01 4 6 2007-06-01 inlcuded
1 102 2007-02-01 5 4 2007-02-01 inlcuded
1 101 2007-01-01 6 1 2007-02-01 excluded
2 201 1992-08-30 1 0 1992-08-30 last
3 303 1997-02-25 1 0 1997-02-25 last
3 302 1995-08-30 2 17,84 1995-08-30 inlcuded
3 301 1995-07-12 3 1,58 1995-08-30 excluded
您只对带有 infolast
或的行感兴趣included
。
它是如何工作的?子查询t
只为行添加编号,它是为每个策略分隔的,最新的案例是第一位的。子查询c
是解决方案的主要部分。它是递归的。我们从行号开始1
,在接下来的每个步骤中,我们都会查找下一个行号并检查其日期是否早于记忆后的三个月。如果是我们保存它(在列 ldt 中),如果不是,则使用前一个。
这就是递归查询的工作原理。我希望我理解正确。如果您只需要检查相邻行之间的功能,那么功能lag
或lead
就足够了,但在这里您需要递归。
希望这会有所帮助,并对任何语言错误表示歉意:)
推荐阅读
- python - Pandas Dataframes - 合并两个 Dataframes 但省略具有同一列的条目
- network-programming - 网卡启用混杂模式,单播数据包不会被嗅出
- javascript - Tensorflow/ffjs:使用 model.fit() 方法训练后嵌入层权重为 NaN
- python - 嵌套字典的值总和
- python - 如何参考python上的字典在列表中查找最高值元素
- ruby-on-rails - 如何实施市场:管理员端显示完整的订单以及购买的物品清单:我无法应对的挑战
- python - Python函数:'for'循环内的返回方法
- jenkins - 无法通过 SCM 跳过插件跳过 Jenkins 构建
- arrays - Julia:从 Voronoi 镶嵌的第一个外壳中提取最近的邻居
- git - githuburl 和 githuburl.git 有什么区别?