복붙노트

[SQL] 시간 사이 데이터베이스에 동시 이벤트를 찾기

SQL

시간 사이 데이터베이스에 동시 이벤트를 찾기

나는 상점 전화 통화 기록이 데이터베이스가 있습니다. 각 전화 통화 기록은 시작 시간과 종료 시간을 가지고있다. 나는 우리가 우리의 전화 은행에서 사용할 전화 라인의 금액을 초과 할 경우 동시에 알기 위해 일어나고있는 전화의 최대 금액은 무엇인지 찾고 싶어요. 어떻게이 문제를 해결 갈 수 있을까?

해결법

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

    1.최대 연결 수는하는 상영 포인트로 수행 할 수 있습니다 가고 있다는 사실을 감안할 때

    최대 연결 수는하는 상영 포인트로 수행 할 수 있습니다 가고 있다는 사실을 감안할 때

    SELECT TOP 1 count(*) as CountSimultaneous
FROM PhoneCalls T1, PhoneCalls T2
WHERE
     T1.StartTime between T2.StartTime and T2.EndTime
GROUP BY
     T1.CallID
ORDER BY CountSimultaneous DESC

    쿼리는 각 통화 동시 통화의 수를 반환합니다. 그들이 하강하고 (순서없이 TOP없이 하위 쿼리)를 첫 번째 혹은 그 이상에서 SELECT MAX (CountSimultaneous를) 중 하나를 선택하기 위해.

  2. ==============================

    2.면책 조항 : 난 후 다음 (우수)를 기반으로 내 대답을 쓰고 있어요 :

    면책 조항 : 난 후 다음 (우수)를 기반으로 내 대답을 쓰고 있어요 :

    http://sqlmag.com/t-sql/calculating-concurrent-sessions-part-3 (파트 1 및 2는 시스템 권장된다)

    이 문제를 여기에 이해하는 첫 번째 일은 인터넷에서 볼 수있는 현재의 솔루션의 대부분은 기본적으로 두 가지 문제가있을 수 있다는 것입니다

    Unreasons에 의해 제안 등 솔루션의 일반적인 성능 문제는 그들이 중복되는 경우 다른 모든 통화를 확인해야하는 각 통화에 대해 이차 솔루션입니다.

    모든 "이벤트"날짜별로 정렬 (전화 및 최종 통화를 시작) 및 중단 업의 시작 및 빼기 1 일을 추가하고, 최대 기억 목록 인 algoritmical 선형 일반적인 솔루션이있다. 커서 쉽게 구현 될 수 있다는 (Hafhor에 의해 제안 된 솔루션을 그런 식으로 것 같다)하지만 커서는 문제를 해결하는 가장 효율적인 방법은 아니다.

    참조 된 기사는 훌륭한 예, 다른 솔루션, 이들의 성능 비교가 있습니다. 제안 된 솔루션입니다 :

    WITH C1 AS
(
  SELECT starttime AS ts, +1 AS TYPE,
    ROW_NUMBER() OVER(ORDER BY starttime) AS start_ordinal
  FROM Calls

  UNION ALL

  SELECT endtime, -1, NULL
  FROM Calls
),
C2 AS
(
  SELECT *,
    ROW_NUMBER() OVER(  ORDER BY ts, TYPE) AS start_or_end_ordinal
  FROM C1
)
SELECT MAX(2 * start_ordinal - start_or_end_ordinal) AS mx
FROM C2
WHERE TYPE = 1

    설명

    데이터의 집합을 가정

    +-------------------------+-------------------------+
|        starttime        |         endtime         |
+-------------------------+-------------------------+
| 2009-01-01 00:02:10.000 | 2009-01-01 00:05:24.000 |
| 2009-01-01 00:02:19.000 | 2009-01-01 00:02:35.000 |
| 2009-01-01 00:02:57.000 | 2009-01-01 00:04:04.000 |
| 2009-01-01 00:04:12.000 | 2009-01-01 00:04:52.000 |
+-------------------------+-------------------------+

    이것은 각 호출의 시작과 각각의 결말 1 substracting 1을 추가하는 쿼리와 같은 생각을 구현하는 방법입니다.

      SELECT starttime AS ts, +1 AS TYPE,
    ROW_NUMBER() OVER(ORDER BY starttime) AS start_ordinal
  FROM Calls

    C1 CTE의이 부분은 각각의 호출 번호와 그 각각 STARTTIME 걸릴

    +-------------------------+------+---------------+
