SQLite 进阶：窗口函数、CTE、生成列与 JSON —— 发展脉络、横向对比与选型建议 未开始

题记与说明

报告性质：本报告为纵轴章节（《SQLite 进阶：窗口函数、CTE、生成列-JSON-vertical》）与横轴章节（《SQLite 进阶：窗口函数-CTE-生成列与JSON-横向对比-horizontal》）的综合稿，于 2026-11-05 完成，时间截面同日。本稿为按独立审读意见修订后的最终稿（同日修订），主要修订：补齐"2050 年向后兼容承诺"（lts.html）、fopen() 官方定位（whentouse.html）、生成列文件兼容边界（gencol.html §3）、STRICT 表概念（stricttables.html）、单线程执行事实（VLDB 2022 论文）五处出处；JSON 函数计数按官方 json1.html 修正为"26 标量 + 4 聚合 + 2 运算符 + 4 表值"；实测结论收窄为"四类特性各抽一例"并列明未测子项；"五个对比对象"改为"五个数据库引擎"；驱动力因果措辞降级为"时间上耦合的合理推测"，3.3 标题改为"相互印证"。

来源说明：核心事实取自 SQLite 官方发布日志（sqlite.org/releaselog）、官方语言文档（lang_with.html、gencol.html、json1.html、windowfunctions.html、changes.html），以及 DuckDB、PostgreSQL、MySQL、MariaDB 官方一手来源与社区一手资料（Hacker News、Simon Willison 博客、MotherDuck/DataCamp 对比文章、SIGMOD Record 访谈等），关键事实均附来源 URL。横轴章节在本次会话中做了本地 SQLite 3.50.4 实测（见 2.6 节）；未能验证的内容在文末"不确定与未核实项"中如实标注，不做臆测。

综合说明：两份章节的事实层高度一致，综合时已消除以下出入与重复——① JSON 能力口径按官方 json1.html 修正为"26 个标量函数 + 4 个聚合函数 + 2 个运算符 + 4 个表值函数"（官方合计原文为 "thirty functions and two operators"；初稿曾误计为"30 个标量函数"），函数清单与计数口径见 2.3.4 脚注；② FILTER 子句表述精确化：聚合函数自 3.30.0 起支持 FILTER，但内置窗口函数不能加 FILTER、不能用 DISTINCT；③ 纠正两处易错的时间锚点：递归 CTE 属 3.8.3 首发而非纯修复版 3.8.3.1，CLI .mode json 出现于 3.33.0（2020）而非常见误传的 3.36.0；④ 两章重复的版本号、特性清单、生成列限制等合并为单处陈述；⑤ 本机实测版本（3.50.4）与最新版本（3.54.1）的差距如实保留。

TL;DR

SQLite 的窗口函数、CTE、生成列与 JSON 于 2014–2024 十年间按需补齐：CTE（2014）→ JSON 扩展（2015）→ 窗口函数（2018）→ 生成列（2020）→ JSON 转正与 JSONB（2022/2024）。实现以 PostgreSQL 为参照交叉测试，技能可近乎原样平移到 PG；差距在"上限"（无 GIN、无并行、无数据修改 CTE 与 QUALIFY）而非语法。生成列最强（双形态、五个数据库引擎通用），JSON 差距最大（无列类型、无 GIN）。SQLite 与 DuckDB 是"存"与"算"互补，务实答案是 SQLite 存 + DuckDB 算；公有领域许可与官方"到 2050 年保持 C API 与磁盘格式完全向后兼容"的承诺（出处见 2.3.6），让技能投资回报期以十年计。

一、纵轴：发展脉络（2000–2026）

1.1 时间线速览

1.2 阶段〇（2000–2013）：慢热的奠基期——为什么"四件套"来得这么晚

SQLite 于 2000 年春由 D. Richard Hipp 在一份通用动力（General Dynamics）承接美国海军的合同项目中着手设计，同年 8 月发布 1.0，并置于公有领域，此后 Hipp 一直担任首席架构师（来源：Wikipedia: D. Richard Hipp、SIGMOD Record 访谈。广为流传的"导弹驱逐舰"细节在 Hipp 本人的讲述中与传闻略有出入，可信的内核是"军方合同、需要无管理员、永不宕机的嵌入式数据库"）。

在其后的十三年里，SQLite 的工程优先级始终是嵌入式场景的可靠、小巧、快速与向后兼容，SQL 方言只实现"标准中最常用的子集"。到 2013 年时，它已具备事务、触发器、视图、外键和全文检索，但本课题的"四件套"全部缺席：

• 没有 CTE——复杂分析查询只能靠嵌套子查询，可读性极差；
• 没有窗口函数——排名、移动平均、同比环比只能导出后到应用层算；
• 没有 JSON——半结构化数据只能存 TEXT 自己解析；
• 没有生成列——派生数据要靠应用代码或触发器手工维护。

这一"特性真空期"是理解后续历史的关键背景：四件套不是被"发明"出来的，而是在 2014–2024 十年间按需求紧迫程度逐一补齐的。

1.3 阶段一（2014–2015）：CTE 落地，JSON 破土

2014-02-03，SQLite 3.8.3 引入 WITH 子句与公共表表达式——官方发布日志原文："Added support for common table expressions and the WITH clause"（releaselog/3_8_3.html）。一周后的 3.8.3.1（2014-02-11）仅为查询规划器缺陷修复，不含任何新特性（releaselog/3_8_3_1.html），可反证 CTE（含递归 CTE）即在此版一次性落地。CTE 对标 SQL:1999 标准；值得注意的是 SQLite 出于与 SQL Server、Oracle 的兼容性考虑，并未强制要求递归 CTE 使用 RECURSIVE 关键字（lang_with.html）。这一阶段的典型用法是序列生成、组织树/图遍历、以及把分步分析拆成可读的"查询管道"——正是个人工具中最常见的三类需求。

2015-10-14，SQLite 3.9.0（自该版起改用语义化版本号）把 json1 扩展加入源码树与 amalgamation，但需以 -DSQLITE_ENABLE_JSON1 编译期选项显式启用（releaselog/3_9_0.html）。同一版本还带来了表达式索引、FTS5、表值函数等重量级特性。JSON 起初只是"可选扩展"而非核心能力——这一"opt-in"状态持续了近七年。

