sql - 如何包含在某一天不重要的值 (APEX)
问题描述
我有这个查询:
SELECT
COUNT(ID) AS FREQ,
TO_CHAR(TRUNC(CREATED_AT),'DD-MON') DATES
FROM TICKETS
WHERE TRUNC(CREATED_AT) > TRUNC(SYSDATE) - 32
GROUP BY TRUNC(CREATED_AT)
ORDER BY TRUNC(CREATED_AT) ASC
这会计算过去一个月每天创建的工单数量。
结果如下所示:(
前 10 行)
FREQ DATES
3 28-DEC
4 04-JAN
8 05-JAN
1 06-JAN
4 07-JAN
5 08-JAN
2 11-JAN
6 12-JAN
3 13-JAN
8 14-JAN
问题是没有创建票证的日子(特别是周末),这条线直接到创建票证的那一天。
在 APEX 或我的查询中是否有办法包括未计算的天数?
解决方案
正如所评论的,使用行生成器技术之一,您将创建一个“日历”表并将其与包含您正在显示的数据的表外部连接。
像这样的东西(见代码中的注释):
SQL> with yours (amount, datum) as
2 -- your sample table
3 (select 100, date '2021-01-01' from dual union all
4 select 200, date '2021-01-03' from dual union all
5 select 300, date '2021-01-07' from dual
6 ),
7 minimax as
8 -- MIN and MAX date (so that they could be used in row generator --> CALENDAR CTE (below)
9 (select min(datum) min_datum,
10 max(datum) max_datum
11 from yours
12 ),
13 calendar as
14 -- calendar, from MIN to MAX date in YOUR table
15 (select min_datum + level - 1 datum
16 from minimax
17 connect by level <= max_datum - min_datum + 1
18 )
19 -- final query uses outer join
20 select c.datum,
21 nvl(y.amount, 0) amount
22 from calendar c left join yours y on y.datum = c.datum
23 order by c.datum;
DATUM AMOUNT
---------- ----------
01.01.2021 100
02.01.2021 0
03.01.2021 200
04.01.2021 0
05.01.2021 0
06.01.2021 0
07.01.2021 300
7 rows selected.
SQL>
应用于您当前的查询:
WITH
minimax
AS
-- MIN and MAX date (so that they could be used in row generator --> CALENDAR CTE (below)
(SELECT MIN (created_at) min_datum, MAX (created_at) max_datum
FROM tickets),
calendar
AS
-- calendar, from MIN to MAX date in YOUR table
( SELECT min_datum + LEVEL - 1 datum
FROM minimax
CONNECT BY LEVEL <= max_datum - min_datum + 1)
-- final query uses outer join
SELECT COUNT (t.id) AS freq, TO_CHAR (TRUNC (c.datum), 'DD-MON') dates
FROM calendar c LEFT JOIN tickets t ON t.created_at = c.datum
WHERE TRUNC (t.created_at) > TRUNC (SYSDATE) - 32
GROUP BY TRUNC (c.datum)
ORDER BY dates ASC
推荐阅读
- python - sort_value 错误下降
- reactjs - 汇总 preserveModulesRoot 未按预期工作
- c++ - 是否有 STL 容器的单元测试?
- java - 使用 Kafka 连接的 Teradata 和 Apache Kafka
- node.js - 码头工人:npm 错误!缺少脚本:开始
- javascript - slickGoTo 不会更改活动幻灯片
- sql - 在 SQL Server 2012 中更改用户密码
- python - 如何使用python填写和提交表单
- node.js - promisify redis 获取并返回正确的打字稿类型
- c# - 启动过程不是从应用程序路径