提到数据仓库，很多人第一反应就是Hive、ClickHouse、Snowflake这类专门的分析型数据库，部署复杂、运维成本高。但如果你的数据量在几个GB以内，比如一个电商店铺的销售流水、一个部门的人事考勤、一个个人项目的埋点日志，完全可以用SQLite来搭建数据仓库。SQLite虽然是个嵌入式数据库，但它支持标准SQL的绝大部分语法，包括外连接、窗口函数、CTE公共表表达式，这些能力对于实现星型模型已经足够了。本文以一个图书销售分析场景为例，完整演示如何用SQLite落地一套简化的星型模型数仓。
星型模型的核心结构：事实表与维度表
星型模型之所以叫星型，是因为它的结构画出来像一颗星星：中心是事实表，周围一圈是维度表。事实表存放的是业务过程产生的度量值，比如一次销售的数量、金额，它只存数字和指向维度表的外键，本身不存储描述性信息。维度表则存放的是分析的角度，比如时间、商品、门店、客户这些维度的详细属性。
以图书销售为例，事实表fact_sales记录每一笔订单的图书ID、日期ID、门店ID、数量和金额。维度表包括dim_book（图书信息：书名、作者、分类、出版社）、dim_date（日期信息：年、月、季度、星期几）、dim_store（门店信息：城市、区域、面积）。分析时先定位维度，再汇聚事实，这样的结构让多角度统计变得非常自然。
和直接在业务库上做宽表查询相比，星型模型的好处是显而易见的。第一，数据高度规范化，图书的属性只存一份，不会因为订单数量多而重复存储，SQLite的单文件体积能得到控制。第二，分析口径统一，所有报表都从同一套维度表取口径，避免了各个报表各算各的导致数据对不上的尴尬。第三，扩展维度只需要加一张维度表和一个外键字段，事实表结构基本不动。
在SQLite中创建星型模型的表结构
下面是完整的建表脚本。这里有几个SQLite特有的细节需要注意：SQLite默认不开启外键约束，必须通过PRAGMA foreign_keys = ON手动激活，否则外键声明只是摆设；另外事实表上的索引非常关键，因为分析查询几乎都会按维度字段做过滤和分组。
-- 开启外键约束
PRAGMA foreign_keys = ON;
-- 日期维度表
CREATE TABLE dim_date (
date_id INTEGER PRIMARY KEY, -- 格式如 20240115
full_date TEXT NOT NULL,
year INTEGER NOT NULL,
month INTEGER NOT NULL,
quarter INTEGER NOT NULL,
day_of_week INTEGER NOT NULL
);
-- 图书维度表
CREATE TABLE dim_book (
book_id INTEGER PRIMARY KEY,
book_name TEXT NOT NULL,
author TEXT,
category TEXT, -- 分类：技术/文学/少儿等
publisher TEXT
);
-- 门店维度表
CREATE TABLE dim_store (
store_id INTEGER PRIMARY KEY,
store_name TEXT NOT NULL,
city TEXT,
region TEXT
);
-- 销售事实表
CREATE TABLE fact_sales (
sale_id INTEGER PRIMARY KEY,
date_id INTEGER REFERENCES dim_date(date_id),
book_id INTEGER REFERENCES dim_book(book_id),
store_id INTEGER REFERENCES dim_store(store_id),
quantity INTEGER NOT NULL,
amount REAL NOT NULL
);
-- 为高频分析字段建索引
CREATE INDEX idx_fact_date ON fact_sales(date_id);
CREATE INDEX idx_fact_book ON fact_sales(book_id);
CREATE INDEX idx_fact_store ON fact_sales(store_id);索引的取舍需要权衡。上面三个单列索引覆盖了按时间、按图书、按门店的常见分析路径。如果你的查询经常是组合条件，比如某城市某月份的销量，可以考虑建复合索引CREATE INDEX idx_city_month ON fact_sales(store_id, date_id)。不过SQLite对单文件的写并发有限制，数仓场景一般是批量导入后只读分析，所以建索引的成本压力不大。
典型分析查询：多维度聚合的实现
表结构搭好后，分析查询就是事实表关联维度表再聚合的过程。下面这个查询统计2024年各城市、各图书分类的销售金额和数量，这就是星型模型最典型的用法：维度表提供分组字段，事实表提供度量值。
SELECT
s.city,
b.category,
SUM(f.amount) AS total_amount,
SUM(f.quantity) AS total_quantity
FROM fact_sales f
JOIN dim_book b ON f.book_id = b.book_id
JOIN dim_store s ON f.store_id = s.store_id
JOIN dim_date d ON f.date_id = d.date_id
WHERE d.year = 2024
GROUP BY s.city, b.category
ORDER BY total_amount DESC;对于环比、同比这类需要对比的计算，窗口函数能大幅简化SQL。SQLite从3.25版本开始完整支持窗口函数，下面的例子计算每个分类的月度销售额及其三个月移动平均，在旧版数据库上这种需求要写多层嵌套子查询，现在一个OVER子句就搞定了。
WITH monthly AS (
SELECT
d.year,
d.month,
b.category,
SUM(f.amount) AS total_amount
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_book b ON f.book_id = b.book_id
WHERE d.year = 2024
GROUP BY d.year, d.month, b.category
)
SELECT
category,
month,
total_amount,
AVG(total_amount) OVER (
PARTITION BY category
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3m
FROM monthly
ORDER BY category, month;用WITH子句把复杂查询拆成命名清晰的中间步骤，是简化星型模型查询的利器。SQLite不支持原生物化视图，但你可以把CTE计算出的聚合结果定期写入一张汇总表（比如agg_monthly_category），实现类似物化的效果，报表查询直接打汇总表，速度可以快一到两个数量级。
简化设计中的常见坑与应对策略
第一个坑是日期维度表的生成。手工插入几百行日期数据不现实，用递归CTE一键生成是更优雅的办法，下面的SQL生成2024年全年的日期维度数据：
WITH RECURSIVE dates(d) AS (
SELECT DATE('2024-01-01')
UNION ALL
SELECT DATE(d, '+1 day') FROM dates WHERE d < DATE('2024-12-31')
)
INSERT INTO dim_date (date_id, full_date, year, month, quarter, day_of_week)
SELECT
CAST(REPLACE(d, '-', '') AS INTEGER),
d,
CAST(STRFTIME('%Y', d) AS INTEGER),
CAST(STRFTIME('%m', d) AS INTEGER),
CAST(STRFTIME('%m', d) AS INTEGER) / 3 + 1,
CAST(STRFTIME('%w', d) AS INTEGER)
FROM dates;第二个坑是维度表膨胀和缓慢变化维。比如图书改了分类，直接UPDATE维度表会导致历史报表跟着变，口径失真。简化场景下可以用增加effective_date字段的方式做最小化的类型二处理：旧记录保留，新记录插入，事实表关联时限定生效日期区间。如果分析精度要求不高，也可以接受直接覆盖，但要在设计文档里明确记录这一取舍。
第三个坑是数据导入的原子性。从业务库抽数写入SQLite时，务必把一次同步的所有写入包在一个事务里：先BEGIN，写完COMMIT。这不仅是数据一致性要求，SQLite的事务机制下批量插入不开事务会比开事务慢几十倍，因为每次写入默认都会触发一次磁盘同步。另外建议启用PRAGMA journal_mode = WAL，让写入期间读操作不被阻塞。
整体来看，SQLite搭建星型模型数仓的核心思路就是：维度建模规范化数据结构，外键加索引保证查询效率，CTE和窗口函数简化分析SQL，汇总表补偿物化能力的缺失。这套方案的全部数据就是单个文件，拷贝、备份、归档都是复制粘贴级别的工作量，非常适合中小数据量的分析场景。当数据量增长到SQLite明显吃力时，再平滑迁移到DuckDB或PostgreSQL，建表语句和查询SQL几乎可以原样复用，前期投入不会浪费。