驱动力：一是用户对"SQLite 连 CTE/JSON 都没有"的长期抱怨在邮件列表与论坛积累——此说未逐条核实原始讨论，属时间上耦合的合理推测；二是官方策略转向对 SQL 标准与 PostgreSQL 子集的渐进补齐——这一条有后续各版本发布日志中明确的兼容性表述可印证（见 1.6 与 1.9）。

1.4 阶段二（2016–2018）：补上分析型 SQL 的最后一块拼图——窗口函数

窗口函数是四件套中最晚出现、也最被期待的一个。2018 年 9 月 15 日的 SQLite 3.25.0 正式支持窗口函数（releaselog/3_25_0.html："Add support for window functions"），实现者是 SQLite 核心开发者 Dan Kennedy，覆盖 ROW_NUMBER、RANK、DENSE_RANK、NTILE、LAG、LEAD 及聚合函数的 OVER 用法（Hacker News 社区讨论）。同一版本还带来 ALTER TABLE RENAME COLUMN 与 Geopoly 空间模块。由于窗口函数实现复杂，3.25.1–3.25.3 连续三个补丁版都在修窗口函数相关缺陷（changes.html）。

驱动力：窗口函数在 SQL:2003 标准化后已在 PostgreSQL、Oracle、SQL Server 等数据库全面普及十余年，社区对 SQLite 缺失开窗能力的期待可见于 2018 年发布当口的 Hacker News 热议（Hacker News 讨论）；至于""不支持开窗"是数据从 SQLite 迁出（或分析者绕开 SQLite）的头号理由""嵌入式与桌面报表需求快速上升最终促成官方立项"，官方没有留下立项决策的明文记录，本报告亦未逐条核实邮件列表原始请愿，此因果链属时间上耦合的合理推测。

1.5 阶段三（2019–2020）：窗口函数增强 + 生成列 + CLI 出 JSON

• 2019-04-16，3.28.0：窗口函数大幅增强——EXCLUDE 子句、窗口链接（window chaining）、GROUPS 帧类型、RANGE 帧的 <expr> PRECEDING/FOLLOWING 边界（releaselog/3_28_0.html）。同年官方 Git 镜像（github.com/sqlite/sqlite）建立，标志着社区开放度提升。
• 2019-10-04，3.30.0：为聚合函数加入 FILTER 子句（SUM(x) FILTER (WHERE …)），并在 ORDER BY 中支持 NULLS FIRST/LAST（releaselog/3_30_0.html）——两者都是分析查询的高频语法。
• 2020-01-22，3.31.0：生成列登场。官方文档明确记载："Generated column support was added with SQLite version 3.31.0 (2020-01-22)"（gencol.html、releaselog/3_31_0.html）。生成列分 VIRTUAL（默认，读时计算、不占空间）与 STORED（写时计算、落盘存储）两类，一步到位双形态同步推出；限制也很清晰：表达式只能引用本行常量与列、只能用确定性标量函数，不能用子查询、聚合或窗口函数，也不能作主键、不能有默认值。早于 3.31.0 的老版本读取含生成列的库文件会直接报"schema 损坏"——官方文档原文："If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt."（gencol.html 第 3 节 Compatibility）——这是 SQLite 少数几个破坏文件级兼容的 schema 变更。同版还加入了 JSON 路径的 #-N 数组记法。
• 2020-08-14，3.33.0：命令行工具新增四种输出模式，原文："Added four new output modes: 'box', 'json', 'markdown', and 'table'"（releaselog/3_33_0.html）。.mode json 让数据分析师可以在 CLI 一条命令把查询结果变成 JSON，同时该版支持了 PostgreSQL 语法的 UPDATE FROM。这是 SQLite 面向数据分析体验的重要转折。（注意：.mode json 出现于 3.33.0 而非网络上常见的误传 3.36.0。）
• 2020-12-01，3.34.0：递归 CTE 的递归分支允许是复合 SELECT（此前只能是单条简单 SELECT，lang_with.html）。

1.6 阶段四（2021–2022）：从"能用"到"顺手"——CTE 物化控制、JSON 转正

• 2021-03-12，3.35.0：CTE 支持 MATERIALIZED / NOT MATERIALIZED 提示，且多次使用的 CTE 默认行为从"不物化"翻转为"物化"（releaselog/3_35_0.html、lang_with.html）。同版带来 RETURNING 子句、ALTER TABLE DROP COLUMN 和内置数学函数（后者仍需编译开关）。分析查询的可控性与 SQL 现代化再进一步。
• 2022-02-22，3.38.0：JSON 转正。官方文档的表述精准概括了这次身份变化："In other words, the JSON functions went from being opt-in with SQLite version 3.37.2 and earlier to opt-out with SQLite version 3.38.0 and later"（json1.html）。同版新增 -> 与 ->> 运算符，官方明确说明"compatible with MySQL and PostgreSQL"（releaselog/3_38_0.html）——为异构迁移铺路。同版还引入布隆过滤器加速大型分析查询、format() 函数与 CLI 列式输出增强。
• 2022-06-25，3.39.0："long overdue" 的 RIGHT 与 FULL OUTER JOIN 终于落地，另加入 PostgreSQL/SQL 标准兼容的 IS DISTINCT FROM 比较运算符，并允许无 GROUP BY 的聚合查询使用 HAVING（releaselog/3_39_0.html）。至此，SQLite 的分析型 SQL 方言与主流数据库的差距基本抹平。

生态背景（外部驱动力成型）：2017 年 Simon Willison 发布 Datasette——起步时自称"a new tool for creating and publishing JSON APIs for SQLite databases"，后发展为数据探索与发布平台（datasette.io、simonwillison.net；Willison 于 2021-11 确认项目四周年）。配套的 sqlite-utils 把"CSV → SQLite → 分析 → 发布"变成一条纯 SQLite 的个人数据管道。与此同时，2019 年 CWI 研究所的 Mark Raasveldt 与 Hannes Mühleisen 发布了面向 OLAP 的嵌入式分析库 DuckDB（SIGMOD 2019 论文《DuckDB: an Embeddable Analytical Database》），以"分析界的 SQLite"自居并迅速走红（Wikipedia: DuckDB）。2017–2018 的 Datasette、2019 年的 DuckDB，与 2018–2022 年 SQLite 密集补齐分析特性，时间上的耦合是可查证的事实；但"外部竞争压力是这一阶段最显性驱动因素"属基于时间耦合的合理推测——官方从未在文档中承认这一因果（讨论见 3.3）。2022 年，Gaffney 等人（含 SQLite 核心团队成员）在 VLDB 发表论文《SQLite: Past, Present, and Future》（vldb.org/pvldb/vol15/p3535-gaffney.pdf），标志其进入学术界的系统审视视野。

