MySQL 数据抽稀 每分钟取一条

真正的

 

SELECT *
FROM cd_ret_well_result
WHERE inputtime IN (
SELECT MIN(inputtime)
FROM cd_ret_well_result
WHERE well_name='57-51'
GROUP BY UNIX_TIMESTAMP(inputtime) DIV (40* 60)
)
ORDER BY inputtime;

-----------后面的不太好

WITH data_01 AS
(
SELECT *
FROM cd_ret_well_result tr
WHERE well_name='74-77' and EXTRACT(HOUR_MINUTE from inputtime)%40=0
ORDER BY inputtime asc
),
data_02 AS
(
SELECT *
,ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT(inputtime,'%Y-%m-%d %H:%i') ORDER BY well_name) Seq
FROM data_01
)

select * from data_02 WHERE Seq=1 ORDER BY inputtime asc

 

抽稀十分钟一条

WITH data_01 AS
(
SELECT *
FROM cd_ret_well_result tr
WHERE well_name='74-77' and MOD(MINUTE
(inputtime),10)=0
ORDER BY inputtime asc
),
data_02 AS
(
SELECT *
,ROW_NUMBER() OVER ( PARTITION BY DATE_FORMAT
(inputtime,'%Y-%m-%d %H:%i') ORDER BY well_name) Seq
FROM data_01
)
SELECT * FROM data_02 WHERE Seq=1 ORDER BY inputtime
asc #LIMIT 150 ;

 

 

 

MySQL 数据抽稀 每分钟取一条

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;