sql - Oracle求和查询
问题描述
我有以下表格:
具有列 entity_id、termination_date 的表实体
entity_id termination_date
401 1/20/21
414 6/10/21
402
403
表 entity_detail,列 entity_id、entity_detail_id、termination_date
entity_id entity_detail_id
425 401
425 414
425 402
425 403
具有列 entity_id、period_end_date、one_month 的表套
entity_id period_end_date one_month
401 12/31/20 50
401 1/31/21 100
414 5/31/21 200
414 6/30/21 300
402 7/31/21 400
403 7/31/21 500
具有列 entity_id、period_end_date、one_month 的表 agg
entity_id period_end_date one_month
425 7/31/21 I want this value = 400+500+100+300
entity_id 425 是实体 401,402,403,414 的聚合。
entity_id 401 已于 21 年 1 月 20 日终止。21 年 1 月 31 日之后,该实体的表套中没有数据。
entity_id 414 已于 21 年 6 月 10 日终止。21 年 6 月 30 日之后,该实体的表套中没有数据。
对于 21 年 7 月 31 日实体 425 的 agg 表,我们要为以下内容添加 one_month:
400 (from entity_id 402 - 7/31/21)
+500 (from entity_id 403 - 7/31/21)
+100 (from entity_id 401 - 1/31/21, the last month it exists, it's terminated on 1/20/21)
+300 (from entity_id 414 - 6/30/21, the last month it exists, it's terminated on 6/10/21)
我们如何创建一个查询来总结 21 年 7 月 31 日的袖子实体的 one_month,以及它存在的上个月的终止实体(在上面的数据示例中为 400+500+100+300)?
我们在存储过程中执行此操作,因此不必在 1 个查询中。
此编辑后的查询(基于答案)将导致 Total_One_Month = 1800(而不是 900)
with entity as
(
select 425 as entity_id, null as termination_date from dual union
all
select 402 as entity_id, null as termination_date from dual union
all
select 403 as entity_id, null as termination_date from dual
),
entity_detail as
(
select 425 as entity_id, 402 as entity_detail_id from dual union all
select 425 as entity_id, 403 as entity_detail_id from dual
),
sleeve as
(
select 402 as sleeve_entity_id, to_date('7/31/2021','MM/DD/YYYY') as
period_end_date, 400 as one_month from dual union all
select 403 as sleeve_entity_id, to_date('7/31/2021','MM/DD/YYYY') as
period_end_date, 500 as one_month from dual
)
select r2.entity_id,max(r2.period_end_date), max(r2.sum_one_mth_ped1)
max(r2.sum_one_mth_ped1)+sum(nvl(s2.one_month,0)) as total_one_month
from
(
select r1.entity_id, max(r1.period_end_date) as period_end_date,
max(r1.sum_one_mth_ped1) as sum_one_mth_ped1,
max(e.termination_date) as termination_date,
ed.entity_detail_id
from
(
select
ed.entity_id,
max(s.period_end_date) as period_end_date,
sum(s.one_month) as sum_one_mth_ped1
from entity_detail ed
join sleeve s on ed.entity_detail_id=s.sleeve_entity_id
where s.period_end_date = to_date('07/31/2021','MM/DD/YYYY')
group by ed.entity_id
) r1
left join entity e on trim(r1.entity_id) = trim(e.entity_id)
left join entity_detail ed on trim(e.entity_id) = trim(ed.entity_id)
left join entity l5 on trim(ed.entity_detail_id) = trim(l5.entity_id) and l5.termination_date is not null
group by r1.entity_id, ed.entity_detail_id
) r2
left join (
select * from (
select sleeve_entity_id, period_end_date, one_month, row_number() over (partition by sleeve_entity_id order by period_end_date desc) as latest_rec
from sleeve) where latest_rec=1 ) s2
on r2.entity_detail_id=s2.sleeve_entity_id
group by r2.entity_id;
谢谢你。
解决方案
with
entity as
(
select 401 as entity_id, to_date('12/31/2020','MM/DD/YYYY') as termination_date from dual union all
select 401 as entity_id, to_date('01/31/2021','MM/DD/YYYY') as termination_date from dual union all
select 414 as entity_id, to_date('05/31/2021','MM/DD/YYYY') as termination_date from dual union all
select 414 as entity_id, to_date('06/10/2021','MM/DD/YYYY') as termination_date from dual union all
select 402 as entity_id, null as termination_date from dual union all
select 403 as entity_id, null as termination_date from dual
),
entity_detail as
(
select 425 as entity_id, 401 as entity_detail_id from dual union all
select 425 as entity_id, 414 as entity_detail_id from dual union all
select 425 as entity_id, 402 as entity_detail_id from dual union all
select 425 as entity_id, 403 as entity_detail_id from dual
),
sleeve as
(
select 401 as sleeve_entity_id, to_date('12/31/2020','MM/DD/YYYY') as period_end_date, 50 as one_month from dual union all
select 401 as sleeve_entity_id, to_date('1/31/2021','MM/DD/YYYY') as period_end_date, 100 as one_month from dual union all
select 414 as sleeve_entity_id, to_date('5/31/2021','MM/DD/YYYY') as period_end_date, 200 as one_month from dual union all
select 414 as sleeve_entity_id, to_date('6/30/2021','MM/DD/YYYY') as period_end_date, 300 as one_month from dual union all
select 402 as sleeve_entity_id, to_date('7/31/2021','MM/DD/YYYY') as period_end_date, 400 as one_month from dual union all
select 403 as sleeve_entity_id, to_date('7/31/2021','MM/DD/YYYY') as period_end_date, 500 as one_month from dual
)
select r2.entity_id, max(r2.period_end_date), max(r2.sum_one_mth_ped1)+sum(nvl(s2.one_month,0)) as total_one_month
from
(
select r1.entity_id, max(r1.period_end_date) as period_end_date, max(r1.sum_one_mth_ped1) as sum_one_mth_ped1,
max(ed2.termination_date) as termination_date, ed2.entity_detail_id
from
(
select
ed.entity_id,
max(s.period_end_date) as period_end_date,
sum(s.one_month) as sum_one_mth_ped1
from entity_detail ed
join sleeve s on ed.entity_detail_id=s.sleeve_entity_id
where s.period_end_date = to_date('07/31/2021','MM/DD/YYYY')
group by ed.entity_id
)r1
left join (select ed.entity_id, e.entity_id as entity_detail_id, e.termination_date
from entity e inner join entity_detail ed on e.entity_id=ed.entity_detail_id
where e.termination_date is not null
)ed2
on r1.entity_id=ed2.entity_id
group by r1.entity_id, ed2.entity_detail_id
) r2
left join (
select * from (
select sleeve_entity_id, period_end_date, one_month, row_number() over(partition by sleeve_entity_id order by period_end_date desc) as latest_rec
from sleeve) where latest_rec=1 ) s2
on r2.entity_detail_id=s2.sleeve_entity_id
group by r2.entity_id;
推荐阅读
- python-3.x - 向数据框添加新列
- oracle - 我们可以用什么来代替 postgresql 中的 V$parameter?
- sql - 如何在postgresql中找到最近7天的平均销售量?
- java - 在没有 glClearColor 的情况下更改 libgdx 中的初始背景颜色?
- azure-devops - 如何为 Azure 中的每个分支设置不同的管道
- intellij-idea - 如何更新整个系统的 JAVA_HOME,而不仅仅是我的终端?
- javascript - 单击时显示具有相同数据值的 div 并隐藏所有其他
- pytorch - PyTorch 中是否有沿着昏暗(块状 Softmax)部分的 Softmax 实现?
- c# - 附加到游戏对象的脚本不起作用但在禁用然后重新启用后启用(检查器中带有复选标记)(UNITY)
- php - codeigniter 是否有可能阻止从某些表中获取数据?