1.7 阶段五（2023–2024）：JSONB 与"现代 SQL"的成熟

• 2023-05-16，3.42.0：JSON5 输入支持与 json_error_position() 函数（json1.html）。
• 2023-11-01，3.44.0：聚合函数允许在最后一个参数之后写 ORDER BY，使 group_concat、string_agg、json_group_array 可以有序输出（changes.html）——对"按分组生成有序 JSON 数组"这类分析任务至关重要。
• 2024-01-15，3.45.0：JSONB 发布。官方表述："All JSON functions are rewritten to use a new internal parse tree format called JSONB. The new parse-tree format is serializable and hence can be stored in the database to avoid unnecessary re-parsing"（releaselog/3_45_0.html）。JSONB 比文本 JSON "更小、更快——可能快数倍"，且与 PostgreSQL 的 JSONB 同名不同质（json1.html）。一个月后的 3.45.1（2024-01-30）修复了损坏 JSONB 输入导致的指数级耗时问题。
• 2024-05-23，3.46.0：新增 json_pretty()，并以 VALUES-as-coroutine 优化将数千行的多值 INSERT 提速近半（changes.html）。
• 2024-10-21，3.47.0：->> 支持负数组下标（如 '$.a[-1]'），对齐 PostgreSQL 风格（json1.html）。

1.8 阶段六（2025–2026）：打磨期与当下现状

• 2025-02-06，3.49.0：session 扩展兼容生成列数据库（changes.html）。
• 2025-05-29，3.50.0：新增 unistr()/unistr_quote()；jsonb_set/jsonb_replace 在大对象内部修改时尽量保持对象体积不变以减少 I/O（changes.html）。
• 2025-11-04，3.51.0：新增 jsonb_each()/jsonb_tree() 表值函数；对生成列强制 STRICT 类型（发布日志原文 "Enforce STRICT typing on computed columns"。STRICT 表是 SQLite 3.37.0（2021-11-27）引入的可选严格类型模式：建表语句末尾加 STRICT 关键字后，每个列都必须声明类型、且只能是 INT/INTEGER/REAL/TEXT/BLOB/ANY 六种之一，写入时严格执行类型检查而非 SQLite 传统类型亲和性的宽松转换，见 stricttables.html；本条变更即把这套规则强制扩展到生成列）；窗口函数在大区间的 BETWEEN :x FOLLOWING AND :y FOLLOWING 场景显著提速（changes.html、json1.html）。
• 2026-03-06，3.52.0 发布后被官方撤回，全部特性并入 3.53.0（releaselog/3_52_0.html 标注 "Withdrawn"）——严谨的版本纪律一以贯之。
• 2026-04-09，3.53.0：新增 json_array_insert()/jsonb_array_insert()；ALTER TABLE 允许增删 NOT NULL 与 CHECK 约束；浮点舍入默认精度提升至 17 位有效数字（changes.html）。
• 2026-10-09 / 10-16，3.54.0 / 3.54.1：3.54.0 为 printf/format 加入 %J、%j JSON 字符串转换，并放弃 Windows XP 支持（最低要求 Vista），另有约 4% 的速度与查询计划器优化；3.54.1 修复 32 位构建的堆溢出。这是截至本报告撰写日（2026-11-05）的最新版本（releaselog/3_54_0.html、changes.html、linuxiac 报道）。

至此四件套的现状：CTE（含递归与物化提示）、窗口函数（全功能 + 聚合 FILTER + 有序聚合）、生成列（VIRTUAL/STORED + STRICT）、JSON（26 个标量函数 + 4 个聚合函数 + ->/->> 运算符 + 4 个表值函数，官方合计口径为"30 个函数与 2 个运算符"，清单见 2.3.4 脚注；JSONB 二进制格式）全部成熟稳定。

1.9 纵轴洞察：四条驱动主线与关键参与者

1. 标准与生态兼容是第一驱动力。 每一个特性落地都有明确的"对标物"：CTE 对标 SQL:1999，窗口函数对标 SQL:2003 已普及十余年的业界实践，->/->>、UPDATE FROM、IS DISTINCT FROM 明确写着"compatible with MySQL and PostgreSQL"。SQLite 的策略不是引领标准，而是把主流数据库验证过的语法子集"追平"。

2. 数据分析与个人工具浪潮是第二驱动力。 Datasette（2017）让 SQLite 成为数据发布格式，CLI 的 .mode json（2020）让单条命令即可产出机器可读结果，聚合 ORDER BY（2023）与 JSONB（2024）让"分组出有序 JSON"和"半结构化存储"达到生产可用——SQLite 从"应用内嵌存储"逐步演化为"单机数据仓库 + 数据交换格式"的双重角色。

3. 官方工程哲学塑造了节奏。 一切按需小步加入、绝不破坏向后兼容：JSON 先做 opt-in 扩展（2015）观察七年再转正（2022）；生成列宁可让老版本报"schema 损坏"也要保持格式纪律；2026 年甚至出现发布后撤回（3.52.0）再整并的罕见操作。

4. 社区反馈与版本演进在时间上高度耦合。 3.25.x 连续三个补丁版修窗口函数、2018 年 Hacker News 上的公开热议，都与特性发布紧贴在一起；"社区声音直接进入了版本规划"属合理推测——本报告未核实邮件列表原始请愿与官方决策记录的一一对应关系。

主要参与者：D. Richard Hipp——创始人、首席架构师，json1/JSONB 等设计的主要推手，2000 年至今从未缺席；Dan Kennedy——核心开发者，窗口函数等关键特性的实现者（releaselog/3_25_0.html 及 Hacker News 讨论）；Gaffney 等人在 VLDB 2022 的论文确认了核心团队（含 Hipp、Kennedy、Brasfield）对演进路径的掌控；生态侧的 Simon Willison（Datasette/sqlite-utils）与 DuckDB 团队（Raasveldt、Mühleisen）则从外部持续施压。

