是否可以消除大量左连接并简化此查询?

Question

All -

如果你看下面，我自己加入了同一张桌子 104 次。 我这样做的原因是，对于每个星期，我需要从每行的起点开始的整个 104 周的输出值。 换句话说，对于 0 周，我需要 104 周的值，对于 1 周，我需要 104 周的值，依此类推。

如果您查看下面的查询和数据快照，它应该可以让您更好地了解我希望实现的目标。

基表结构

使用下面的查询，数据看起来像这样，但它是超级手动的，需要很长时间才能运行，而且我无法轻松透视数据

我认为理想情况下数据看起来像这样，但我迷失了如何改变连接和结构来实现这一点

select 
a.*
,sum(a.upb)/ sum(a.ila) as wo
,sum(a.upb)/ sum(a.ila) - sum(b.upb)/ sum(b.ila) as w1
,sum(a.upb)/ sum(a.ila) - sum(c.upb)/ sum(c.ila) as w2
,sum(a.upb)/ sum(a.ila) - sum(d.upb)/ sum(d.ila) as w3
,sum(a.upb)/ sum(a.ila) - sum(e.upb)/ sum(e.ila) as w4
,sum(a.upb)/ sum(a.ila) - sum(f.upb)/sum(f.ila) as w5
,sum(a.upb)/ sum(a.ila) - sum(g.upb)/sum(g.ila) as w6
from
scratchpad.term_upb_cube1 a
left join scratchpad.term_upb_cube1 b on b.loan_id = a.loan_id and a.weeks_out + 1 = b.weeks_out
left join scratchpad.term_upb_cube1 c on c.loan_id = a.loan_id and a.weeks_out + 2 = c.weeks_out
left join scratchpad.term_upb_cube1 d on d.loan_id = a.loan_id and a.weeks_out + 3 = d.weeks_out
left join scratchpad.term_upb_cube1 e on e.loan_id = a.loan_id and a.weeks_out + 4 = e.weeks_out
left join scratchpad.term_upb_cube1 f on f.loan_id = a.loan_id and a.weeks_out + 5 = f.weeks_out
left join scratchpad.term_upb_cube1 g on g.loan_id = a.loan_id and a.weeks_out + 6 = g.weeks_out
where a.loan_id = '2200835473524998'
group by 1,2,3,4,5,6,7,8

Answer 1

我真的不明白为什么你认为你需要GROUP BY和SUM在这里。您的示例数据恰好显示每个贷款 ID 和每周的一行，因此没有要聚合的内容。您似乎只想窥视以下行。为此使用LEAD。

select 
tuc.*,
upb / ila as wo,
1 - (lead(upb, 1) over (order by weeks_out) / ubp) as w1,
1 - (lead(upb, 2) over (order by weeks_out) / ubp) as w2,
1 - (lead(upb, 3) over (order by weeks_out) / ubp) as w3,
1 - (lead(upb, 4) over (order by weeks_out) / ubp) as w4,
1 - (lead(upb, 5) over (order by weeks_out) / ubp) as w5,
1 - (lead(upb, 6) over (order by weeks_out) / ubp) as w6
from scratchpad.term_upb_cube1 tuc
where loan_id = 2200835473524998
order by weeks_out;