@mmm159357456 楼主最后还用了啥方法？大概用时多久？占多少内存？

@specter119 请教一下，像 60 楼那样的数据（ 2600W 行数据），spark 计算 4 个不同的滑窗，需要多久？总共要多少内存？

@mmm159357456 这些都是单线程计算。

如果你是 8 核 CPU ，那可以同时计算 8 张表。

那么 40 张表总共只需不到 1 小时即可完成。



如果你自己转数据（即，用不到 generate_series 表值函数），可以直接在 Python 里用 sqlite 标准库。开个多进程，刷刷刷~

@mmm159357456 写漏了。。使用方式应该要将结果转成 csv ，再重定向至文件：

```shell
sqlite3.exe -csv data.db < main.sql > result.csv
```

@mmm159357456 用 SQLite 测试了生成整张表、计算整张表，结果如下：

　　项目　　　　　　结果大小　　　　用时
————————————————————
生成数据库　１．０２ＧＢ的数据库　２分钟
计算整张表　１．３５ＧＢ的ＣＳＶ　７分钟


## 数据库预览（ CSV 形式预览。200 年 x 365/366 天 x 360 个经纬度 = 26297640 行）：

year,dateday,geometry_x,geometry_y,element1,element2,element3,element4
1900,1900-01-01,0.0,0.0,1,1001,2001,3001
1900,1900-01-02,0.0,0.0,2,1002,2002,3002
1900,1900-01-03,0.0,0.0,3,1003,2003,3003
……
1900,1900-12-29,0.0,0.0,363,1363,2363,3363
1900,1900-12-30,0.0,0.0,364,1364,2364,3364
1900,1900-12-31,0.0,0.0,365,1365,2365,3365
1900,1900-01-01,1.0,-1.0,1,1001,2001,3001
1900,1900-01-02,1.0,-1.0,2,1002,2002,3002
1900,1900-01-03,1.0,-1.0,3,1003,2003,3003
……
1900,1900-12-29,359.0,-359.0,363,1363,2363,3363
1900,1900-12-30,359.0,-359.0,364,1364,2364,3364
1900,1900-12-31,359.0,-359.0,365,1365,2365,3365
1901,1901-01-01,0.0,0.0,1,1001,2001,3001
1901,1901-01-02,0.0,0.0,2,1002,2002,3002
1901,1901-01-03,0.0,0.0,3,1003,2003,3003
……
2099,2099-12-29,359.0,-359.0,363,1363,2363,3363
2099,2099-12-30,359.0,-359.0,364,1364,2364,3364
2099,2099-12-31,359.0,-359.0,365,1365,2365,3365


## 计算结果预览（ CSV 文件）：

1900,0.0,0.0,1900-01-01,1.0,1001.0,2001.0,3001.0
1900,0.0,0.0,1900-01-02,1.5,1001.5,2001.5,3001.5
1900,0.0,0.0,1900-01-03,2.0,1002.0,2002.0,3002.0
1900,0.0,0.0,1900-01-04,2.5,1002.5,2002.5,3002.5
1900,0.0,0.0,1900-01-05,3.0,1003.0,2003.0,3003.0
1900,0.0,0.0,1900-01-06,4.0,1003.5,2003.5,3004.0
……


## 计算方式预览（ CSV 形式。可保存后用 Excel 查看）：

