SQLite按时间分组

Iffy 发表于 Dev

菲菲

我有一个这样的SQLite表：

sqlite> select * from things order by id;
id          created         
----------  ----------------
1           2458171.46967479
2           2458171.46967747
3           2458171.46968049
4           2458171.46968362
5           2458171.46968647
6           2458171.46968948
7           2458171.46969561
8           2458171.46973709
9           2458171.46974006
10          2458171.46974368
11          2458171.46978387

created是Julianday时间戳。我想选择彼此几乎同时记录的最近一组行。“大约在同一时间”类似于“彼此之间在100ms之内”。

我知道如何将整个表分成谨慎的存储桶，但是我想要的东西有所不同。例如（与上表不匹配），假设最近的记录的时间戳为0。下一个最近的记录的时间戳为+75，最近的第三记录的时间戳为+160。

换句话说：

id     tstamp
------ -------
156    0
155    75
154    160

如果我的阈值为50，则仅应返回＃156。
如果我的阈值为75，则应同时返回＃156和＃155。
如果我的阈值为85，则应返回所有三个，因为160在下一个最近行的85以内。

有关如何进行的任何建议？我可以在应用程序代码中做到这一点，但如果可以的话，在SQL中它将更快。我怀疑我可能需要做些什么WITH RECURISIVE？

菲菲

一堆阅读WITH RECURSIVE文档后：https : //www.sqlite.org/lang_with.html

这是我的解决方案：

WITH RECURSIVE
    what(x) AS (
        SELECT max(created) FROM things
        UNION
        SELECT things.created FROM what, things
        WHERE things.created >= (what.x - 85)
    )
SELECT x FROM what ORDER BY 1;

这是一些示例查询，表明它有效：

sqlite> select * from things;
id          created   
----------  ----------
1           160       
2           85        
3           0         

sqlite> WITH RECURSIVE
   ...>     what(x) AS (
   ...>         SELECT max(created) FROM things
   ...>         UNION
   ...>         SELECT things.created FROM what, things
   ...>         WHERE things.created >= (what.x - 50)
   ...>     )
   ...> SELECT x FROM what ORDER BY 1;
x         
----------
160       

sqlite> WITH RECURSIVE
   ...>     what(x) AS (
   ...>         SELECT max(created) FROM things
   ...>         UNION
   ...>         SELECT things.created FROM what, things
   ...>         WHERE things.created >= (what.x - 75)
   ...>     )
   ...> SELECT x FROM what ORDER BY 1;
x         
----------
85        
160       

sqlite> WITH RECURSIVE
   ...>     what(x) AS (
   ...>         SELECT max(created) FROM things
   ...>         UNION
   ...>         SELECT things.created FROM what, things
   ...>         WHERE things.created >= (what.x - 85)
   ...>     )
   ...> SELECT x FROM what ORDER BY 1;
x         
----------
0         
85        
160

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。