복붙노트

[SQL] SQL 쿼리는 여러 날짜 범위 사이의 차이를 표시합니다

SQL

SQL 쿼리는 여러 날짜 범위 사이의 차이를 표시합니다

임은 SSRS / SQL 프로젝트를 진행하고 날짜 사이의 간격을 얻을 수있는 쿼리를 작성하려고 나는 완전히 우리가 사용하기 위해 예약 할 수있는 장치의 번호를 가지고 있고이에 보고서를 필요 this.Basically 작성하는 방법으로 손실입니다 사용하지 않을 때 표시됩니다.

나는 장치 ID, EventStart 및 EventEnd 시간으로 테이블이, 나는 각 장치에 대해 이러한 이벤트 사이의 시간을 얻을 수있는 쿼리를 실행해야하지만 난 정말 확실이 작업을 수행하는 방법을 모르겠습니다.

예를 들면 :

Device 1 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
Device 1 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`    
Device 1 Event C runs from `02/01/2012 18:00 - 02/01/2012 20:00`    
Device 2 Event A runs from `01/01/2012 08:00 - 01/01/2012 10:00`
Device 2 Event B runs from `01/01/2012 18:00 - 01/01/2012 20:00`

내 쿼리의 결과로해야한다

`Device 1 01/01/2012 10:00 - 01/01/2012 18:00`
`Device 1 01/01/2012 20:00 - 02/01/2012 18:00`
`Device 2 01/01/2012 10:00 - 01/01/2012 18:00`

어쩌면 200 (5)이 테이블의 평균 장치, - - 300 개 + 이벤트 약 4있을 것입니다.

업데이트 :

내가 너무 잘 설명하지 않은 것 같습니다 때문에 좋아 나는 좀 더 많은 정보를 줄 시도하는이 업데이트됩니다 (죄송!)

내가 처리하고하는 이벤트에 대한 세부 사항이있는 테이블, 각 이벤트는 비행 시뮬레이터의 예약입니다, 우리는 비행 심즈의 번호를 (테이블의 장치로에 대해 참조) 그리고 우리는 SSRS 보고서를 생성하려고하는 우리는 각각의 시뮬레이션이 가능한 일 / 시간을 표시하는 고객에게 제공 할 수 있습니다.

내가 시작 / 종료 날짜 매개 변수에 전달하고 그 날짜 사이의 모든 이용 가능성을 선택하기 위하여려고하고 그래서. 결과는 다음과 같이로 표시되어야합니다 :

Device   Available_From       Available_To
 1       01/01/2012 10:00    01/01/2012 18:00`
 1       01/01/2012 20:00    02/01/2012 18:00`
 2       01/01/2012 10:00    01/01/2012 18:00`

또한 이벤트 때로는 중복이 매우 희귀하고 잘못된 데이터로 인해하지만, 내가 별도로 각 장치에 대한 가용성을 알 필요가 같은 다른 장치에서 이벤트를 겹쳐 하나의 장치에서 이벤트에 대한 문제가 나던 수 있습니다.

해결법

  1. ==============================

    1.간격을 포함하는 필드를 가정하면 시작과 마침을 명명하고, 테이블 YOUR_TABLE 쿼리라는 ...

    간격을 포함하는 필드를 가정하면 시작과 마침을 명명하고, 테이블 YOUR_TABLE 쿼리라는 ...

    SELECT Finish, Start
FROM
    (
        SELECT DISTINCT Start, ROW_NUMBER() OVER (ORDER BY Start) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Start > T2.Start AND T1.Start < T2.Finish
            )
        ) T1
    JOIN (
        SELECT DISTINCT Finish, ROW_NUMBER() OVER (ORDER BY Finish) RN
        FROM YOUR_TABLE T1
        WHERE
            NOT EXISTS (
                SELECT *
                FROM YOUR_TABLE T2
                WHERE T1.Finish > T2.Start AND T1.Finish < T2.Finish
            )
    ) T2
    ON T1.RN - 1 = T2.RN
WHERE
    Finish < Start

    ... 테스트 데이터에 다음과 같은 결과를 제공합니다 :

    Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

    이 쿼리의 중요한 특성은 그것뿐만 아니라 중복 간격으로 작업을 할 것입니다.

    부질 T1 밖에 다른 구간들만 구간 시작을 받아 들인다. 부질 T2 간격 단부에서 동일하지. 이것은 중복을 제거하는 것입니다.

    별개의 두 개의 동일한 간격으로 시작 (또는 종료)를 모두 외부에있는 다른 간격으로 존재하는 경우에 중요하다. 마침 <시작은 단순히 빈 간격 (즉, 시간 0)를 제거하는 곳.

    우리는 또한 다음 단계에서 필요합니다 시간적 순서에 행 번호의 연결합니다.

    T1의 수율 :

    Start                       RN
