Oracle SQL - 没有计数结果时返回日期记录

马尔卡斯

我有下表，我需要我的查询来获取按日期分组的操作数量。对于没有操作的日期，我无论如何都需要以零计数返回日期。像这样：

OPERATION_DATE | COUNT_OPERATION | COUNT_OPERATION2 |
04/06/2019     |            453  |              81  |
05/06/2019     |              0  |               0  |

-- 我试过的查询

SELECT 
    T1.DATE_OPERATION AS  DATE_OPERATION,
    NVL(T1.COUNT_OPERATION, '0') COUNT_OPERATION,
    NVL(T1.COUNT_OPERATION2, '0') COUNT_OPERATIONX,

FROM
(  
SELECT
    trunc(t.DATE_OPERATION) as DATE_OPERATION,
    count(t.ID_OPERATION) AS COUNT_OPERATION,
    COUNT(CASE WHEN O.OPERATION_TYPE = 'X' THEN 1 END) COUNT_OPERATIONX,
from OPERATION o
    left join OPERATION_TYPE ot on ot.id_operation = o.id_operation
where ot.OPERATION_TYPE in ('X', 'W', 'Z', 'I', 'J', 'V') 
    and TRUNC(t.DATE_OPERATION) >= to_date('01/06/2019', 'DD-MM-YYYY')
    group by trunc(t.DATE_OPERATION)
) T1

-- 表格

CREATE TABLE OPERATION
(   ID_OPERATION NUMBER NOT NULL,
    DATE_OPERATION DATE NOT NULL,
    VALUE NUMBER NOT NULL )

CREATE TABLE OPERATION_TYPE
(   ID_OPERATION NUMBER NOT NULL,
    OPERATION_TYPE VARCHAR2(1) NOT NULL,
    VALUE NUMBER NOT NULL)

小脚怪

我猜这是您需要的日历，即包含所有相关日期的表格。否则，你怎么能显示一些不存在的东西？

这是您目前拥有的（我只使用operation表格；您自己再添加一张）：

SQL> with
  2  operation (id_operation, date_operation, value) as
  3    (select 1, date '2019-06-01', 100 from dual union all
  4     select 2, date '2019-06-01', 200 from dual union all
  5     -- 02/06/2019 is missing
  6     select 3, date '2019-06-03', 300 from dual union all
  7     select 4, date '2019-06-04', 400 from dual
  8    )
  9  select o.date_operation,
 10         count(o.id_operation)
 11  from operation o
 12  group by o.date_operation
 13  order by o.date_operation;

DATE_OPERA COUNT(O.ID_OPERATION)
---------- ---------------------
01/06/2019                     2
03/06/2019                     1
04/06/2019                     1

SQL>

由于没有属于 02/06/2019 的行，因此查询无法返回任何内容（您已经知道了）。

因此，添加日历。如果您已经有了那张桌子，那很好 - 使用它。如果没有，请创建一个。它是一个分层查询，它添加level到某个日期。我使用 01/06/2019 作为起点，创建 5 天（注意connect by条款）。

SQL> with
  2  operation (id_operation, date_operation, value) as
  3    (select 1, date '2019-06-01', 100 from dual union all
  4     select 2, date '2019-06-01', 200 from dual union all
  5     -- 02/06/2019 is missing
  6     select 3, date '2019-06-03', 300 from dual union all
  7     select 4, date '2019-06-04', 400 from dual
  8    ),
  9  dates (datum) as                              --> this is a calendar
 10    (select date '2019-06-01' + level - 1
 11     from dual
 12     connect by level <= 5
 13    )
 14  select d.datum,
 15         count(o.id_operation)
 16  from operation o full outer join dates d on d.datum = o.date_operation
 17  group by d.datum
 18  order by d.datum;

DATUM      COUNT(O.ID_OPERATION)
---------- ---------------------
01/06/2019                     2
02/06/2019                     0      --> missing in source table
03/06/2019                     1
04/06/2019                     1
05/06/2019                     0      --> missing in source table

SQL>

可能是一个更好的选择是动态创建一个日历，以便它不依赖于任何硬编码值，但使用min(date_operation)到max(date_operation)的时间跨度。开始了：

SQL> with
  2  operation (id_operation, date_operation, value) as
  3    (select 1, date '2019-06-01', 100 from dual union all
  4     select 2, date '2019-06-01', 200 from dual union all
  5     -- 02/06/2019 is missing
  6     select 3, date '2019-06-03', 300 from dual union all
  7     select 4, date '2019-06-04', 400 from dual
  8    ),
  9  dates (datum) as                              --> this is a calendar
 10    (select x.min_datum + level - 1
 11     from (select min(o.date_operation) min_datum,
 12                  max(o.date_operation) max_datum
 13           from operation o
 14          ) x
 15     connect by level <= x.max_datum - x.min_datum + 1
 16    )
 17  select d.datum,
 18         count(o.id_operation)
 19  from operation o full outer join dates d on d.datum = o.date_operation
 20  group by d.datum
 21  order by d.datum;

DATUM      COUNT(O.ID_OPERATION)
---------- ---------------------
01/06/2019                     2
02/06/2019                     0      --> missing in source table
03/06/2019                     1
04/06/2019                     1

SQL>

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。