对数据分析与个人工具的结论：四件套的补齐史，本质上是一部"SQLite 从嵌入式引擎长成个人数据栈底座"的历史。今天，一个数据分析工作流可以完全建立在单文件 SQLite 之上——CSV 经 sqlite-utils 导入，生成列自动维护派生字段，JSON 列存放半结构化配置，CTE 组织多步清洗，窗口函数完成排名与滚动统计，.mode json 或 Datasette 直接对外输出——而这在 2013 年的 SQLite 上每一环都做不到。

方法与教训：官方发布日志是唯一可靠的时间锚点——本次研究先后纠正了两处易错点（.mode json 实际出现于 3.33.0 而非 3.36.0；递归 CTE 属 3.8.3 首发而非 3.8.3.1）；"Withdrawn" 也是一种历史（3.52.0 的撤回说明版本史研究必须查到具体 releaselog 页面）；外部生态（Datasette、DuckDB）与内核特性演进的耦合，是纯技术编年史容易漏掉的驱动因素。

二、横轴：横向对比（2026-11-05 截面）

2.1 课题界定与对比对象选取

横向对比要回答的不是"哪个数据库更好"，而是三个更精确的问题：

1. 这四项特性在 SQLite 中的实现，与同类实现相比处于什么水平（全不全、准不准、快不快）？
2. 做数据分析和个人工具时，谁是对标物、谁是互补物、谁是替代物？
3. 学这套技能的投资，能否迁移到别的引擎上？

据此选取四个圈层的对比对象：

• 同类嵌入式引擎（最直接对标）：DuckDB——同样是进程内、单文件、零运维，但为 OLAP 分析而生，2026 年已是"个人数据分析"赛道的事实标准；
• 服务端关系库（功能参照与成长方向）：PostgreSQL 18（窗口函数与 JSONB 的行业标杆）、MySQL 9.7 LTS / MariaDB 11.8+（Web 应用生态最大公约数）；
• 非数据库替代路径：pandas / Polars 等 DataFrame 引擎（不做存储，直接在文件与内存上完成窗口运算与展开）；
• SQLite 衍生生态（部署形态替代）：Cloudflare D1、Turso（libSQL / Rust 重写版）。

2.2 总对比表（2026-11-05 截面）

来源：SQLite 版本与特性见 sqlite.org/releaselog/3_54_0.html、sqlite.org/download.html；DuckDB 2.0 见 duckdb.org/2026/10/21/announcing-duckdb-200.html；PostgreSQL 18 见 postgresql.org/about/press/presskit18/en；MySQL 生命周期见 endoflife.date/mysql；MariaDB 11.8 见 mariadb.org/11-8-lts-released。

2.3 逐项对比

2.3.1 窗口函数：晚到四年，但精度向 PostgreSQL 看齐

SQLite 在 3.25.0（2018-09-15） 才引入窗口函数，明显晚于 PostgreSQL（2010 年前后的 9.x 时代）和 Oracle/SQL Server。但官方文档明确说明：实现"以 PostgreSQL 为参照，并与其做了大量交叉测试"（sqlite.org/windowfunctions.html）。这意味着语法与语义几乎可以无缝平移到 PG。3.28.0（2019）补齐了 EXCLUDE 子句、GROUPS 帧类型和窗口链（window chaining）。

当前覆盖面：

• 11 个内置窗口函数：row_number / rank / dense_rank / percent_rank / cume_dist / ntile / lag / lead / first_value / last_value / nth_value；
• 任意聚合函数都可加 OVER，应用还能通过 sqlite3_create_window_function() 注册自定义聚合窗口函数；
• 帧规格完整：ROWS / RANGE / GROUPS 三种帧类型，UNBOUNDED/<expr> PRECEDING/FOLLOWING 边界，EXCLUDE NO OTHERS / CURRENT ROW / GROUP / TIES 四种排除方式——EXCLUDE 是连很多商用库都没有的能力；
• FILTER 子句的精确边界（综合两章表述）：聚合函数自 3.30.0 起支持 FILTER 子句，但内置窗口函数不能加 FILTER、也不能用 DISTINCT；
• 限制：窗口函数只能出现在结果列与 ORDER BY 中。

对比：DuckDB 支持 PG 风格的窗口函数超集，且提供 QUALIFY 子句（对窗口结果直接过滤，省掉一层子查询，见 duckdb.org/docs/stable/sql/query_syntax/qualify.html），SQLite 没有这一便利。PostgreSQL 仍是功能上限。MySQL 8.0（2018）与 MariaDB 10.2 才补齐窗口函数，与 SQLite 同期。pandas/Polars 用 shift() / cumsum() / groupby().rank() 覆盖常见窗口需求，但语法完全脱离 SQL，学习成本无法迁移。

2.3.2 CTE：SQLite 支持得又早又全，递归是招牌

SQLite 在 3.8.3（2014） 就支持 WITH 子句与递归 CTE，比 MySQL 8.0（2018）早四年，比"支持窗口函数"还早。官方文档（sqlite.org/lang_with.html）确认当前支持：

• 递归 CTE 完整（递归分支可以是复合 SELECT，3.34.0 起）；甚至不强制 RECURSIVE 关键字（SQL Server/Oracle 兼容风格）；
• 单个 WITH 中可混合多个普通/递归 CTE；
• 3.35.0（2021-03）起支持 MATERIALIZED / NOT MATERIALIZED 提示——官方直言是"从 PostgreSQL 抄来的"非绑定提示，且多次引用的 CTE 默认行为自该版翻转为物化；
• WITH 可挂在 SELECT / INSERT / UPDATE / DELETE 前。

限制：递归分支内不能用聚合或窗口函数；WITH 不能用于 CREATE TRIGGER 内。

对比：PostgreSQL 功能最全；DuckDB 2.0 新增了数据修改 CTE（把 INSERT/UPDATE/DELETE/COPY 当作查询管道的一步，见 duckdb.org/2026/10/21/announcing-duckdb-200.html），SQLite 没有。对个人工具而言，递归 CTE 是被低估的杀器：树形目录展开（笔记/书签/账目科目）、依赖图遍历、生成日期序列，全都可以在纯 SQL 里完成，无需应用层递归。

