mysql - SQL - 只保留每天的第一条和最后一条记录

Question

我有一个存储简单日志数据的表:

CREATE TABLE chronicle (
    id INT auto_increment PRIMARY KEY, 
    data1 VARCHAR(256),
    data2 VARCHAR(256),
    time DATETIME
);

该表接近 100 万条记录，因此我想开始合并数据。

我希望能够每天获取每个 DISTINCT(data1, data2) 的第一个和最后一个记录，并删除所有其余记录。

我知道如何提取数据并用我想要的任何语言处理它，然后删除带有巨大 IN (...) query 的记录。 ，但直接使用 SQL 似乎是更好的选择(我错了吗？)

我尝试了几个查询，但除了 JOIN 之外，我不太擅长 SQL。

这是我到目前为止所拥有的:

SELECT id, Max(time), Min(time)
FROM   (SELECT id, data1 ,data2, time, Cast(time AS DATE) AS day
        FROM chronicle) AS initial
GROUP BY day;

这让我得到每天的第一次和最后一次，但它没有被数据分开(即我得到每天的最后一条记录，而不是每天每组不同数据的最后一条记录。) ，id 仅用于 Min(时间)。

我在这个特定问题上找到的信息仅用于查找当天的最后一条记录，而不是查找数据集的每条最后记录。

重要:我想要每天每个 DISTINCT(data1, data2) 的第一条/最后一条记录，而不仅仅是每天的第一条/最后一条记录 table 。每天会有2条以上记录。

解决方案:我的解决方案感谢 Jonathan Dahan 和 Gordon Linoff:

SELECT o.data1, o.data2, o.time FROM chronicle AS o JOIN (
    SELECT Min(id) as id FROM chronicle GROUP BY DATE(time), data1, data2
    UNION SELECT Max(id) as id FROM test_chronicle GROUP BY DATE(time), data1. data2
) AS n ON o.id = n.id;

从这里开始，只需引用同一个表来删除行即可。

Answer 1

这将提高搜索日期时的性能。

ALTER TABLE chronicle
ADD INDEX `ix_chronicle_time` (`time` ASC);

这将删除记录:

CREATE TEMPORARY TABLE #tmp_ids (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO #tmp_ids (id)
SELECT
    min(id)
FROM
    chronicle
GROUP BY
    CAST(day as DATE),
    data1,
    data2
UNION
SELECT
    Max(id)
FROM
    chronicle
GROUP BY
    CAST(day as DATE),
    data1,
    data2;

DELETE FROM
    chronicle
WHERE
    ID not in (select id FROM #tmp_ids)
    AND date <= '2015-01-01'; -- if you want to consider all dates, then remove this condition