collapse

* Benutzer Info

 
 
Willkommen Gast. Bitte einloggen oder registrieren. Haben Sie Ihre Aktivierungs E-Mail übersehen?

* Wer ist Online

  • Punkt Gäste: 11
  • Punkt Versteckte: 0
  • Punkt Mitglieder: 0

Es sind keine Mitglieder online.

* Forenstatistik

  • stats Mitglieder insgesamt: 14660
  • stats Beiträge insgesamt: 74451
  • stats Themen insgesamt: 10018
  • stats Kategorien insgesamt: 5
  • stats Boards insgesamt: 17
  • stats Am meisten online: 933

Autor Thema: Abfrage: Maximale Anzahl von Datensätzen in einem Zeitraum  (Gelesen 1877 mal)

Offline PhilS

  • Global Moderator
  • Access-Meister
  • *****
  • Beiträge: 733
    • Tipps zu Access, VBA, SQL und Co.
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.
WidgetIdZugangAbgang
A01.05.201920.05.2019
BNULL15.05.2019
C16.05.2019NULL
DNULLNULL

 (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
CREATE TABLE tblPool (
    WidgetId    VARCHAR(10) NOT NULL PRIMARY KEY,
    Zugang        datetime NULL,
    Abgang        datetime NULL
);

Daten für SQL-Server:
Code: SQL
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
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.
 

Offline Josef P.

  • Access-Profi
  • **
  • Beiträge: 313
    • Code-Bibliothek für Access-Entwickler
Re: Abfrage: Maximale Anzahl von Datensätzen in einem Zeitraum
« Antwort #1 am: Juni 01, 2019, 08:21:23 »
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?
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

Offline PhilS

  • Global Moderator
  • Access-Meister
  • *****
  • Beiträge: 733
    • Tipps zu Access, VBA, SQL und Co.
Re: Abfrage: Maximale Anzahl von Datensätzen in einem Zeitraum
« Antwort #2 am: Juni 01, 2019, 09:41:56 »
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: T-SQL
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
 

Offline Josef P.

  • Access-Profi
  • **
  • Beiträge: 313
    • Code-Bibliothek für Access-Entwickler
Re: Abfrage: Maximale Anzahl von Datensätzen in einem Zeitraum
« Antwort #3 am: Juni 01, 2019, 09:50:50 »
Hallo!

Zitat
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
« Letzte Änderung: Juni 01, 2019, 10:48:47 von Josef P. »
 

 

Datensatzzähler in einer Abfrage

Begonnen von peterBoard Tabelle/Abfrage

Antworten: 6
Aufrufe: 16886
Letzter Beitrag Juni 16, 2010, 16:32:34
von cyorps
Abfrage für Terminüberschneidungen und visuelle Darstellung

Begonnen von centaurusBoard Tabelle/Abfrage

Antworten: 3
Aufrufe: 7764
Letzter Beitrag Januar 21, 2011, 21:30:14
von cy_one_1
Datenerfassung mit Abfrage

Begonnen von Daniel-GSBoard Tabelle/Abfrage

Antworten: 2
Aufrufe: 6584
Letzter Beitrag Mai 05, 2010, 22:52:18
von oma
SQL Abfrage DELETE rückgängig machen??

Begonnen von hmfmartinBoard Tabelle/Abfrage

Antworten: 1
Aufrufe: 9247
Letzter Beitrag Mai 08, 2010, 19:18:08
von MzKlMu
6 Mio Datensätze bei Abfrage

Begonnen von JohannaBoard Tabelle/Abfrage

Antworten: 11
Aufrufe: 10208
Letzter Beitrag Mai 19, 2010, 12:29:47
von Wurliwurm