sql - SQL：创建按月份和类型排序的视图 [复杂]

问题描述

我正在尝试根据 Oracle PL-SQL 数据库中的审计表创建一个视图。审计表存储可以通过不同状态移动的请求的修改时间。任何一个

打开 -> 关闭

或者

打开 -> 暂停 -> 恢复 -> 关闭

请求也可以是不同类型的 A、B 或 C 下面是一些表示表格的示例数据

ID	创建时间	修改时间	关闭时间	地位	类型
1	2020 年 2 月 1 日	2020 年 1 月 1 日	无效的	打开	一个
2	2020 年 4 月 1 日	2020 年 4 月 1 日	无效的	打开	乙
3	20/01/2020	20/01/2020	无效的	打开	C
4	21/01/2020	21/01/2020	无效的	打开	一个
1	2020 年 2 月 1 日	2020 年 2 月 2 日	无效的	暂停	一个
3	20/01/2020	2020 年 1 月 3 日	2020 年 1 月 3 日	关闭	C
1	2020 年 2 月 1 日	2020 年 2 月 3 日	无效的	已恢复	一个
2	2020 年 4 月 1 日	2020 年 5 月 4 日	无效的	暂停	乙
2	2020 年 4 月 1 日	2020 年 1 月 5 日	无效的	已恢复	乙
1	2020 年 2 月 1 日	2020 年 5 月 6 日	2020 年 5 月 6 日	关闭	一个
2	2020 年 4 月 1 日	2020 年 6 月 6 日	2020 年 6 月 6 日	关闭	乙
4	21/01/2020	2020 年 8 月 6 日	2020 年 8 月 6 日	关闭	一个

我需要使用这些数据来计算每个月最后一天的指标：

开放请求总数：处于“已关闭”以外的任何状态的请求数

暂停的请求总数：处于“暂停”状态的请求数

正在进行的工作：打开的请求总数 - 暂停的请求总数

所有这些都应该按类型分组。因此，此数据的正确视图是：

最后一天	类型	开放请求总数	暂停的请求总数	工作正在进行中
2020 年 1 月 31 日	一个	2	0	2
2020 年 1 月 31 日	乙	1	0	1
2020 年 1 月 31 日	C	1	0	1
2020 年 2 月 28 日	一个	2	1	1
2020 年 2 月 28 日	乙	1	0	1
2020 年 2 月 28 日	C	1	0	1
2020 年 3 月 31 日	一个	2	0	1
2020 年 3 月 31 日	乙	1	0	1
2020 年 4 月 30 日	一个	2	0	1
2020 年 4 月 30 日	乙	1	1	0
2020 年 4 月 30 日	一个	2	0	1
2020 年 4 月 30 日	乙	1	1	0
2020 年 5 月 31 日	一个	2	0	1
2020 年 5 月 31 日	乙	1	0	1

为了进一步澄清事情，让我们考虑以下情况： | 身份证 | 创作时间 | 修改时间 | 关闭时间 | 状态 | 类型 | |----|---------------|--------|-------- ------|--------|------| | 1 | 2020 年 1 月 1 日 | 2020 年 1 月 1 日 | 空 | 已打开 | 一个 | | 1 | 2020 年 1 月 1 日 | 2020 年 4 月 4 日 | 空 | 暂停 | 一个 |

请求 1 不应仅在 2020 年 1 月 31 日算作开放，而应算作 2020 年 1 月 31 日、2020 年 2 月 28 日和 2020 年 3 月 31 日。这是因为它一直保持状态，直到它在 2020 年 4 月 4 日更改，此时审计表记录了此更改。

以类似的方式，如果上述 2 行是审计表中的唯一行，则创建的视图应该在 2020 年 1 月 31 日到今天之前一个月的最后一天之间的每个月都有一个条目，显示每个月都有 1 个暂停的请求（前三个）：

最后一天	类型	开放请求总数	暂停的请求总数	工作正在进行中
2020 年 1 月 31 日	一个	1	0	1
2020 年 2 月 28 日	一个	1	0	1
2020 年 3 月 31 日	一个	1	0	1
2020 年 4 月 30 日	一个	1	1	0
...	...	...	...	...
2021 年 5 月 31 日	一个	1	1	0

我已经制作了生成这些值的函数，但是必须修改它们才能按类型分隔，而且我也不觉得它是最有效的方法。另外值得注意的是，请求永远不会到达“关闭”阶段，它应该显示为从创建到当前月份的所有月份。如果非常感谢，任何和所有的帮助！

谢谢

标签： sqloraclesql-view

试试这个，我假设你的表名是测试：

select * from 
    (select distinct x.last_day, x.type,
        ((SELECT COUNT(1) FROM (select distinct type, id from testing t2 WHERE STATUS <> 'Closed' and t2.creation_time <= x.last_day and t2.type = x.type)) - (SELECT COUNT(1) FROM (select distinct type, id from testing t2 WHERE STATUS = 'Closed' and t2.closure_time <= x.last_day and t2.type = x.type))) open_request,
        ((SELECT count(1) FROM (select distinct type, id from testing t2 WHERE STATUS = 'Paused' and t2.creation_time <= x.last_day and t2.modification_time <= x.last_day and t2.type = x.type)) - (SELECT count(1) FROM (select distinct type, id from testing t2 WHERE STATUS = 'Resumed' and t2.creation_time <= x.last_day and t2.modification_time <= x.last_day and t2.type = x.type))) paused_request
    from (select distinct lastdaytable.LASTDAY AS LAST_DAY, t.TYPE, t.id
          from testing t, 
               (SELECT mo, lastday
                FROM (SELECT mod(ROWNUM -1, 12)+1 AS mo,
                             last_day(TO_DATE((EXTRACT(DAY FROM (select min(creation_time) from testing))|| '/'|| (mod(ROWNUM -1, 12)+1)|| '/'|| (EXTRACT(YEAR FROM (select min(creation_time) from testing)) + trunc(rownum/12))), 'DD/MM/YYYY')) lastday
                      FROM dual
                      CONNECT BY ROWNUM <= ABS(TRUNC(months_between((select min(creation_time) from testing), SYSDATE))) + 1)
                WHERE mo <= rownum) lastdaytable
    ) x
)
where (open_request > 0 or paused_request > 0)
order by last_day asc, type asc;

sql - SQL：创建按月份和类型排序的视图 [复杂]

问题描述

解决方案

推荐阅读