SQLite:计算计数器在当前一天和一周内的增加量

Question

我有一个SQLite数据库，它在unixtime中有一个计数器和时间戳，如下所示：

+---------+------------+
| counter | timestamp  |
+---------+------------+
|         | 1582933500 |
|    1    |            |
+---------+------------+
|    2    | 1582933800 |
+---------+------------+
|   ...   |     ...    |
+---------+------------+

我想计算一下"计数器"在当天和本周的增长情况。

这在SQLite查询中是可能的吗？

谢谢！

Answer 1

如果您的SQLite版本>=3.25.0，SQLite窗口函数将帮助您实现这一点。

使用LAG函数从上一条记录中检索值-如果没有(第一行将是这种情况(，则会提供

默认值为了演示此代码：

SELECT counter, timestamp,
LAG (timestamp, 1, timestamp) OVER (ORDER BY counter) AS previous_timestamp,
(timestamp - LAG (timestamp, 1, timestamp) OVER (ORDER BY counter)) AS diff
FROM your_table
ORDER BY counter ASC

将给出以下结果：

1   1582933500  1582933500  0
2   1582933800  1582933500  300

Answer 2

在CTE中，获取每天的最小和最大时间戳，并将其加入表中两次：

with cte as (
select date(timestamp, 'unixepoch', 'localtime') day,
min(timestamp) mindate, max(timestamp) maxdate
from tablename
group by day
)
select c.day, t2.counter - t1.counter difference
from cte c
inner join tablename t1 on t1.timestamp = c.mindate
inner join tablename t2 on t2.timestamp = c.maxdate;

使用类似的代码获得每周的结果：

with cte as (
select strftime('%W', date(timestamp, 'unixepoch', 'localtime')) week,
min(timestamp) mindate, max(timestamp) maxdate
from tablename
group by week
)
select c.week, t2.counter - t1.counter difference
from cte c
inner join tablename t1 on t1.timestamp = c.mindate
inner join tablename t2 on t2.timestamp = c.maxdate;