python - 使用时间戳计算总持续时间

Question

在我的 SQLite 表中，我有以下字段（timestamp、和） zone_id，这些是人们在区域中排队的数据。每当有人加入队列时，他就会被传感器捕获并分配一个. 是每秒从传感器服务器发送到数据库的数据。object_countobject_idsobject_idtimestamp

我需要找到object_ids队列中每个的持续时间，以使用烧瓶框架计算和显示我的 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']

我该如何查询和解决这个问题？

标签： pythonsqlite

Answer 1

假设您的表的名称是queues并且object_id定义了 s 的表是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 查询获得所需的结果，方法是使用窗口函数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