sql - 计算每个月每个记录的差异日期时间

Question

假设我有下表。我怎样才能单独计算每个月的延迟。

Req start_dt    end_dt
1   1/2/2017    3/5/2017
2   5/2/2017    7/6/2017

我希望结果如下表所示。

Req start_dt    end_dt     delay    MM
1   1/2/2017    3/5/2017    30      1
1   1/2/2017    3/5/2017    28      2
1   1/2/2017    3/5/2017    5       3
2   5/2/2017    7/6/2017    30      5
2   5/2/2017    7/6/2017    30      6
2   5/2/2017    7/6/2017    6       7

Answer 1

您可以使用相关的分层查询来做到这一点:

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE table_name ( Req, start_dt, end_dt ) AS
SELECT 1, DATE '2017-01-02', DATE '2017-03-05' FROM DUAL UNION ALL
SELECT 2, DATE '2017-05-02', DATE '2017-07-06' FROM DUAL;

查询 1:

SELECT t.*,
       LEAST( LAST_DAY( d.COLUMN_VALUE ), t.end_dt )
         - GREATEST( d.COLUMN_VALUE, t.start_dt ) + 1 AS delay,
       EXTRACT( MONTH FROM d.COLUMN_VALUE ) AS MM
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT ADD_MONTHS( TRUNC( t.start_dt, 'MM' ), LEVEL - 1 )
             FROM   DUAL
             CONNECT BY LEVEL <= MONTHS_BETWEEN( t.end_dt, TRUNC( t.start_dt, 'MM' ) ) + 1
           ) AS SYS.ODCIDATELIST
         )
       ) d

Results :

| REQ |             START_DT |               END_DT | DELAY | MM |
|-----|----------------------|----------------------|-------|----|
|   1 | 2017-01-02T00:00:00Z | 2017-03-05T00:00:00Z |    30 |  1 |
|   1 | 2017-01-02T00:00:00Z | 2017-03-05T00:00:00Z |    28 |  2 |
|   1 | 2017-01-02T00:00:00Z | 2017-03-05T00:00:00Z |     5 |  3 |
|   2 | 2017-05-02T00:00:00Z | 2017-07-06T00:00:00Z |    30 |  5 |
|   2 | 2017-05-02T00:00:00Z | 2017-07-06T00:00:00Z |    30 |  6 |
|   2 | 2017-05-02T00:00:00Z | 2017-07-06T00:00:00Z |     6 |  7 |