使用时间戳条件在一个表内删除MySQL中的行
问题描述:
我在MYSQL数据库中有10 GB tabel事件。它是Web应用程序服务器的一部分。登录这个数据库以前没有配置好,所以我需要制作一个单独的表“事件”更薄。 我的意思是我有数据点值日志(行)和这个值的时间戳。 我无法删除所有,我需要能够创建图表从值将保留在此表中。这就是为什么我需要留下一些代表性数据。使用时间戳条件在一个表内删除MySQL中的行
一个想法是当ts diff小于X或者它应该构建在pointValues diff中时删除具有相同datapId的行?
请帮我构建正确的查询。
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
<table>
<tr>
<th>id</th>
<th>datapId</th>
<th>dataType</th>
<th>pointValue</th>
<th>ts</th>
</tr>
<tr>
<td>1</td>
<td>5194</td>
<td>1</td>
<td>1</td>
<td>15060882793523</td>
</tr>
<tr>
<td>2</td>
<td>5194</td>
<td>1</td>
<td>1.1</td>
<td>15060882793524</td>
</tr>
<tr>
<td>3</td>
<td>5194</td>
<td>1</td>
<td>2.25</td>
<td>15060882793560</td>
</tr>
<tr>
<td>4</td>
<td>5194</td>
<td>1</td>
<td>2.23</td>
<td>15060882793590</td>
</tr>
<tr>
<td>5</td>
<td>5194</td>
<td>1</td>
<td>0</td>
<td>15060882793620</td>
</tr>
</table>
</body>
</html>
答
如果我明白你的问题,一个办法可能是尝试建立与数据的平均值表:
试试这个(创建测试数据):
CREATE TABLE data
(
id INT AUTO_INCREMENT PRIMARY KEY,
datapId INT,
dataType INT,
pointValue FLOAT,
ts BIGINT
);
INSERT INTO data VALUES (NULL,5194,1,1,15060882793523);
INSERT INTO data VALUES (NULL,5194,1,1.1,15060882793524);
INSERT INTO data VALUES (NULL,5194,1,2.25,15060882793560);
INSERT INTO data VALUES (NULL,5194,1,2.23,15060882793590);
INSERT INTO data VALUES (NULL,5194,1,0,15060882793620);
要建立一个表的平均值@interval_count
间隔:
-- TODO: Manually set interval count
SET @interval_count := 10;
-- Find ts bounds from data
SELECT @ts_min := min(ts) FROM data;
SELECT @ts_max := max(ts) FROM data;
SELECT @interval_size := (@[email protected]_min)/(@interval_count-1);
SELECT
-- ts at center of range with width @interval_size
ts_c as ts_mean,
-- Average of values in this range
AVG(d.pointValue) as pointValue_mean
FROM
(
-- Table with equally spaced ts points at range center
SELECT DISTINCT
-- ts value in the middle of the averaging range
ROUND(([email protected]_min)/@interval_size)*@[email protected]_min as ts_c
-- Lower bound for averaging range
,ROUND(([email protected]_min)/@interval_size)*@[email protected][email protected]_size/2
-- Upper bound for averaging range
,ROUND(1+([email protected]_min)/@interval_size)*@[email protected][email protected]_size/2
FROM data
) d_c
JOIN data d ON d.ts BETWEEN -- Self join to calculate average in ranges
ROUND(([email protected]_min)/@interval_size)*@[email protected][email protected]_size/2 AND
ROUND(([email protected]_min)/@interval_size)*@[email protected][email protected]_size/2
GROUP BY 1
ORDER BY 1
不知道这是否适用于10 GB。
你可以在这里尝试一下:http://sqlfiddle.com/#!9/612cf/1
最后我决定这样做下面的方式,有少行,但不从数据计算平均值。我列出了所有具有最多值的数据点标识,并且我从database.pointvalues中删除了所有甚至(基于表自动增量索引)的recrod,其中dataPointId ='X'和(id%2)= 0; – user4201838