2.3.3 生成列：五个数据库引擎都支持、SQLite 实现紧凑、可迁移性最好

SQLite 在 3.31.0（2020-01-22） 引入生成列，VIRTUAL（默认，读时计算、不占空间）与 STORED（写时计算、占空间）双形态同步推出。官方文档（sqlite.org/gencol.html）给出的能力与限制清单非常明确：

• ✔ 可指定类型/亲和性、NOT NULL、CHECK、UNIQUE、外键约束；VIRTUAL 列可以建索引（等价于表达式索引）；表达式可引用其他生成列；位置可放列清单任意处；3.51.0（2025-11）起对生成列强制 STRICT 类型（STRICT 表的定义见 1.8 节注：3.37.0 起的可选严格类型模式，列类型仅限 INT/INTEGER/REAL/TEXT/BLOB/ANY）；
• ✘ 不能有 DEFAULT；不能作为 PRIMARY KEY；表达式只能用常量、同行列和确定性标量函数（不能有子查询、聚合、窗口函数）；ALTER TABLE 不能追加 STORED 列；PRAGMA table_info 看不到（要用 table_xinfo）；早于 3.31.0 的版本读取含生成列的库文件会报"schema 损坏"（官方原文见 1.5 节引文）。

对比各家的"时间线错位"很有意思：PostgreSQL 12（2019）只有 STORED 生成列，直到 18（2025-09-25）才补上 VIRTUAL 形态（见 postgresql.org/about/press/presskit18/en 与 neon.com/postgresql/18-new-features）；SQLite 则是 VIRTUAL/STORED 一步到位。MySQL 5.7（2015）与 MariaDB 5.2（2010）引入更早——MariaDB 事实上是生成列的最早普及者。DuckDB 支持 GENERATED ALWAYS AS (expr) [VIRTUAL|STORED]，默认 VIRTUAL（duckdb.org/docs/2.0/sql/statements/create_table.html）。

对个人工具，这项的性价比最高：从 URL 派生域名列、从时间戳派生日历列、从 JSON 字段派生排序键——派生数据的一致性下沉到存储层，配合表达式索引等于免费的"预计算 + 自动同步"。而且五个数据库引擎（SQLite/DuckDB/PostgreSQL/MySQL/MariaDB）全部支持此特性，是四项中数据库引擎之间迁移性最好的（pandas/Polars 等 DataFrame 引擎无此概念，见表 2.2）。

2.3.4 JSON：SQLite 从"扩展"升为一等公民，但上限低于 PostgreSQL jsonb

官方文档（sqlite.org/json1.html）确认的现状（计数口径见本节末脚注）：

• 26 个标量函数 + 2 个运算符（-> / ->>）+ 4 个聚合函数 + 4 个表值函数（json_each / json_tree 及 jsonb 变体）——官方 Overview 原文为 "thirty functions and two operators"（30 个函数 = 26 个标量 + 4 个聚合），另有 "The four table-valued functions are…"；构造、抽取、修改（json_set/json_patch RFC-7396）、检视一应俱全；
• 3.38.0（2022-02）起 JSON 函数默认编入 amalgamation，不再需要编译期开关；
• 3.42.0 起可读 JSON5（输出仍是规范 JSON）；3.45.0（2024-01）引入 JSONB 二进制格式：免重复解析、略省磁盘，函数间传递时用 jsonb_ 变体更高效；官方明确 SQLite JSONB 与 PostgreSQL 的 JSONB 不兼容，且多数操作是 O(N)，不存在 PG 那种 O(1) 内部查找；
• 没有 JSON 列类型：所谓"JSON 列"只是 TEXT/BLOB，不能像 PG jsonb 那样建 GIN 反向索引，只能逐路径建表达式索引。

脚注：函数清单与计数口径（据 json1.html Overview 章节逐一点名核对）：官方原文三句——"There are twenty-eight scalar functions and operators"（28 = 26 个标量函数 + 2 个运算符）、"There are four aggregate SQL functions"、"The four table-valued functions are…"；合计口径 "thirty functions and two operators" 中的"三十个函数" = 26 标量 + 4 聚合。26 个标量函数为：json, jsonb, json_array, jsonb_array, jsonb_array_insert, json_array_insert, json_array_length, json_error_position, json_extract, jsonb_extract, json_insert, jsonb_insert, json_object, jsonb_object, json_patch, jsonb_patch, json_pretty, json_quote, json_remove, jsonb_remove, json_replace, jsonb_replace, json_set, jsonb_set, json_type, json_valid。计数口径：含全部 jsonb_ 变体（json/jsonb 成对各计一次）；4 个聚合函数 json_group_array, jsonb_group_array, json_group_object, jsonb_group_object 与 4 个表值函数 json_each, json_tree, jsonb_each, jsonb_tree 不计入标量；->/->> 为运算符，不计入函数。初稿"30 个标量函数"的口径与官方页面不符，最终稿已按上述官方原文修正。

对比：PostgreSQL jsonb（9.4 起）配 GIN 索引与 jsonpath（PG 12 起），仍是深度 JSON 查询的上限；MySQL 有原生 JSON 类型与 ->/->>（8.0 起，见 dev.mysql.com/doc/en/json.html）；MariaDB 长期把 JSON 当 LONGTEXT 别名，直到 13.1 才补上 ->/->> 运算符（mariadb.org 公告，未核对原文全文，见文末未核实项）；DuckDB 的 json 扩展默认自动加载，支持 ->/->>、read_json 直读文件、0 基 JSON 索引（duckdb.org/docs/stable/data/json/overview.html），2.0 又引入 VARIANT 一等类型，把半结构化数据做进列式引擎。

个人工具视角：存 API 响应、LLM 输出、扩展配置，SQLite 的 JSON 能力绰绰有余，JSONB 之后性能顾虑大幅缓解；"几十万条 JSON 文档做复杂 ad-hoc 聚合"则是 DuckDB/PG 的舒适区。

2.3.5 性能与规模：行式 vs 列式的代差

