如何从包含date和amount列的表中选择峰值日期

Question

我有一个表，有两列，日期和金额。我希望能够找到x天的峰值。因此，如果我有下表，并正在寻找比方说3天峰值，意味着当天的金额高于之前或之后3天的任何一天，那么将选择该日期。

date         amount
2012-09-04 | 53137.47
2012-09-05 | 53137.2
2012-09-06 | 53137.54
2012-09-07 | 53138.58
2012-09-10 | 53138.73
2012-09-11 | 53138.28
2012-09-12 | 53138.22
2012-09-13 | 53138.48
2012-09-14 | 53140.14
2012-09-17 | 53139.82
2012-09-18 | 53139.86
2012-09-19 | 53140.01
2012-09-20 | 53139.75
2012-09-21 | 53139.82
2012-09-24 | 53139.01
2012-09-25 | 53138.93
2012-09-26 | 53138.48
2012-09-27 | 53138.83
2012-09-28 | 53138.62

应选择2012-09-10,2012-09-14

我尝试过使用连接和派生表，但似乎无法使其正常工作。

尝试了以下操作：

    select * from a WHERE  
amount=(select MAX(amount) from a where 
date<date_add(date,interval 3 day) and 
date>date_sub(date,interval 3 day));

和

    SELECT a1.*
    FROM a AS a1
    JOIN a AS a2 ON 
(select * from a1 where 
a2.DATE < a1.DATE + 3 and 
a2.DATE > a1.DATE - 3) myalias
    WHERE a1.amount > a2.amount;

Answer 1

为了找到日期间隔中的最大值，您必须将中心日期的记录与该间隔中其他日期的记录连接起来，然后按中心日期分组。

尝试此查询：

SELECT a1.date, a1.amount, max(a2.amount) AS highest
FROM tbl a1 LEFT JOIN tbl a2
ON a2.date BETWEEN date_sub(a1.date, interval 3 DAY)
           AND date_add(a1.date, interval 3 DAY)
GROUP BY a1.date, a1.amount
HAVING a1.amount = highest
ORDER BY a1.date;

要获得基于连续记录而不是日历天数的答案，您必须在表中增加行号(通过执行表与自身的自连接)，然后在比较金额时选择每个日期的+/- 3行内的行，然后对此扩展表进行自连接：

SELECT t3.date FROM
  (SELECT t1.date AS date, t1.amount AS amount, count(t2.date) AS rownum
   FROM tbl t1
   INNER JOIN tbl t2
   ON t2.date <= t1.date
   GROUP BY t1.date, t1.amount) AS t3
LEFT JOIN
  (SELECT t1.date AS date, t1.amount AS amount, count(t2.date) AS rownum
   FROM tbl t1
   INNER JOIN tbl t2
   ON t2.date <= t1.date
   GROUP BY t1.date, t1.amount) AS t4
ON t3.rownum <> t4.rownum
AND t3.rownum - t4.rownum  BETWEEN -3 AND 3
AND t4.amount >= t3.amount
WHERE t4.date IS NULL
ORDER BY t3.date

这在MySQL和PostgreSQL中都有效，在其他数据库引擎中也应该有效。在MySQL以外的其他数据库中，可以通过对这两个子查询使用CTE来简化查询。

使用此SQLfiddle对其进行测试。