Как посчитать среднее значение по сгруппированным данным?

Question

[zaartix]

Простой маппинг:

player_id: int
stat_date: date
battles: int

Нужно посчитать среднее количество боев среди игроков.
Видимо, нужно сначала использовать term агрегацию по полю player_id, но при этом важно "выбрать" запись с максимально свежей датой для каждого игрока. Затем, по получившимся результатам посчитать avg для поля battles.

Уже неделю бьюсь, не получается придумать как сделать такую агрегацию.

Answer 1

[Алексей Черемисин]

Делайте запрос с фильтром по дате и stats агрегацию по полю battles. Получите и среденее и min и max и медиану https://www.elastic.co/guide/en/elasticsearch/refe...

Comments

[zaartix]

Спасибо за ответ, но, если я вас правильно понял, то так не сработает.

Фильтр по дате невозможен, дата всегда разная.

можно представить более простой вариант, предположим у меня еще есть поле
is_last: boolean
у каждого игрока есть только одна запись с is_last=true, очевидно с самой максимальной датой и самыми свежими данными Но максимальная дата всегда разная у всех.
При такой схеме среднее считалось бы элементарно, через выборку term is_last=1 и агрегацией avg по всем данным, которые отфильтровались. Но такого поля нет и заводить его очень не хочется, не так просто держать его актуальным будет.

[Алексей Черемисин]

так так так... А как же у вас данные в индексе лежат? И зачем вам is_last вообще?!
И почему нельзя сделать запрос по дате через range?!

[Алексей Черемисин]

Я так понял, что вам нужно примерно следующее?
Дата: от - до , среднее battles
Дата: от - до , среднее battles
Дата: от - до , среднее battles

[zaartix]

Алексей Черемисин
>И зачем вам is_last вообще

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

> И почеме нельзя сделать запрос по дате чернз range?!
Потому что в этот рейндж могут попасть несколько записей одного игрока и среднее количество "боев" будет уже не корректным.

Речь идет именно о том, чтоб посчитать просто среднее количество боев
на sql это выглядело бы так:

select AVG(battles) from (select * from stats group by player_id)

только тут не учитывается, что нужно не просто сгруппировать по игроку, но еще и желательно, чтоб это была свежайшая строка с этим игроком.

[zaartix]

Алексей Черемисин,
> Дата: от - до , среднее battles

С небольшим нюансом. На одного игрока только одна запись должна участвовать в расчете среднего значения, самая свежая. Причем не факт, что у всех игроков будут срезы статистики попадать в рейндж по датам. У кого-то последняя статистика год назад, у кого-то месяц назад или день назад.

На самом деле я сильно упростил задачу, среднее количество боев довольно бессмысленны, как данные. В реальности мне нужно посчитать среднее количество побед и среднее количество поражений, из этих чисел я смогу вычислить средний винрейт для периода времени, но как вы понимаете, если не использовать группировку по юзерам, то данные будут сильно искажены в зависимости от того как много в выборку попадут срезы за другие даты в рамках одного игрока.

[Алексей Черемисин]

Боюсь, что эластик здесь не очень поможет, на sql я тоже не очень представляю как это сделать... Возможно, вам лучше завести еще один индекс, куда класть документы с id по игроку и эти документы постоянно обновлять на самые последние значения. В этом индексе тогда будут тоько последние данные по каждому игроку - один документ на игрока! Тогда уже можно агрегировать как вы хотите за любой диапазон дат!

[Алексей Черемисин]

Так, а как исказятся данные, если я например считаю среднее за предыдущий месяц? Туда же не попадут данные за другие даты.

[zaartix]

Алексей Черемисин
> Так, а как исказятся данные, если я например считаю среднее за предыдущий месяц? Туда же не попадут данные за другие даты.

Ну смотрите, предположим есть игрок 1, 2 и 3. У них есть такие срезы:

игрок 1, дата 2019-02-01, боев 10
игрок 1, дата 2019-02-10, боев 11
игрок 2, дата 2019-02-01, боев 5
игрок 3, дата 2019-02-21, боев 7

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

Итого получается, если посчитать среднее по всем данным, то будет (10+11+5+7)/4 = 8.25
А если сделать так как было бы "правильней", когда от каждого игрока по последнему срезу учитывается, то получится (11+5+7)/3 = 7.6

[Алексей Черемисин]

zaartix, у вас просто неправильно данные лежат! Пишите каждый бой игрока и все получится! Не нужно делать накопление, эластик для хранения логов больше подходит, вот и храните записи.
Дата - игрок - проиграл/выиграл - регультат сессии - чтототамеще..
Вот эти данные и посчитать можно и агрегировать и статистику сделать

[zaartix]

Алексей Черемисин, у меня данные лежат в том виде, в котором отдает АПИ источника данных. Спасибо, подумаю насколько это возможно, каждый раз вычислять дельты между срезами и хранить только саму разницу за дату.

[Алексей Черемисин]

zaartix, либо попробуйте не эластик, а influxdb , у него есть deriative и агрегации за периоды, но там тогда счётчик должен расти всегда и не сбрасываться!

[zaartix]

Алексей Черемисин, так и есть, все счетчики растут всегда. Только их очень много. Сейчас в эластике почти 400 млн записей, 50 GB данных.

[Алексей Черемисин]

Так, у вас наращиваемый счётчик за какой-то период, как я понял? Тогда создавайте на этот период индекс и в нем один документ на игрока, который (документ) будете каждый раз обновлять.

Блин, завтра подумаю, спать пора.

[zaartix]

Алексей Черемисин, не за период, просто с нуля и до момента, когда я запрашиваю данные с АПИ. Сегодня запросил, у игрока 100 боев суммарно сделано, через месяц запрашиваю, там 300. Т.е. этого итоговое число всегда на конкретную дату. Но задача от этого вроде же не меняется. Если задача вычислить среднее число боев среди всех игроков.

[Алексей Черемисин]

Тогда либо одна и единственная запись на игрока,где id документа равно id игрока и эту запить мы всегда обновляем. Либо считайте дельту по игроку.