SQL-запрос, чтобы найти изменения в статусе в течение дня

0

У меня есть таблица базы данных с именем STATUS_HISTORY

  1. ID- ID записи (комбинация ID и REV уникальна)
  2. REV - идентификатор ревизии, если какая-либо запись изменена, создается новая запись с увеличенным идентификатором ревизии

  3. СТАТУС - Может содержать три значения

    • ОТКРЫТЬ
    • БЛИЗКО
    • В ХОДЕ ВЫПОЛНЕНИЯ

  4. UPDATED_BY_ID - идентификатор пользователя, который обновил запись

  5. UPDATED_TIMESTAMP - когда была обновлена эта запись.
  6. TASK_ID - задача, статус которой меняется

Теперь за целый день запись может переходить в разные статусы.

Я ищу запрос, чтобы дать ответ, как показано ниже:

╔═════════════════╤══════╤════════════╤═══════╗
║ Hour_of_the_day │ OPEN │ INPROGRESS │ CLOSE ║
╠═════════════════╪══════╪════════════╪═══════╣
║ 1               │ 10   │ 0          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 2               │ 10   │ 0          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 3               │ 10   │ 0          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 4               │ 9    │ 1          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 5               │ 9    │ 1          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 6               │ 8    │ 2          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 7               │ 8    │ 2          │ 0     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 8               │ 8    │ 1          │ 1     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 9               │ 8    │ 0          │ 2     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 10              │ 6    │ 2          │ 2     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 11              │ 6    │ 1          │ 3     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 12              │ 5    │ 2          │ 3     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 13              │ 4    │ 3          │ 3     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 14              │ 4    │ 3          │ 3     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 15              │ 2    │ 3          │ 5     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 16              │ 1    │ 2          │ 7     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 17              │ 0    │ 1          │ 9     ║
╟─────────────────┼──────┼────────────┼───────╢
║ 18              │ 0    │ 0          │ 10    ║
╟─────────────────┼──────┼────────────┼───────╢
║ 19              │ 0    │ 0          │ 10    ║
╟─────────────────┼──────┼────────────┼───────╢
║ 20              │ 0    │ 0          │ 10    ║
╟─────────────────┼──────┼────────────┼───────╢
║ 21              │ 0    │ 0          │ 10    ║
╟─────────────────┼──────┼────────────┼───────╢
║ 22              │ 0    │ 0          │ 10    ║
╟─────────────────┼──────┼────────────┼───────╢
║ 23              │ 0    │ 0          │ 10    ║
╟─────────────────┼──────┼────────────┼───────╢
║ 24              │ 0    │ 0          │ 10    ║
╚═════════════════╧══════╧════════════╧═══════╝

Примечание. Предположим, что задание помечено как ОТКРЫТОЕ, и в состоянии за последние 5 часов состояние не изменилось, его нужно считать за все 5 часов. То же самое относится и к другим состояниям, таким как close и inprogress, потому что не все записи будут меняться каждый час.

В основном на каждый час, сколько билетов было открыто, закрыто или в процессе.

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

 SELECT TO_CHAR(t.UPDATED_TIMESTAMP , 'HH24') AS hour,
         COUNT(*) AS numPerHour, t.STATUS
    FROM STATUS_HISTORY t
    WHERE t.UPDATED_TIMESTAMP  >= to_date('29-OCT-18','DD-MON-YY')
    AND t.UPDATED_TIMESTAMP     < to_date('30-OCT-18','DD-MON-YY')
GROUP BY TO_CHAR(t.UPDATED_TIMESTAMP , 'HH24'), t.STATUS;

╔═════════════════╤════════════╤════════════╗
║ Hour_of_the_day │ NumPerHour │ INPROGRESS ║
╠═════════════════╪════════════╪════════════╣
║ 06              │ 2          │ OPEN       ║
╟─────────────────┼────────────┼────────────╢
║ 09              │ 5          │ OPEN       ║
╟─────────────────┼────────────┼────────────╢
║ 09              │ 2          │ OPEN       ║
╟─────────────────┼────────────┼────────────╢
║ 09              │ 3          │ CLOSE      ║
╟─────────────────┼────────────┼────────────╢
║ 07              │ 1          │ INPROGRESS ║
╟─────────────────┼────────────┼────────────╢
║ 08              │ 8          │ OPEN       ║
╟─────────────────┼────────────┼────────────╢
║ 11              │ 1          │ CLOSE      ║
╟─────────────────┼────────────┼────────────╢
║ 08              │ 1          │ INPROGRESS ║
╟─────────────────┼────────────┼────────────╢
║ 07              │ 3          │ OPEN       ║
╟─────────────────┼────────────┼────────────╢
║ 11              │ 2          │ CLOSE      ║
╟─────────────────┼────────────┼────────────╢
║ 12              │ 1          │ INPROGRESS ║
╚═════════════════╧════════════╧════════════╝

Я хотел бы понять, если есть другой способ справиться с этим с помощью Java-программы или запрос может предоставить эти результаты.

  • 0
    Когда закрытое задание перестает быть интересным? Что, если в этот день вообще нет активности для задачи - есть ли открытая задача в прошлом? Является ли status_history большой таблицей, можете ли вы позволить себе заблокировать или вы довольны иметь приблизительные числа (следствие отсутствия блокировки) во время выполнения запроса). Намерены ли вы выполнить этот запрос в течение дня или в конце дня, или как-нибудь еще? Freqency?
  • 0
    TASK_ID имеет задачи для всех задач на день, поэтому изначально все задачи открыты в начале дня. это не очень большой стол.
Показать ещё 2 комментария
Теги:

2 ответа

1

Используйте условную агрегацию.

select COUNT( CASE WHEN STATUS = 'OPEN' THEN 1 END ) as OPEN,
       COUNT( CASE WHEN STATUS = 'INPROGRESS' THEN 1 END ) as INPROGRESS,
       COUNT( CASE WHEN STATUS = 'CLOSE' THEN 1 END ) as CLOSE
      ....
      ....
 GROUP BY TO_CHAR(t.UPDATED_TIMESTAMP , 'HH24') --remove t.STATUS
Поделиться
  • 0
    Это дает результаты только в тот час, когда происходит изменение. Но предположим, что нет изменений, которые мы хотели бы иметь в том же статусе, что и в предыдущий час. например, в час 1 статус 1 - открыт, 1 - закрыт, 0 - выполняется. в час 2 изменений нет, статус остается прежним. но это не доступно через запрос, который вы упомянули.
  • 0
    @Ashu: отредактируйте свой вопрос и добавьте примеры строк для STATUS_HISTORY чтобы получить именно тот результат, который вы ожидаете. Моделирование dbfiddle будет более полезным.
0

Вы можете сделать это с помощью CASE:

SELECT TO_CHAR(t.UPDATED_TIMESTAMP , 'HH24') AS hour,
         COUNT(*) AS numPerHour, sum(case when t.STATUS='OPEN' then 1 ELSE 0 END) AS OPEN,
         sum(case when t.STATUS='CLOSE' then 1 ELSE 0 END) AS CLOSE,
         sum(case when t.STATUS='INPROGRESS' then 1 ELSE 0 END) AS INPROGRESS

    FROM STATUS_HISTORY t
    WHERE t.UPDATED_TIMESTAMP  >= to_date('29-OCT-18','DD-MON-YY')
    AND t.UPDATED_TIMESTAMP     < to_date('30-OCT-18','DD-MON-YY')
GROUP BY TO_CHAR(t.UPDATED_TIMESTAMP , 'HH24'), t.STATUS
Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню