가능한 중복된 날짜의 총 길이를 찾기 위해 Oracle 쿼리를 작성하는 방법

가능한 중복된 날짜의 총 길이를 찾기 위해 Oracle 쿼리를 작성하는 방법

다음 작업에 대한 쿼리를 찾는 데 어려움을 겪고 있습니다.

나는 다음의 데이터를 가지고 있고 각 고유 ID에 대한 총 네트워크 일을 찾고 싶습니다.

ID  From        To          NetworkDay
1   03-Sep-12   07-Sep-12   5
1   03-Sep-12   04-Sep-12   2
1   05-Sep-12   06-Sep-12   2
1   06-Sep-12   12-Sep-12   5
1   31-Aug-12   04-Sep-12   3
2   04-Sep-12   06-Sep-12   3
2   11-Sep-12   13-Sep-12   3
2   05-Sep-12   08-Sep-12   3

문제는 날짜 범위가 겹칠 수 있고 다음과 같은 결과를 줄 SQL을 생각해 낼 수 없다는 것입니다.

ID  From        To          NetworkDay
1   31-Aug-12   12-Sep-12   9
2   04-Sep-12   08-Sep-12   4
2   11-Sep-12   13-Sep-12   3

그리고 나서.

ID  Total Network Day
1   9
2   7

네트워크 데이 계산이 불가능한 경우에는 두 번째 테이블로 이동하면 됩니다.

내 질문이 분명하길 바랍니다.

Oracle Analytics, 즉 "OVER..."를 사용할 수 있습니다."PARTITION BY"(파티션 바이) 절은 Oracle에서 이를 수행합니다.PARTITION BY 절은 집계 부분이 없는 그룹 BY와 유사합니다.즉, 행을 함께 그룹화(즉, 분할)하고 행에 대한 작업을 별도의 그룹으로 수행할 수 있습니다.각 행에 대해 작업을 수행하면 위의 이전 행의 열에 액세스할 수 있습니다.PARTITION BY가 제공하는 기능입니다. (PARTITION BY는 성능을 위한 테이블 파티셔닝과 관련이 없습니다.)

그렇다면 중복되지 않는 날짜는 어떻게 출력해야 합니까?먼저 (ID,DFROM) 필드를 기반으로 쿼리를 정렬한 다음 ID 필드를 사용하여 파티션(행 그룹)을 만듭니다.그런 다음 다음 다음과 같은 식을 사용하여 이전 행의 TO 값과 현재 행 FROM 값이 겹치는지 테스트합니다. (pseudo code에서)

 max(previous.DTO, current.DFROM) as DFROM

이 기본 식을 사용하면 중복되지 않으면 원래 DFROM 값이 반환되지만 중복되면 이전 TO 값이 반환됩니다.우리의 행은 순서가 정해져 있기 때문에 마지막 행만 신경 쓰면 됩니다.이전 행이 현재 행과 완전히 겹치는 경우에는 해당 행의 날짜 범위가 '0'이 되도록 합니다.따라서 DTO 분야에서도 동일한 작업을 수행하여 다음과 같은 이점을 얻을 수 있습니다.

max(previous.DTO, current.DFROM) as DFROM, max(previous.DTO, current.DTO) as DTO

조정된 DFROM 및 DTO 값으로 새로운 결과 세트를 생성하면 이를 집계하여 DFROM 및 DTO의 범위 간격을 셀 수 있습니다.

데이터베이스에 있는 대부분의 날짜 계산은 데이터와 같이 포함되지 않습니다.그래서 DATEIFF(dto,dfrom)와 같은 것은 d가 실제로 지칭하는 날을 포함하지 않을 것이기 때문에 우리는 먼저 하루까지 조정하고 싶습니다.

더 이상 Oracle 서버에 액세스할 수 없지만 Oracle Analytics를 사용하면 가능하다는 것을(를) 알고 있습니다.쿼리는 다음과 같이 진행됩니다. (이 작업이 완료되면 제 게시물을 업데이트해주세요.)

SELECT id, 
    max(dfrom, LAST_VALUE(dto) OVER (PARTITION BY id ORDER BY dfrom) ) as dfrom, 
    max(dto, LAST_VALUE(dto) OVER (PARTITION BY id ORDER BY dfrom) ) as dto
  from (
    select id, dfrom, dto+1 as dto from my_sample   -- adjust the table so that dto becomes non-inclusive
      order by id, dfrom
  ) sample;

여기서 비밀은 LAST_VALUE(dto) OVER(PARTITION BY id Order By dfrom) 식으로, 현재 행 이전의 값을 반환합니다.따라서 이 쿼리는 중복되지 않는 새로운 dfrom/d to 값을 출력해야 합니다.그러면 이 작업(dto-dfrom)을 하위 쿼리하고 합계를 합산하는 간단한 문제입니다.

MySQL 사용

제가 mysql 서버에 접속해서 거기서 작동시켰어요.MySQL은 Oracle과 같이 Partitioning(Analytics) 결과가 없으므로 결과 집합 변수를 사용해야 합니다.따라서 @var:=xxx 형식의 식을 사용하여 마지막 날짜 값을 기억하고 dfrom/dto를 그에 맞게 조정합니다.같은 알고리즘은 조금 더 길고 복잡한 구문입니다.ID 필드가 바뀔 때마다 마지막 날짜 값도 잊어버려야 합니다!

다음은 샘플 테이블(사용자가 가지고 있는 값과 동일한 값)입니다.

create table sample(id int, dfrom date, dto date, networkDay int);
insert into sample values
    (1,'2012-09-03','2012-09-07',5),
    (1,'2012-09-03','2012-09-04',2),
    (1,'2012-09-05','2012-09-06',2),
    (1,'2012-09-06','2012-09-12',5),
    (1,'2012-08-31','2012-09-04',3),
    (2,'2012-09-04','2012-09-06',3),
    (2,'2012-09-11','2012-09-13',3),
    (2,'2012-09-05','2012-09-08',3);

쿼리에 대해 위와 같이 그룹화되지 않은 결과 집합을 출력합니다.변수 @ld는 "last date"이고 변수 @lid는 "last id"입니다.@lid가 변경될 때마다 @ld를 null로 재설정합니다.참고로 mysql에서 := 연산자는 할당이 발생하는 곳이며 = 연산자는 동등합니다.

이것은 3단계 질의이지만, 2단계로 축소될 수 있습니다.저는 좀 더 가독성을 유지하기 위해 추가적인 외부 질의를 했습니다.가장 안쪽의 쿼리는 단순하며 dto 열을 포함하지 않도록 조정하고 적절한 행 순서를 지정합니다.중간 쿼리는 dfrom/d to 값을 중복되지 않도록 조정합니다.외부 쿼리는 단순히 사용되지 않은 필드를 삭제하고 간격 범위를 계산합니다.

set @ldt=null, @lid=null;
select id, no_dfrom as dfrom, no_dto as dto, datediff(no_dto, no_dfrom) as days from (
select if(@lid=id,@ldt,@ldt:=null) as last, dfrom, dto, if(@ldt>=dfrom,@ldt,dfrom) as no_dfrom, if(@ldt>=dto,@ldt,dto) as no_dto, @ldt:=if(@ldt>=dto,@ldt,dto), @lid:=id as id,
        datediff(dto, dfrom) as overlapped_days
  from (select id, dfrom, dto + INTERVAL 1 DAY as dto from sample order by id, dfrom) as sample
  ) as nonoverlapped
  order by id, dfrom;

위의 쿼리는 결과를 제공합니다(여기서는 중복되지 않음을 알립니다).

+------+------------+------------+------+
| id   | dfrom      | dto        | days |
+------+------------+------------+------+
|    1 | 2012-08-31 | 2012-09-05 |    5 |
|    1 | 2012-09-05 | 2012-09-08 |    3 |
|    1 | 2012-09-08 | 2012-09-08 |    0 |
|    1 | 2012-09-08 | 2012-09-08 |    0 |
|    1 | 2012-09-08 | 2012-09-13 |    5 |
|    2 | 2012-09-04 | 2012-09-07 |    3 |
|    2 | 2012-09-07 | 2012-09-09 |    2 |
|    2 | 2012-09-11 | 2012-09-14 |    3 |
+------+------------+------------+------+

