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

Question

全部 -

如果你往下看，我实际上将同一张桌子加入了 104 次。我这样做的原因是因为对于每一周 out ，我都需要从每一行的起点开始的整个 104 周的 out 值。换句话说，对于 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

标签： sqlpostgresqlsnowflake-cloud-data-platform

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;