sql - ORACLE - Interval between dates line by line
问题描述
Good afternoon, tell me, please, how to break the interval between dates line by line. I have a table:
with data as (
SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual)
select * from data
I want to get the result:
with result_data as (
SELECT 1 AS id, 232 AS status, to_date('21.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 232 AS status, to_date('22.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 232 AS status, to_date('23.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 232 AS status, to_date('24.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('26.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('27.08.2019') AS dte FROM dual
UNION ALL
SELECT 1 AS id, 233 AS status, to_date('28.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('21.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('22.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('24.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('25.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('26.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('27.08.2019') AS dte FROM dual
UNION ALL
SELECT 2 AS id, 259 AS status, to_date('28.08.2019') AS dte FROM dual)
select * from result_data
I tried to do through this request, but nothing worked for me
SELECT trunc(t.start_dte) + level -1 dte FROM (select * from data
where data.end_dte != to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss')) t
CONNECT BY level < trunc(t.end_dte) - trunc(t.start_dte) + 1
There are a lot of rows in the table, are there any thoughts on how to do this rationally?
解决方案
我已经在您的示例中评论了 2999 年结束的日期范围,以使结果更加清晰:
with
maxdays
as
(select max( end_dte-start_dte )+1 days from
(SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
--UNION ALL
--SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
--UNION ALL
--SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
)
),
data
as
(select level-1 l from maxdays connect by level <= days )
select id,status,start_dte+l
from data, (SELECT 1 AS id, 232 AS status, to_date('21.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('25.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 1 AS id, 235 AS status, to_date('25.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('27.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
--UNION ALL
--SELECT 1 AS id, 233 AS status, to_date('27.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 301 AS status, to_date('20.08.2019 10:47:39','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('23.08.2019 0:19:37','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
UNION ALL
SELECT 2 AS id, 264 AS status, to_date('23.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('26.08.2019 0:25:11','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
--UNION ALL
--SELECT 2 AS id, 259 AS status, to_date('26.08.2019 19:11:00','dd.mm.yyyy hh24:mi:ss') AS start_dte, to_date('31.12.2999 23:59:59','dd.mm.yyyy hh24:mi:ss') AS end_dte FROM dual
)
where l <= end_dte-start_dte
order by 2,3,1;
推荐阅读
- tensorflow - Tensorflow Object Detection API:从检查点恢复模型变量以进行推理时推理性能不佳
- python - 熊猫新列计数 id 在其他数据框中的存在
- javascript - 将现有数据库与 AdonisJS 一起使用
- python - 跨 3d numpy 数组堆叠列
- docker - Apache HTTPD 服务器启动等待时间
- netsuite - 库存调整 - 记录加载之前:创建 - 设置项目子列表
- python - 如何将十六进制字符串转换为base64字符串而不在python中编码?
- javascript - 如何在提交表单之前从 JavaScript 更改输入值
- python - 从输出表向后跟踪变量到原始表或源表
- python - Python - 对 2 个列表进行排序 - 升序和降序并返回为 2 个列表