查询近7天，近1个月，近3个月每天的数据量，查询近一年每个月的数据量

客官°小女子只卖身不卖艺 2022-06-02 05:07 202阅读 0赞

统计近7天每天，近一个月每天，近三个月每天，近一年每个月的新增数量，用于画折线图，由于是根据create\_time字段统计的，所以如果有一天没有新增，就会缺少这一天的日期，要对日期进行补充，当天没有新增的new\_count置为0，所以要建立一个日期表calendar

-- 创建一个num表，用于存储0-9
    drop table if EXISTS num;
    CREATE TABLE num (
    `i`  int(10) NOT NULL 
    );
    INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
    select * from num;
    
    -- 创建日期表
    drop table if EXISTS calendar;
    CREATE TABLE calendar (
    `id`  int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `datelist`  date NULL DEFAULT NULL 
    )ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT ;
    
    -- 插入日期数据
    INSERT INTO calendar (datelist)
    SELECT
        adddate((date_format('2013-01-01', '%Y-%m-%d')), numlist.id1) AS DATE
    FROM
        (
            SELECT
                n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 + n10000.i * 10000 AS id1
            FROM
                num n1
            CROSS JOIN num AS n10
            CROSS JOIN num AS n100
            CROSS JOIN num AS n1000
            CROSS JOIN num AS n10000
            order by id1 asc 
        ) AS numlist;
    select * from calendar order by datelist desc limit 50;

1、查询单表account，近7天每天新增的数量

SELECT
    	IFNULL(COUNT(a.create_time), 0) AS new_count,
    	DATE_FORMAT(c.datelist, '%Y-%m-%d') AS show_time 
    FROM
    	account a
    RIGHT JOIN calendar c ON DATE_FORMAT(c.datelist, '%Y-%m-%d') = DATE_FORMAT(a.create_time, '%Y-%m-%d')  WHERE c.datelist < CURDATE()
    AND c.datelist >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    GROUP BY
    	show_time
    ORDER BY
    	show_time ASC;

![SouthEast][]

2、查询单表account近一个月每天、近三个月每天的数据量写法同上

3、查询近一年每个月的数据量

SELECT
        IFNULL(count(a.create_time), 0) AS new_count,
        DATE_FORMAT(c.datelist, '%Y-%m') AS show_time
    FROM
        account a
    RIGHT JOIN calendar c ON DATE_FORMAT(c.datelist, '%Y-%m-%d') = DATE_FORMAT(a.create_time, '%Y-%m-%d')
    WHERE
        c.datelist < CURDATE()
    AND c.datelist >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
    GROUP BY
        show_time
    ORDER BY
        show_time ASC;

![SouthEast 1][]

4、查询近7天，account表字段为reward符合日期查询条件的total reward

SELECT
    	IFNULL(SUM(reward), 0) AS reward,
    	DATE_FORMAT(c.datelist, '%Y-%m-%d') AS show_time
    FROM
    	account a
    RIGHT JOIN calendar c ON DATE_FORMAT(c.datelist, '%Y-%m-%d') = DATE_FORMAT(a.create_time, '%Y-%m-%d')
    WHERE
    	c.datelist < CURDATE()
    AND c.datelist >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    GROUP BY
    	show_time
    ORDER BY
    	show_time ASC;

![SouthEast 2][]

(查询近一个月每天，近三个月每天的total reward同上)

5、查询近一年每个月的total reward

SELECT
    	IFNULL(SUM(reward), 0) AS reward,
    	DATE_FORMAT(c.datelist, '%Y-%m') AS show_time
    FROM
    	account a
    RIGHT JOIN calendar c ON DATE_FORMAT(c.datelist, '%Y-%m-%d') = DATE_FORMAT(a.create_time, '%Y-%m-%d')
    WHERE
    	c.datelist < CURDATE()
    AND c.datelist >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
    GROUP BY
    	show_time
    ORDER BY
    	show_time ASC;

[SouthEast]: /images/20220602/9981d997dddc491f8b375570ecc304ee.png
[SouthEast 1]: /images/20220602/61348040b5124a669ced051398e18dfb.png
[SouthEast 2]: /images/20220602/ad909784978b4c0fb61eac2a9c6e9bcf.png