四特性本身是语法层的事，但选型的真实分水岭在存储引擎：SQLite 是行式 B-tree，全表扫描+聚合的 OLAP 负载天然吃亏。MotherDuck 与 DataCamp 的对比文章口径一致：分析查询上 DuckDB 常见快 10–100×（motherduck.com/learn/duckdb-vs-sqlite-databases、datacamp.com/blog/duckdb-vs-sqlite-complete-database-comparison）。Reddit r/dataengineering 一篇实战帖的结论更有参考价值：迁移到 DuckDB 后查询快约 5×，但事务性写入比 SQLite 慢约 2×，最终"两个都用"（reddit 帖）。这印证了官方定位——whentouse.html 原文："SQLite does not compete with client/server databases. SQLite competes with fopen()."（SQLite 竞争的从来不是客户/服务端数据库，而是 fopen()）；它的甜区是"写多读频次低的应用存储"，DuckDB 的甜区是"读多算多的分析"。

除存储格式外，还有一项"上限"层面的硬差异需要交代（TL;DR、2.5.1、3.5.3 三处结论提到"无并行"，在此补足论证）：SQLite 单条查询没有并行执行。VLDB 2022 论文《SQLite: Past, Present, and Future》原文："Notably, SQLite generally does not use multiple threads, which limits its ability to take advantage of the available hardware parallelism. For sorting large amounts of data, SQLite uses an optional multithreaded external merge sort algorithm. For all other operations, SQLite performs all work in the calling thread."（除大规模排序的可选多线程外部归并排序外，其余操作全部在调用线程内完成，论文）；PostgreSQL 支持查询内并行，DuckDB 更是并行+向量化执行，这是 SQLite 在 OLAP 上限上落后的两个结构性原因之一（另一个是行式存储）。

2.3.6 成本、生态与版本节奏

• 许可：SQLite 是公有领域（比 MIT 还宽松），嵌入式再分发零负担——这是它与所有对比对象在"成本"维度最大的差别；DuckDB MIT、PostgreSQL License、MySQL GPL 双许可、MariaDB GPL。
• 运维：SQLite 与 DuckDB 都是零进程零配置；PG/MySQL 需要服务、备份、监控。
• 学习：SQLite 的窗口函数/CTE 语义官方就是照 PostgreSQL 实现并交叉测试的，学 SQLite 进阶 SQL 的技能几乎可原样平移到 PG；MySQL/MariaDB 方言差异更大（如 JSON 运算符的迟到）。
• 生态：SQLite 是部署量最大的数据库引擎——官方页面称活跃使用中的 SQLite 数据库超过 1 万亿个（每部智能手机内含数百个库），并推测它是"仅次于 libz 的第二大部署软件模块"（sqlite.org/mostdeployed.html）。Python 标准库内置 sqlite3、所有主流浏览器内嵌、DBeaver/DB Browser 等桌面工具开箱即用。DuckDB 生态偏分析侧：Jupyter、Parquet/Iceberg/Delta 直读、DuckLake、MotherDuck 云；其官方扩展库中甚至有 SQLite 扩展，可以直接 ATTACH 一个 .db 文件做分析——两库互补在工具层已被打通。衍生形态方面：Cloudflare D1 是托管 SQLite（绑定 Workers 生态）；Turso 基于 libSQL 并已用 Rust 重写引擎，2026-08 在 Turso Cloud 上线了核心 SQLite 不具备的并发写预览，但仍处 beta（turso.tech/blog/concurrent-writes-on-turso-cloud）。
• 版本节奏：SQLite 3.54.0/3.54.1 为当前最新；官方长期支持页（lts.html）原文："The intent of the developers is to support SQLite through the year 2050."（开发者打算支持 SQLite 直到 2050 年），并明确承诺 "keep the SQLite C-language API and on-disk format fully backwards compatible"（保持 C 语言 API 与磁盘格式完全向后兼容——今天编写的应用应能链接并使用"数十年后发布的未来版本"；该页写作时官方自述"距 2050 尚有 25 年"，初稿"25 年 API 兼容承诺"即由此而来，严格说官方的承诺是到 2050 年完全向后兼容）——这是个人工具"十年不坏"的底气。DuckDB 2.0.0 LTS（2026-10-21）支持期到至少 2027-10，节奏为一年一个特性版本 + 一年一个 LTS，但 2.0 数据库文件不向后兼容旧版读取，升级需迁移（duckdb.org 公告）；PostgreSQL 18 已 GA（2025-09-25），19 处于 RC1（2026-10-15，因特性回退与 AI 辅助 bug 检测略有延期，postgresql.org RC1 公告）；MySQL 8.0 已于 2026-04 到期 EOL，现行 LTS 为 8.4 与 9.7（2026-04-21 GA），另设 26.x Innovation 快速线（endoflife.date/mysql）；MariaDB 11.8 LTS（2025-06-04 GA）为当前推荐版。

2.4 适用场景矩阵（数据分析与个人工具视角）

2.5 横向洞察

1. SQLite 的四特性不是"阉割版 PG"，而是"精度向 PG 看齐、范围刻意收窄"。官方把 PG 当参照实现并交叉测试，连 MATERIALIZED 提示都是照抄的；差距集中在"上限"（GIN 索引、并行执行、数据修改 CTE、QUALIFY），而非"语法"。
2. 四特性中，生成列是 SQLite 最不输给任何对手的一项（双形态同步推出、可索引、五个数据库引擎通用）；JSON 是差距最大的一项（无列类型、无 GIN，JSONB 只是解析加速）；窗口函数与 CTE 属于"够用，且学完即可平移 PG"。
3. SQLite 与 DuckDB 是互补而非竞争：一个管"存"（OLTP、应用内嵌），一个管"算"（OLAP、文件分析）。2026 年个人数据分析栈的务实答案是"SQLite 存 + DuckDB 算"，DuckDB 官方 SQLite 扩展让两者可以互读文件。
4. 对个人工具而言，SQLite 最强的横向优势甚至不在四特性本身，而在承载它们的载体：公有领域许可 + 单文件 + 数十年 API 兼容承诺，让"进阶 SQL"这项技能投资的回报期以十年计——这是任何对比对象都无法给出的承诺。

2.6 本地实测记录（横轴章节在本次会话执行）

在横轴章节撰写时，本机 Git Bash 中运行 sqlite3 --version 输出：3.50.4 2025-07-30 19:33:53 ... (64-bit)。随后在内存库中对四类特性各抽代表性用例验证：

