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 (я не знаю о более поздних версиях).

Если у вас есть миллионы или даже миллиарды строк, вам может быть лучше перенести вычисления в хранимую процедуру или во внутреннее приложение, а не делать это, как показано выше.

Другие вопросы по тегам