Abfrage: Maximale Anzahl von Datensätzen in einem Zeitraum

[PhilS]

Hallo!
Ich versuche seit Stunden eine Lösung für folgendes Problem zu finden.
Es gibt einen Pool von Widgets. Deren Zugang zu und Abgang vom Pool wird mit Datum erfasst.
Ich suche eine Abfrage, mit der ich zählen kann, wie viele Widgets in einem über Anfangs-/Enddatum definierten Zeitraum maximal zur gleichen Zeit im Pool waren.

WidgetId	Zugang	Abgang
A	01.05.2019	20.05.2019
B	NULL	15.05.2019
C	16.05.2019	NULL
D	NULL	NULL

(Wenn Zugang=NULL war das Widget bereits im Pool bevor die Datumserfassung eingeführt wurde. Wenn Abgang=NULL ist das Widget jetzt noch im Pool drin.)

Für den Zeitraum 10.05.2019 bis 21.05.2019 wäre die richtige Antwort 3. (B und C waren nicht zur gleichen Zeit im Pool.)

Mein konkretes Problem besteht in einer SQL-Server-DB, aber eine Access-SQL-Lösung (ohne VBA!) sollte dort ebenfalls funktionieren.

Hier eine Beispieltabelle mit Daten für Access und SQL-Server.

Code (sql) [Auswählen] Erweitern

CREATE TABLE tblPool (
    WidgetId    varchar(10) NOT NULL PRIMARY KEY,
    Zugang        datetime NULL,
    Abgang        datetime NULL
);

Daten für SQL-Server:

Code (sql) [Auswählen] Erweitern

INSERT INTO tblPool (WidgetId, Zugang, Abgang)
    VALUES   ('A', '20190501', '20190520'),
            ('B', NULL, '20190515'),
            ('C', '20190516', NULL ),
            ('D', NULL, NULL);

Daten für Access:

Code (sql) [Auswählen] Erweitern


INSERT INTO tblPool (WidgetId, Zugang, Abgang)
SELECT WidgetId, Zugang, Abgang FROM (
    SELECT 'A' AS WidgetId, #05/01/2019# AS Zugang, #05/20/2019# AS Abgang   
                             FROM MSysObjects WHERE Name ='tblPool' UNION
    SELECT 'B', NULL, #05/15/2019#    FROM MSysObjects WHERE Name ='tblPool' UNION     
    SELECT 'C', #05/16/2019#, NULL     FROM MSysObjects WHERE Name ='tblPool' UNION
    SELECT 'D', NULL, NULL        FROM MSysObjects WHERE Name ='tblPool'
) AS x ;


PS: Wer ein konkreteres Beispiel möchte, stelle sich Autos vor, die bei der Ein- und Ausfahrt in ein Parkhaus erfasst werden. Dann die Frage: Wie viele Autos waren maximal zur gleichen im Parkhaus.

[Josef P.]

Hallo!

Gleichzeitig bedeutet, dass die Widgets im kleinst zu erfassenden Zeitraum gemeinsam vorhanden sind.
Da im Beispiel keine Uhrzeiten erfasst sind, reicht ein "Tageszeitpunkt" zum Prüfen.

Würde folgendes passen?

Code [Auswählen] Erweitern

DECLARE  @tblPool TABLE (
    WidgetId    VARCHAR(10) NOT NULL PRIMARY KEY,
    Zugang        datetime NULL,
    Abgang        datetime NULL
)

INSERT INTO @tblPool (WidgetId, Zugang, Abgang)
    VALUES   ('A', '20190501', '20190520'),
            ('B', NULL, '20190515'),
            ('C', '20190516', NULL ),
            ('D', NULL, NULL)

-- Für den Zeitraum 10.05.2019 bis 21.05.2019 wäre die richtige Antwort 3. (B und C waren nicht zur gleichen Zeit im Pool.)
DECLARE @Start datetime = '20190510'
DECLARE @Ende datetime = '20190521'

DECLARE @Zeitraum Table (Tag datetime primary key)

