博客

最近在使用mysql统计数据库表的数据总会使用到一些常用的统计某年、某月、某日的sql语句。手动mark一下。

获取某月所有日期列表数据

1. 首先创建一个从01-31的中间表。

DROP TABLE IF EXISTS `t_datetime`;
CREATE TABLE "t_datetime" (
  "STATS_DAY" varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '日期点'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. 连接查询

SELECT
	CONCAT(
		'2020',
		'-' ,'02',
		'-',
		STATS_DAY
	) AS DAY
FROM
	t_datetime
WHERE
	CAST(STATS_DAY AS SIGNED) <= DAYOFMONTH(

		IF (
			DATE_FORMAT(
				CONCAT(
					'2020',
					'-' ,'02',
					'-',
					'01'
				),
				'%Y-%m'
			) < DATE_FORMAT(CURDATE(), '%Y-%m'),
			LAST_DAY(
				DATE_FORMAT(
					CONCAT(
						'2020',
						'-' ,'02',
						'-',
						'01'
					),
					'%Y-%m-%d'
				)
			),
			CURDATE()
		)
	)

在这个sql中你可传入对应的参数年和月你就可以得到当前月的所有时间节点。

获取过去12个月或30天的时间节点

直接创建过去12个月的视图即可。

CREATE 
  //  ALGORITHM = UNDEFINED 
  //  DEFINER = `root`@`%` 
  //  SQL SECURITY DEFINER
VIEW `past_12_month_view` AS
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`

这样就可以获取当前时间的过去12个月的日期节点了，同理过去30天也是一样的。

获取每年的数据量

SELECT CREATION_TIMESTAMP,COUNT(*) AS total FROM t_incident GROUP BY YEAR(CREATION_TIMESTAMP);

获取每月的数据量

SELECT CREATION_TIMESTAMP,COUNT(*) AS total FROM t_incident GROUP BY MONTH(CREATION_TIMESTAMP);

获取每天的数据量

SELECT CREATION_TIMESTAMP,COUNT(*) AS total FROM t_incident GROUP BY MONTH(CREATION_TIMESTAMP);