SQLite 中查询结果行之间的时差:如何? [英] Time Difference between query result rows in SQLite: How To?

问题描述

考虑以下 reviews 表格内容:

CustomerName ReviewDTDoe，约翰 2011-06-20 10:13:24Doe，约翰 2011-06-20 10:54:45Doe，约翰 2011-06-20 11:36:34Doe,Janie 2011-06-20 05:15:12

结果按ReviewDT排序，按CustomerName分组，如:

SELECT顾客姓名，评论DT从评论在哪里客户名称不为空ORDER BY CustomerName ASC, ReviewDT ASC;

我想为每个客户创建此查询的每一行之间的时间差列...... rowid 给出原始行，并且没有模式包含来自 rowid 等......

对于 CustomerName 的第一个条目，该值将是 0.我在这里询问是否可以将其计算为原始查询的一部分.如果没有，我打算通过一系列查询来做到这一点 - 最初创建一个新的 TABLE 选择上面查询的结果 - 然后 ALTERING 添加新列并使用 UPDATE/strftime 通过使用 rowid-1 来获取时间差异(不知何故)...

解决方案

要计算从一个 ReviewDT 行到下一行经过的秒数:

 SELECT q.CustomerName, q.ReviewDT,strftime('%s',q.ReviewDT)- strftime('%s',coalesce((从评论中选择 r.ReviewDT 作为 r其中 r.CustomerName = q.CustomerName和 r.ReviewDT 

要获取每个 ReviewDT 及其前面的 CustomerName 行的 DT:

SELECT q.CustomerName, q.ReviewDT,合并((从评论中选择 r.ReviewDT 作为 r其中 r.CustomerName = q.CustomerName和 r.ReviewDT 

Consider the following reviews table contents:

CustomerName    ReviewDT
Doe,John        2011-06-20 10:13:24
Doe,John        2011-06-20 10:54:45
Doe,John        2011-06-20 11:36:34
Doe,Janie       2011-06-20 05:15:12

The results are ordered by ReviewDT and grouped by CustomerName, such as:

SELECT
  CustomerName,
  ReviewDT
FROM
  Reviews
WHERE
  CustomerName NOT NULL
ORDER BY CustomerName ASC, ReviewDT ASC;

I'd like to create a column of the time difference between each row of this query for each Customer... rowid gives the original row, and there is no pattern to the inclusion from the rowid etc...

For the 1st entry for a CustomerName, the value would be 0. I am asking here incase this is something that can be calculated as part of the original query somehow. If not, I was planning to do this by a series of queries - initially creating a new TABLE selecting the results of the query above - then ALTERING to add the new column and using UPDATE/strftime to get the time differences by using rowid-1 (somehow)...

解决方案

To compute the seconds elapsed from one ReviewDT row to the next:

 SELECT q.CustomerName, q.ReviewDT,
   strftime('%s',q.ReviewDT) 
   - strftime('%s',coalesce((select r.ReviewDT from Reviews as r
                       where r.CustomerName = q.CustomerName
                       and r.ReviewDT < q.ReviewDT
                       order by r.ReviewDT DESC limit 1), 
                       q.ReviewDT))
  FROM Reviews as q WHERE q.CustomerName NOT NULL 
  ORDER BY q.CustomerName ASC, q.ReviewDT ASC;

To get the DT of each ReviewDT and its preceding CustomerName row:

SELECT q.CustomerName, q.ReviewDT,
  coalesce((select r.ReviewDT from Reviews as r
                      where r.CustomerName = q.CustomerName
                      and r.ReviewDT < q.ReviewDT
                      order by r.ReviewDT DESC limit 1), 
                      q.ReviewDT)
 FROM Reviews as q WHERE q.CustomerName NOT NULL 
 ORDER BY q.CustomerName ASC, q.ReviewDT ASC;

这篇关于SQLite 中查询结果行之间的时差:如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！