Поиск по среднему времени в связанной таблице?

Question

[kedavr13]

Есть таблица с пользователями, также есть связанная таблица которая в двух колонках хранит даты в формате timestamp.
Связь по hasMany.
Как мне получить все записи из первой таблицы у которых среднее значение между датами в минутах больше 10 допустим.
Пробовал использовать TIMESTAMPDIFF, но не знаю будет ли он работать при связи 1:n и как его впихнуть в построитель запросов.
есть таблица user у нее есть связь с таблице work_time по hasMany
В work_time есть колонки time_in и time_out
Необходимо получить всех юзеров у которых среднее время в work_time равно 20 по схеме все time_out - time_in/на число таких записей

Comments

[Konstantin]

Вопрос явно не из мира "Yii" а из баз данных.
и сводится к тому - как в таблице вычислить "среднее" значение между датами

давайте представим что у нас условно 5 дат. упростим только до часов и минут

10:10
10:03
10:14
09:05
12:30

Какое здесь среднее значение между датами ? между какими датами? первой и последней? между соседними? может ли у них быть отрицательное значение ? (тоесть дата 2 раньше даты 1 )

[kedavr13]

Konstantin, Не совсем так, есть таблица user у нее есть связь с таблице work_time по hasMany
В work_time есть колонки time_in и time_out
Необходимо получить всех юзеров у которых среднее время в work_time равно 20 по схеме все time_out - time_in/на число таких записей

[Konstantin]

Разобъем вопрос на два: как выбрать всех пользователей у которых time > 10 минут

select user_id,  avg(time_out - time_in) as avgtime
from work_time
group by user_id
having avgtime > 600

а теперь можем либо использовать эти id-шники во втором запросе, либо сделать inner join

select id, name, usertime.avgtime from user
inner join (
    select user_id, avg(time_out - time_in) as avgtime
    from work_time
    group by user_id
    having avgtime > 600
) usertime on user.id = usertime.user_id

примерно так получится.

[kedavr13]

Konstantin, Все верно, еле получилось перетащить в билдер запросов yii2 но в итоге результат получился вот такой

$query
                ->innerJoin('histories' , 'users.id = histories.user_id')
                ->select('users.*, histories.user_id, avg(timestampdiff(minute,`histories`.`time_in`,`histories`.`time_out`)) as avgtime')
                ->groupBy('user_id')
                ->having('avgtime > ' . $this->wash_middle_time_more);

Пришлось все же добавить timestampdiff так как без него почему то считало неправильно.

[Konstantin]

можно денормализировать таблицу, добавить поле work_time (например) и высчитывать разницу в момент записи time_out, таким образом гораздо проще будет проводить поиск просто по колонке, не заставляя базу данных просчитывать каждую запись при каждом подходе.

Answer 1

[kedavr13]

На основе ответа Konstantin получилось собрать вот такой вот запросик

$query
                ->innerJoin('histories' , 'users.id = histories.user_id')
                ->select('users.*, histories.user_id, avg(timestampdiff(minute,`histories`.`time_in`,`histories`.`time_out`)) as avgtime')
                ->groupBy('user_id')
                ->having('avgtime > ' . $this->wash_middle_time_more);