Sqlite子查询Epoch的比较

Question

我试图捕获时间戳在ts和nextts之间的下一行。我有以Epoch的形式带有ts和next的列。

id,ts,tsepoch,origin,product,bid,ask,nextts,nexttsepoch
1,2016-10-18  20:20:54.733,1476822054733,sourceA,EUR/USD,1.09812,1.0982,2016-10-18  20:20:59.579,1476822059579
2,2016-10-18  20:20:55.093,1476822055093,sourceA,UK 100,7010.5,7011.5,2016-10-18  20:20:57.972,1476822057972
3,2016-10-18  20:20:55.149,1476822055149,sourceA,US 30,18159.0,18161.0,2016-10-18  20:20:55.871,1476822055871
4,2016-10-18  20:20:55.871,1476822055871,sourceA,US 30,18159.0,18161.0,2016-10-18  20:20:57.256,1476822057256
5,2016-10-18  20:20:57.256,1476822057256,sourceA,US 30,18160.0,18162.0,2016-10-18  20:20:58.745,1476822058745

为什么下面的查询返回null?

select _rowid_, ts, nextts, origin, bid, ask, 
(select sub.bid from end as sub where sub.origin <> origin and sub.product = product
and sub.tsepoch > tsepoch and sub.tsepoch < nexttsepoch ) as bid
from test
order by ts;

我不明白为什么两个整数列的比较没有返回任何结果。当我只是试图返回任何值，其中时间戳大于当前一个，它返回null也。

select _rowid_, ts, nextts, origin, bid, ask, 
(select sub.bid from end as sub where sub.ts > ts) as bid
from end where product = "UK 100" 
order by ts;

我有正确的数据库结构吗?ts和nextts是datetime，而nextsepoch是NUMERIC。

Answer 1

问题出在这个子查询。

select sub.bid
from end as sub
where sub.origin <> origin  and
      sub.product = product and
      sub.tsepoch > tsepoch and
      sub.tsepoch < nexttsepoch

将每一行与自身进行比较。没有连接表所以origin和sub.origin都是end.origin。就像写:

select bid
from end
where origin <> origin  and
      product = product and
      tsepoch > tsepoch and
      tsepoch < nexttsepoch

相反，您可能需要一个自连接来比较end的列与end的其他列。

select end1.bid
from end as end1
join end as end2 on end1.id <> end2.id
where end1.origin <> end2.origin  and
      end1.product = end2.product and
      end1.tsepoch > end2.tsepoch and
      end1.tsepoch < end2.nexttsepoch

没有返回任何结果，因为它们都具有相同的起源，并且where子句过滤掉了所有结果。