|           ts            | TYPE | start_ordinal |
+-------------------------+------+---------------+
| 2009-01-01 00:02:10.000 |    1 |             1 |
| 2009-01-01 00:02:19.000 |    1 |             2 |
| 2009-01-01 00:02:57.000 |    1 |             3 |
| 2009-01-01 00:04:12.000 |    1 |             4 |
+-------------------------+------+---------------+

    이제이 코드

      SELECT endtime, -1, NULL
  FROM Calls

    행 번호없이 모든 "endtimes"를 생성합니다

    +-------------------------+----+------+
|         endtime         |    |      |
+-------------------------+----+------+
| 2009-01-01 00:02:35.000 | -1 | NULL |
| 2009-01-01 00:04:04.000 | -1 | NULL |
| 2009-01-01 00:04:52.000 | -1 | NULL |
| 2009-01-01 00:05:24.000 | -1 | NULL |
+-------------------------+----+------+

    이제 UNION을하면 전체 C1 CTE 정의를 가지고, 당신은 두 테이블이 혼합 된 것

    +-------------------------+------+---------------+
|           ts            | TYPE | start_ordinal |
+-------------------------+------+---------------+
| 2009-01-01 00:02:10.000 |    1 |             1 |
| 2009-01-01 00:02:19.000 |    1 |             2 |
| 2009-01-01 00:02:57.000 |    1 |             3 |
| 2009-01-01 00:04:12.000 |    1 |             4 |
| 2009-01-01 00:02:35.000 | -1   |     NULL      |
| 2009-01-01 00:04:04.000 | -1   |     NULL      |
| 2009-01-01 00:04:52.000 | -1   |     NULL      |
| 2009-01-01 00:05:24.000 | -1   |     NULL      |
+-------------------------+------+---------------+

    C2 정렬 새로운 컬럼 C1 넘버링 계산

    C2 AS
(
  SELECT *,
    ROW_NUMBER() OVER(  ORDER BY ts, TYPE) AS start_or_end_ordinal
  FROM C1
)

+-------------------------+------+-------+--------------+
|           ts            | TYPE | start | start_or_end |
+-------------------------+------+-------+--------------+
| 2009-01-01 00:02:10.000 |    1 | 1     |            1 |
| 2009-01-01 00:02:19.000 |    1 | 2     |            2 |
| 2009-01-01 00:02:35.000 |   -1 | NULL  |            3 |
| 2009-01-01 00:02:57.000 |    1 | 3     |            4 |
| 2009-01-01 00:04:04.000 |   -1 | NULL  |            5 |
| 2009-01-01 00:04:12.000 |    1 | 4     |            6 |
| 2009-01-01 00:04:52.000 |   -1 | NULL  |            7 |
| 2009-01-01 00:05:24.000 |   -1 | NULL  |            8 |
+-------------------------+------+-------+--------------+

    #ends이 순간에 병류 통화의 양입니다 - 마법 언제든지 #START의 결과로 발생되는 곳이있다.

    각 유형 = 1 (시작 이벤트)을 위해 우리는 3 열에서 #START 값을 가지고있다. 우리는 또한 (4 열 단위) #START +의 #end이

    #start_or_end = #start + #end

#end = (#start_or_end - #start)

#start - #end = #start - (#start_or_end - #start)

#start - #end = 2 * #start - #start_or_end

    그래서 SQL에서 :

    SELECT MAX(2 * start_ordinal - start_or_end_ordinal) AS mx
FROM C2
WHERE TYPE = 1

    전화의 제안 집합이 경우, 결과는 2입니다.

    제안 된 기사에서, 예를 들어 서비스 나 "전화 회사"또는 "전화 중심"이 아이디어는 타임 슬롯에 의해 예를 들어 그룹에 사용할 수 있습니다에 의해 그룹화 된 결과를 가지고 최대 동시성을 가지고 약간의 improvment가 특정 일의 시간에 의해 시간.

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

    3.이 시도:

    이 시도:

    DECLARE @Calls table (callid int identity(1,1), starttime datetime, endtime datetime)
INSERT @Calls (starttime,endtime) values ('6/12/2010 10:10am','6/12/2010 10:15am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 11:10am','6/12/2010 10:25am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 12:10am','6/12/2010 01:15pm')
INSERT @Calls (starttime,endtime) values ('6/12/2010 11:10am','6/12/2010 10:35am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 12:10am','6/12/2010 12:15am')
INSERT @Calls (starttime,endtime) values ('6/12/2010 10:10am','6/12/2010 10:15am')


DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='6/12/2010'
      ,@EndDate='6/13/2010'
;with AllDates AS
(
    SELECT @StartDate AS DateOf
    UNION ALL
    SELECT DATEADD(second,1,DateOf) AS DateOf
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT
    a.DateOf,COUNT(c.callid) AS CountOfCalls
    FROM AllDates           a
        INNER JOIN @Calls   c ON a.DateOf>=c.starttime and a.DateOf<=c.endtime
    GROUP BY a.DateOf
    ORDER BY 2 DESC
    OPTION (MAXRECURSION 0)

    산출:

    DateOf                  CountOfCalls
----------------------- ------------
2010-06-12 10:10:00.000 3
2010-06-12 10:10:01.000 3
2010-06-12 10:10:02.000 3
2010-06-12 10:10:03.000 3
2010-06-12 10:10:04.000 3
2010-06-12 10:10:05.000 3
2010-06-12 10:10:06.000 3
2010-06-12 10:10:07.000 3
2010-06-12 10:10:08.000 3
2010-06-12 10:10:09.000 3
2010-06-12 10:10:10.000 3
2010-06-12 10:10:11.000 3
2010-06-12 10:10:12.000 3
2010-06-12 10:10:13.000 3
2010-06-12 10:10:14.000 3
2010-06-12 10:10:15.000 3
2010-06-12 10:10:16.000 3
2010-06-12 10:10:17.000 3
2010-06-12 10:10:18.000 3
2010-06-12 10:10:19.000 3
2010-06-12 10:10:20.000 3
2010-06-12 10:10:21.000 3
2010-06-12 10:10:22.000 3
2010-06-12 10:10:23.000 3
2010-06-12 10:10:24.000 3
2010-06-12 10:10:25.000 3
2010-06-12 10:10:26.000 3
2010-06-12 10:10:27.000 3
....

    최고 1을 추가하거나 파생 테이블이 쿼리를 넣고 필요한 경우 추가를 집계.

  4. ==============================

    4.

    SELECT COUNT(*) FROM calls 
    WHERE '2010-06-15 15:00:00' BETWEEN calls.starttime AND calls.endtime

    모든 초에 대해이 과정을 반복합니다.

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

    5.다음과 같이 내가 생각할 수있는 유일한 실용적인 방법은 :

    다음과 같이 내가 생각할 수있는 유일한 실용적인 방법은 :

    당신이 임의의 "버킷", 말, 낮 24 1 시간 버킷에서 분석 할 기간을 분할합니다. 각 버킷 수를 위해 얼마나 많은 통화 중 하나를 시작하거나 시작 또는 간격의 끝 사이에 완료

    1 시간 제한은 하드 및 빠른 규칙이 아닙니다. 당신은 당신이 계산이 원하는 방법을 정확하게에 따라, 더 이상이 짧아을 만들거나 할 수있다. 당신은 평균 통화 시간의 함수 버킷의 실제 "길이"를 만들 수 있습니다. 그래서, 평균 통화 3 분이라고 가정하자. 이 계산의 측면에서 너무 비용이 경우이 정확한 결과를 제공하기 위해 세분화 충분하다 3 회 이상 평균 통화 (9 분)에 비해 사용 버킷됩니다.

  6. ==============================

    6.

    -- assuming calls table with columns starttime and endtime
declare @s datetime, @e datetime;
declare @t table(d datetime);
declare c cursor for select starttime,endtime from calls order by starttime;
open c
while(1=1) begin
  fetch next from c into @s,@e
  if @@FETCH_STATUS<>0 break;
  update top(1) @t set d=@e where d<=@s;
  if @@ROWCOUNT=0 insert @t(d) values(@e);
end
close c
deallocate c

select COUNT(*) as MaxConcurrentCalls from @t

    7. from https://stackoverflow.com/questions/3044764/finding-simultaneous-events-in-a-database-between-times by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 분에서 2 날짜 사이의 시간 차이를 계산  (0) 2020.06.28
[SQL] 어떻게 일시적으로 MySQL의에서 외래 키 제약 조건을 비활성화 할 수 있습니다?  (0) 2020.06.28
[SQL] PHP에서 "관련 항목"을 찾는 방법  (0) 2020.06.28
[SQL] SQL 쿼리 내에서 단일 인용 탈출  (0) 2020.06.27
[SQL] 오라클 SQL에 대한 파서  (0) 2020.06.27
,

티스토리툴바