数据清洗 – P.S. Let.Me.Show.You.The.Way

以一个数据表为例, 创建sql如下:

CREATE TABLE http_table (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    Host     TEXT    DEFAULT "",
    IP       TEXT    DEFAULT "",
    Port     INTEGER DEFAULT (0),
    Info     TEXT    DEFAULT "",
    Uptime   TEXT    DEFAULT (datetime('now', 'localtime') ) 
);

当前该表已有不少数据存在，只有id为自增长主键，未创建约束。

之前创建数据过程，先查询（1），不存在新插入一条（2），存在更新该条记录（3）：

（1）先查询

SELECT id FROM http_table WHERE Host = 'dns9.quad9.net' AND IP = '9.9.9.9' AND Port = '80' order by Uptime desc LIMIT 1;

（这里，可能是多条，只返回一条）

（2）不存在新插入一条

INSERT INTO http_table (Host, IP, Port, Info) VALUES ('dns9.quad9.net', '9.9.9.9', '80', 'new_info');

（3）存在更新该条记录

UPDATE http_table SET Info = 'new_info' WHERE Host = 'dns9.quad9.net' AND IP = '9.9.9.9' AND Port = '80';

本身这个更新并不严谨，没有指定唯一id条件，可能会更新多条，但这不是要解决的问题。

问题在于在大批量的数据插入时，每次都要查询，再判断是插入还更新，速度很慢，现需要一条sql一次完成查询，并完成插入或更新。

尝试修改：在 SQLite 中，可以使用 INSERT INTO...ON CONFLICT 子句配合 DO UPDATE 动作，实现根据特定条件存在则更新记录，不存在则插入新记录的功能。

INSERT INTO http_table (Host, IP, Port, Info)
VALUES ('dns9.quad9.net', '9.9.9.9', '80', 'new_info')
ON CONFLICT(Host, IP, Port) DO UPDATE SET
    Info=excluded.Info;

解释：

INSERT INTO http_table (Host, IP, Port, Info) VALUES (...) 尝试插入一条新记录。

ON CONFLICT(Host, IP, Port) 指定当 Host, IP, Port 这三个字段的组合发生冲突时（即已存在这样的记录时）应采取的动作。

DO UPDATE SET Info=excluded.Info 指定当冲突发生时，更新已有记录的 Info 字段。excluded 是一个特殊的表别名，它引用尝试插入但因冲突而未能插入的值。

这样，如果记录已存在，Info 字段将被更新为新的值；如果记录不存在，则将插入新的记录

首先，你需要确保你的表有一个唯一约束（UNIQUE CONSTRAINT）来检测冲突。在这个例子中，需要设定 Host, IP, 和 Port 的组合是唯一的，UNIQUE(Host, IP, Port)否则会报错。

CREATE TABLE http_table(
    Host TEXT,
    IP TEXT,
    Port TEXT,
    Info TEXT,
    UNIQUE(Host, IP, Port)
);

由于之前未添加唯一约束，执行INSERT INTO http_table ... ON CONFLICT(Host, IP, Port) DO UPDATE ... 会如下报错：

执行 SQL 查询时发生错误：ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

解决方法：建立约束前,清洗现有数据

在建立唯一性约束之前，如果现有数据中存在违反该约束的重复记录，你需要进行数据清洗。数据清洗的过程通常包括识别重复记录、决定如何处理这些记录（如删除、合并或修改），以及执行相应的操作。以下是一些步骤和建议，帮助你清洗现有数据以便能够添加唯一性约束：

‌识别重复记录‌：

使用SQL查询来找出在Host、IP和Port列上有重复组合的记录。可以使用GROUP BY和HAVING子句来识别这些重复项。以下查询将找出所有重复的Host、IP和Port组合以及它们出现的次数：

SELECT Host, IP, Port, COUNT(*) as count FROM http_table GROUP BY Host, IP, Port HAVING COUNT(*) > 1;

‌决定如何处理重复记录‌：

‌删除重复项‌：如果重复记录没有保留价值，你可以选择删除它们。确保在删除之前备份数据，以防万一。
‌合并记录‌：如果重复记录包含有用的信息，你可能想要合并它们。这通常涉及更新一条记录以包含所有相关信息，并删除其他重复记录。
‌修改记录‌：在某些情况下，你可能能够通过修改重复记录中的一个或多个字段来消除重复。

‌执行数据清洗操作‌：

根据你的决定，编写SQL语句来执行删除、合并或修改操作。

如果你决定删除重复的记录，并且只保留每个组合的第一条记录，你可以使用以下查询（请注意，这个查询假设你有一个唯一标识每条记录的id字段）：

DELETE FROM http_table WHERE id NOT IN ( SELECT MIN(id) FROM http_table GROUP BY Host, IP, Port );

这个查询会删除每个Host、IP和Port组合中除id最小的一条记录之外的所有记录。

‌验证数据清洗结果‌：

