SQLite中按日期统计各用户累计出现次数的优化方案

需求说明 #

现有一张包含Id（非数值型）、Date、Username的表，需新增Instance列，统计每个用户截至对应日期的累计出现次数。同一日期内的记录按Id排序后依次递增序号（参考示例输出）。

示例输入 #

Id      Date      Username
------  --------  --------
6age    1/3/23    User1
8s9a    1/3/23    User1
d29j    1/3/23    User2
fj48    1/2/23    User1
39k9    1/2/23    User3
a8j3    1/1/23    User1
0ao2    1/1/23    User1
ajd6    1/1/23    User1
am50    1/1/23    User2
amv8    1/1/23    User3

期望输出 #

Id      Date      Username  Instance
------  --------  --------  --------
6age    1/3/23    User1     6
8s9a    1/3/23    User1     5
d29j    1/3/23    User2     2
fj48    1/2/23    User1     4
39k9    1/2/23    User3     2
a8j3    1/1/23    User1     3
0ao2    1/1/23    User1     2
ajd6    1/1/23    User1     1
am50    1/1/23    User2     1
amv8    1/1/23    User3     1

高效解决方案 #

针对2200万行的超大表，核心优化点是利用索引避免全表排序，结合SQLite窗口函数实现需求。

1. 创建复合索引（关键优化）

窗口函数需要按Username分区、Date+Id排序，提前创建对应索引可让SQLite直接利用索引的有序性，避免内存中进行海量数据排序：

CREATE INDEX idx_username_date_id ON your_table(Username, Date, Id);

注意：如果Date是字符串格式（如示例中的1/3/23），字符串排序会导致日期逻辑错误（比如1/10/23会排在1/2/23之前），建议先将Date转换为DATE类型：

-- 添加日期类型列
ALTER TABLE your_table ADD COLUMN date_dt DATE;
-- 转换字符串为日期（注意格式匹配，示例是mm/dd/yy）
UPDATE your_table SET date_dt = DATE(Date, 'mm/dd/yy');
-- 用转换后的日期列创建索引
CREATE INDEX idx_username_datedt_id ON your_table(Username, date_dt, Id);

2. 生成Instance列的查询语句

使用ROW_NUMBER()窗口函数，按用户分组、日期+Id排序生成累计序号：

SELECT 
    Id,
    Date,
    Username,
    ROW_NUMBER() OVER (PARTITION BY Username ORDER BY Date, Id) AS Instance
FROM your_table;

如果用转换后的日期列，将ORDER BY Date替换为ORDER BY date_dt。

3. 批量写入新表（推荐）

直接对原表执行ALTER TABLE ADD COLUMN再UPDATE会非常缓慢，建议直接创建包含Instance列的新表：

CREATE TABLE your_table_with_instance AS
SELECT 
    Id,
    Date,
    Username,
    ROW_NUMBER() OVER (PARTITION BY Username ORDER BY Date, Id) AS Instance
FROM your_table;

完成后可给新表添加必要索引：

-- 给Id加唯一索引（如果Id是唯一标识）
CREATE UNIQUE INDEX idx_id ON your_table_with_instance(Id);
-- 保留用户+日期的索引，方便后续查询
CREATE INDEX idx_username_date ON your_table_with_instance(Username, Date);

4. 额外性能调优

• 调整SQLite缓存大小：根据服务器内存情况，增大缓存可提升读写速度，例如设置200MB缓存：

  PRAGMA cache_size = -200000; -- 单位是页，默认页大小4KB，-200000即200MB
  

• 执行查询前关闭自动提交：

  PRAGMA autocommit = OFF;
  

  完成后手动提交：COMMIT;

内容的提问来源于stack exchange，提问作者MrChadMWood