mysql计算同比和环比的区别_Mysql同比环比统计

SELECT'浏览量'text,DATE_FORMAT('2020-06-01','%Y/%m/%d')

startTime,DATE_FORMAT('2020-07-24','%Y/%m/%d')

endTime,SUM(tpv.num)

number,CONCAT(FORMAT(SUM(tpv.yoy)*100,2),'%')

yoys,CONCAT(FORMAT(SUM(tpv.mom)*100,2),'%') moms

FROM

(select

t5.date

pvDate,t5.eTime pveTime,SUM(t5.newhan) num,

((t5.newhan-t5.oldhan)/t5.oldhan) yoy,

((t5.newhan-t5.oldhan7)/t5.oldhan7) mom

from

(

select t3.day

as

date,t3.day_pv as newhan,t2.day_pv1 as

oldhan,t7.day_pv7

oldhan7,t3.endTime eTime

from

(select

date_format(start_time,'%Y-%m-%d')

as day,sum(pv) as day_pv,date_format(end_time,'%Y-%m-%d') endTime

from

page_data group by day) t3

left join

(

select date_add(t1.day,INTERVAL1DAY) as last_day,t1.day_pv

as day_pv1

from

(select

date_format(start_time,'%Y-%m-%d') as

day,sum(pv) as day_pv

from

page_data group by day) t1

) t2

on

t3.day=t2.last_day

LEFT JOIN

(

select

date_add(t1.day7,INTERVAL7DAY) as

last_day7,t1.day_pv7 as

day_pv7

from

(select

date_format(start_time,'%Y-%m-%d') as day7,sum(pv)

as day_pv7

from

page_data group by day7) t1

)

t7

ON t3.day=t7.last_day7

)

t5 WHERE

DATE_FORMAT(t5.date,'%Y-%m-%d')

between'2020-06-01'and'2020-07-24'GROUP

BY t5.date)

tpv