需求:
求当前业务种类在十二个月份中每个月的占比。
数据表: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的执行效率更高,效率低的那个为什么低,主要是哪方面的原因。