忽略sqlite表选择中时间戳中的日副本

Question

我有一个sqlite数据库，包含时间戳和滴答数据。时间戳包含日期和时间。对于“一天结束”的分析，我想省去同一天重复的时间戳。选择重复日期的哪个数据库行并不重要。

这张桌子是这样的：

CREATE TABLE StockQuotes
    (`Timestamps` varchar(19), `Open` float)
;

INSERT INTO StockQuotes
    (`Timestamps`, `Open`)
VALUES
    ('2010-09-16 13:16:22', 33.63),
    ('2010-09-17 13:16:22', 33.53),
    ('2010-09-20 11:26:30', 33.46),
    ('2010-09-20 13:16:22', 33.46),
    ('2010-09-21 11:26:30', 33.76),
    ('2010-09-22 11:26:30', 33.56),
    ('2010-09-23 11:26:30', 33.86),
    ('2010-09-23 13:26:30', 33.86)
; 

我想要的结果是：

Timestamps              Open
2010-09-16 13:16:22     33.63
2010-09-17 13:16:22     33.53
2010-09-20 11:26:30     33.46
2010-09-21 11:26:30     33.76
2010-09-22 11:26:30     33.56
2010-09-23 11:26:30     33.86

或

Timestamps              Open
2010-09-16 13:16:22     33.63
2010-09-17 13:16:22     33.53
2010-09-20 13:16:22     33.46
2010-09-21 11:26:30     33.76
2010-09-22 11:26:30     33.56
2010-09-23 13:26:30     33.86

我可以通过这个查询找到副本：

SELECT Timestamps, COUNT(SubTS) AS CountSub FROM
(
  SELECT Timestamps,substr (Timestamps,1,10) AS SubTS, Open
  FROM StockQuotes
)
GROUP BY SubTS HAVING (COUNT(SubTS)>1);

但最后，这是行不通的：

SELECT * FROM StockQuotes WHERE Timestamps NOT IN
(
SELECT Timestamps, COUNT(SubTS) AS CountSub FROM
(
  SELECT Timestamps,substr (Timestamps,1,10) AS SubTS, Open
  FROM StockQuotes
)
GROUP BY SubTS HAVING (COUNT(SubTS)>1)
);

我犯了什么错？

Answer 1

错误在于，您正在过滤包含重复项的所有行，因此这两个副本都将丢失。

要为(可能)多个输入行获取一个输出行，请使用GROUP BY：

SELECT MAX(Timestamps) AS Timestamps,
       Open
FROM StockQuotes
GROUP BY date(Timestamps);

MAX()确保获得组中的最新行。

Answer 2

可以使用以下命令删除副本：

delete from StockQuotes where rowid not in (select max(rowid) from StockQuotes group by substr (Timestamps,1,10));

​

希望它能成功！如果我错了就纠正我。