Блог

Это текущая компоновка таблицы.

• Есть 3 ветви
• Каждая ветвь имеет 2 точки, где is_start = 1 — это начало ветви и is_start конец ветви.
• Когда пользователь регистрируется в точке, создается запись points_user .

В этом приложении у вас есть несколько ветвей, которые имеют 2 точки, где одна отмечает начало ветви, а другая — конец ветви. Таким образом, сумма пользовательских (с id = 2) ветвей (с id = 1) равна points_users . created где points_users . leg_id = 1 и points_users . user_id = 2 и points_users . is_start = 0 минус points_users где is_start = 1 (а остальные параметры остаются прежними). И это только для одной части.

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

 | User.id | User.name | total_time |
| 1       | John      | 129934     |
  

Кто-нибудь знает, как я могу объединить эти таблицы и суммировать их, сгруппированные по пользователям?

(Нет, это не домашнее задание)

Насколько я понял:

 SELECT
( `end_time` - `start_time` ) AS `diff`
FROM
(
    SELECT SUM(UNIX_TIMESTAMP(`p1`.`created`)) AS `start_time`
    FROM `points_users` AS `pu1`
    LEFT JOIN `points` AS `p1` ON `pu1`.`point_id` = `p1`.`id`
    WHERE `p1`.`is_start` = 1
) AS `start_time`,
(
    SELECT SUM(UNIX_TIMESTAMP(`pu2`.`created`)) AS `end_time`
    FROM `points_users` AS `pu2`
    LEFT JOIN `points` AS `p2` ON `pu2`.`point_id` = `p2`.`id`
    WHERE `p2`.`is_start` = 0
) AS `end_time`
  

Попробуйте это:

 select users.user_id,
    users.user_name,
    SUM(timeDuration) totalTime
from users
join (
    select 
        pStart.User_id,
        pStart.leg_id,
        (pEnd.created - pStart.created) timeDuration                
    from (select pu.user_id,  pu.leg_id,  pu.created
        from points_users pu
        join points p on  pu.id = p.point_id and pu.leg_id = p.leg_id
        where p.is_start = 1 ) pStart
    join  (select pu.user_id, pu.leg_id,  pu.created
        from points_users pu
        join points p on  pu.id = p.point_id and pu.leg_id = p.leg_id
        where p.is_start = 0 ) pEnd
    on      pStart.user_id = pEnd.user_id
    and     pStart.leg_id = pEnd.leg_id
    ) tt
on users.user_id = tt.user_id
group by users.user_id, users.user_name
  

Подзапрос получает продолжительность времени для каждого пользователя / ветви, а затем основной запрос суммирует их для всех ветвей каждого пользователя.

РЕДАКТИРОВАТЬ: добавлена points таблица, теперь я вижу вашу попытку запроса.

Самый простой способ — присоединиться points_users к самому себе:

 select leg_start.user_id, sum(leg_end.created - leg_start.created)
from points_users leg_start
join points_users leg_end on leg_start.user_id = leg_end.user_id
    and leg_start.leg_id = leg_end.leg_id
join points point_start on leg_start.point_id = point_start.id
join points point_end on leg_end.point_id = point_end.id
where point_start.is_start = 1 and point_end.is_start = 0
group by leg_start.user_id
  

Некоторые люди предпочитают помещать эти is_start фильтры в условие соединения, но поскольку это внутреннее соединение, это в основном просто точка стиля. Если бы это было внешнее объединение, то перемещение их из WHERE в JOIN могло повлиять на результаты.