Oracle关于WINDOW SORT PUSHED RANK一些探究猜想
2022/6/22 2:21:22
本文主要是介绍Oracle关于WINDOW SORT PUSHED RANK一些探究猜想,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Oracle关于WINDOW SORT PUSHED RANK一些探究猜想
最近遇到一条SQL,开发让我做出优化
with tmp as (select * from T_VERIFY_APPLY ty where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate), tmp1 as (select count(*) as month_call_num from tmp where to_char(sysdate, 'yyyy/mm') = to_char(tmp.create_time, 'yyyy/mm')), tmp2 as (select count(*) as last_year_month from tmp where to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') = to_char(tmp.create_time, 'yyyy/mm')), tmp3 as (select count(*) as month_on_month from tmp where to_char(ADD_MONTHS(sysdate, -1), 'yyyy/mm') = to_char(tmp.create_time, 'yyyy/mm')), tmp4 as (select count(*) as this_year from tmp where to_char(sysdate, 'yyyy') = to_char(tmp.create_time, 'yyyy')), tmp5 as (select count(*) as past_year_num from tmp where to_char(ADD_MONTHS(sysdate, -12), 'yyyy') = to_char(tmp.create_time, 'yyyy')), temp as (select tl.entry_id, ty.create_time, ty.sender_id, row_number() over(partition by tl.entry_id order by 1) rn from t_verify_apply_list tl --418M left join T_VERIFY_APPLY ty --14M on ty.head_seq_no = tl.head_seq_no where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate), tmp6 as (select count(temp.entry_id) month_goods_num from temp where temp.rn = 1 and to_char(sysdate, 'yyyy/mm') = to_char(temp.create_time, 'yyyy/mm')), tmp7 as (select count(temp.entry_id) month_goods_with from temp where temp.rn = 1 and to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') = to_char(temp.create_time, 'yyyy/mm')), tmp8 as (select count(temp.entry_id) this_month_goods_with from temp where temp.rn = 1 and to_char(ADD_MONTHS(sysdate, -1), 'yyyy') = to_char(temp.create_time, 'yyyy')), tmp9 as (select count(temp.entry_id) this_year_goods_with from temp where temp.rn = 1 and to_char(sysdate, 'yyyy') = to_char(temp.create_time, 'yyyy')), tmp10 as (select count(temp.entry_id) year_goods_than from temp where temp.rn = 1 and to_char(ADD_MONTHS(sysdate, -12), 'yyyy') = to_char(temp.create_time, 'yyyy')) SELECT tmp1.month_call_num, tmp2.last_year_month, tmp3.month_on_month, tmp4.this_year, tmp5.past_year_num, tmp6.month_goods_num, tmp7.month_goods_with, tmp8.this_month_goods_with, tmp9.this_year_goods_with, tmp10.year_goods_than from tmp1, tmp2, tmp3, tmp4, tmp5, tmp6, tmp7, tmp8, tmp9, tmp10;View Code
别看SQL很长,实际上“架构”很简单。我给优化成如下SQL了。
with tmp as (select count(case when to_char(ty.create_time, 'yyyy/mm') = to_char(sysdate, 'yyyy/mm') then 1 else null end) month_call_num, count(case when to_char(ty.create_time, 'yyyy/mm') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') then 1 else null end) last_year_month, count(case when to_char(ty.create_time, 'yyyy/mm') = to_char(ADD_MONTHS(sysdate, -1), 'yyyy/mm') then 1 else null end) month_on_month, count(case when to_char(ty.create_time, 'yyyy') = to_char(sysdate, 'yyyy') then 1 else null end) this_year, count(case when to_char(ty.create_time, 'yyyy') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy') then 1 else null end) past_year_num from T_VERIFY_APPLY ty where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate), temp as (select count(case when to_char(create_time, 'yyyy/mm') = to_char(sysdate, 'yyyy/mm') then 1 else null end) month_goods_num, count(case when to_char(create_time, 'yyyy/mm') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy/mm') then 1 else null end) month_goods_with, count(case when to_char(create_time, 'yyyy') = to_char(ADD_MONTHS(sysdate, -1), 'yyyy') then 1 else null end) this_month_goods_with, count(case when to_char(create_time, 'yyyy') = to_char(sysdate, 'yyyy') then 1 else null end) this_year_goods_with, count(case when to_char(create_time, 'yyyy') = to_char(ADD_MONTHS(sysdate, -12), 'yyyy') then 1 else null end) year_goods_than from ( select /*+ no_merge */ create_time from ( select ty.create_time, row_number() over(partition by tl.entry_id order by 1) rn from t_verify_apply_list tl,T_VERIFY_APPLY ty where ty.head_seq_no = tl.head_seq_no and ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate and tl.entry_id is not null ) where rn = 1) ) SELECT tmp.month_call_num, tmp.last_year_month, tmp.month_on_month, tmp.this_year, tmp.past_year_num, temp.month_goods_num, temp.month_goods_with, temp.this_month_goods_with, temp.this_year_goods_with, temp.year_goods_than from tmp, temp;View Code
两者等价,但是会引起wrong result的bug出来,这个后边在单独写一篇另说。
拿出原SQL中的部分,
select * from (select tl.entry_id, ty.create_time, ty.sender_id, row_number() over(partition by tl.entry_id order by ty.create_time) rn from t_verify_apply_list tl left join T_VERIFY_APPLY ty on ty.head_seq_no = tl.head_seq_no where ty.result_id in ('11', '12') and ty.sender_id = 'SWBHAP' and ty.create_time >= ADD_MONTHS(sysdate, -12) and ty.create_time <= sysdate) where rn = 1;
这篇关于Oracle关于WINDOW SORT PUSHED RANK一些探究猜想的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-15PingCAP 黄东旭参与 CCF 秀湖会议,共探开源教育未来
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升