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

Question

[Влад Володько]

У меня есть таблице такого вида. Куда я записываю изменение по статусу для тикета


Мне это нужно для аналитики, чтобы я сделал таблицу в которой отобразил кто какие тикеты и менял с какого статуса на какой. Вот что должно выйти


Но мне не нравится мой алгоритм

$getTickets = mPDO::query("SELECT COUNT(t.id) as count_id, t.user_id FROM log_ticket_statuses t GROUP BY t.user_id")->fetchAll();

        foreach ($getTickets as $ticket) {

            $ticket['user'] = mPDO::select(UsersEntity::class, ['uid' => $ticket['user_id']])->fetch();

            $status = ['Ready to Resolve', 'Assigned', 'On Hold', 'In Progress', 'Resolved'];
            foreach ($status as $item) {
                $where_status = [
                    'user_id' => $ticket['user_id'],
                    'new_status' => $item
                ];
                $t = strtolower(str_replace(' ', '_', $item));
                $list = mPDO::select([LotTicketsStatusEntity::class], $where_status)->fetchAll();
                $ticket['count_'.$t] = count($list);
            }

            $where_note = [
                'userid' => $ticket['user_id'],
                'deleted_is' => 'no',
            ];

            $ticket['count_note'] = mPDO::select([NotesEntity::class, 'select' => 'count(userid) as counts', 'group' => 'userid'], $where_note)->fetch(\PDO::FETCH_ASSOC)['counts']  ?? 0;
         

            $items[] = $ticket;
        }

А именно то что я группирую потом делаю перебор и еще раз группирую.

Answer 1

[alexalexes]

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

select t.user_id,
          count(*) as calls,
          count(case when t.old_status = 'Open' then 1 end) as opened,
          count(case when t.new_status = 'Assigned' then 1 end) as assigned
          ....
 from log_ticket_statuses t
group by  t.user_id

PS: Хорошо бы статусы вынести в отдельный справочник, не комильфо использовать его как строку.
PPS: Почему выбрали модель использования колонок old_stasus/new_status, почему бы не делать трек истории через связанные строки id_next? Туда неограниченное количество переходов статусов можно запихнуть на одного пользователя и по маркеру id_next is null удобно составлять запросы все ли треки завершены, и какие текущие статусы у пользователей с задачами.
history_id, ticket_id, user_id, status_id, next_history_id
1, 1, 1, 1, 2
2, 1, 1, 2, null
3, 1, 2, 1, null
4, 2, 2, 1, 5
5, 2, 2, 2, null