홀을 제거하고 최대 간격만을 고려하여 간격을 병합하는 SQL을 구성하는 것은 어떨까요?다음과 같습니다(테스트되지 않음).

SELECT DISTINCT F.ID, F.From, L.To
 FROM Temp AS F, Temp AS L
 WHERE F.From < L.To AND F.ID = L.ID
   AND NOT EXISTS (SELECT *
                 FROM Temp AS T
                 WHERE T.ID = F.ID
                 AND F.From < T.From AND T.From < L.To
                         AND NOT EXISTS ( SELECT *
                         FROM Temp AS T1
                            WHERE T1.ID = F.ID
                            AND T1.From < T.From
                            AND T.From <= T1.To)
               )
   AND NOT EXISTS (SELECT *
              FROM Temp AS T2
              WHERE T2.ID = F.ID
              AND (
                    (T2.From < F.From AND F.From <= T2.To)
                 OR (T2.From < L.To AND L.To < T2.To)
                   )
             )

with t_data as (
    select 1 as id,
           to_date('03-sep-12','dd-mon-yy') as start_date,
           to_date('07-sep-12','dd-mon-yy') as end_date from dual
    union all
    select 1,
           to_date('03-sep-12','dd-mon-yy'),
           to_date('04-sep-12','dd-mon-yy') from dual
    union all
    select 1,
           to_date('05-sep-12','dd-mon-yy'),
           to_date('06-sep-12','dd-mon-yy') from dual
    union all
    select 1,
           to_date('06-sep-12','dd-mon-yy'),
           to_date('12-sep-12','dd-mon-yy') from dual
    union all 
    select 1,
           to_date('31-aug-12','dd-mon-yy'),
           to_date('04-sep-12','dd-mon-yy') from dual
    union all 
    select 2,
           to_date('04-sep-12','dd-mon-yy'), 
           to_date('06-sep-12','dd-mon-yy') from dual
    union all 
    select 2, 
           to_date('11-sep-12','dd-mon-yy'), 
           to_date('13-sep-12','dd-mon-yy') from dual
    union all 
    select 2, 
           to_date('05-sep-12','dd-mon-yy'), 
           to_date('08-sep-12','dd-mon-yy') from dual
),
t_holidays as (
    select to_date('01-jan-12','dd-mon-yy') as holiday
      from dual
),
t_data_rn as (
    select rownum as rn, t_data.* from t_data
),
t_model as (
    select distinct id,
           start_date
    from t_data_rn
      model
         partition by (rn, id)
         dimension by (0 as i)
         measures(start_date, end_date)
         rules
         (  start_date[for i 
                      from 1 
                        to end_date[0]-start_date[0]
                 increment 1] = start_date[0] + cv(i),
            end_date[any] = start_date[cv()] + 1
         )
      order by 1,2
),
t_network_days as (
    select t_model.*,
           case when
               mod(to_char(start_date, 'j'), 7) + 1 in (6, 7)
               or t_holidays.holiday is not null               
               then 0 else 1
           end as working_day
      from t_model
      left outer join t_holidays
        on t_holidays.holiday = t_model.start_date
)
select id, 
       sum(working_day) as network_days
  from t_network_days
 group by id;

• t_data를...-
• t_holidays이 들어 있습니다.합니다.
• t_data_rn(-합니다()rownum의 각 t_data
• t_model --t_data입니다 날짜의 입니다.
• t_network_days부터 각 합니다.t_model요일(토·일)과 휴일 목록을 기준으로 근무일 또는 주말로 지정합니다.
• final query - 각 그룹당 네트워크 일 수를 계산합니다.

언급URL : https://stackoverflow.com/questions/12315573/how-to-write-oracle-query-to-find-a-total-length-of-possible-overlapping-from-to