Как выбрать даты максимальной суммы из таблицы со столбцами даты и суммы

У меня есть таблица с 2 столбцами, датой и суммой. Я хочу иметь возможность находить пики 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;

mysql sql date-range
person lloyd dobler    schedule 20.11.2012    source источник
comment
Должен ли максимум рассчитываться в интервале +/- 3 календарных дня (как указано в ваших попытках запроса) или в интервале +/- 3 последовательных дат в таблице (как указано путем исключения 2012-09-19 из желаемый результат)?   -  person Terje D.    schedule 21.11.2012
comment
Я имел в виду вычислить на основе последовательных дат в таблице, чего, как я вижу, я не делал.   -  person lloyd dobler    schedule 21.11.2012

Ответы (1)


arrow_upward
0
arrow_downward

Чтобы найти максимум в интервале дат, вы должны соединить запись для центральной даты с записями для других дат в интервале, а затем сгруппировать по центральной дате.

Попробуйте этот запрос:

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, чтобы протестировать его.

person Terje D.    schedule 21.11.2012
comment
Потрясающий! Это отлично работает для календарных дней. Мне нужно будет попытаться настроить последовательные даты в таблице, как указано выше. Спасибо! - person lloyd dobler; 21.11.2012
comment
Упс. Я попросил разъяснений, а потом неправильно понял ответ. - person Terje D.; 21.11.2012
comment
Спасибо еще раз. Я использую MySQL, поэтому, как вы сказали, мне нужно будет найти другой способ. Я получаю следующую ошибку в MySQL, когда пробую ваш пример: ОШИБКА 1235 (42000): эта версия MySQL еще не поддерживает подзапрос LIMIT & IN/ALL/ANY/SOME. - person lloyd dobler; 24.11.2012
comment
Проверьте обновленный ответ на запрос, который также работает в MySQL. - person Terje D.; 24.11.2012
comment
Да, обновленный ответ отлично работает в MySQL. Я бы никогда не понял этого. Это далеко за пределами моих навыков новичка. Я преклоняюсь перед вашим превосходным интеллектом. Спасибо! - person lloyd dobler; 26.11.2012