Блог

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

     SELECT (n.TOTAL - v.TODAY)   d.GISTER AS GISTER
FROM 
(
    -- Counts yesterday
    SELECT

        COUNT(ID) AS Gister

    FROM FRESHDESK_API
    -- 4 = resolved 5 = closed 
    -- Both count as closed
    WHERE STATUS IN(4, 5)
    AND TRUNC(UPDATED_AT) = TRUNC(SYSDATE - 1)
) d
CROSS JOIN
(
    -- Total pending
    SELECT

        COUNT(ID) AS TOTAL

    FROM FRESHDESK_API
    -- 3 is pending
    WHERE STATUS IN(3) 
) n
CROSS JOIN
(
    -- Pending tickets today
    SELECT

        COUNT(ID) AS TODAY

    FROM FRESHDESK_API
    -- 3 is pending
    WHERE STATUS IN(3) 
    AND TRUNC(UPDATED_AT) = TRUNC(SYSDATE) 
) v
 

Я хочу получить такой результат:

  ---------------------------------- --------- ----------   
|               day                |  pending_tickets   |            
 ---------------------------------- --------- ----------   
| Monday                           | 20                 |  
| Tuesday                          | 22                 |  
| Wednesday                        | 25                 |  
| Thursday                         | 24                 |  
| Friday                           | 19                 |  
 ---------------------------------- --------- ----------   
 

Таблица выглядит примерно так (неиспользуемые данные удалены):

  ---------------------------------- --------- ---------- --------- ----------- ---------- ---------- 
|               id                 |     created_at     |     updated_at      |       status        |
 ---------------------------------- --------- ---------- ---------- ---------- ---------- ---------- 
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
 ---------------------------------- --------- ---------- --------- ----------- --------- ----------- 
 

Вы можете использовать left join и group by следующим образом:

 Select to_char(tday.updated_at, 'day') as updated_at, 
       count(tday.id) - count(yday.id) as pending_tickets
  From FRESHDESK_API tday
  Left join FRESHDESK_API yday
       On trunc(tday.UPDATED_AT) = trunc(yday.UPDATED_AT - 1)
       And trunc(yday.UPDATED_AT   1, 'iw') = trunc(sysdate, 'iw')
       And yday.status in (4,5)
 Where trunc(tday.UPDATED_AT, 'iw') = trunc(sysdate, 'iw')
   And tday.status = 3
Group by to_char(tday.updated_at, 'day'), trunc(tday.updated_at)
Order by trunc(tday.updated_at);