如果只是千万级的业务表,用存储过程来实现。在存储过程中多利用临时表,创建好索引,效率并不低。MYSQL超过千万级的数据,还是采取ETL方案吧,数据用定时任务预先汇总好存入数据仓库,报表直接从数据仓库拿数据,而不从业务表拿。我还以为是MYSQL,MSSQL优化这个很简单的。
set nocount on
declare @starttime datetime,@endtime datetime
set @starttime='${tstart1}'
set @endtime='${tend1}',' 23:59:59.997'
--今年的数据
if object_id('tempdb..#t1') is not null drop table #t1
select cast(o.created as date) orderTime,o.province,o.city,count(o.city) num
into #t1
from [order] o join shop sh on o.shop_id=sh.id
where o.created between @starttime and @endtime
/*and o.status=99 订单完成*/
and pay_status=2 /*支付成功*/
group by cast(o.created as date),o.province,o.city
--去年的数据
set @starttime=dateadd(year,-1,@starttime)
set @endtime=dateadd(year,-1,@endtime)
if object_id('tempdb..#t2') is not null drop table #t2
select cast(dateadd(year,-1,o.created) as date) orderTime,o.province,o.city,count(o.city) num
into #t2
from [order] o join shop sh on o.shop_id=sh.id
where o.created between @starttime and @endtime
/*and o.status=99 订单完成*/
and pay_status=2 /*支付成功*/
group by cast(dateadd(year,-1,o.created) as date),o.province,o.city
--临时表创建索引
create index ix_#t1 on #t1 (orderTime,province,city)
create index ix_#t2 on #t2 (orderTime,province,city)
--历史数据的num要给别名,否则帆软分不出你的num是今年的还是去年的数据
select #t1.orderTime,#t1.province,#t1.city,#t1.num,isnull(#t2.num) hisnum,
case when #t2.num=0 then 1 else #t1.num*1.00/#t2.num -1 end as 同比
from #t1 left join #t2 on #t1.orderTime=#t2.orderTime
and #t1.province=#t2.province
and #t1.city=#t2.city
set nocount off
order表里面,最好要有索引,索引要覆盖created 和status,pay_status,shop表有没有索引无所谓,因为这个表数据量并不大。然后看你的代码,并没有对店铺过滤,意味着这个查询其实是找所有店铺的数据,所以最重要的,还是最起码要有created