@deweixu @Features 这个 ROI 计算公式是啥？


1. 某天的 ROI = 该天所有新增用户，从当天到今天的总消费 / 该天广告花费？

每天都要投广告吗？没投广告的，岂不是 / 0 了？

而且，好像看楼主的计算结果，不是这样。。


2. 指定统计起始日期（如 11-02 ），某天的 ROI = 起始日期~当天，所有新增用户的总消费 / 起始日期~当天，所有广告总花费？

有没有啥表结构和数据？数据库新手想试试，能不能用 SQL 解决

@shade 这种做法。。不就是枚举路径？

@mmm159357456 像 #48 楼、#52 、#60 那样，编个数据不就好了。。

只要给出的解决方案也能通用到你原始数据上，目的不就达到了。。

如果是要获取所有后代，我倒觉得枚举路径和嵌套集会比这俩更高效

检索目录？全文索引？

@sadhen 硬编码图像和视频？？

@sadhen 可以再多写写应用场景吗？还能在哪些方面解决现有编程语言的什么痛点？

@mmm159357456 我好奇问一下，为啥你不愿意放出原始问题呢？

不怕问成 xy problem ，束缚大家的看问题的角度和解决思路么。。

- 比如，有回 换 Python 其他写法、上协程 /多线程 /多进程、升级 Python 、换 C/C++/Rust 提升三次 for 速度的，

- 有剔除重复数据、剪枝来减少 for 数量空间的

- 还有零星几个回复是改变 pandas 运算方法，改变数据存储结构使得能顺序读取的


万一，根本不用三次 for 呢？（比如，如果真的只是计算滑窗数据的话，真的不用三层 for 。另外，个人觉得，既然你用了 pandas ，就该少让 python 掺和进来，多用 pandas 的方法去整体计算 dataframe ）

万一，换种数据存储结构，就能高效读取数据和计算呢？（比如，不用随机读，减少 groupby 、sort 、join 了）

万一，有数学大佬能推出个啥神奇公式，能 O(1) 解决问题了呢？😂

@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
...