如何在SQL中实现O(1)查询（类似数组访问）？针对OHLCV数据场景

针对你需要的O(1)级查询（类似数组直接访问），结合SQLite的特性，有以下可行方案，核心是利用数据连续递增的特性，直接映射到数据库的物理存储定位：

1. 基于ROWID的直接偏移查询 #

SQLite默认给每张表分配自增的ROWID（若你显式定义INTEGER PRIMARY KEY类型的主键，该主键会直接映射到ROWID）。如果你的数据是按symbol分组、日期连续增量插入且无缺失日期，可按以下步骤操作：

• 新建一张symbol_meta表，存储每个symbol的起始信息：

CREATE TABLE symbol_meta (
    symbol TEXT PRIMARY KEY,
    start_date TEXT NOT NULL,
    start_rowid INTEGER NOT NULL
);

• 计算目标日期相对于start_date的偏移天数：offset_days = julianday(target_date) - julianday(start_date)
• 直接通过计算出的ROWID定位查询：

SELECT date, symbol, open, high, low, close, volume
FROM price_data
WHERE ROWID = (SELECT start_rowid + offset_days FROM symbol_meta WHERE symbol = 'BTC')
AND symbol = 'BTC';

这种方式利用SQLite对ROWID的直接定位能力，无需遍历索引，查询效率接近O(1)——因为ROWID直接对应数据页中的物理存储位置。

2. 自定义连续序号复合主键 #

显式为每个symbol的记录分配连续递增的序号，将其与symbol组合作为主键：

• 修改price_data表结构：

CREATE TABLE price_data (
    symbol TEXT NOT NULL,
    seq INTEGER NOT NULL,
    date TEXT NOT NULL,
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER NOT NULL,
    PRIMARY KEY (symbol, seq)
);

• 插入数据时，对每个symbol的seq从1开始递增（可通过SELECT MAX(seq) + 1 FROM price_data WHERE symbol = 'XXX'获取下一个序号）
• 查询时直接通过symbol和seq定位：

SELECT * FROM price_data WHERE symbol = 'BTC' AND seq = 100;

由于(symbol, seq)是复合主键，SQLite会为其创建唯一索引，而seq是连续无间隙的，查询时索引定位开销极低，接近O(1)的访问效率。

3. 单Symbol独立表存储 #

若你的symbol数量不多，可为每个symbol单独创建一张表：

CREATE TABLE btc_ohlcv (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    open REAL NOT NULL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER NOT NULL
);

• 插入数据时无需额外处理，id会自动连续递增
• 查询时直接通过id定位：

SELECT * FROM btc_ohlcv WHERE id = 100;

这种方式下，自增主键id直接映射到ROWID，SQLite可直接定位到该行，实现近似O(1)的查询速度。

关键前提 #

所有方案都依赖一个核心条件：每个symbol的日期必须严格连续，无缺失、无重复，否则偏移量或序号的映射会失效。插入数据时必须做校验，确保日期序列的连续性。

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