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个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值