假如原始数据为每5秒一个数据,现在想展示为每4分钟一条数据,先按照分钟数把除以4余数为0的行选出来,在按照 年月日 时分,做组内排序(窗函数ROW_NUMBER),最后再拿出序号为1的行。
以下每分钟一条
with data_02 AS
(
SELECT *
,ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT(UploadTime,'%Y-%m-%d %H:%i') ORDER BY ID) Seq
FROM TprRecord
ORDER BY id DESC
)
SELECT * FROM data_02 WHERE Seq=1 ORDER BY id DESC LIMIT 500;
以下每4分钟一条文章来源:https://www.toymoban.com/news/detail-607720.html
WITH data_01 AS
(
SELECT *
FROM TprRecord tr
WHERE MOD(MINUTE(uploadtime),4)=0
ORDER BY id DESC
),
data_02 AS
(
SELECT *
,ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT(UploadTime,'%Y-%m-%d %H:%i') ORDER BY ID) Seq
FROM data_01
)
SELECT * FROM data_02 WHERE Seq=1 ORDER BY id DESC LIMIT 150 ;
文章来源地址https://www.toymoban.com/news/detail-607720.html
到了这里,关于MySQL 数据抽稀 每分钟取一条的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!