问题:查询返回时差超过阈值的行

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

假设我有下表:

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

解答

现代解决方案

使用现代 Postgres(8.4 或更高版本),您可以使用窗口函数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 上运行,_应该_也适合您。临时序列和临时表和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;