Как посчитать среднюю разницу между значениями в SQL?

Question

[freelion93]

Структура таблицы следующая:

Требуется найти среднюю разницу между действиями по времени (timestamp) для каждого объекта (file).

Например у Файла1 было 4 активности:
Активность1 во время 1549256689
Активность2 во время 1549655521
Активность3 во время 1550165431
Активность4 во время 1550317858

Например у Файла2 было 2 активности:
Активность1 во время 1548548573
Активность2 во время 1549875732

Итого у Файла1 средняя разница между активностями 353723‬, а у Файла2 1327159, итого общая средняя 973436‬

Как составить SQL запрос чтобы найти такую среднюю разницу?

Comments

[freelion93]

VicTHOR, ну если бы только AVG было бы нужно :D
еще и ABS требуется и group by

[freelion93]

VicTHOR, Сначала считаем разницу между значениями timestamp для каждого из файлов, а потом находим среднее значение этой разницы у всех файлов

Answer 1

[freelion93]

Получил довольно топовый ответ со Stack Overflow, ребята там все поняли без ссылкокидания на референсы и посылом на простую функцию avg, получилось следующее:

SELECT SUM(time_diff) / ((SELECT COUNT(*) FROM oc_activity) - 1) AS avg_diff FROM ( SELECT ABS(o1.timestamp - COALESCE((SELECT o2.timestamp FROM oc_activity o2 WHERE o2.object_id > o1.object_id ORDER BY o2.object_id LIMIT 1), o1.timestamp)) time_diff FROM oc_activity o1) t;

Всем спасибо кто пытался помочь!

P.S. в восьмой версии еще есть оператор LEAD для итераций

Answer 2

[Boris Köln]

https://dev.mysql.com/doc/refman/8.0/en/group-by-f...

Comments

[freelion93]

Мсье, а разница как же? мне же средняя разница нужна а не просто среднее значение

[Boris Köln]

freelion93, что такое "средняя разница"?
Это сначала 2 средних (чего именно?), а потом разница?
Или это сначала разница (между чем и чем? а если N значений, то сравнивать каждое с каждым N*(N-1)/2), а потом среднее?
Или это среднее и потом разница между конкретным значением и средним?
Или это математическое ожидание?
Или дисперсия?
Или стандартное отклонение?
Или среднеквадратичное отклонение?

[freelion93]

Boris Korobkov,
Средняя разница - разница между имеющимися значениями для каждого объекта усредненная

Скажем например вот у нас была бы таблица заказов в магазине, где у каждого клиента по 3 заказа., причем каждый следующий на большую сумму.

У первого клиента например есть заказы на следующие суммы (10, 20, 30), разница между ними 10, у другого клиента суммы заказа (5, 10, 15) и разница между ними 5.

Если же мы найдем среднее между средними разницами двух этих клиентов то будет 7.5.

По аналогии мне требуется тоже самое только с времеными отметками для каждого из файлов

[Boris Köln]

freelion93, тогда сначала найдите разницу, а потом среднее.
Разницу можно посчитать триггером при создании нового заказа, на любом ЯП при ... order by client_id, id desc или на MySQL при join этой же таблицы на себя (предыдущего заказа того же юзера)

[freelion93]

Boris Korobkov, мне нужно посчитать разницу именно в SQL запросе в существующей таблице, скорее всего через команду ABS пойдет, и да наверно join нужен, не знаю пока как сделать поэтому и спросил