oracle - 如何在oracle-sql中计算本周每天的待处理票总数？

Question

我想计算本周每一天的待处理票的总数。我一次只能得到它一天。我现在有这个查询：

    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        |
+----------------------------------+---------+----------+----------+----------+----------+----------+
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
|                                  |                    |                     |                     |
+----------------------------------+---------+----------+---------+-----------+---------+-----------+

标签： oracleoracle-sqldeveloperoracle-apexoracle-apex-20.1

Answer 1

您可以使用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);