首页 文章

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

提问于
浏览 次
3

我正在使用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行（状态从Online更改为Offline），因此_dateExpired字段在第1行中设置，第3行成为最新记录 .

我用这样的东西查询这个表：

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

在SQLite中这种减少是否可行？

我仍然是SQL和数据库设计的初学者，因此我可能没有以最佳方式构建数据库 . 我也愿意在那里提出建议......我想要在给定的时间点查询数据 - 例如，“Jim在2016-05-06左右的状态是什么？”

提前致谢！

sql sqlite

2 回答

  • 1

    考虑使用转储表进入DumpTable（在每次转储之前定期清除），然后 INSERT...SELECT 查询迁移到最终表 .

    现在 SELECT 部分维护一个相关子查询（为所需行计算新的 [_dateExpired] ）和派生表子查询（根据您的标准过滤掉非重复） . 最后，带有FinalTable的 LEFT JOIN...NULL 是为了确保不附加重复记录，假设 [_id] 是唯一标识符 . 以下是例程：

    • 清理DumpTable
    DELETE FROM DumpTable;

    • Run Dump Routine将附加到DumpTable中

    • 将记录附加到FinalTable

    INSERT INTO FinalTable ([_id], [_dateUpdated], [_dateEffective], [_dateExpired], 
                        [name], status, location)
SELECT d.[_id], d.[_dateUpdated], d.[_dateEffective],     
       (SELECT Min(date(sub.[_dateEffective], '-1 day'))
          FROM DumpTable sub  
         WHERE sub.[name] = DumpTable.[name] 
           AND sub.[_dateEffective] > DumpTable.[_dateEffective]  
           AND sub.status <> DumpTable.status) As calcExpired 
       d.name, d.status, d.location
FROM DumpTable d    

INNER JOIN 
          (SELECT Min(DumpTable.[_id]) AS min_id, 
                  DumpTable.name, DumpTable.status 
             FROM DumpTable 
         GROUP BY DumpTable.name, DumpTable.status)  AS c     
 ON (c.name = d.name) 
AND (c.min_id = d.[_id]) 
AND (c.status = d.status)

LEFT JOIN FinalTable f
   ON d.[_id] = f.[_id]
WHERE f.[_id] IS NULL;

-- INSERTED RECORDS:
-- _id  _dateUpdated    _dateEffective  _dateExpired    name    status     location
-- 1    2016-05-01      2016-05-01      2016-05-07      Fred    Online     USA
-- 2    2016-05-01      2016-05-01                      Jim     Online     USA
-- 3    2016-05-08      2016-05-08                      Fred    Offline    USA
    回复于
  • 0

    在SQLite中这种减少是否可行？

    SQL中任何“减少”问题的答案始终是“是” . 诀窍是找到你正在减少的轴 .

    这是一个部分解决方案来说明;它为每个名称和位置提供了第一个在线日期 .

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

    如果外部联接返回到表（名称和位置），其状态为'Offline'且_dateEffective大于 start_date ，则会得到 _dateExpired .

    _id是主键

    人们普遍存在一种误解，即每个表都需要某种顺序"ID"数字作为主键 . 您真正关心的密钥称为自然密钥，数据中有一列或多列唯一标识数据 . 在你的情况下，它看起来像是 _dateEffective, name, status, and location . 至少，声明它们 unique 以防止意外重复 .

    回复于

相关问题