Advanced Average Date Diffference с уникальными идентификаторами
Я вернулся к переполнению стека с другой головной болью, к которой я пытался добраться до сути безуспешно. Независимо от того, сколько раз я использую функции avg(datediff).
У меня есть таблица SQL, как показано ниже:
ID | PersonID | Начало | Конец
1 | 1 | 2006-03-21 00:00:00 | 2007-05-19 00:00:00 | активный
2 | 1 | 2007-05-19 00:00:00 | 2007-05-20 00:00:00 | активный
3 | 2 | 2016-08-24 00:00:00 | 2016-08-25 00:00:00 | активный
4 | 2 | 2005-08-25 00:00:00 | 2016-08-28 00:00:00 | активный
5 | 2 | 2016-08-28 00:00:00 | 2017-10-05 00:00:00 | активный
Я пытаюсь найти средний активный отдых (в днях) среди всех уникальных людей.
Т.е. среднее количество дней, основанное на их самой ранней дате начала и последней дате окончания (поскольку один идентификатор человека может иметь несколько активных статусов).
Например, лицо с идентификатором 1, его самая ранняя дата начала была 2006-03-21, а его последняя дата окончания - 2007-05-20. Поэтому их пребывание составило 425 дней.
Повторите это для удостоверения личности № 2, их пребывание составляет 407 дней.
Сделав это для всех на столе... Я хочу получить среднюю продолжительность пребывания, среднее значение для 5 вышеупомянутых рядов с 2 уникальными людьми - 416. Выполнение простого среднего по датам по всем строкам даст мне очень неточную информацию в среднем 102.
Надеюсь, это имеет смысл. Как всегда, любая помощь, которую вы можете оказать, очень ценится.
1 ответ
Так почему бы не попробовать это:
SELECT
AVG(DATEDIFF(PersonEnd, PersonStart))
FROM
(SELECT
MIN(Start) AS PersonStart,
MAX(End) AS PersonEnd
FROM
table
GROUP BY
PersonID) PeriodsPerPerson
Конечно, у вас должны быть правильные индексы, чтобы MySQL мог вычислять MAX
а также MIN
быстро и может группировать быстро, а это означает, что индексы по крайней мере на PersonID
, Start
а также End
,
Обратите внимание, что вам действительно нужен псевдоним для внутреннего запроса, хотя я его нигде не использую. Если вы откажетесь от этого, вы столкнетесь с ошибкой, по крайней мере, с MySQL 5.5 (я не знаю о более поздних версиях).
Если у вас есть миллионы или даже миллиарды строк, вам может быть лучше перенести вычисления в хранимую процедуру или во внутреннее приложение, а не делать это, как показано выше.