这是一个奇怪的问题。我不知道这是否可行。
假设我有以下表格:
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分钟，这意味着有两个实例的时间差超过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;
您可以尝试使用'ON'子句将表连接到表本身:
SELECT a.Person, CAST((DATEDIFF(mi, b.purchaseDateb a.purchaseDate)/60.0) AS Decimal) AS TimeDiff, a.Product, b.Product FROM <TABLE> a
JOIN <TABLE> b
ON a.Person = b.Person AND b.purchaseDate > a.purchaseDate
WHERE
(a.Product = 'Circle' AND b.Product = 'Square')
OR
(a.Product = 'Square' AND b.Product = 'Circle')
通过将表连接到表本身，您将得到包含同一个人的两次购买的行。通过将其限制为'b.purchaseDate>你防止行匹配自己。然后，您可以简单地查看购买的不同产品。
时差是最后一个棘手的部分。我上面的内容是基于我在这里找到的一个答案。它看起来应该可以工作，如果这个输出不适合你，你可以使用几个变量。
您需要在WHERE语句上添加一个子句，该子句使用相同的DATEDIFF函数来测试时间>10分钟，但这应该不是什么大挑战。
请注意，这不会完全返回您在问题中的内容-这将包括Jim的第一次交易的一行以及Jim的第二次方形购买的一行。两者都将匹配到同一个圆，并且您将获得两次(ijkl-abcd and ijkl-aaaa)。感谢xQbert的评论指出了这一点。
—假设
- 您想知道当天购买的分钟数差异。如果日期无关紧要，则取消where子句。
- 您只需要考虑在purchase_date之后从圆到方，而不是之前。．
。
SELECT A.person, A.product, a.Trans, A.Purchase_date, B.Purchase_date,
hours_diff * 60 + DATE_PART('minute', B.purchase_date - A.Purchase_date ) as minuteDifference
FROM yourTable A
LEFT JOIN yourTable B
on A.person = B.Person
and ((A.product = 'square' and b.product = 'circle')
OR (A.Product = 'circle' and b.product = 'square'))
and A.purchase_date <= B.Purchase_date
WHERE (A.purchase_Date::date = B.purchase_date::date OR B.purchase_date is null)