@la0wei #17

我也不知道你实际执行的 SQL 是啥，不太好判断延迟从何而来。。

# 1. 你不需要切换到 SQLite

我用 SQLite 是出于演示目的，而且本身也比较便捷。

另外，其功能少，也容易改造成其他数据库的 SQL 。

你可以参考 16 楼的 SQL 里的思想 *（扫一遍，就算出 6 个字段的结果）* ，稍加改造，应用到 MySQL 上。

*（虽然我认为，单机非并发写场景，SQLite 会比 MySQL 快。。）*

# 2. 16 楼的 SQL 对于机械硬盘都还算友好，不需要用内存数据库

我用内存数据库，主要是因为建表时，有大量随机写入。真的写到硬盘上，太慢了。

实际你的数据应该有很多是顺序写入的 *（你的日期是自增的）*。

另外，在计算时，实际是顺序读取了 2000 次、三个月内的覆盖索引记录。

所以，对于机械硬盘都还算友好，根本不需要用到内存数据库。

从这方面说，你的索引应该建成：

```sql
CREATE INDEX idx ON data(devicecode, date, "其他有可能用到的字段，防止回表")
```

@la0wei #15 装个[油猴插件]( https://greasyfork.org/zh-CN/scripts/449771-v2ex 评论 markdown 支持)，就能渲染 markdown 内容了。

## 1. 为什么你的 network 时间这么长？

我认为，主要是你把数据库里的内容，全部传输到 Python ，导致长时间耗时在数据传输上。

所以，让数据库自己计算出最终结果，再保存成 csv ，应该能节省很多时间。

*（除非数据库计算性能太差，还不如传数据让 Python 来）*

## 2. SQLite 可在 30 秒内，处理一亿数据，生成所需 CSV （ 9 字段，2000 行）

### 2.1 说明

我用 SQLite 进行了下测试。预先生成**一亿**行随机数据。各字段取值范围：

- **devicecode**：[1000001, 1002000]
- **date**：[2023-01-01 00:00:00, 2023-06-25 00:00:00]
- **code**：[9520, 9529]
- **status**：[1, 4]

然后假设 CSV 的设备编号是 1000001 ~ 1002000 ，让 SQLite 计算如下字段的值：

- 设备编号
- 3 个月内数据量
- 3 个月内上传量
- 1 个月内数据量
- 1 个月内上传量
- 1 个月内 9527 数据量
- 1 个月内 9527 上传量
- 3 个月内最新数据
- 3 个月内最新上传数据

### 2.2 结果

```
[ 0.001s] 开始建表……
[319.384s] 建表完成。正在查询……
[347.006s] 查询完毕！前后五行结果：
(1000001, 26306, 6608, 8786, 2222, 827, 199, '{"业务":9524,"时间":"2023-06-24 23:59:27"}', '{"业务":9524,"时间":"2023-06-24 23:59:27"}')
(1000002, 26351, 6651, 8703, 2201, 854, 228, '{"业务":9524,"时间":"2023-06-24 23:58:17"}', '{"业务":9524,"时间":"2023-06-24 23:58:17"}')
(1000003, 26297, 6655, 8755, 2225, 891, 218, '{"业务":9529,"时间":"2023-06-24 23:47:57"}', '{"业务":9527,"时间":"2023-06-24 23:37:26"}')
(1000004, 26502, 6576, 8812, 2208, 901, 239, '{"业务":9521,"时间":"2023-06-24 23:53:41"}', '{"业务":9521,"时间":"2023-06-24 23:53:41"}')
(1000005, 26225, 6520, 8766, 2128, 902, 219, '{"业务":9527,"时间":"2023-06-24 23:49:21"}', '{"业务":9524,"时间":"2023-06-24 23:19:29"}')
……
(1001996, 26328, 6663, 8853, 2282, 899, 251, '{"业务":9521,"时间":"2023-06-24 23:51:59"}', '{"业务":9528,"时间":"2023-06-24 23:38:47"}')
(1001997, 26186, 6633, 8699, 2234, 914, 242, '{"业务":9527,"时间":"2023-06-24 23:57:44"}', '{"业务":9521,"时间":"2023-06-24 23:31:51"}')
(1001998, 25887, 6418, 8727, 2111, 897, 220, '{"业务":9521,"时间":"2023-06-24 23:46:42"}', '{"业务":9521,"时间":"2023-06-24 23:46:42"}')
(1001999, 26192, 6397, 8686, 2108, 859, 201, '{"业务":9521,"时间":"2023-06-24 23:57:37"}', '{"业务":9529,"时间":"2023-06-24 23:05:04"}')
(1002000, 26070, 6470, 8841, 2166, 857, 220, '{"业务":9528,"时间":"2023-06-24 23:59:51"}', '{"业务":9523,"时间":"2023-06-24 23:21:12"}')
```