1900,0.0,0.0,1900-01-01,(1)/1,(1001)/1,(2001)/1,(3001)/1
1900,0.0,0.0,1900-01-02,(1+2)/2,(1001+1002)/2,(2001+2002)/2,(3001+3002)/2
1900,0.0,0.0,1900-01-03,(1+2+3)/3,(1001+1002+1003)/3,(2001+2002+2003)/3,(3001+3002+3003)/3
1900,0.0,0.0,1900-01-04,(1+2+3+4)/4,(1001+1002+1003+1004)/4,(2001+2002+2003+2004)/4,(3001+3002+3003+3004)/4
1900,0.0,0.0,1900-01-05,(1+2+3+4+5)/5,(1001+1002+1003+1004+1005)/5,(2001+2002+2003+2004+2005)/5,(3001+3002+3003+3004+3005)/5
1900,0.0,0.0,1900-01-06,(2+3+4+5+6)/5,(1001+1002+1003+1004+1005+1006)/6,(2001+2002+2003+2004+2005+2006)/6,(3002+3003+3004+3005+3006)/5
1900,0.0,0.0,1900-01-07,(3+4+5+6+7)/5,(1001+1002+1003+1004+1005+1006+1007)/7,(2001+2002+2003+2004+2005+2006+2007)/7,(3003+3004+3005+3006+3007)/5
1900,0.0,0.0,1900-01-08,(4+5+6+7+8)/5,(1001+1002+1003+1004+1005+1006+1007+1008)/8,(2001+2002+2003+2004+2005+2006+2007+2008)/8,(3004+3005+3006+3007+3008)/5
1900,0.0,0.0,1900-01-09,(5+6+7+8+9)/5,(1001+1002+1003+1004+1005+1006+1007+1008+1009)/9,(2001+2002+2003+2004+2005+2006+2007+2008+2009)/9,(3005+3006+3007+3008+3009)/5
1900,0.0,0.0,1900-01-10,(6+7+8+9+10)/5,(1001+1002+1003+1004+1005+1006+1007+1008+1009+1010)/10,(2001+2002+2003+2004+2005+2006+2007+2008+2009+2010)/10,(3006+3007+3008+3009+3010)/5
1900,0.0,0.0,1900-01-11,(7+8+9+10+11)/5,(1002+1003+1004+1005+1006+1007+1008+1009+1010+1011)/10,(2001+2002+2003+2004+2005+2006+2007+2008+2009+2010+2011)/11,(3007+3008+3009+3010+3011)/5
……
1900,0.0,0.0,1900-12-31,(361+362+363+364+365)/5,(1356+1357+1358+1359+1360+1361+1362+1363+1364+1365)/10,(2351+2352+2353+2354+2355+2356+2357+2358+2359+2360+2361+2362+2363+2364+2365)/15,(3361+3362+3363+3364+3365)/5
1900,1.0,-1.0,1900-01-01,(1)/1,(1001)/1,(2001)/1,(3001)/1
1900,1.0,-1.0,1900-01-02,(1+2)/2,(1001+1002)/2,(2001+2002)/2,(3001+3002)/2
……


## 使用方式：

去 SQLite 官网下载个 1 MB 的 sqlite3.exe ，然后保存下面的 SQLite 代码为 main.sql ，然后命令行运行：

```shell
sqlite3.exe data.db < main.sql
```


## SQLite 代码：

*（ V 站排版原因，行首有全角空格）*

```sql
-- PRAGMA journal_mode = off; -- 取消日志记录。但这会输出个 off 。。
PRAGMA synchronous = off; -- 提交写请求给操作系统后，就可继续后续计算
PRAGMA cache_size = -8192; -- 占用 8 MB 内存

-- 建表
CREATE TABLE IF NOT EXISTS data (
　　 year INT,
　　 dateday DATE,
　　 geometry_x REAL,
　　 geometry_y REAL,
　　 element1 INT,
　　 element2 INT,
　　 element3 INT,
　　 element4 INT,
　　 PRIMARY KEY (year, geometry_x, geometry_y, dateday)
) WITHOUT ROWID;

-- 生成数据
INSERT INTO data
SELECT year.value,
　　　 DATE(year.value || '-01-01', day_of_year.value || ' days'),
　　　 area.value * 1.0,
　　　 area.value * -1.0,
　　　 day_of_year.value + 1,
　　　 day_of_year.value + 1001,
　　　 day_of_year.value + 2001,
　　　 day_of_year.value + 3001
　 FROM generate_series(1900, 2099) year,
　　　 generate_series(0, STRFTIME('%j', year.value || '-12-31') - 1) day_of_year,
　　　 generate_series(0, 359) area;

-- 计算表
SELECT year,
　　　 geometry_x,
　　　 geometry_y,
　　　 dateday,
　　　 /* -- 下面 4 行用于预览平均值的计算方式对不对
　　　 FORMAT('(%s)/%d', GROUP_CONCAT(element1, '+') OVER win5, COUNT(*) OVER win5),
　　　 FORMAT('(%s)/%d', GROUP_CONCAT(element2, '+') OVER win10, COUNT(*) OVER win10),
　　　 FORMAT('(%s)/%d', GROUP_CONCAT(element3, '+') OVER win15, COUNT(*) OVER win15),
　　　 FORMAT('(%s)/%d', GROUP_CONCAT(element4, '+') OVER win5, COUNT(*) OVER win5)
　　　 */
　　　 AVG(element1) OVER win5,
　　　 AVG(element2) OVER win10,
　　　 AVG(element3) OVER win15,
　　　 AVG(element4) OVER win5
FROM data
WINDOW win AS (PARTITION BY year, geometry_x, geometry_y ORDER BY dateday),
　　　 win5 AS (win ROWS 4 PRECEDING),
　　　 win10 AS (win ROWS 9 PRECEDING),
　　　 win15 AS (win ROWS 14 PRECEDING);
```