在执行数据清洗操作后，再次运行识别重复记录的查询，确保所有重复项都已被处理。

检查数据以确保没有其他问题，如数据丢失或不一致。

‌添加唯一性约束‌：

确认数据已经清洗干净，没有违反唯一性约束的重复记录，可以安全地添加唯一性约束了。使用ALTER TABLE语句来添加约束：

ALTER TABLE http_table 
ADD CONSTRAINT unique_host_ip_port UNIQUE(Host, IP, Port);

这时又发生错误！

执行 SQL 查询时发生错误：near "CONSTRAINT": syntax error

在SQLite中，ALTER TABLE 语句用于修改表的结构，比如添加列、重命名表、添加约束等。但是，SQLite 在使用 ALTER TABLE 添加约束时有一些限制，特别是关于唯一性约束（UNIQUE CONSTRAINT）。

在SQLite中，如果你想要给一个已经存在的表添加一个新的唯一性约束，并且这个约束涉及到多个列，你不能直接在 ALTER TABLE 语句中使用 ADD CONSTRAINT 语法。SQLite不支持这种直接添加多列唯一性约束的方式。

相反，你需要采取一种间接的方法来实现这一点。以下是一种可能的解决方案：

‌创建一个新表‌：这个新表应该包含与原始表相同的列，并且包含你想要添加的唯一性约束。
‌复制数据‌：将原始表中的数据复制到新表中，同时确保没有违反唯一性约束的数据被插入。
‌删除原始表‌（可选）：如果数据复制成功，并且你确定新表包含了所有需要的数据，你可以删除原始表。
‌重命名新表‌：将新表重命名为原始表的名称。

务必注意：备份数据‌：

在进行任何数据修改或结构更改之前，始终确保你有最新的数据备份。这是防止数据丢失或损坏的重要步骤。

请记住，数据清洗是一个可能对数据产生重大影响的操作。在执行任何删除或修改操作之前，务必仔细考虑并备份你的数据。

最终执行的SQL语句是：

--修改原更新语句，使用 INSERT INTO ... ON CONFLICT ... DO UPDATE 方式，一次查询执行插入或删除。

INSERT INTO http_table (Host, IP, Port, Title, Url, Info, Location, Method, Code, Tag, Server) 
VALUES ('Host', 'IP', 'Port', 'Title', 'Url', 'Info', 'Location', 'Method', 'Code', 'Tag', 'Server') 
ON CONFLICT (Host, IP, Port) DO UPDATE SET Info = excluded.Info;

-- 添加唯一约束，操作步骤：

-- 首先备份原始的 .db 文件

-- 识别重复记录
SELECT Host, IP, Port, COUNT( * ) AS count
  FROM http_table
 GROUP BY Host, IP, Port
HAVING COUNT( * ) > 1;

-- 删除重复记录
DELETE FROM http_table
      WHERE id NOT IN (
    SELECT MIN(id) 
      FROM http_table
     GROUP BY Host, IP, Port
);

-- 创建包含唯一性约束的新表
CREATE TABLE new_http_table (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    Host     TEXT    DEFAULT "",
    IP       TEXT    DEFAULT "",
    Port     INTEGER DEFAULT (0),
    Title    TEXT    DEFAULT "",
    Url      TEXT    DEFAULT "",
    Info     TEXT    DEFAULT "",
    Location TEXT    DEFAULT "",
    Method   TEXT    DEFAULT "",
    Code     INTEGER DEFAULT "",
    Tag      TEXT    DEFAULT "",
    Server   TEXT    DEFAULT "",
    Uptime   TEXT    DEFAULT (datetime('now', 'localtime') ) ,
    UNIQUE(Host, IP, Port)
);

-- 复制数据到新表（确保没有重复项）
INSERT INTO new_http_table (id, Host, IP, Port, Title, Url, Info, Location, Method, Code, Tag, Server, Uptime)
	SELECT id, Host, IP, Port, Title, Url, Info, Location, Method, '', Tag, Server, Uptime
	FROM http_table
	WHERE (Host, IP, Port) IN (SELECT Host, IP, Port
								FROM http_table
								GROUP BY Host, IP, Port
								HAVING COUNT( * ) = 1);

-- 查询对比新旧两个表数据总数
SELECT (SELECT COUNT( * ) 
          FROM http_table) AS old_count, (SELECT COUNT( * ) 
                                                    FROM new_http_table) AS new_count;

-- （可选）删除原始表
--DROP TABLE http_table;

-- 重命名原始表的名称
ALTER TABLE http_table RENAME TO back_http_table;

-- 重命名新表为原始表的名称
ALTER TABLE new_http_table RENAME TO http_table;

-- （可选）删除备份表
--DROP TABLE back_http_table;

--释放空闲占用（类似收缩数据库）
VACUUM;

--检查数据库完整性
PRAGMA integrity_check;

--数据处理全部完成

标签：数据清洗

对sqlite已有数据,添加多列的唯一性约束

解决方法：建立约束前,清洗现有数据