代码之家 › 专栏 › 技术社区 › Tam Bui

q:sqlite查询以在数据中查找孤岛(即,值满足某个阈值的连续行)

gaps-and-islands window-functions sqlite

0

Tam Bui · 技术社区 · 6 年前

我有一个sqlite数据库,比如说,随着时间的推移,在一个癌变的生物体中发现的死亡细胞的百分比(注:为了可读性,时间列的值被改为简单的数字)。

id  time                deadcellspercent
1   000000001000000000  35
2   000000002000000000  54
3   000000003000000000  31
4   000000004000000000  15
5   000000005000000000  38
6   000000006000000000  70
7   000000007000000000  28
8   000000008000000000  13
9   000000009000000000  99
10  000000010000000000  51

我想创建一个sqlite查询,它将返回百分比超过某个阈值的时间范围。例如,如果我说希望阈值为>=20,则查询结果应返回:

ts_start            ts_end
000000001000000000  000000003000000000
000000005000000000  000000007000000000
000000009000000000  000000010000000000

如何形成查询来执行此操作?我读过诸如“sqlite窗口函数”、“间隙和孤岛问题”和“分析函数”之类的主题,但我是一个SQL新手,无法对其进行正面或反面的分析以获得所需的结果。

任何帮助都将不胜感激。

1 回复 | 直到 6 年前

1

Shawn 6 年前

你在正确的轨道上与窗口功能,间隙和岛屿。

首先,让我们获取示例数据并用它填充一个表:

CREATE TABLE cells(id INTEGER PRIMARY KEY, time TEXT, deadcellspercent INTEGER);
INSERT INTO cells VALUES(1,'000000001000000000',35);
INSERT INTO cells VALUES(2,'000000002000000000',54);
INSERT INTO cells VALUES(3,'000000003000000000',31);
INSERT INTO cells VALUES(4,'000000004000000000',15);
INSERT INTO cells VALUES(5,'000000005000000000',38);
INSERT INTO cells VALUES(6,'000000006000000000',70);
INSERT INTO cells VALUES(7,'000000007000000000',28);
INSERT INTO cells VALUES(8,'000000008000000000',13);
INSERT INTO cells VALUES(9,'000000009000000000',99);
INSERT INTO cells VALUES(10,'000000010000000000',51);

一个可能的查询(使用窗口函数,因此需要最新版本的sqlite-3.25或更高版本):

WITH islands AS (SELECT id, time
                      , row_number() OVER w1 - row_number() OVER w2 AS diff
                      , deadcellspercent >= 20 AS wanted
                 FROM cells
                 WINDOW w1 AS (ORDER BY time)
                      , w2 AS (PARTITION BY deadcellspercent >= 20 ORDER BY time))
SELECT min(time) AS ts_start, max(time) AS ts_end
FROM islands
WHERE wanted = 1
GROUP BY diff
ORDER BY diff;

生产:

ts_start            ts_end            
------------------  ------------------
000000001000000000  000000003000000000
000000005000000000  000000007000000000
000000009000000000  000000010000000000

(严重受 this post 在DBA StackExchange上;请参阅以获取解释)。