@mmm159357456

1. 你不是说『将值安置在滑动<起点>的同一行』吗？我就在想，(11+12)/2 要放在 11 那行。。

2. 上面假设了，『列 1 窗口大小为 2 ，列 2 为 4 ，列 3 为 6 』。所以，列 4 的窗口大小是怎么调整的？直接 = 列 1 窗口大小 = 2 ？？

3. 所以，最终结果是 40 张表，每张表的表头是 (日期、经纬度、列 1 、2 、3 、4)，共计 3KW 行？？

@mmm159357456

假设下表是 2000 年某地区的一张表（省略年份和经纬度），列 1 窗口大小为 2 ，列 2 为 4 ，列 3 为 6：

　　日期　　列１　列２　列３　列４
—————————————————
０１－０１　１１　２１　３１　４１
０１－０２　１２　２２　３２　４２
０１－０３　１３　２３　３３　４３
０１－０４　１４　２４　３４　４４
０１－０５　１５　２５　３５　４５
０１－０６　１６　２６　３６　４６
０１－０７　１７　２７　３７　４７
０１－０８　１８　２８　３８　４８
０１－０９　１９　２９　３９　４９

1. 1 日、2 日、9 日，新的『列 1 』值应为多少？

a. (11+12)/2 ，(12+13)/2 ，19
b. (11+12)/2 ，不算 2 日，19
c. ……？

2. 新的『列 4 』值怎么算？（像上面那样，随便写两三个日期即可）

@mmm159357456

你是说：

1. 滑动窗口按 <年, 地区> 分组，按 <时间> 顺序滑动
2. element1/2/3 是窗口大小分别为 5/10/15 的平均值，element4 = 此时这仨平均值之和
3. 每张表都这么算，共计算 40 次

？

@mmm159357456 你还真跨了 200 年嘛。。😂


假设只有 3 天和 3 个 geometry ，下表符合最终源数据表的格式了嘛？（我把经纬度拆开了）

dateday, geometry_x, geometry_y, element1, element2, element3, element4
2000-01-01, 1.0000, -1.0000, 1, 2, 3, 4
2000-01-01, 2.0000, -2.0000, 5, 6, 7, 8
2000-01-01, 3.0000, -3.0000, 9, 10, 11, 12
2000-01-02, 1.0000, -1.0000, 13, 14, 15, 16
2000-01-02, 2.0000, -2.0000, 17, 18, 19, 20
2000-01-02, 3.0000, -3.0000, 21, 22, 23, 24
2000-01-03, 1.0000, -1.0000, 25, 26, 27, 28
2000-01-03, 2.0000, -2.0000, 29, 30, 31, 32
2000-01-03, 3.0000, -3.0000, 33, 34, 35, 36


下面这俩，是生成上表的规则？还是要求 SQL 计算时要实现的功能？

- 『 element[1-3]均需要处理成滑动平均，element4 计算滑动窗口的总和』
- 『以上组合完成后需要两个处理水平重复，外加 20 种模式（即 X 2 X 20 』


另外：

1. 滑动窗口有多大？要按什么分组和排序吗？（比如，按<年>分组，按<天, 经纬度>排序？）
2. 『两个处理水平重复，外加 20 种模式』是啥意思。。

@mmm159357456 给个模拟数据吧

- 列名是像上面那样吗？
- 平均每一年有多少行？
- 有多少种 geometry ？平均有多少个字符？ geometry1 ？ ggeeoommeettrryy11 ？
- 有多少个 element ？

