sql - 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>
推荐阅读
- javascript - 在 JavaScript 类中使用箭头函数的继承和多态
- mpi - MPI 程序执行错误 - 未找到活动端口
- java - Camera 2 Api Error - 不拍照的错误
- python - 如何询问用户是否希望代码重新开始
- java - Codename One 中用于验证 url 的 URL 模式匹配
- appium - Android UiAutomation 未连接!Appium 1.17.1-1
- java - 正则表达式 java 屏蔽
- memory-management - /proc/[pid]/status:RssAnon 和 /proc/[pid]/smaps_rollup:Anonymous 之间存在差异的原因是什么?
- python - Python BeautifulSoup 输出异常的间距和字符
- python - Backtrader 错误:“DataFrame”对象没有属性“setenvironment”