@
ys1992 #10 不一定要扫全表吧。。
不断根据索引,查最接近 24 小时前的推送时间,应该只需要检查很少数据量,就能算出来了?
@
Zaden 我用最垃圾的 SQLite ,在七年前的 i5-8250U 轻薄本上,效率一般的浏览器 wasm 环境里,试了下,
100 设备、一亿数据(每分钟推送、持续两年),每设备断线十次,每次 1~2 天,
只需 7 秒,就能全找出来了?
## 截图
## SQL 测试代码
```sql
-- V 站吞空格,缩进改成全角空格了
-- 建表,当 (point_id, push_time) 索引用
DROP TABLE IF EXISTS data;
CREATE TABLE data (
point_id INT,
push_time INT,
PRIMARY KEY (point_id, push_time)
) WITHOUT ROWID;
-- 添加一亿条数据( 100 设备、每分钟推送、持续两年)
INSERT INTO data
SELECT point.value, time.value
FROM generate_series(1, 100) point
JOIN generate_series(
unixepoch('2024-05-24', '-2 year'),
unixepoch('2024-05-24'), 60) time;
-- 删掉断线数据( 100 设备,每台断线 10 次,第 N 次是 id*N 天前,持续 1, 1.1, ..., 1.9 天)
DELETE FROM data
WHERE (point_id, push_time) IN (
SELECT point_id, push_time
FROM generate_series(1, 100) point
JOIN generate_series(1, 10) nth
CROSS JOIN data
WHERE point_id = point.value
AND push_time >= unixepoch('2024-05-24', format('-%f day', nth.value * (point.value + 0.1) - 0.1))
AND push_time < unixepoch('2024-05-24', format('-%f day', nth.value * point.value - 1))
);
-- 循环每个设备,从今天开始,不断往前找,最接近 24 小时前的推送时间
-- 若俩时间 >= 24 小时,则属于断线过久
WITH RECURSIVE
t(id, a, b) AS (
SELECT point_id, unixepoch('2024-05-24'), NULL
FROM data
GROUP BY point_id
UNION ALL
SELECT id, ifnull((
SELECT min(push_time)
FROM data
WHERE point_id =
t.id AND push_time > t.a - 86400
AND push_time < t.a
), (
SELECT max(push_time)
FROM data
WHERE point_id =
t.id AND push_time < t.a - 86400
)), a
FROM t
WHERE a
)
SELECT
id "设备 ID",
datetime(a, 'auto') "最后在线",
format('%d 天 %d 小时 %d 分钟', (b-a)/86400, (b-a)%86400/3600, (b-a)%3600/60) "断线时长"
FROM t
WHERE b - a >= 86400
ORDER BY id IN (1, 2, 73) DESC, id, a DESC;
```