CREATE TABLE t(g TEXT, m INT, v REAL);
INSERT INTO t VALUES ('a',1,10.0),('a',2,20.0),('b',1,30.0),('b',2,40.0);
SELECT g, m, v, ROW_NUMBER() OVER (PARTITION BY g ORDER BY m),
       SUM(v) OVER (PARTITION BY g ORDER BY m ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
       LAG(v) OVER (PARTITION BY g ORDER BY m) FROM t;
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<5)
  SELECT group_concat(n) FROM seq;
CREATE TABLE gc(a INT, b INT, c INT GENERATED ALWAYS AS (a*b) STORED);
INSERT INTO gc(a,b) VALUES (3,4);  SELECT a,b,c FROM gc;
SELECT json_extract('{"name":"ada","tags":["x","y"]}', '$.tags[1]'),
       '{"a":1}' ->> '$.a', jsonb('{"a":1}') IS NOT NULL;

实测输出（关键行）：窗口三列分别得到 1|10.0|NULL、2|30.0|10.0 等正确结果；递归 CTE 返回 1,2,3,4,5；生成列返回 3|4|12；JSON 三项返回 y|1|1。上述实测的覆盖范围是"四类特性各抽一例"——窗口函数抽了 ROW_NUMBER / SUM OVER 累计帧 / LAG 三个代表，CTE 抽了递归序列生成，生成列抽了 STORED 形态，JSON 抽了 json_extract / ->> / jsonb 构造——各例结果均与文档语义一致；不能据此得出"四项特性的全部能力均已验证"。未在本机实测的子项包括：VIRTUAL 生成列与生成列索引、生成列的 STRICT 类型约束、EXCLUDE 子句 / GROUPS 帧 / 窗口链接 / 聚合 FILTER、MATERIALIZED 与 NOT MATERIALIZED 提示、jsonb_ 变体的读写与性能、JSON5 输入、json_error_position() 等。注意本机实测版本（3.50.4，2025-07）比最新 3.54.1 落后一个特性版本，3.54 的改动（如 CLI 列头默认显示）未在本机验证。

三、交叉洞察：两轴结合说明了什么

3.1 "补齐史"与"看齐度"合起来回答了同一个问题：学 SQLite 进阶 SQL，究竟在学什么

纵轴的叙事是"四件套是 2014–2024 按需补齐的"；横轴的发现是"补齐的参照系自始至终是 PostgreSQL"。两轴叠加得出一个单靠任何一轴都得不到的结论：SQLite 的进阶 SQL 本质上是一套"PostgreSQL 方言的可嵌入式子集"——官方文档亲口说窗口函数以 PG 为参照并交叉测试、MATERIALIZED 提示"从 PG 抄来"、->/->> 运算符明确兼容 MySQL 与 PG。这意味着学这套东西不是学一门封闭方言，而是在用最低成本的载体（单文件、零安装、Python 标准库自带）练习一套将来可以原样搬进 PG 的通用技能。对个人工具作者与数据分析师，这是"入门即 PG 预科"的性价比判断依据。

3.2 双轴交叉给四件套排出清晰的强弱序

纵轴记录了每项特性的"出生日期与成长曲线"，横轴测出了每项特性在当下的"竞争位置"，两者交叉后的强弱序是：

• 生成列——最强项。纵轴显示它 2020 年一步到位推出 VIRTUAL/STORED 双形态，此后持续加固（3.49.0 让 session 扩展兼容、3.51.0 强制 STRICT 类型）；横轴显示它是五方（SQLite/DuckDB/PG/MySQL/MariaDB）全部支持、且 SQLite 形态最完整的一项——连 PG 都要到 18 才补上 VIRTUAL。它是四件套中唯一"SQLite 不输给任何对手"的能力。
• JSON——差距最大项。纵轴显示它走了"opt-in 扩展（2015）→ 观察七年 → 转正（2022）→ JSONB（2024）"的最保守路径；横轴显示即便转正后它仍无 JSON 列类型、无 GIN 索引，JSONB 与 PG 同名不同质且是 O(N) 操作。SQLite 的 JSON 定位是"存取与轻查询"，不是"深度 JSON 检索"。
• CTE——最早的牌，最稳的地基。纵轴显示它 2014 年即落地、比窗口函数早四年；横轴显示它的递归能力至今是个人工具的"被低估杀器"，且语法可平移 PG。
• 窗口函数——晚到但精度高。纵轴显示它 2018 年才来、3.25.x 连修三个补丁版；横轴确认其语义与 PG 几乎无缝，唯一实质缺口是 QUALIFY。

3.3 纵轴的"外部压力"与横轴的"分工格局"相互印证

纵轴观察到 Datasette（2017）与 DuckDB（2019）同四件套密集补齐期（2018–2022）在时间上高度耦合——"外部压力驱动补齐"的因果判断属合理推测，官方无明文（见 1.6）；横轴则展示了竞争的最终形态——不是替代，而是分工："SQLite 存 + DuckDB 算"，DuckDB 官方 SQLite 扩展可直接 ATTACH 一个 .db 文件做分析，Reddit 实战帖的结论也是"两个都用"。两轴合起来呈现的图景是：补齐分析特性之后，SQLite 与分析型引擎的生态位更清晰（分工而非冲突加剧）——"防御性回应"的因果链只有时间耦合证据、无官方明文，而"分工格局"本身有横轴的多重证据（官方 SQLite 扩展、Reddit"两个都用"）支持，故两轴标题为"相互印证"而非"互为因果"——SQLite 站稳"应用内嵌存储 + 数据交换格式"，DuckDB 站稳"单机分析引擎"。这一判断对选型的含义是：把"学 SQLite 进阶 SQL"与"学 DuckDB"视为同一条技能树的两段，而非二选一。

3.4 版本纪律与兼容哲学，是技能投资"十年回报期"的底层担保

纵轴记录的三个细节——JSON 扩展观察七年才转正、生成列宁可让老版本报"schema 损坏"也要维持格式纪律、2026 年 3.52.0 发布后撤回重整——与横轴发现的官方兼容承诺——"keep the SQLite C-language API and on-disk format fully backwards compatible"，且开发者打算支持 SQLite 直到 2050 年（lts.html；该页写作时官方自述"距 2050 尚有 25 年"，初稿"25 年兼容承诺"即源于此）（对照：DuckDB 2.0 数据库文件不向后兼容旧版读取）——形成呼应。两轴共同说明：SQLite 演进的最大特点不是快，而是可信的慢——你今天学的语法、建的库文件，十年后大概率原样可用；而选 DuckDB 这类快速演进的引擎时，要把"文件格式不兼容、需迁移"纳入升级成本。对个人工具（数据要陪人十年），这是权重最高的选型变量。

