删除最新的冗余行并更新时间戳

Question

我正在使用一个SQLite数据库，该数据库定期从多个来源接收大型数据转储。不幸的是，这些消息来源对于他们转储的内容并不了解，最后我得到了很多重复的记录。我正在寻找一种方法，以删除这些重复的记录，而不影响记录已合法地从过去的转储到这一个。

以下是数据的一般结构(_id是主键)：

| _id | _dateUpdated | _dateEffective | _dateExpired | name | status | location |
|-----|--------------|----------------|--------------|------|--------|----------|
|  1  |  2016-05-01  |    2016-05-01  |     NULL     | Fred | Online |  USA     |
|  2  |  2016-05-01  |    2016-05-01  |     NULL     | Jim  | Online |  USA     |
|  3  |  2016-05-08  |    2016-05-08  |     NULL     | Fred | Offline|  USA     |
|  4  |  2016-05-08  |    2016-05-08  |     NULL     | Jim  | Online |  USA     |
|  5  |  2016-05-15  |    2016-05-15  |     NULL     | Fred | Offline|  USA     |
|  6  |  2016-05-15  |    2016-05-15  |     NULL     | Jim  | Online |  USA     |

我希望能够将这些数据简化为这样的数据：

| _id | _dateUpdated | _dateEffective | _dateExpired | name | status | location |
|-----|--------------|----------------|--------------|------|--------|----------|
|  1  |  2016-05-01  |    2016-05-01  |  2016-05-07  | Fred | Online |  USA     |
|  2  |  2016-05-15  |    2016-05-01  |     NULL     | Jim  | Online |  USA     |
|  3  |  2016-05-15  |    2016-05-08  |     NULL     | Fred | Offline|  USA     |

这里的想法是，第4行、第5行和第6行完全重复了第2行和第3行，但时间戳除外(我需要按这三个字段进行比较--名称、状态、位置)。但是，第3行不重复第1行(状态从联机到脱机)，因此在第1行中设置了_dateExpired字段，第3行成为最近的记录。

我用这样的东西来查询这张桌子：

SELECT * FROM Data WHERE
    date(_dateEffective) <= date("now")
    AND (_dateExpired IS NULL OR date(_dateExpired) > date("now"))

这种减少在SQLite中是可能的吗？

总的来说，我仍然是SQL和数据库设计的初学者，所以我可能还没有以最好的方式构建数据库。我对这里的建议也持开放态度.我打算在给定的时间点查询数据--例如，“Jim在2016-05-06年的状态是什么？”

提前感谢！

Answer 1

考虑使用一个临时表，其中转储文件进入DumpTable (在每个转储之前定期清除)，然后INSERT...SELECT查询迁移到最后一个表。

现在，SELECT部分维护一个相关子查询(用于计算所需行的新[_dateExpired] )和派生表子查询(根据您的标准筛选出非阻塞)。最后，如果LEFT JOIN...NULL是唯一的标识符，带有FinalTable的[_id]将确保不追加重复的记录。以下是例行程序：

1. 清除DumpTable 从DumpTable中删除；
2. 运行要附加到DumpTable中的转储例程
3. 将记录附加到FinalTable 插入FinalTable (_id，_dateUpdated，_dateEffective，_dateExpired，name，status，location)，选择d._id，d._dateUpdated，d._dateEffective，(选择Min(日期(sub._dateEffective)，'-1天‘))，从DumpTable子( sub.name = DumpTable.name和sub._dateEffective > DumpTable._dateEffective和sub.status <> DumpTable.status)到calcExpired d.name，d.status，d.location来自DumpTable d内连接(选择Min(DumpTable._id)作为min_id，DumpTable.name，DumpTable.name，DumpTable.status ON群由DumpTable.name，DumpTable.status)作为c ON (c.name = d.name)和(c.min_id = d._id)和(c.status = d.status)在d._id = f._id上左加入FinalTable f，其中f._id为空；-插入记录：- _id _dateUpdated _dateEffective _dateExpired name status - 2016-05-01 2016-05-01 2016-05-07 Fred Online USA -2 2016-05-01 2016-01 Jim Online USA -3 2016-05-08 2016-05-08脱机美国

Answer 2

这种减少在SQLite中是可能的吗？

SQL中任何“约简”问题的答案总是“是”。诀窍是找出你在减少什么轴。

这里有一个部分解决方案来说明；它给出了每个名称和位置的第一个在线日期。

select min(_dateEffective) as start_date
    , name
    , location
from Data
where status = 'Online'
group by  
      name
    , location

将外部联接返回到状态为“脱机”且_dateEffective大于start_date的表(在名称和位置上)，您将得到_dateExpired。

_id是主键

一个普遍存在的误解是，每个表都需要某种顺序的"ID“号作为主键。您真正关心的键被称为自然键，即数据中唯一标识数据的1列或多列。在你的例子中，在我看来，那是_dateEffective, name, status, and location。至少，声明它们为unique，以防止意外复制。