### 2.3 Python 代码

运行下面代码大约需要 2.5 GB 内存*（因为直接在内存里建表了）*。

由于 v 站 会吃掉行首空格，所以我替换成了全角空格。若要运行，记得替换回来。

```python
import time
import sqlite3

start_time = time.time()
def debug(s):
　　 print(f'[{time.time() - start_time:7.3f}s] {s}')


db = sqlite3.connect(':memory:')

db.execute('''
CREATE TABLE data(
　　 id INT,
　　 devicecode INT,
　　 date TIMESTAMP,
　　 code INT,
　　 status INT,
　　 PRIMARY KEY (devicecode, date, id)
) WITHOUT ROWID
''')

debug('开始建表……')
db.execute('''
WITH
　　 num10(num) AS (
　　　　 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
　　),
　　 num10000(num) AS (
　　　　 SELECT a.num * 1000 + b.num * 100 + c.num * 10 + d.num
　　　　 FROM num10 a, num10 b, num10 c, num10 d
　　),
　　 generate_series(value) AS (
　　　　 SELECT a.num * 10000 + b.num
　　　　 FROM num10000 a, num10000 b
　　)
INSERT INTO data
SELECT
　　 value,
　　 abs(random() % 2000) + 1000001,
　　 abs(random() % (SELECT unixepoch('2023-06-25') - unixepoch('2023-01-01'))) + unixepoch('2023-01-01'),
　　 abs(random() % 10) + 9520,
　　 abs(random() % 4) + 1
FROM generate_series
''')

debug('建表完成。正在查询……')
rows = db.execute('''
WITH
　　 csv(设备编号) AS (
　　　　 SELECT DISTINCT devicecode
　　　　 FROM data
　　)
SELECT
　　 devicecode 设备编号,
　　 count(*) "3 个月内数据量",
　　 IFNULL(SUM(status = 1), 0) "3 个月内上传量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month'))), 0) "1 个月内数据量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month')) AND status = 1), 0) "1 个月内上传量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month')) AND code = 9527), 0) "1 个月内 9527 数据量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month')) AND code = 9527 AND status = 1), 0) "1 个月内 9527 上传量",
　　(
　　　　 SELECT json_object('业务', code, '时间', datetime(date, 'unixepoch'))
　　　　 FROM data AS inner
　　　　 WHERE inner.devicecode = data.devicecode
　　　　　 AND inner.date >= (SELECT unixepoch('now', '-3 month'))
　　　　 ORDER BY date DESC
　　　　 LIMIT 1
　　) "3 个月内最新数据",
　　(
　　　　 SELECT json_object('业务', code, '时间', datetime(date, 'unixepoch'))
　　　　 FROM data AS inner
　　　　 WHERE inner.devicecode = data.devicecode
　　　　　 AND inner.date >= (SELECT unixepoch('now', '-3 month'))
　　　　　 AND inner.status = 1
　　　　 ORDER BY date DESC
　　　　 LIMIT 1
　　) "3 个月内最新上传数据"
FROM data
WHERE devicecode IN (SELECT 设备编号 FROM csv)
　 AND date >= unixepoch('now', '-3 month')
GROUP BY 1
''').fetchall()

debug('查询完毕！前后五行结果：')
print(*rows[:5], '……', *rows[-5:], sep='\n')
```

@Syiize 还好吧。看过知乎上有大佬，用嵌入式 Python 环境 + 手工裁剪 PyQT 5 ，最小能打包成 14MB 即可

