optymalizacja zapytań: przedziały czasowe

Zasadniczo mam dwa rodzaje przedziałów czasowych:

presence time i absence time

absence time mogą być różnego rodzaju (np. przerwy, nieobecności, specjalne dni itd.), a odstępy czasu mogą się nakładać i / lub przecinać.

To nie na pewno, że tylko prawdopodobne kombinacje odstępach istnieć w surowych danych, np. nakładające się interwały obecności nie mają sensu, ale mogą istnieć. Próbowałem zidentyfikować wynikowe interwały czasu obecności na wiele sposobów - dla mnie najwygodniejszy wydaje się następujący.

;with "timestamps"
as
(
    select
        "id" = row_number() over ( order by "empId", "timestamp", "opening", "type" )
        , "empId"
        , "timestamp"
        , "type"
        , "opening"
    from
    (
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 1 as "type" from "worktime" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
        union all
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 2 as "type" from "break" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
        union all
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 3 as "type" from "absence" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
    ) as data
)
select 
      T1."empId"
    , "starttime"   = T1."timestamp"
    , "endtime"     = T2."timestamp"
from 
    "timestamps" as T1
    left join "timestamps" as T2
        on T2."empId" = T1."empId"
        and T2."id" = T1."id" + 1
    left join "timestamps" as RS
        on RS."empId" = T2."empId"
        and RS."id" <= T1."id"      
group by
    T1."empId", T1."timestamp", T2."timestamp"
having
    (sum( power( 2, RS."type" ) * RS."opening" ) = 2)
order by 
    T1."empId", T1."timestamp";

zobacz SQL-Fiddle dla niektórych danych demonstracyjnych.

Surowe dane istnieją w różnych tabelach w postaci "starttime" - "endtime"lub "starttime" - "duration".

Chodziło o to, aby uzyskać uporządkowaną listę każdego znacznika czasu z „sumą bitów” kroczącą sumą otwartych interwałów za każdym razem, aby oszacować czas obecności.

Skrzypce działa i daje szacunkowe wyniki, nawet jeśli początki różnych przedziałów są równe. W tym przykładzie nie użyto żadnych indeksów.

Czy to właściwy sposób na wykonanie kwestionowanego zadania, czy może jest to bardziej elegancki sposób?

Jeśli ma to znaczenie dla udzielenia odpowiedzi: ilość danych wyniesie do kilku dziesięciu tysięcy zestawów danych na pracownika na tabelę. sql-2012 nie jest dostępny do obliczenia kroczącej sumy wbudowanych poprzedników.

edytować:

Właśnie wykonałem zapytanie w stosunku do większej ilości danych testowych (1000, 10 000, 100 000, 1 milion) i widzę, że czas działania rośnie wykładniczo. Oczywiście flaga ostrzegawcza, prawda?

Zmieniłem zapytanie i usunąłem agregację kroczącej sumy przez dziwaczną aktualizację.

Dodałem stolik pomocniczy:

create table timestamps
(
  "id" int
  , "empId" int
  , "timestamp" datetime
  , "type" int
  , "opening" int
  , "rolSum" int
)

create nonclustered index "idx" on "timestamps" ( "rolSum" ) include ( "id", "empId", "timestamp" )

i przeniosłem obliczanie kroczącej sumy do tego miejsca:

declare @rolSum int = 0
update "timestamps" set @rolSum = "rolSum" = @rolSum + power( 2, "type" ) * "opening" from "timestamps"

zobacz SQL-Fiddle tutaj

Czas pracy skrócił się do 3 sekund w odniesieniu do 1 miliona wpisów w tabeli „czasu pracy”.

Pytanie pozostaje takie samo : jaki jest najskuteczniejszy sposób rozwiązania tego problemu?