@mmm159357456 你原始数据是 CSV 之类的吗？

能给个 1900 年的数据吗？我想试试用 SQL 能多快 /慢

如果数据比较敏感，可以给个模拟数据。比如：（只要模拟完后，数据量差不多和原来一样即可。如存为 csv 后 1GB ）

dateday, geometry, element1, element2, ..., elementN
1900-01-01, geometry1, 11111111, 11111111, ..., 11111111
1900-01-02, geometry2, 22222222, 22222222, ..., 22222222
...

@FYFX @mmm159357456 我觉得没必要做啥 join ，直接在 data 上 groupby 后，对 el1, el2, ..., elN 做 sum 即可（只累加 >= theshold 的值）

换成 SQL 应该是 28 楼那样

结果应该是 31 楼那样，(年份数 * len(geometry_list)) 行 x (len(elements_in_data)) 列 的表

@mmm159357456 我觉得根据 year(dateday), geometry 来 groupby ，要不了多少内存吧？

大概只需要：200 年 * len(geometry_list) 行，len(elements_in_data) 列

@mmm159357456 我没看出 level 作用是啥。。


考虑使用数据库吗？感觉可以转成 SQL：

SELECT year(dateday), geometry, SUM(IF(el1 >= theshold, el1, NULL)), SUM(IF(el2 >= theshold, el2, NULL)), ...
　 FROM ...
GROUP BY year(dateday), geometry


基本上，扫一遍文件就算出来了

@mmm159357456 为啥每一天（ list_a ）都要计算每一年（ list_b ）的指标。。

当 x1 = 2022-10-31 时，也要计算 x2 = 1970/1971/1972/.../2099 的指标吗？？？

还是说，你遍历 list_b 是为了找到 x2 = 2022 ？

list_a 为啥要按日排列？[2099-12-31, 2000-01-01, 1970-12-31, 2099-01-01] 这样的排列有啥问题吗？反正每个日子都要计算 range(1970, 2100) 年的指标的。。



可能你放出函数 f 的伪代码，大家能更好讨论

@mmm159357456 我觉得在多线程多进程面前，协程是非常友好容易调试的了。。

另外，你的附加留言里说的好抽象。。要不，举点例子？

@mmm159357456 IO 部分用多线程，CPU 部分用多进程，整体用协程管理？

参考： https://docs.python.org/zh-cn/3/library/asyncio-eventloop.html#executing-code-in-thread-or-process-pools

希望能优化下 `WITH RECURSIVE` 递归 CTE 查询

相同数据量和结果的情况下，几层递归就比非递归版本耗时翻一倍。。损耗有点大。。

