SQLite 取证查询与 WAL 实验

SQLite 查询结果容易在两处发生偏差：统计对象选错，以及参与读取的文件集合不完整。前者可能把记录数当成不同对象数，后者可能漏掉 WAL 中已经提交的变更。两种情况下，SQL 都可能正常返回一个整数。

本篇提供合成数据实验。前三节可在全新的内存数据库中执行；WAL 实验使用 Python 标准库，在临时目录自行创建全部文件。练习中的建表、写入和 checkpoint 仅用于生成教学数据，不应对原始检材执行。

建立最小数据集

可在已安装的 SQLite 命令行中运行 sqlite3 :memory:，然后输入以下语句。内存数据库从空状态开始，不读取或修改现有证据文件。

CREATE TABLE follows (
    id INTEGER PRIMARY KEY,
    account_id TEXT NOT NULL,
    channel_id TEXT,
    follow_state INTEGER NOT NULL
);

INSERT INTO follows VALUES
    (1, 'X', 'C01', 1),
    (2, 'X', 'C01', 1),
    (3, 'X', 'C02', 1),
    (4, 'X', 'C03', 0),
    (5, 'Y', 'C04', 1);

SELECT id, account_id, channel_id, follow_state
FROM follows
ORDER BY id;

本例规定 follow_state=1 表示当前关注，0 表示当前未关注；channel_id 标识频道。第 1、2 行是两条不同记录，但指向同一频道。真实应用中，表结构、状态含义及重复成因都需要另外核对，不能由本例推定。

目标问题是“账号 X 当前关注多少个不同频道”。筛选得到第 1、2、3 行，再按频道标识统计，候选答案应为 2。

记录数 非空值与不同对象

SELECT COUNT(*) AS matching_rows,
       COUNT(channel_id) AS non_null_ids,
       COUNT(DISTINCT channel_id) AS distinct_channels
FROM follows
WHERE account_id = 'X'
  AND follow_state = 1;

输出为 3, 3, 2。三个表达式分别计算筛选后的行数、非 NULL 标识数量和不同的非 NULL 标识数量。这里的区别与 SQLite 聚合函数定义 一致。

去重依据来自题目和对象标识。若问题问“有多少条当前关注记录”，答案就是 3；若问“多少个不同频道”，答案才是 2。消息正文相同，也不自动表示同一条消息；按正文去重可能误删重复发送的真实事件。

空字符串与 NULL 还需要分开检查。下面的查询独立构造一组数据，不改变前面的表。

WITH sample(channel_id) AS (
    VALUES ('C01'), ('C01'), (NULL), ('')
)
SELECT COUNT(*) AS all_rows,
       COUNT(channel_id) AS non_null_ids,
       COUNT(DISTINCT channel_id) AS distinct_ids
FROM sample;

输出为 4, 3, 2。空字符串参与了非空值计数和去重，但不能因此认定它代表一个有效频道。如果实际材料中出现缺失或无效标识，应先报告数据质量问题，依据已确认的业务含义决定如何处理。把它统一删掉、替换成 0 或算作一个对象，都会额外引入假设。

实际程序中，目标账号等值应采用工具支持的参数绑定传入。将账号文本直接拼接