Nie mogę odpowiedzieć na twoje pytanie co do absolutnie najlepszego sposobu. Ale mogę zaoferować inny sposób rozwiązania problemu, który może, ale nie musi być lepszy. Ma dość płaski plan wykonania i myślę, że dobrze się sprawdzi. (Chcę się dowiedzieć, więc podziel się wynikami!)

Przepraszam za użycie własnego stylu składni zamiast twojego - pomaga mi to w tworzeniu zapytań, kiedy wszystko układa się w zwykłym miejscu.

Zapytanie jest dostępne w SqlFiddle . Wrzuciłem nakładkę na EmpID 1, żeby się upewnić, że to pokryłem. Jeśli w końcu okaże się, że nakładanie się nie może wystąpić w danych obecności, możesz usunąć ostatnie zapytanie i Dense_Rankobliczenia.

WITH Points AS (
  SELECT DISTINCT
    T.EmpID,
    P.TimePoint
  FROM
    (
      SELECT * FROM dbo.WorkTime
      UNION SELECT * FROM dbo.BreakTime
      UNION SELECT * FROM dbo.Absence
    ) T
    CROSS APPLY (VALUES (StartTime), (EndTime)) P (TimePoint)
), Groups AS (
  SELECT
    P.EmpID,
    P.TimePoint,
    Grp =
      Row_Number()
      OVER (PARTITION BY P.EmpID ORDER BY P.TimePoint, X.Which) / 2
  FROM
    Points P
    CROSS JOIN (VALUES (1), (2)) X (Which)
), Ranges AS (
  SELECT
    G.EmpID,
    StartTime = Min(G.TimePoint),
    EndTime = Max(G.TimePoint)
  FROM Groups G
  GROUP BY
    G.EmpID,
    G.Grp
  HAVING Count(*) = 2
), Presences AS (
  SELECT
    R.*,
    P.Present,
    Grp =
       Dense_Rank() OVER (PARTITION BY R.EmpID ORDER BY R.StartTime)
       - Dense_Rank() OVER (PARTITION BY R.EmpID, P.Present ORDER BY R.StartTime)
  FROM
    Ranges R
    CROSS APPLY (
      SELECT
        CASE WHEN EXISTS (
          SELECT *
          FROM dbo.WorkTime W
          WHERE
            R.EmpID = W.EmpID
            AND R.StartTime < W.EndTime
            AND W.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.BreakTime B
          WHERE
            R.EmpID = B.EmpID
            AND R.StartTime < B.EndTime
            AND B.StartTime < R.EndTime
        ) AND NOT EXISTS (
          SELECT *
          FROM dbo.Absence A
          WHERE
            R.EmpID = A.EmpID
            AND R.StartTime < A.EndTime
            AND A.StartTime < R.EndTime
        ) THEN 1 ELSE 0 END
    ) P (Present)
)
SELECT
  EmpID,
  StartTime = Min(StartTime),
  EndTime = Max(EndTime)
FROM Presences
WHERE Present = 1
GROUP BY
  EmpID,
  Grp
ORDER BY
  EmpID,
  StartTime;

Uwaga: wydajność tego zapytania zostałaby poprawiona po połączeniu trzech tabel i dodaniu kolumny wskazującej, jaki to był czas: praca, przerwa lub nieobecność.

A dlaczego wszystkie CTE, pytasz? Ponieważ każdy jest zmuszony przez to, co muszę zrobić z danymi. Istnieje agregat lub muszę ustawić warunek WHERE w funkcji okienkowania lub użyć go w klauzuli, w której funkcje okienkowania są niedozwolone.

Teraz odejdę i zobaczę, czy nie mogę wymyślić innej strategii, aby to osiągnąć. :)

Dla rozrywki dołączam tutaj „schemat”, który pomogłem rozwiązać problem:

------------
   -----------------
                ---------------
                           -----------

    ---    ------   ------       ------------

----   ----      ---      -------

Trzy zestawy myślników (oddzielone spacjami) reprezentują w kolejności: dane obecności, dane nieobecności i pożądany wynik.