3.5 对用户的判断与建议

选型决策（按场景）：

1. 默认答案仍是 SQLite：桌面/App 内嵌存储、个人结构化库（账目/书签/日志/收藏）、树形与图查询（递归 CTE）、需要"十年不坏"的本地数据——四特性在 3.38+ 上全部生产可用，零运维、公有领域许可。
2. 分析量大就加一个 DuckDB：CSV/Parquet 百万行级聚合、ad-hoc 探索——不要试图把 SQLite 调优成分析引擎（行式存储是代差，社区口径 10–100× 差距），让 DuckDB 通过官方 SQLite 扩展直读你的 .db 文件即可。
3. 出现三个信号就计划平移 PG：需要 GIN 索引做深度 JSON 检索、需要数据修改 CTE / QUALIFY / 并行执行、需要多客户端并发写服务端化。窗口函数、CTE、生成列、->/->> 语法几乎原样平移，重写成本主要在运维而非 SQL。

学习路径建议（按投入产出排序）：

1. 先学 CTE（含递归）：最早成熟（2014）、语法最可迁移、个人工具使用频率最高（树形展开、依赖遍历、日期序列）；
2. 再学窗口函数：语义与 PG 交叉测试过，学了不亏；注意两点边界——内置窗口函数不能加 FILTER、不能用 DISTINCT，EXCLUDE 子句是超出多数商用库的加分项；
3. 第三学生成列：四件套中性价比最高的一项，"派生字段下沉到存储层 + 表达式索引"是个人工具的一致性基石；记住限制清单（无 DEFAULT、不作 PRIMARY KEY、只允许确定性标量函数）；
4. 最后学 JSON，且只学到"够用"：构造/抽取/修改（json_set/json_patch/->/->>）+ JSONB 缓存思路即可；若业务真的依赖深度 JSON 检索，那说明该换 PG jsonb 了。

版本下限提醒（实操清单）：

• 目标用户环境的 SQLite 至少 3.38.0（2022-02）：JSON 函数默认内置，无需编译开关；
• 要用 JSONB 性能优化则需 3.45.0+（2024-01），要用 jsonb_each/jsonb_tree 则需 3.51.0+；
• Python 标准库 sqlite3 绑定的是编译期版本，部署前先 sqlite3.sqlite_version 确认；
• DuckDB 侧：2.0 LTS 文件不向后兼容，个人项目升级前先导出。

四、参考来源

SQLite 官方

• 发布日志：3.8.3、3.8.3.1、3.9.0、3.25.0、3.28.0、3.30.0、3.31.0、3.33.0、3.35.0、3.38.0、3.39.0、3.45.0、3.51.0、3.52.0（Withdrawn）、3.54.0
• 官方文档：lang_with.html（CTE）、windowfunctions.html（窗口函数）、gencol.html（生成列）、json1.html（JSON/JSONB）、stricttables.html（STRICT 表）、lts.html（长期支持与兼容承诺）、whentouse.html（何时用 SQLite，fopen() 出处）、changes.html（总变更史）、download.html、mostdeployed.html（部署量）

论文与人物

• SQLite: Past, Present, and Future（VLDB 2022）
• Wikipedia: D. Richard Hipp、SIGMOD Record 访谈
• Wikipedia: DuckDB

社区与生态

• Hacker News（2018 窗口函数）、datasette.io、simonwillison.net 项目页

对比对象官方来源

• DuckDB：2.0.0 LTS 公告、1.4.0 LTS 公告、JSON 文档、生成列文档、QUALIFY 文档
• PostgreSQL：18 新闻稿、Neon: PostgreSQL 18 新特性、19 RC1 公告
• MySQL / MariaDB：MySQL 生命周期（endoflife.date）、MySQL JSON 文档、MariaDB 11.8 LTS、MariaDB 13.1 JSON 运算符
• 衍生形态：Turso 并发写预览

性能对比口径

• MotherDuck: DuckDB vs SQLite、DataCamp: DuckDB vs SQLite、Reddit r/dataengineering 实战帖、Linuxiac: SQLite 3.54 报道

五、不确定与未核实项（如实说明）

1. MySQL 各版本日期与 26.x Innovation 线的命名来自 endoflife.date 抓取（2026-10-31 快照），未逐条核对 Oracle 官方公告原文。
2. "DuckDB 分析快 10–100×"来自 MotherDuck/DataCamp 文章与社区实战帖的口径，本次未在本机复现基准（本机仅对四类特性各抽一例验证了功能语义正确性，未测子项清单见 2.6 节）。
3. MariaDB 13.1 增加 ->/->> 的说法来自 mariadb.org 公告的搜索摘要，未抓取原文全文。
4. PostgreSQL 19 RC1 的延期原因来自搜索结果综述（含 Snowflake 工程博客），未逐字核对。
5. 本机实测用的是 SQLite 3.50.4（2025-07），比最新 3.54.1 落后一个特性版本；3.54 的改动（CLI 列头默认显示等）未在本机验证。
6. 生成列"早于 3.31.0 的版本读取含生成列的库文件报 schema 损坏"已有官方文档原文出处（gencol.html 第 3 节 Compatibility，引文见 1.5 节）；3.52.0 撤回等历史细节取自官方 releaselog 页面。两者均未在旧版本二进制上做降级实测。
7. 1.3/1.4/1.6/1.9 各处"驱动力"叙述（邮件列表长期抱怨积累、外部竞争压力促成官方立项、社区声音直接进入版本规划）未找到官方立项决策记录或可逐条对应的原始讨论链接，正文均已降级为"时间上耦合的合理推测"；3.3 节相应由"互为因果"改为"相互印证"。
8. "SQLite 单条查询无并行执行"引自 VLDB 2022 论文（引文见 2.3.5 节），未在官方文档中找到等价表述；论文还提到大规模排序有一个可选的多线程外部归并排序，本报告未单独实测验证。