我有一个表，其中包含发卡行调用服务的时间段。此表可以有重叠和不重叠的时间段：
with mht_issuer_revoked_call (issuerid, startdate, enddate) as (values
(4, to_date('25-11-2022', 'dd-mm-yyyy'), to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(4, to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2022', 'dd-mm-yyyy'),
(40, to_date('25-11-2022', 'dd-mm-yyyy'), to_date('25-11-2022 06:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 06:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 11:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 18:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 18:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 19:30:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022', 'dd-mm-yyyy'), to_date('25-11-2022 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022 11:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2022 01:30:00', 'dd-mm-yyyy hh24:mi:ss'),
(40, to_date('25-11-2022 19:31:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('26-11-2022', 'dd-mm-yyyy'),
(50, to_date('25-11-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 23:30:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022 23:30:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 23:45:00', 'dd-mm-yyyy hh24:mi:ss'),
(50, to_date('25-11-2022 23:50:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('25-11-2022 23:55:00', 'dd-mm-yyyy hh24:mi:ss')
)我设法到合并时间期间和新的时间期间没有任何相互重叠.我的输出如下：
with issuer_calls_merged (issuerid, start_date_time, end_date_time) as (values
(4 ,'11/25/2022' , '11/26/2022'),
(40 ,'11/25/2022', '11/25/2022 6:00:00 PM'),
(40 ,'11/25/2022 6:30:00 PM', '11/25/2022 7:30:00 PM'),
(40 ,'11/25/2022 7:31:00 PM', '11/26/2022' ),
(50 ,'11/25/2022', '11/26/2022 1:30:00 AM')
)我正在尝试编写一个过程，该过程获取FromDate和EndDate作为输入参数，并根据检索到的FromDate和EndDate参数计算每个发卡方未覆盖的分钟数。例如，我将给予以下参数：起始日期：= '11/20/2022'结束日期：= '11/28/2022'，则根据issuer_calls表中插入的时间段，对于issuerid 40，我预期会有以下输出：
| issuerid | start_date_time(uncovered) | end_date_time(uncovered) | uncovered_time_minutes
| 40 | 11/20/2022 | 11/25/2022 | 7200
| 40 | 11/25/2022 6:00:00 PM | 11/25/2022 6:30:00 PM | 30
| 40 | 11/25/2022 7:30:00 PM | 11/25/2022 7:31:00 PM | 1
| 40 | 11/26/2022 | 11/28/2022 | 2880型
我试着按照以下程序完成这项工作：
create or replace procedure GAP(out_res out sys_refcursor,
in_FromDate mht_issuer_revoked_call.startdate%type,
in_EndDate mht_issuer_revoked_call.enddate%type
) AS
BEGIN
**-- i tried to compare the given time period(FromDate-EndDate) with previous merged time periods and calculate the gaps and then union with previous gap**
open out_res for
select ut.issuerid,
ut.startdate,
ut.enddate,
ut.initialgap as gap
from
(
with minStartDate as
(
select r.issuerid,
min(r.startdate) as min_StartDate
from mht_issuer_revoked_call r
group by r.issuerid
)
select m.issuerid,
in_FromDate as StartDate,
case
when m.min_StartDate >= in_EndDate then in_EndDate
else m.min_StartDate
end as EndDate,
case
when m.min_StartDate >= in_EndDate then (in_EndDate - in_FromDate + 1)*24*60
else (min_StartDate - in_FromDate + 1)*24*60
end as initialgap
from minStartDate m
union all
**--- bellow part merges the time periods and calculate the gaps between them**
SELECT issuerid,
end_date_time,
next_row_start,
(next_row_start - end_date_time)*24*60 as gap
from
(
SELECT issuerid,
start_date_time,
end_date_time,
case
when lead(start_date_time) over(partition by issuerid order by start_date_time) is null then end_date_time
else lead(start_date_time) over(partition by issuerid order by start_date_time)
end as next_row_start
FROM (
SELECT issuerid,
LAG( dt ) OVER ( PARTITION BY issuerid ORDER BY dt ) AS start_date_time,
dt AS end_date_time,
start_end
FROM (
SELECT issuerid,
dt,
CASE SUM( value ) OVER ( PARTITION BY issuerid ORDER BY dt ASC, value DESC, ROWNUM ) * value
WHEN 1 THEN 'start'
WHEN 0 THEN 'end'
END AS start_end
FROM mht_issuer_revoked_call
UNPIVOT ( dt FOR value IN ( startdate AS 1, enddate AS -1 ) )
)
WHERE start_end IS NOT NULL
)
WHERE start_end = 'end'
)
where (next_row_start - end_date_time) > 0
group by issuerid,next_row_start,end_date_time
) ut
order by ut.issuerid, ut.StartDate;
END gap;但最后我没能达到上面解释的结果
2条答案
按热度按时间zzwlnbp81#
您可以只使用SQL来获取结果，并在稍后进行处理。在此答案中，FromDate和EndDate（P_FROM，P_UNTILL）设置为问题中的值。您可以将它们定义为参数或绑定变量，以便更改它们。代码中有注解。
jgovgodb2#
i think i could finally finish the job. mht_issuer_revoked_call is the name of the table where issuer requests are submitted.
With Merged_Recs as ( select issuerid, start_date_time as start_date, end_date_time as end_date FROM ( SELECT issuerid, LAG(dt) OVER(PARTITION BY issuerid ORDER BY dt) AS start_date_time, dt AS end_date_time, start_end FROM ( SELECT issuerid, dt, CASE SUM(value) OVER(PARTITION BY issuerid ORDER BY dt ASC, value DESC, ROWNUM) * value WHEN 1 THEN 'start' WHEN 0 THEN 'end' END AS start_end FROM mht_issuer_revoked_call UNPIVOT(dt FOR value IN(startdate AS 1, enddate AS - 1)) ) WHERE start_end IS NOT NULL ) WHERE start_end = 'end' ), my_tbl AS -- create CTE to prepare your data ( Select ISSUERID, ROW_NUMBER() OVER(Partition By ISSUERID Order By START_DATE) "RN", -- ordering ID events START_DATE "START_DATE", To_Char(START_DATE, 'hh24:mi:ss') "START_TIME", -- just showing the time part of START_DATE END_DATE "END_DATE", To_Char(END_DATE, 'hh24:mi:ss') "END_TIME", -- just showing the time part of END_DATE To_Date('20.11.2022', 'dd.mm.yyyy') "P_FROM", -- column with P_FROM - you could define it as bind variable To_Date('28.11.2022', 'dd.mm.yyyy') "P_UNTILL", -- column with P_FROM - you could define it as bind variable (END_DATE - START_DATE) * 24 * 60 "MINS" -- first calculation used for first and last row From ( Select * From ( -- for each ID create starting and ending row and union them with your data Select issuerid "ISSUERID", START_DATE "START_DATE", END_DATE "END_DATE" From Merged_Recs Union ALL -- row with P_FROM as START_DATE - you could define it as bind variable Select issuerid, To_Date('20.11.2022', 'dd.mm.yyyy'), Min(START_DATE) From Merged_Recs GROUP BY issuerid Union All -- row with P_UNTILL as END_DATE - you could define it as bind variable Select issuerid, Max(END_DATE), To_Date('28.11.2022', 'dd.mm.yyyy') From Merged_Recs GROUP BY issuerid) Order By ISSUERID, START_DATE ) ) select ISSUERID, START_DATE AS START_DATE, SELECTED_END_DATE AS END_DATE, MINS AS GAP_MINUTES from ( Select ISSUERID, RN, START_DATE, trunc(start_date), START_TIME, END_DATE, END_TIME, P_FROM, P_UNTILL, FIRST_VALUE(END_DATE) OVER(Partition By ISSUERID, TRUNC(START_DATE) Order By START_DATE Rows Between 1 Preceding And Current Row) as Selected_End_Date, CASE -- first and last row already calculated WHEN RN = 1 Or RN = Max(RN) OVER(Partition By ISSUERID) THEN MINS ELSE -- else --> second calculation for rows that are not first nor last Round((START_DATE - FIRST_VALUE(END_DATE)OVER(Partition By ISSUERID, TRUNC(START_DATE) Order By START_DATE Rows Between 1 Preceding And Current Row)) * 24 * 60,0) END "MINS" From my_tbl ) where mins > 0