可见这个 [帖子回复]( https://v2ex.com/t/889443#r_12274236 )

@Mithril 我做了下『闭包表』和『改良后的邻接表』的测试 *（结尾附上一键建表和查询的 `SQL` 供测试）*

- 数据源：[2022 年中国全国 5 级行政区划]( https://github.com/adyliu/china_area/blob/master/area_code_2022.csv.gz)
- 数据库：`MySQL 8.0.29` 和 `SQLite 3.39.0`
- 表结构：『闭包表』和『 `(<pid, id>, is_leaf)` 型邻接表』
- 测试项：『查询根节点所有后代』和『查询根节点第 5 层后代』

结果如下 *（多次测试稳定后）*：



## 『查询根节点所有后代』速度对比

　　　　　表结构　　　　　　ＭｙＳＱＬ　ＳＱＬｉｔｅ

——————————————————————————

　　　　　闭包表　　　　　　　１．３秒　　０．１３秒

　　　　递归邻接表　　　　　　１．２秒　　０．６０秒

理想中递归损耗很小的邻接表　　０．６秒　　０．１２秒



Markdown 表格：

| 表结构 | `MySQL` | `SQLite` |
| :------------------------: | :-----: | :------: |
| 闭包表 | 1.3 秒 | 0.13 秒 |
| 递归邻接表 | 1.2 秒 | 0.60 秒 |
| 理想中递归损耗很小的邻接表 | 0.6 秒 | 0.12 秒 |



## 『查询根节点第 5 层后代』速度对比

　　　　　表结构　　　　　　ＭｙＳＱＬ　ＳＱＬｉｔｅ

——————————————————————————

　　　　　闭包表　　　　　　　１．２秒　　０．１２秒

　　　　递归邻接表　　　　　　０．５秒　　０．１３秒

理想中递归损耗很小的邻接表　　０．４秒　　０．１０秒



Markdown 表格：

| 表结构 | `MySQL` | `SQLite` |
| :------------------------: | :-----: | :------: |
| 闭包表 | 1.2 秒 | 0.12 秒 |
| 递归邻接表 | 0.5 秒 | 0.13 秒 |
| 理想中递归损耗很小的邻接表 | 0.4 秒 | 0.10 秒 |



## 目前观点

1. 4W 多次的 `ref` 级 `WHERE pid = ?`，还是能和 66W 次 `eq_ref` 级的 `WHERE id = ?` 过过招，甚至更快的。而且，磁盘 IO 越慢，这个差异应该越大。

2. 数据库们的 `WITH RECURSIVE` 查询，损耗有点大。

- `MySQL` 好歹每次递归都将上一次所有结果当作一张表来计算。但大概 5 次递归的耗时，就比非递归的多一倍了

- `SQLite` 最摆烂，每次递归只取以前结果的一行来计算，直到取完为止。所以有 66W 次的递归，耗时大概 5 倍。。😡

> Extract a single row from the queue.
>
> Pretend that the single row just extracted is the only row in the recursive table and run the recursive-select, adding all results to the queue.



## 『查询根节点所有后代』通用 `SQL`

*（ V 站排版原因，行首有全角空格）*

```sql
PRAGMA cache_size = -204800; -- 允许 SQLite 缓存 200 MB

-- 闭包表查询
SELECT COUNT(*), SUM(code), SUM(CHAR_LENGTH(name)) -- SQLite 写法：SUM(LENGTH(name))
　 FROM closure_tree
FORCE INDEX (idx_closure_tree) -- 我这测试，MySQL 不加这行，耗时翻好几倍。SQLite 需去掉此行
　 JOIN closure ON id = descendant
WHERE ancestor = 0;

-- 递归邻接表查询
WITH RECURSIVE
　 find(id, code, name, is_leaf) AS (
　　 SELECT id, code, name, is_leaf
　　　 FROM adjacent
　　 WHERE pid = 0
　　 UNION ALL
　　 SELECT b.id, b.code, b.name, b.is_leaf
　　　 FROM find a
　　　 JOIN adjacent b ON NOT a.is_leaf AND b.pid = a.id
　)
SELECT COUNT(*), SUM(code), SUM(CHAR_LENGTH(name)) -- SQLite 写法：SUM(LENGTH(name))
　 FROM find;

-- 理想中，没有递归损耗的邻接表查询
SELECT COUNT(*), SUM(b.code), SUM(CHAR_LENGTH(b.name)) -- SQLite 写法：SUM(LENGTH(name))
　 FROM adjacent a
CROSS JOIN adjacent b ON b.pid = a.id -- SQLite 需要 CROSS JOIN ，否则耗时翻几倍
WHERE NOT a.is_leaf;
```



## 『查询根节点第 5 层后代』通用 `SQL`

*（ V 站排版原因，行首有全角空格）*

```sql
PRAGMA cache_size = -204800; -- 允许 SQLite 缓存 200 MB

-- 闭包表查询
SELECT COUNT(*), SUM(code), SUM(CHAR_LENGTH(name)) -- SQLite 写法：SUM(LENGTH(name))
　 FROM closure_tree
FORCE INDEX (idx_closure_tree) -- 我这测试，MySQL 不加这行，耗时翻好几倍。SQLite 需去掉此行
　 JOIN closure ON id = descendant
WHERE ancestor = 0
　 AND distance = 5;

-- 递归邻接表查询
WITH RECURSIVE
　 var(depth) AS (
　　 SELECT 5
　),

　-- 递归部分查前 N - 1 层
　 find(id, is_leaf, depth) AS (
　　 SELECT 0, FALSE, var.depth - 1
　　　 FROM var
　　 UNION ALL
　　 SELECT b.id, b.is_leaf, a.depth - 1
　　　 FROM find a
　　　 JOIN adjacent b ON b.pid = a.id
　　 WHERE a.depth > 0
　　　 AND NOT a.is_leaf
　)

-- 最后一次性 JOIN 第 N 层
SELECT COUNT(*), SUM(b.code), SUM(CHAR_LENGTH(b.name)) -- SQLite 写法：SUM(LENGTH(b.name))
　 FROM find a
CROSS JOIN adjacent b ON a.id = b.pid -- SQLite 要加 CROSS ，否则耗时翻几倍
WHERE a.depth = 0;

-- 理想中，没有递归损耗的邻接表查询（需要根据层数 N ，动态生成 SQL ）
SELECT COUNT(*), SUM(t5.code), SUM(CHAR_LENGTH(t5.name)) -- SQLite 写法：SUM(LENGTH(t5.name))
　 FROM adjacent t1
　 JOIN adjacent t2 ON t2.pid = t1.id
　 JOIN adjacent t3 ON t3.pid = t2.id
　 JOIN adjacent t4 ON t4.pid = t3.id
　 JOIN adjacent t5 ON t5.pid = t4.id
WHERE t1.pid = 0;
```



## `MySQL` 一键建表 `SQL`

*（在我低配笔记本和固态上，大约执行了 1 分钟）*

*（ V 站排版原因，行首有全角空格）*

```sql
-- 允许 200 MB 的内存表
SET max_heap_table_size = 200 << 20;

-- 建临时数据表，装载 csv 数据，以及计算序号和父子关系
CREATE TABLE data (
　　 code 　　 BIGINT 　　　 NOT NULL,
　　 p_code 　 BIGINT 　　　 NOT NULL,
　　 type 　　 TINYINT 　　 NOT NULL,
　　 name 　　 VARCHAR(25) NOT NULL,
　　 id 　　　 INT 　　　　 NOT NULL,
　　 pid 　　 INT 　　　　 NOT NULL,
　　 PRIMARY KEY (code) USING BTREE,
　　 INDEX USING BTREE (id),
　　 INDEX USING BTREE (pid, id)
) ENGINE = MEMORY;

-- 加载 csv
LOAD DATA INFILE 'area_code_2022.csv'
INTO TABLE data
CHARACTER SET UTF8MB4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
(code, name, type, p_code);

-- 按照 code 顺序计算 id
UPDATE data
　 JOIN (SELECT code, ROW_NUMBER() OVER win row_num
　　　　　 FROM data
　　　　 WINDOW win AS (ORDER BY code)) t USING(code)
　 SET id = row_num;

-- 计算 parent_id （不存在的标 0 ）
UPDATE data a
　 LEFT JOIN data b ON b.code = a.p_code
　 SET a.pid = IFNULL(b.id, 0);

-- 建邻接表，并从临时数据表填充数据
CREATE TABLE adjacent (
　　 id 　　　 INT 　　　　 NOT NULL,
　　 pid 　　 INT 　　　　 NOT NULL,
　　 is_leaf BOOL 　　　　 NOT NULL,
　　 type 　　 TINYINT 　　 NOT NULL,
　　 code 　　 BIGINT 　　　 NOT NULL,
　　 name 　　 VARCHAR(25) NOT NULL,
　　 PRIMARY KEY (pid, id)
)
SELECT -1 pid, 0 id, FALSE is_leaf, 0 type, 0 code, '' name
UNION ALL
SELECT pid, id, type = 5 is_leaf, type, code, name
　 FROM data;

-- 建闭包表主表，并从临时数据表填充数据
CREATE TABLE closure (
　　 id 　　　 INT 　　　　 NOT NULL,
　　 type 　　 TINYINT 　　 NOT NULL,
　　 code 　　 BIGINT 　　　 NOT NULL,
　　 name 　　 VARCHAR(25) NOT NULL,
　　 PRIMARY KEY (id)
)
SELECT 0 id, 0 type, 0 code, '' name
UNION ALL
SELECT id, type, code, name
　 FROM data;

-- 建闭包表树形关系表
CREATE TABLE closure_tree (
　　 ancestor 　　 INT 　　 NOT NULL,
　　 descendant 　 INT 　　 NOT NULL,
　　 distance 　　 TINYINT NOT NULL,
　　 PRIMARY KEY (descendant, distance)
);

-- 递归构建树形关系
INSERT INTO closure_tree(ancestor, descendant, distance)
WITH RECURSIVE
　 parent_of(orig_id, id, dist) AS (
　　 SELECT id, id, 0
　　　 FROM data
　　 UNION ALL
　　 SELECT orig_id, pid, dist + 1
　　　 FROM parent_of
　　　 JOIN data USING(id)
　　 WHERE id
　)
SELECT id, orig_id, dist
　 FROM parent_of;

-- 为闭包表树形关系表建二级索引
CREATE INDEX idx_closure_tree ON closure_tree (ancestor, distance);

-- 丢弃临时数据表
DROP TABLE data;
```



## `SQLite` 一键建表 `SQL`

下列 `SQL` 需要依赖 `SQLite Shell` 的 `.import --csv`，但核心 `SQLite` 库不提供此功能。

因此，需要使用命令行的 `SQLite` 来运行*（`Windows` 可去官网下载个 1~2 MB 的 `sqlite3.exe`）*。

下面使用 `Bash Shell` 来包装执行命令与 `SQL`，大约需要运行 30 秒，然后在同目录下生成 150 MB 左右的 `test.db`。

*（ V 站排版原因，行首有全角空格）*

```sql
#!/bin/bash

sqlite3 :memory: <<'EOF'

-- 在内存中计算，最后整理紧凑才写入文件
PRAGMA TEMP_STORE = MEMORY;

-- 导入 csv 文件至临时表
CREATE TEMP TABLE csv (code INTEGER PRIMARY KEY, name TEXT, type INT, p_code INT);
.import --csv area_code_2022.csv csv

-- 建邻接表
CREATE TABLE adjacent (
　　 id 　　　 INT 　　 NOT NULL,
　　 pid 　　 INT 　　 NOT NULL,
　　 is_leaf INT 　　 NOT NULL,
　　 type 　　 INT 　　 NOT NULL,
　　 code 　　 INT 　　 NOT NULL,
　　 name 　　 TEXT 　　 NOT NULL,
　　 PRIMARY KEY (pid, id)
) WITHOUT ROWID;

-- 填充邻接表
INSERT INTO adjacent (pid, id, is_leaf, type, code, name)
SELECT -1, 0, FALSE, 0, 0, ""
UNION ALL
SELECT p_code, ROW_NUMBER() OVER (), type = 5, type, code, name
　 FROM csv
ORDER BY code;

-- 建临时索引，提速 code 搜索
CREATE INDEX i ON adjacent (code);

-- 更新 pid
UPDATE adjacent
　 SET pid = t2.id
　 FROM adjacent t2
WHERE adjacent.pid = t2.code;

-- 丢弃临时索引
DROP INDEX i;

-- 建 id -> pid 索引
CREATE INDEX idx_adjacent_id ON adjacent (id);

-- 建闭包表主表
CREATE TABLE closure (
　　 id 　　　 INTEGER PRIMARY KEY,
　　 type 　　 INT 　　 NOT NULL,
　　 code 　　 INT 　　 NOT NULL,
　　 name 　　 TEXT 　　 NOT NULL
);

-- 建闭包表树形关系表
CREATE TABLE closure_tree (
　　 ancestor 　　 INT NOT NULL,
　　 descendant 　 INT NOT NULL,
　　 distance 　　 INT NOT NULL,
　　 PRIMARY KEY (descendant, distance)
) WITHOUT ROWID;

-- 填充闭包表主表
INSERT INTO closure (id, type, code, name)
SELECT id, type, code, name
　 FROM adjacent;

-- 递归构建树形关系
WITH RECURSIVE
　 parent_of(orig_id, id, dist) AS (
　　 SELECT id, id, 0
　　　 FROM adjacent
　　 UNION ALL
　　 SELECT orig_id, pid, dist + 1
　　　 FROM parent_of
　　　 JOIN adjacent USING(id)
　　 WHERE id
　)
INSERT INTO closure_tree (ancestor, descendant, distance)
SELECT id, orig_id, dist
　 FROM parent_of;

-- 为闭包表树形关系表建二级索引
CREATE INDEX idx_closure_tree ON closure_tree (ancestor, distance);

-- 整理紧实数据库后，写入磁盘
ANALYZE;
VACUUM INTO 'test.db';

EOF
```