sql - 返回时差超过阈值的行的查询

Question

这是一个奇怪的问题。我不知道这是否可行。

假设我有下表:

person | product  | trans  | purchase_date
-------+----------+--------+---------------
jim    | square   | aaaa   | 2013-03-04 00:01:00
sarah  | circle   | aaab   | 2013-03-04 00:02:00
john   | square   | aac1   | 2013-03-04 00:03:00
john   | circle   | aac2   | 2013-03-04 00:03:10
jim    | triangle | aad1   | 2013-03-04 00:04:00
jim    | square   | abcd   | 2013-03-04 00:05:00
sarah  | square   | efgh   | 2013-03-04 00:07:00
jim    | circle   | ijkl   | 2013-03-04 00:22:00
sarah  | circle   | mnop   | 2013-03-04 00:24:00
sarah  | square   | qrst   | 2013-03-04 00:26:00
sarah  | circle   | uvwx   | 2013-03-04 00:44:00

我需要知道任何人在正方形和圆形(或圆形和正方形)之间的购买差异何时超过 10 分钟。理想情况下，我也想知道这种差异，但这不是必需的。

因此，这是我需要的:

person | product  | trans  | purchase_date
-------+----------+--------+---------------
jim    | square   | abcd   | 2013-03-04 00:05:00
jim    | circle   | ijkl   | 2013-03-04 00:22:00
sarah  | square   | efgh   | 2013-03-04 00:07:00
sarah  | circle   | mnop   | 2013-03-04 00:24:00
sarah  | square   | qrst   | 2013-03-04 00:26:00
sarah  | circle   | uvwx   | 2013-03-04 00:44:00

这将每天运行，因此我将添加一个“where”子句以确保查询不会失控。另外，我知道可能会出现多个交易(比如购买一个圆圈之间有 20 分钟，然后是 20 分钟购买一个正方形，然后再次购买 20 分钟购买一个圆圈，这意味着有 2 个时差超过 10 分钟)。

有什么建议吗？我在 postgres 8.1.23

Answer 1

现代解决方案

对于现代 Postgres(8.4 或更高版本)，您可以使用 window function row_number()获得每组的连续编号。然后你可以左连接到上一行和下一行，看看它们中的任何一个是否符合条件。瞧。

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY person ORDER BY purchase_date) AS rn
   FROM   tbl
   WHERE  product IN ('circle', 'square')
   )
SELECT x.person, x.product, x.trans, x.purchase_date
FROM   x
LEFT   JOIN x y ON y.person = x.person AND y.rn = x.rn + 1
LEFT   JOIN x z ON z.person = x.person AND z.rn = x.rn - 1
WHERE (y.product <> x.product
       AND y.purchase_date > x.purchase_date + interval '10 min')
   OR (z.product <> x.product
       AND z.purchase_date < x.purchase_date - interval '10 min')
ORDER  BY x.person, x.purchase_date;

SQLfiddle.

Postgres 8.1 的解决方案

我无法在 Postgres 8.1 上测试它，没有可用的幸存实例。经过测试并适用于 v8.4，应该 也适用于您。 Temporary sequences和临时表和CREATE TABLE AS已经可用。
临时序列和表只对您可见，因此即使并发查询也可以获得连续的数字。

CREATE TEMP SEQUENCE s;

CREATE TEMP TABLE x AS
SELECT *, nextval('s') AS rn  -- get row-numbers from sequence
FROM  (
   SELECT *
   FROM   tbl
   WHERE  product IN ('circle', 'square')
   ORDER  BY person, purchase_date  -- need to order in a subquery first!
   ) a;

然后与上面相同的 SELECT 应该可以工作:

SELECT x.person, x.product, x.trans, x.purchase_date
FROM   x
LEFT   JOIN x y ON y.person = x.person AND y.rn = x.rn + 1
LEFT   JOIN x z ON z.person = x.person AND z.rn = x.rn - 1
WHERE (y.product <> x.product
       AND y.purchase_date > x.purchase_date + interval '10 min')
   OR (z.product <> x.product
       AND z.purchase_date < x.purchase_date - interval '10 min')
ORDER  BY x.person, x.purchase_date;