Mysql统计数据，数据为空补0

之前项目中做了些根据特定时间做报表统计的功能，这里简单记录下，希望能帮到不会的同学。

1.根据指定年份，统计某年全年每月的数据，数据为空补0。（没有为系统维护时间表情况 create_time为时间戳  毫秒级别）

 sql如下：

SELECT
        a.`month` AS `month`,
        ifnull( b.backAmtStr, '0.00' ) as backAmtStr,
        ifnull( b.finishedAmtStr, '0.00' ) as finishedAmtStr,
        ifnull( b.invoiceAmtStr, '0.00' ) as invoiceAmtStr
        FROM
            (
        SELECT
            CONCAT(#{date},'-01') AS `month` UNION
        SELECT
            CONCAT(#{date},'-02') AS `month` UNION
        SELECT
            CONCAT(#{date},'-03') AS `month` UNION
        SELECT
            CONCAT(#{date},'-04') AS `month` UNION
        SELECT
            CONCAT(#{date},'-05') AS `month` UNION
        SELECT
            CONCAT(#{date},'-06') AS `month` UNION
        SELECT
            CONCAT(#{date},'-07') AS `month` UNION
        SELECT
            CONCAT(#{date},'-08') AS `month` UNION
        SELECT
            CONCAT(#{date},'-09') AS `month` UNION
        SELECT
            CONCAT(#{date},'-10') AS `month` UNION
        SELECT
            CONCAT(#{date},'-11') AS `month` UNION
        SELECT
            CONCAT( #{date},'-12') AS `month`
            ) a
            LEFT JOIN (
        SELECT
            from_unixtime( tc.create_time / 1000, '%Y-%m' ) AS `month`,
            CAST( IFNULL( sum( tcs.order_amt ), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS backAmtStr,
            CAST( IFNULL( sum( tcs.order_in_amount ), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS finishedAmtStr,
            CAST( IFNULL( sum( tcs.invoice_amount ), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS invoiceAmtStr
        FROM
            trade_contract tc
            INNER JOIN trade_contract_stat tcs ON tc.id = tcs.trade_contract_id
        WHERE
            from_unixtime( tc.create_time / 1000, '%Y' ) = #{date}

        GROUP BY
            `month`
            ) b ON a.`month` = b.`month`

2. 根据今天 、本周 、本月 、本季度 、本年进行数据统计

   SELECT
            CAST( IFNULL( sum(weight), 0 ) / 10000 AS DECIMAL ( 20, 3 ) ) AS weightStr,
            CAST( IFNULL( sum(amount), 0 ) / 100 AS DECIMAL ( 20, 2 ) ) AS amountStr
        FROM
            trade_order_item
        <where>
            <if test="type ==1">
                and TO_DAYS( FROM_UNIXTIME( update_time / 1000, '%Y%m%d' ) ) = TO_DAYS(  NOW( ) )
            </if>
            <if test="type ==2">
               and  YEARWEEK(DATE_FORMAT(FROM_UNIXTIME( update_time / 1000 ), '%Y%m%d') ) = YEARWEEK(NOW())
            </if>
            <if test="type ==3">
               and  DATE_FORMAT(FROM_UNIXTIME( update_time / 1000 ), '%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
            </if>
            <if test="type ==4">
               and QUARTER(FROM_UNIXTIME( update_time / 1000 )) = QUARTER(NOW())
            </if>
            <if test="type ==5">
               and YEAR(FROM_UNIXTIME( update_time / 1000 ))= YEAR(NOW())
            </if>
        </where>
        GROUP BY  breed_code

3. 统计近30天数据，为空补0 （java代码传个长度为30的空数组即可，例如：int[] days = new int[29] ）

        select a.click_date as dateStr ,ifnull(b.count,0) as `count`
            from (
                SELECT curdate() as click_date
                <foreach collection="days" item="item" index="index">
                    union all
                    SELECT date_sub(curdate(), interval ${index+1} day) as click_date
                </foreach>
            ) a left join (
              select date(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d')) as datetime, count(*) as count
              from ${tableName}
              group by date(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d'))
            ) b on a.click_date = b.datetime;

4. 从当年前第一周开始截止至今的周数，单条维度按每周（周一-周日7天）统计，数据为空补0

      select a.click_date as dateStr ,ifnull(b.count,0) as `count`
        from (
        SELECT DATE_FORMAT(curdate(), '%Y-%u') as click_date
        <foreach collection="weeks" item="item" index="index">
            union all
            SELECT CONCAT(DATE_FORMAT( curdate( ), '%Y' ), "-", DATE_FORMAT( curdate( ), '%u' ) - ${index+1})
        </foreach>
        ) a left join (
        select date(FROM_UNIXTIME(create_time/1000,'%Y-%u')) as datetime, count(*) as count
        from ${tableName}
        group by date(FROM_UNIXTIME(create_time/1000,'%Y-%u'))
        ) b on a.click_date = b.datetime;

5. 统计近12个月的数据，为空补0

 select a.click_date as dateStr ,ifnull(b.count,0) as `count`
        from (
        SELECT DATE_FORMAT(curdate(), '%Y-%m') as click_date
        <foreach collection="months" item="item" index="index">
            union all
            SELECT SUBSTRING(date_sub(DATE_FORMAT(curdate(), '%Y-%m-%d'), interval ${index+1} month),1,7) as click_date
        </foreach>
        ) a left join (
        select date(FROM_UNIXTIME(create_time/1000,'%Y-%m')) as datetime, count(*) as count
        from ${tableName}
        group by date(FROM_UNIXTIME(create_time/1000,'%Y-%m'))
        ) b on a.click_date = b.datetime;

6.统计近12年的数据，为空补0

select a.click_date as dateStr ,ifnull(b.count,0) as `count`
        from (
        SELECT  DATE_FORMAT(CURDATE(), '%Y')  as click_date
        <foreach collection="years" item="item" index="index">
            union all
            SELECT SUBSTRING(date_sub(DATE_FORMAT(curdate(), '%Y-%m-%d'), interval ${index+1} year),1,4) as click_date
        </foreach>
        ) a left join (
        select date(FROM_UNIXTIME(create_time/1000,'%Y')) as datetime, count(*) as count
        from ${tableName}
        group by date(FROM_UNIXTIME(create_time/1000,'%Y'))
        ) b on a.click_date = b.datetime;

7.统计客户流失率 （流失条件自定义，这里就不写了）

     SELECT
        t.lossCondition as lossCondition,
        ifnull( t.customerNum, 0 ) as customerNum
        FROM
        (
        SELECT
        '近1年未成交' AS lossCondition,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 1 YEAR ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )
        UNION ALL
        SELECT
        '近9个月未成交' AS lossCondition,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 9 MONTH ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )
        UNION ALL
        SELECT
        '近6个月未成交' AS lossCondition,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 6 MONTH ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )
        UNION ALL
        SELECT
        '近3个月未成交' AS lossCondition,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 3 MONTH ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )
        UNION ALL
        SELECT
        '近1个月未成交' AS lossCondition,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )
        UNION ALL
        SELECT
        '近7天未成交' AS lossConditio,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )
        UNION ALL
        SELECT
        '近3天未成交' AS lossCondition,
        count( * ) AS customerNum
        FROM
        trade_company
        WHERE
        DATE_SUB( CURDATE( ), INTERVAL 3 DAY ) <= date( FROM_UNIXTIME( create_time / 1000, '%Y-%m-%d' ) )

        ) t

根据年月日查询数据的时间处理（数据库存储时间类型是bigint）：

查询某天，例如：'2021-01-01'

date_format(FROM_UNIXTIME(order_time/1000,'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d') = #{createDay,jdbcType=VARCHAR}

查询某年某月，例如：'2021-01'

date_format(FROM_UNIXTIME(order_time/1000,'%Y-%m-%d %H:%i:%s'),'%Y-%m') = #{createDay,jdbcType=VARCHAR}

查询某年，例如：'2021'

date_format(FROM_UNIXTIME(order_time/1000,'%Y-%m-%d %H:%i:%s'),'%Y') = #{createDay,jdbcType=VARCHAR}