mysql按天，按周，按月，按季度，按年统计数据

信息技术时间序列分析：一周数据统计与可视化

最新推荐文章于 2024-04-28 20:59:15 发布

原创最新推荐文章于 2024-04-28 20:59:15 发布 · 330 阅读

1 ·

CC 4.0 BY-SA版权

MySQL sql 专栏收录该内容

11 篇文章

订阅专栏

本文探讨了如何通过SQL查询从rd_track_info表中统计过去7天、1周、1月、1季度和1年的数据，包括按天、周、月、季度和年粒度的计数，并展示了日期函数在数据处理中的应用。通过实例学习了MySQL日期时间函数和数据可视化的方法。

/*查询2小时前的数据*/
select * from tableName WHERE
create_time < DATE_SUB(NOW(), INTERVAL 2 HOUR)


 
SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) < DATE_SUB(CURDATE(), INTERVAL 7 DAY);

SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

SELECT
count(id)
FROM
rd_track_info
WHERE
DATE(create_time) > DATE_SUB(CURDATE(), INTERVAL 7 DAY);


 
/*按天统计*/
SELECT
    count(id) countNum,
    DATE(create_time) createTime
FROM
    rd_track_info
GROUP BY
    DATE(create_time)
ORDER BY
    DATE(create_time) DESC;


/*按周统计*/
SELECT
    count(id) countNum,
    WEEK(create_time) createTime
FROM
    rd_track_info
GROUP BY
    WEEK(create_time)
ORDER BY
    WEEK(create_time) DESC;


/*按月统计*/
SELECT
    count(id) countNum,
    MONTH(create_time) createTime
FROM
    rd_track_info
GROUP BY
    MONTH(create_time)
ORDER BY
    MONTH(create_time) DESC;


/*按季度统计*/
SELECT
    count(id) countNum,
    QUARTER(create_time) createTime
FROM
    rd_track_info
GROUP BY
    QUARTER(create_time)
ORDER BY
    QUARTER(create_time) DESC;

/*按年统计*/
SELECT
    count(id) countNum,
    YEAR(create_time) createTime
FROM
    rd_track_info
GROUP BY
    YEAR(create_time)
ORDER BY
    YEAR(create_time) DESC;

学习：https://www.yiibai.com/mysql/mysql_date_time_functions.html