在我的SQLite表我有以下字段（timestamp，zone_id，object_count和object_ids）和这些人在区排队的数据。每当一个人加入队列时，他都会被传感器捕获并被分配一个object_id. timestamp是每秒从传感器服务器发送到数据库的数据。
我需要找到object_ids队列中每个的持续时间，以使用 Flask 框架在我的 Web 应用程序中计算和显示等待时间。例如，object_ids（2166）在 进入队列，在2020-10-19 17:03:46.000000离开2020-10-19 17:03:50.000000，他的排队时间为 5 秒。简单的逻辑是object_id从object_id最后一个出现的时间戳中减去第一个出现的时间戳。
# timestamp zone_id object_count object_ids #
2020-10-19 17:03:43.000000 10 2 ['2140', '2143']
2020-10-19 17:03:44.000000 10 2 ['2140', '2143']
2020-10-19 17:03:45.000000 10 2 ['2140', '2143']
2020-10-19 17:03:46.000000 10 3 ['2140', '2143', '2166']
2020-10-19 17:03:47.000000 10 3 ['2140', '2143', '2166']
2020-10-19 17:03:49.000000 10 3 ['2140', '2143', '2166']
2020-10-19 17:03:50.000000 10 3 ['2140', '2143', '2166']
2020-10-19 17:03:51.000000 10 2 ['2140', '2143']
2020-10-19 17:03:53.000000 10 2 ['2140', '2143']
我该如何查询和解决这个问题？
假设您的表的名称是定义 squeues的表object_id是objects（将名称更改为实际名称），因此您有如下内容：
CREATE table objects (`object_id` TEXT);
INSERT INTO objects (`object_id`) VALUES
('2140'), ('2143'), ('2166');
CREATE TABLE queues (`timestamp` TEXT, `zone_id` INTEGER, `object_count` INTEGER, `object_ids` VARCHAR(24));
INSERT INTO queues (`timestamp`, `zone_id`, `object_count`, `object_ids`) VALUES
('2020-10-19 17:03:43.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:44.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:45.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:46.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:47.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:49.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:50.000000', '10', '3', '[''2140'', ''2143'', ''2166'']'),
('2020-10-19 17:03:51.000000', '10', '2', '[''2140'', ''2143'']'),
('2020-10-19 17:03:53.000000', '10', '2', '[''2140'', ''2143'']');
你可以得到的结果要与一个SQLite查询加入2个表，利用窗口函数MIN()与MAX()和函数strftime()：
SELECT DISTINCT o.object_id,
strftime('%s', MAX(q.timestamp) OVER (PARTITION BY o.object_id)) -
strftime('%s', MIN(q.timestamp) OVER (PARTITION BY o.object_id)) diff
FROM objects o INNER JOIN queues q
ON ',' || REPLACE(REPLACE(REPLACE(REPLACE(q.object_ids, '''', ''), '[', ''), ']', ''), ' ', '') || ','
LIKE '%,' || o.object_id || ',%'
如果您的 SQLite 版本不支持窗口函数，请使用GROUP BY：
SELECT o.object_id,
strftime('%s', MAX(q.timestamp)) - strftime('%s', MIN(q.timestamp)) diff
FROM objects o INNER JOIN queues q
ON ',' || REPLACE(REPLACE(REPLACE(REPLACE(q.object_ids, '''', ''), '[', ''), ']', ''), ' ', '') || ','
LIKE '%,' || o.object_id || ',%'
GROUP BY o.object_id
请参阅演示。
结果：
> object_id | diff
> :-------- | ---:
> 2140 | 10
> 2143 | 10
> 2166 | 4
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句