mysql 每隔五分钟/五小时/五天/五月/两年统计一次记录等

本是古典何须时尚 2023-07-18 12:43 141阅读 0赞

# 每隔五分钟 #

SELECT concat( date_format(create_time, '%Y-%m-%d %H:' ) , floor( date_format(create_time, '%i' ) /5 ) ) AS 时间段, count( * ) as 数量
    FROM 表名
    GROUP BY 时间段

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjE4NjY0_size_16_color_FFFFFF_t_70]

## 每隔五小时 ##

SELECT concat( date_format(create_time, '%Y-%m-%d:' ) , floor( date_format(create_time, '%H' ) /5 ) ) AS 时间段, count( * ) as 数量
    FROM user
    GROUP BY 时间段

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjE4NjY0_size_16_color_FFFFFF_t_70 1]

## 每隔五天 ##

SELECT concat( date_format(create_time, '%Y-%m-%d,' ) , floor( date_format(create_time, '%d' ) /5 ) ) AS 时间段, count( * ) as 数量
    FROM user
    GROUP BY 时间段

## 每隔五月 ##

SELECT concat( date_format(create_time, '%Y-%m,' ) , floor( date_format(create_time, '%m' ) /5 ) ) AS 时间段, count( * ) as 数量
    FROM user
    GROUP BY 时间段

![在这里插入图片描述][20200327111106679.png]

## 每隔两年 ##

SELECT concat( date_format(create_time, '%Y,' ) , floor( date_format(create_time, '%Y' ) /2 ) ) AS 时间段, count( * ) as 数量
    FROM user
    GROUP BY 时间段

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjE4NjY0_size_16_color_FFFFFF_t_70]: /images/20230528/ba2ab6f450f4423ca207d66e340f24bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNjE4NjY0_size_16_color_FFFFFF_t_70 1]: /images/20230528/4577dbc3f6c240c8993bcd0e18352162.png
[20200327111106679.png]: /images/20230528/1fbf9fe2c3334b31b7ebf6cd69b1cbff.png