mysql统计一个连续日期天次数

因为有些天可能没有统计数据,所以需要借助一个日期表来补充没有数据的日期

1.生成日期表,借助num临时表

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);



CREATE TABLE  if not exists tb_calendar (datelist date);  INSERT INTO tb_calendar (datelist) SELECT
    adddate(
        (   
            DATE_FORMAT("2019-1-1", '%Y-%m-%d') 
        ),
        numlist.id
    ) AS `date`
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
    
    ) AS numlist;

2.按实际业务统计数据,这里是统计操作日志里面文件上传,下载,删除操作次数,周期是30天

SELECT
	date( dday ) ddate,
	max( uploadnum ) AS uploadnum,
	max( downloadnum ) AS downloadnum,
	max( deletenum ) AS deletenum 
FROM
	(
	SELECT
		datelist AS dday,
		0 AS uploadnum,
		0 AS downloadnum,
		0 AS deletenum 
	FROM
		tb_calendar 
	WHERE
		DATE_SUB( CURDATE(), INTERVAL 29 DAY ) <= date( datelist ) 
		AND date( datelist )<= CURDATE() UNION ALL
	SELECT
		DATE( invoke_time ) dday,
		count( 1 ) uploadnum,
		0 AS downloadnum,
		0 AS deletenum 
	FROM
		interface_invoke_log 
	WHERE
		invoke_name IN ( 'dfssUploadFile', 'dfssUpload' ) 
		AND response_status = '200' 
		AND DATE_SUB( CURDATE(), INTERVAL 29 DAY ) <= DATE( invoke_time ) 
	GROUP BY
		DATE( invoke_time ) UNION ALL
	SELECT
		DATE( invoke_time ) dday,
		0 uploadnum,
		count( 1 ) AS downloadnum,
		0 AS deletenum 
	FROM
		interface_invoke_log 
	WHERE
		invoke_name IN ( 'dfssDownload', 'dfssDownloadByFileID' ) 
		AND response_status = '200' 
		AND DATE_SUB( CURDATE(), INTERVAL 29 DAY ) <= DATE( invoke_time ) 
	GROUP BY
		DATE( invoke_time ) UNION ALL
	SELECT
		DATE( invoke_time ) dday,
		0 uploadnum,
		0 AS downloadnum,
		count( 1 ) AS deletenum 
	FROM
		interface_invoke_log 
	WHERE
		invoke_name IN ( 'dfssFileDelete', 'fileDeleteByFileID' ) 
		AND response_status = '200' 
		AND DATE_SUB( CURDATE(), INTERVAL 29 DAY ) <= DATE( invoke_time ) 
	GROUP BY
		DATE( invoke_time ) 
	) a 
GROUP BY
	ddate 
ORDER BY
	ddate

3.结果如下