查询以提供所有产品最近两个日期之间的差异

Question

我有一个数据库，看起来像这样:

person | product    |    purchase_time
-------+------------+---------------------
jim    | football   | 2013-02-28 00:01:00
jane   | basketball | 2013-02-28 01:01:00
ben    | baseball   | 2013-02-28 02:02:00
jim    | basketball | 2013-02-28 00:01:00
jim    | basketball | 2013-02-28 00:03:00
jim    | football   | 2013-02-28 00:04:00
jane   | baseball   | 2013-02-28 00:05:00
jane   | football   | 2013-02-28 00:08:00
jane   | basketball | 2013-02-28 01:03:00
ben    | baseball   | 2013-02-28 03:02:00

我需要运行一个报告，它将告诉我"人"购买任意两种产品的最近两个日期之间的差异(以秒为单位)。

所以，例如，如果我用football和basketballs，我需要知道这样的东西:

 jim  | 60
 jane | 3180

结果来自jim最近的足球和篮球购买间隔60秒(分别在04:00和03:00)，而jane的足球和篮球购买间隔3180秒(分别在08:00和01:03)。

我可以在一个人对一个人的基础上做这个循环，我可以使用这样的查询:

     SELECT a.person,
       abs(DATE_PART('epoch', a.purchase_time - b.purchase_time)
FROM
  (SELECT *
   FROM mytable
   WHERE person = 'jim'
     AND product = 'football'
   ORDER BY purchase_time LIMIT 1) a,
  (SELECT *
   FROM mytable
   WHERE person = 'jim'
     AND product = 'basketball'
   ORDER BY purchase_time LIMIT 1) b

但是为了看到每个人的结果，我必须在一个循环中执行这个查询。

我肯定有更聪明的方法来做这件事，我只是不知道是什么。

谁能给点建议?我使用的是Postgres 8.1.23.

Answer 1

这是你想要的吗?

select person, abs(DATE_PART('epoch', p1 - p2) 
from (select person,
             MAX(case when product = 'football' then purchase_time end) as p1,
             MAX(case when product = 'basketball' then purchase_time end) as p2
      from mytable
      group by person
     ) p
where p1 is not null and p2 is not null

我将问题解释为最近一次购买产品A和最近一次购买产品B，而不是最近一次购买这两种产品(例如都可能是产品A)。