sql需求记录一二

需求:
在这里插入图片描述求当前业务种类在十二个月份中每个月的占比。
数据表:p016_counter_biz_stat
方法一:
select
‘在业务总量中的占比’ as legend,
round(t.trans_num/(
select
sum(trans_num) as trans_num_sum
from
p016_counter_biz_stat t1
where org_id = #{}
and agent_id=#{}
and t1.summ_date=t.summ_data) *100,2),
to_char(t.summ_date,‘yyyy-mm’) as xAxis,
2 as value2
from
p016_counter_biz_stat t
where org_id = #{}
and agent_id=#{}
and t.biz_type =#{}
and to_char( summ_date,‘yyyy-mm’)
between (select to_char(date(max(summ_date)-interval ‘11 month’),‘yyyy-mm’) from p016_counter_biz_stat)
and (select to_char(date(max(summ_date)),‘yyyy-mm’) from p016_counter_biz_stat)
order by t.summ_date

方法二:
select
‘在业务总量中的占比’ as legend,
round(t1.trans_num/t2.trans_num_sum*100,2) as series,
t1.xAxis,
2 as value2
from(
( select
t.trans_num,
to_char(t.summ_date,‘yyyy-mm’) as xAxis,
from
p016_counter_biz_stat t
where org_id = #{}
and agent_id=#{}
and t.biz_type =#{}
and to_char( summ_date,‘yyyy-mm’)
between (select to_char(date(max(summ_date)-interval ‘11 month’),‘yyyy-mm’) from p016_counter_biz_stat)
and (select to_char(date(max(summ_date)),‘yyyy-mm’) from p016_counter_biz_stat)
order by t.summ_date ) t1
inner join(
select
sum(trans_num) as trans_num_sum,
to_char( summ_date,‘yyyy-mm’) as xAxis
from p016_counter_biz_stat
where org_id = #{}
and agent_id=#{}
and to_char( summ_date,‘yyyy-mm’)
between (select to_char(date(max(summ_date)-interval ‘11 month’),‘yyyy-mm’) from p016_counter_biz_stat)
and (select to_char(date(max(summ_date)),‘yyyy-mm’) from p016_counter_biz_stat)
group by summ_date
order by summ_date
) t2
on t1.xAxis=t2.xAxis
)
苦于没有数据量,想问下各位数据大佬这俩sql,正常来说一样数据量的前提下,哪个sql的执行效率更高,效率低的那个为什么低,主要是哪方面的原因。

发布了10 篇原创文章 · 获赞 3 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术工厂 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览