sql - 获取配对时间差的总和

Question

我有一张卡控制入口门使用情况的 sqlite 表。

我需要奇偶时间间隔的总和，例如卡 02 的预期总和是 (12:03 - 08:07) + (16:03 - 14:52) = 03:56 + 01:21 = 05:07

人们在白天可以自由事件，所以一张卡可以有很多条目。

Test table

该表如下所示:

card_id | time
----------------------------
   03   | 07:55
   01   | 08:02
   02   | 08:07
   03   | 11:56
   02   | 12:03
   03   | 12:23
   02   | 14:52
   03   | 15:56
   01   | 15:58
   02   | 16:03

Answer 1

查询并不难，但时间间隔处理是!

SELECT card_id,
    TIME(1721059.5+SUM(JULIANDAY(time)*((SELECT COUNT() FROM t AS _ WHERE card_id=t.card_id AND time<t.time)%2*2-1)))
FROM t
GROUP BY card_id;

这个怎么运作:
((SELECT COUNT() FROM t AS _ WHERE card_id=t.card_id AND time<t.time)%2*2-1)计算当前记录之前的所有记录并返回 -1或 1 .
JULIANDAY(time)将时间字符串转换为数字量。前两个结果的乘积将返回所需的计算(以天为单位)。
TIME(1721059.5+...)将返回格式正确的时间字符串。 1721059.5是必需的，因为 JULIANDAY定义和 SQLite date/time functions仅对 0000-01-01 00:00:00 之后的日期有效.

编辑:查看@CL 答案，我看到使用 JULIANDAY('00:00')比 1721059.5 更优雅.但是，我保持不变，因为它应该比使用函数调用更好。