mysql实现流量的同比环比将子查询做链接实现相应业务

mysql实现流量的同比环比将子查询做链接实现相应业务

同比环比的概念：

实现的相应代码：
思路，获取今月和上月想要查询的信息做连接查询，把子查询作为连接查询的联表，然后计算出相应的同比环比，注意同比环比的公式对应的条件，同时把数据字段中为空的信息给删掉
CASE WHEN： 类似分支条件
TRUNCATE(IFNULL(SUM(tdf.arrive_flow),0),2)： 剔除为空的值，求和条件，以及小数点位数
时间格式正确判断

SELECT
        t.nowMonth time,
        t.siteName,
        t.cityName,
        CASE WHEN t.arriveFlow IS NULL OR t.arriveFlow = 0 THEN 0 ELSE t.arriveFlow END arriveFlow,
        CASE WHEN y.arriveFlow IS NULL OR y.arriveFlow = 0 THEN 0.00 ELSE ROUND((t.arriveFlow-y.arriveFlow) / y.arriveFlow * 100, 2) END ProportionT
        FROM
        (
        SELECT
        DATE_FORMAT(tdf.statistics, '%Y-%m' ) AS nowMonth,
        TRUNCATE(IFNULL(SUM(tdf.arrive_flow),0),2) arriveFlow,
        ts.site_name AS siteName,
        a.full_name AS cityName
        FROM  train_daily_flow tdf
        LEFT JOIN train_site  ts ON ts.uuid=tdf.site_id
        LEFT JOIN sys_area a ON a.area_code = ts.administrative_code
        WHERE
        DATE_FORMAT(tdf.statistics,'%Y-%m') BETWEEN DATE_FORMAT(DATE_SUB(tdf.statistics,INTERVAL DATE_FORMAT(tdf.statistics,'%m')-1 MONTH),'%Y-%m')
        AND #{"日期"}
        AND ts.administrative_code IS NOT NULL
        GROUP BY ts.uuid,DATE_FORMAT(tdf.statistics,"%Y")
        ORDER BY arriveFlow DESC LIMIT 0,5
        ) AS t
        LEFT JOIN
        (
        SELECT
        YEAR(DATE_SUB(tdf.statistics, INTERVAL 1 YEAR))AS lastMonth,
        TRUNCATE(IFNULL(SUM(tdf.arrive_flow),0),2) arriveFlow,
        ts.site_name AS siteName,
        a.full_name AS cityName
        FROM  train_daily_flow tdf
        LEFT JOIN train_site ts ON ts.uuid=tdf.site_id
        LEFT JOIN sys_area a ON a.area_code =ts.administrative_code
        WHERE
        DATE_FORMAT(tdf.statistics,'%Y-%m') BETWEEN DATE_FORMAT(DATE_SUB(tdf.statistics,INTERVAL DATE_FORMAT(tdf.statistics,'%m')-1 MONTH),'%Y-%m')
        AND
        DATE_FORMAT(DATE_SUB(#{"日期"},INTERVAL 1 MONTH),'%Y-%m')
        AND ts.administrative_code IS NOT NULL
        GROUP BY ts.uuid,YEAR(DATE_ADD(tdf.statistics, INTERVAL 1 MONTH))
        ORDER BY arriveFlow DESC LIMIT 0,5
        ) AS y
        ON t.cityName = y.cityName