/*
CREATE FUNCTION [dbo].[SequenceList]
(
@Max int
) 
RETURNS @T  table (seq int)
AS
BEGIN
DECLARE @C int
SET @C=1
While @C <= @Max
BEGIN
insert into @T (seq) values(@C)
SET @C=@C+1
END
return

END
GO
*/


insert into @Zeitraum (Tag)
select
DATEADD(DAY, S.seq - 1, @Start)
from
dbo.SequenceList(DATEDIFF(DAY, @Start, @Ende)) S


select
   MAX(Cnt) as MaxCnt
from (
select
ZR.Tag, Count(distinct P.WidgetId) as Cnt
from
@Zeitraum ZR
inner join
@tblPool P ON (P.Zugang <= ZR.Tag OR P.Zugang IS NULL)
AND (P.Abgang >= ZR.Tag OR P.Abgang IS NULL)
group by
ZR.Tag
) as X

LG
Josef

[PhilS]

Hallo Josef!

Vielen Dank für diesen sehr guten Denkanstoß. – Das Ergebnis stimmt!

Dass der Bestand zu einem definierten Zeitpunkt einfach zu ermitteln ist, war mir klar. – Aber die ,,Zeitraum Frage" mit Cursor/Schleifen/etc. zu lösen, um für jeden Tag im Zeitraum den Bestand zu zählen, hatte ich erstmal aus Performancegründen geistig ausgeblendet.

In diese Kategorie fällt dein Ansatz weitgehend auch. Für lange Zeiträume wird die Performance durch die vielen Datensätze in @Zeitraum nicht ideal sein.

Beim kritischen Betrachten deiner Lösung ist mir aber eine signifikante Vereinfachung eingefallen.

Es ist überhaupt nicht nötig, die Zähl-Abfrage für jeden Tag auszuführen. Es reicht völlig aus das für @StartDate und dann für die Tage zu tun, an denen eine Bestandsveränderung stattfand; bzw. sogar nur für die, an denen eine Bestandserhöhung stattfand.

Die @Zeitraum-Tabelle kann also einfach durch eine Abfrage auf @tblPool.Zugang ersetzt werden und enthält somit auch nur die wirklich relevanten Tage. Hier die Abfrage:

Code (tsql) [Auswählen] Erweitern


DECLARE @Start datetime = '20190510'
DECLARE @Ende datetime = '20190521'

select
   MAX(Cnt) as MaxCnt
from (
select
ZR.Tag, Count(distinct P.WidgetId) as Cnt
from
(SELECT @Start AS Tag
UNION
SELECT DISTINCT Zugang FROM @tblPool WHERE Zugang BETWEEN @Start AND @Ende
) AS ZR
inner join
@tblPool P ON (P.Zugang <= ZR.Tag OR P.Zugang IS NULL)
AND (P.Abgang >= ZR.Tag OR P.Abgang IS NULL)
group by
ZR.Tag
) as X

[Josef P.]

Hallo!

Für lange Zeiträume wird die Performance durch die vielen Datensätze in @Zeitraum nicht ideal sein.

Da ein Jahr nicht mehrere tausend Tage hat, sollte das keine sehr großen Performance-Nachteile bringen. (Die Funktion in meinem ersten Beitrag ist nicht optimiert für viele Datensätze - die kann man aber verbessern. Ich würde die Zeitraumtabelle durch eine gefilterte vorhandene Kalendertabelle ersetzen.)
Wichtig ist ein Index in der Pool-Tabelle damit der join per index-Seek laufen kann.

Deine Lösung mit den relevanten Tagen wird vermutlich schneller sein - hängt ein wenig davon ab, wie schnell die distinct/union die Daten ermitteln kann.
Vor allem brauchst du keine zusätzlichen Tabellendaten für die Tage => weniger Schreiboperationen.

Tipp: Prüfe beim SQL-Server im Ausführungsplan, dass er beim Inner-Join auf der "richtigen" Seite beginnt. Ich machte schon die Erfahrung, dass gerade bei Subselcts mit Union oder Funktionen als Datenlieferant, der Ausführungsplan verbesserungswürdig war. (Mit Index-Hilfestellung ergab das dann einen erkennbaren Geschwindigkeitszuwachs.)

mfg
Josef