用 Python 实现的逐字符遍历，可能还真的比不上几次 in 呢。。

可能用正则来干可以。/Andy|Jack|Jim|Mike/，然后匹配到后回调判断

@albin504 请教一下，会不会搞一个《假期表》好一些？

这个表假设所有 [周一, 周五] 是工作日，[周六, 周日] 是周末。

其余不同之处才添加记录至该表。（比如，添加 22 ～ 24 日为节假日，25 日为工作日）

各个地方可根据需要，添加自己的节日（比如广西三月三）。

各个公司有特殊需要，也能添加自己的特殊日子。

员工特殊请假、调休等，也才添加进去。

这样，同一条 SQL ，都能兼顾上述所有情况，算出需要的数据了？

每年只需添加十来条新数据即可？

@la0wei 《等价》表结构也不能吗？

列名替换成 a 、b 、c 、d……，省略无关列 delete_at 等，数据类型统一为 VARCHAR(255)……

CSV 表头也替换成 e 、f 、g 、h……

一切不影响讨论的信息，都可以抹去

@la0wei 要不给出一些等价的表结构？这样方便用 SQL 或者 Python 交流嘛

比如：

数据库：data 表，字段：id INT, a VARCHAR(255), b VARCHAR(255), c VARCHAR(255), ...
CSV：condition_a, condition_b, condition_c, ...

当前 Python 代码：

...

@la0wei 你的原始问题是啥？

有个 CSV ，里面是书籍查询请求？（每一行是一个请求，列是 warehouse 、category 、subcategory 等？）

对于每一个请求，你按要求的过滤条件，去数据库搜索符合的书目数据，保存到独立的 CSV 里？


1. 那你说的“重用上一次查询获取的数据”，意思是想重用 1999 次？

2. 还是说，每个请求，有 6 种或 12 种过滤条件？你想重用 5 次或 11 次？

3. 过滤条件一定是严格的包含关系吗？（一定是 北京仓、北京仓计算机类、北京仓计算机类数据库类 这种层层递进？）

4. 数据库数据量有多少？


目前能想到的几种办法：

1. 每次查询，只查出 id （此时你可用 len(ids) 获取总数），然后再根据 id 查出详细数据。（查过的直接用缓存。如果数据总量不大，甚至可以缓存整个 2000 行 CSV 的结果）

2. 如果 过滤条件是严格的包含关系，可以用 WITH AS + 物化查询？

3. 如果是一次性任务，换用 pandas （要求数据库数据量不大）或其他数据库可能更快（如 SQLite 、DuckDB ，十倍差距都有可能）

@Yuesh1 @albin504 能不能根据过去放假数据，总结规律，预测出未来的假期呢？（假设节日种类及放假调休天数不变）

@la0wei 加个 (warehouse, category, subcategory) 索引，应该能快很多吧？

> 我根据一个 csv 查询相关信息，整个文件跑完要 3 个多小时

你的 CSV 有多少数据呢？而且，MySQL 能直接查询 CSV ？

什么时候 ChatGPT 能写出日赚一万的程序出来？要求一运行就打款到支付宝等。

能做出来了，被替代就被替代呗。

做不出来，怎么指望它能胜任其他任务呢？

@sunmoon1983 你要多少秒内出结果呢？

@7911364440 比如，100W 数据里，只有 1 个城市户籍、1 个男性、1 个 2022 的，那算式是 1 - (100W - 1) - (100W - 1) ？还是咋算的？

@jones2000 我也感觉是，就算一行需要 1KB ，全部放进内存也才不到 1GB ，for 一百万次实际就连 Python 都不会很慢

如果只挑可能用到的字段出来，一百万行可能也只用到几十 MB 内存即可

@yasea @agui2200 能不能抽出可能用到的字段，建个小小的覆盖索引呢？

比如，（行政编码, 性别, 年份, 年龄, 项目 ID ），看起来一行只需 5+10+1+2+1+4+8 = 31 字节，100W 行索引只需 30 MB

如果不指定『当前地区』，扫全部索引也能满足各种查询需求。
如果指定了『当前地区』，可能只需扫几百上千行，几百 KB 的索引即可统计出来。