2012-01-01 08:00:00.000     1
2012-01-01 18:00:00.000     2

    T2 수율 :

    Finish                      RN
2012-01-01 10:00:00.000     1
2012-01-01 20:00:00.000     2

    이제 우리는 하나 "활성"또는 "비활성"간격으로 재구성 할 수 있습니다.

    1 ON 절에 - 비활성 간격은 따라서, 다음의 처음 부분과 이전 구간의 함께 끝을 넣어 재구성된다. 효과적으로, 우리는 넣어 ...

    Finish                      RN
2012-01-01 10:00:00.000     1

    ...과...

    Start                       RN
2012-01-01 18:00:00.000     2

    ... 함께 결과 :

    Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000

    (액티브주기를 이용하여 T2에서 T1 함께 행에서 행으로 바꾸어 재구성 될 수 JOIN ... ON T1.RN = T2.RN 및 WHERE 되돌리기).

    여기에 조금 더 현실적인 예이다. 다음 테스트 데이터 :

    Device      Event      Start                      Finish
Device 1    Event A    2012-01-01 08:00:00.000    2012-01-01 10:00:00.000
Device 2    Event B    2012-01-01 18:00:00.000    2012-01-01 20:00:00.000
Device 3    Event C    2012-01-02 11:00:00.000    2012-01-02 15:00:00.000
Device 4    Event D    2012-01-02 10:00:00.000    2012-01-02 12:00:00.000
Device 5    Event E    2012-01-02 10:00:00.000    2012-01-02 15:00:00.000
Device 6    Event F    2012-01-03 09:00:00.000    2012-01-03 10:00:00.000

    다음과 같은 결과를 제공합니다 :

    Finish                      Start
2012-01-01 10:00:00.000     2012-01-01 18:00:00.000
2012-01-01 20:00:00.000     2012-01-02 10:00:00.000
2012-01-02 15:00:00.000     2012-01-03 09:00:00.000
  2. ==============================

    2.첫 번째 대답은 - 영업 이익 추가 추가적인 제약 마지막 하나는 아래를 참조하십시오.

    첫 번째 대답은 - 영업 이익 추가 추가적인 제약 마지막 하나는 아래를 참조하십시오.

    - 가장 최근의 endTime- 사용자와 피할 중복 후 다음 startTime을 얻을하려는 경우, 당신은 뭔가를 원하는 :

    select
    distinct
    e1.deviceId,
    e1.EventEnd,
    e3.EventStart
from Events e1 
join Events e3 on e1.eventEnd < e3.eventStart     /* Finds the next start Time */
and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd)
and not exists (select *                          /* Eliminates an e1 rows if it is overlapped */
                from Events e5 
                where e5.eventStart < e1.eventEnd
                    and e5.eventEnd > e1.eventEnd)

    당신의 세 행의 경우 경우 :

    INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')

    이것은 한 결과를 제공합니다 :

    January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800

    그러나, 나는 당신이 아마 또한의 DeviceID에 일치한다고 가정. 조인에있는 경우, 당신은 e1.DeviceId = e3.DeviceId 및 e1.deviceId = e5.deviceId을 추가 할 것

    SQL 바이올린 여기 : http://sqlfiddle.com/#!3/3899c/8

    --

    OK, 최종 편집. 다음은 쿼리 deviceIds에 추가하고 simultenously 이벤트 종료에 대한 계정에 별개의 추가는 다음과 같습니다

    SELECT distinct
    e1.DeviceID,
    e1.EventEnd as LastEndTime,
    e3.EventStart as NextStartTime
FROM Events e1 
join Events e3 on e1.eventEnd < e3.eventStart
     and e3.deviceId = e1.deviceId
     and e3.eventStart = (select min(eventStart) from Events e5
                     where e5.eventStart > e1.eventEnd
                    and e5.deviceId = e3.deviceId)
where not exists (select * from Events e7 
                    where e7.eventStart < e1.eventEnd
                      and e7.eventEnd > e1.eventEnd
                      and e7.deviceId = e1.deviceId)
