MySQL 统计近 30 天和近 12 个月的每月记录数

2024-09-20T12:02:00

大屏数据有一个折线图,需要近 30 天的和近 12 个月的每月记录数统计。

一开始想的是数据先按照天或者月统计完数据之后,再通过排序解决:

List<DistributionDTO> distributionDTOList = statisticService.selectCarDistribution();
distributionDTOList.sort(Comparator.comparing(DistributionDTO::getDate));

后来考虑到分组查询的不足,如果当月没有记录,那么当月的统计项就会缺失,这与想要的结果不符。

谷歌到 Mysql统计近 30 天的数据,无数据的填充 0,里面提到了生成 n 天日期的 sql 语句:

SELECT
    @s := @s + 1 AS indexs,
    DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
FROM
    mysql.help_topic,
    ( SELECT @s := -1 ) temp  #不想包含当天,@s:=0
WHERE
    @s < 30 
ORDER BY
    dates 

因为变量 @s 是从 -1 + 1 = 0 开始循环生成的,所以生成的日期从当天开始(包含当天)往前数,一直到 30 不满足条件(类似循环里的先执行再循环判断 do..while)才结束。所以上面的查询其实生成了近 31 天日期。

如果只要近 30 天数据(包含当天),则需要 where @s < 30 改成 where @s < 29;
如果不想包含当天,则将 @s 初始值设置为 0,即 select @s := 0

另外一篇里 查询近 n 天的日期(以查询最近七天为例),只有 FROM 后跟的表名不一样,为 information_schema. TABLES,不了解有什么区别。

主要生成日期的函数 DATE_FORMAT( DATE( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ),其中最外层为将日期格式化(我这边是 datetime 类型),这里转化为 年-月-日 格式;再往里是日期转化函数 DATE,大概是将时间戳转为日期;最里面的 DATE_SUB 看名称 sub 是减,第一个参数 CURRENT_DATE 返回当前的日期,盲猜第二个参数是减多少天。

菜鸟教程里 MySQL DATE_SUB() 函数 定义:DATE_SUB() 函数从日期减去指定的时间间隔。第二个参数可以取很多,年、月、日、小时、分钟、秒、毫秒等,所以转化为月份统计就是 INTERVAL @s MONTH

最后,将当前的日期临时表和记录数分组查询的临时表进行左联:

SELECT
    date_table.dates AS date_value,
    IFNULL( temp.count, 0 ) AS num 
FROM
    (
    SELECT
        @s := @s + 1 AS indexs,
        DATE_FORMAT( DATE ( DATE_SUB( CURRENT_DATE, INTERVAL @s DAY ) ), '%Y-%m-%d' ) AS dates 
    FROM
        mysql.help_topic,
        ( SELECT @s := - 1 ) temp #不想包含当天,@s:=0
        
    WHERE
        @s & lt;
    29 
    ORDER BY
        dates 
    ) date_table
LEFT JOIN (
    SELECT LEFT
        ( created_at, 10 ) AS date_value,
        SUM( wireline_num ) AS count 
    FROM
        biz_work_order t1 
    WHERE
        t1.order_state = 7 
    GROUP BY
        LEFT ( created_at, 10 ) 
    ) temp ON date_table.dates = temp.date_value 
ORDER BY
    date_table.dates ASC

其中的 LEFT 函数,就是取左边多少位,用在这里因为存储的是 datetime,日期+时间,需要截取日期部分,才能左联。

当前页面是本站的「Baidu MIP」版。发表评论请点击:完整版 »