order by e1.deviceId, e1.eventEnd

    는 E3의 발견에 다음 시작을 가입 할 수 있습니다. 이 현재 종료 시각 이후 최초의 STARTTIME 것을 E5 보장에 가입 할 수 있습니다. 고려 행의 종료 시간이 다른 행에 의해 중복되는 경우는 E7을 제거해에 행을 가입 할 수 있습니다.

    이 데이터의 경우 :

    INSERT INTO Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')
insert into Events values (2, '01/01/2012 09:00', '01/01/2012 11:00')
insert into Events values (2, '01/02/2012 11:00', '01/02/2012 15:00')
insert into Events values (1, '01/02/2012 10:00', '01/02/2012 12:00')
insert into Events values (2, '01/02/2012 10:00', '01/02/2012 15:00')
insert into Events values (2, '01/03/2012 09:00', '01/03/2012 10:00')

    이 결과를 얻을 :

    1   January, 01 2012 10:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 01 2012 11:00:00-0800  January, 01 2012 18:00:00-0800
2   January, 01 2012 20:00:00-0800  January, 02 2012 10:00:00-0800
2   January, 02 2012 15:00:00-0800  January, 03 2012 09:00:00-0800

    SQL 바이올린 여기 : http://sqlfiddle.com/#!3/db0fa/3

  3. ==============================

    3.나는 그것이 정말 간단하지 ...하지만 여기에 시나리오의 내 현재의 이해를 기반으로 쿼리가 있다고 가정하겠습니다 :

    나는 그것이 정말 간단하지 ...하지만 여기에 시나리오의 내 현재의 이해를 기반으로 쿼리가 있다고 가정하겠습니다 :

    DECLARE @Events TABLE (
    DeviceID INT,
    EventStart DATETIME,
    EventEnd DATETIME
)

INSERT INTO @Events VALUES (1, '01/01/2012 08:00', '01/01/2012 10:00')
INSERT INTO @Events VALUES (2, '01/01/2012 18:00', '01/01/2012 20:00')

SELECT
    e1.DeviceID,
    e1.EventEnd,
    e2.EventStart
FROM 
    @Events e1 
    JOIN @Events e2 
        ON e2.EventStart = (
            SELECT MIN(EventStart)
            FROM @Events
            WHERE EventStart > e1.EventEnd
        )
  4. ==============================

    4.이 문제를 해결합니까 :

    이 문제를 해결합니까 :

    두 번째는 더 관련성이 보인다

  5. ==============================

    5.여기에 저장 프로 시저를 포함하지 않는 난 그냥했다하는 포스트 그레스 솔루션입니다 :

    여기에 저장 프로 시저를 포함하지 않는 난 그냥했다하는 포스트 그레스 솔루션입니다 :

    SELECT minute, sum(case when dp.id is null then 0 else 1 end) as s 
FROM generate_series( 
   '2017-12-28'::timestamp,
   '2017-12-30'::timestamp,
   '1 minute'::interval
) minute 
left outer join device_periods as dp
on minute >= dp.start_date and minute < dp.end_date 
group by minute order by minute

    generate_series 함수 기간에서 분당 하나 개의 행을 가진 테이블을 생성한다. 좀 더 정확히 말하면, 1 초 간격을 변경할 수 있습니다. 그것은 포스트 그레스의 특정 기능이지만, 아마도 비슷한 다른 엔진에 존재합니다.

    이 쿼리는 당신에게 모든 가득 분, 비어있는 모든 것을 제공 할 것입니다. 당신은 외부 쿼리에서 시간, 일에 의한 캔 그룹이 쿼리를 포장하거나 필요에 따라 정확한 출력을 얻기 위해 일부 윈도우 함수 작업을 할 수 있습니다. 내 목적을 위해, 나는 단지 공백 여부가있는 경우 계산이 필요했다.

    6. from https://stackoverflow.com/questions/9604400/sql-query-to-show-gaps-between-multiple-date-ranges by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 업데이트 타임 스탬프없이 업데이트 항목  (0) 2020.06.03
[SQL] 오라클 RAC 및 시퀀스  (0) 2020.06.03
[SQL] 트위스트와 임시 데이터베이스 설계, (초안 행 VS 라이브)  (0) 2020.06.03
[SQL] 어떻게 내가 같은 테이블에서 2 개 행을 비교합니까 (SQL 서버)  (0) 2020.06.03
[SQL] 많은 인덱스가있는 테이블에 대한 느린 대량 삽입  (0) 2020.06.03
,

티스토리툴바