Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

CNT mit Bedingung

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von thecrow1304, 8 Januar 2013.

  1. thecrow1304

    thecrow1304 Benutzer

    Hi,

    ich such seit Stunden nach der Lösung, bin aber glaub grad echt betriebsblind!
    Folgendes Problem:

    Eine Tabelle (Zugriffe) spiegelt meinetwegen Datenbankzugriffe wieder:

    Mandant Timestamp Dauer
    aaa 2013-01-08 14:45:19 11
    bbb 2013-01-08 14:47:29 13
    bbb 2013-01-08 17:29:24 19
    aaa 2013-01-08 17:19:24 22

    Nun möcht ich eine Auswertung haben, die mir im Intervall von meinetwegen 1h ausgibt, wie viele Mandanten an einem Tag zugegriffen haben, und deren Zugriff max 20 sec gedauert hat. Der Mandant an sich spielt keine Rolle!

    So solls aussehen:

    Intervall Anzahl
    2013-01-08 07:00:00 - 2013-01-08 07:59:59 0
    2013-01-08 08:00:00 - 2013-01-08 08:59:59 0
    .
    .
    .
    2013-01-08 14:00:00 - 2013-01-08 14:59:59 2
    .
    .
    .
    2013-01-08 17:00:00 - 2013-01-08 17:59:59 1
    .
    .
    .

    Wie würd eine SQL aussehen? Geht das in einer einfachen Abfrage zu bewerkstelligen oder eher eine SP?

    Vielen Dank für die Hilfe.
     
  2. akretschmer

    akretschmer Datenbank-Guru


    Kennt M$SQL sowas wie generate_series() unter PostgreSQL? falls ja:

    Code:
    test=*# select * from thecrow1304 ;
     mandant |         ts          | dauer 
    ---------+---------------------+-------
     aaa     | 2013-01-08 14:45:19 |    11
     bbb     | 2013-01-08 14:47:29 |    13
     bbb     | 2013-01-08 17:29:24 |    19
     aaa     | 2013-01-08 17:19:24 |    22
    (4 rows)
    
    Time: 0,181 ms
    test=*# select start, ende, count(t) from (select '2013-01-08 00:00:00'::timestamp + s * '1hour'::interval as start, '2013-01-08 00:00:00'::timestamp + (s+1) * '1hour'::interval as ende from generate_series(0,23) s) foo left join (select * from thecrow1304 where dauer < 20) t on t.ts between foo.start and foo.ende  group by start, ende order by 1;
            start        |        ende         | count
    ---------------------+---------------------+-------
     2013-01-08 00:00:00 | 2013-01-08 01:00:00 |     0
     2013-01-08 01:00:00 | 2013-01-08 02:00:00 |     0
     2013-01-08 02:00:00 | 2013-01-08 03:00:00 |     0
     2013-01-08 03:00:00 | 2013-01-08 04:00:00 |     0
     2013-01-08 04:00:00 | 2013-01-08 05:00:00 |     0
     2013-01-08 05:00:00 | 2013-01-08 06:00:00 |     0
     2013-01-08 06:00:00 | 2013-01-08 07:00:00 |     0
     2013-01-08 07:00:00 | 2013-01-08 08:00:00 |     0
     2013-01-08 08:00:00 | 2013-01-08 09:00:00 |     0
     2013-01-08 09:00:00 | 2013-01-08 10:00:00 |     0
     2013-01-08 10:00:00 | 2013-01-08 11:00:00 |     0
     2013-01-08 11:00:00 | 2013-01-08 12:00:00 |     0
     2013-01-08 12:00:00 | 2013-01-08 13:00:00 |     0
     2013-01-08 13:00:00 | 2013-01-08 14:00:00 |     0
     2013-01-08 14:00:00 | 2013-01-08 15:00:00 |     2
     2013-01-08 15:00:00 | 2013-01-08 16:00:00 |     0
     2013-01-08 16:00:00 | 2013-01-08 17:00:00 |     0
     2013-01-08 17:00:00 | 2013-01-08 18:00:00 |     1
     2013-01-08 18:00:00 | 2013-01-08 19:00:00 |     0
     2013-01-08 19:00:00 | 2013-01-08 20:00:00 |     0
     2013-01-08 20:00:00 | 2013-01-08 21:00:00 |     0
     2013-01-08 21:00:00 | 2013-01-08 22:00:00 |     0
     2013-01-08 22:00:00 | 2013-01-08 23:00:00 |     0
     2013-01-08 23:00:00 | 2013-01-09 00:00:00 |     0
    (24 rows)
    
    Mag sein, daß Du das nicht 1:1 übernehmen kannst, aber der Fahrplan ist vielleicht erkennbar ...


    Andreas
     
  3. thecrow1304

    thecrow1304 Benutzer

    Hallo Andreas,

    ich fürchte MSSQL kennt kein generate_series(). Ggf. kennt einer der MSSQL-Profis ja eine adäquate Funktion?
    Wie könnt ich das ohne diese generate_series()-Funktion anstellen?

    Fabian
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Sicherlich machbar, sowas nachzubauen. Denke ich mal. Man kann doch sicher eigene Funktionen definieren, generate_series() braucht 2 Parameter: start und anzahl. Man kann auch 3 angebenen: zusätzlich die Schrittweite. Unten raus fällt dann einfach alles zwischen start und start+anzahl. Keine große Magie, aber sehr nützlich. Und wenn das nicht geht: für den Fall fülle einfach eine Tabelle mit den Zahlen 0-23, und verwende diese halt.

    .oO( oder halt gleich PG verwenden ... )

    Andreas

    Edit: 2. Parameter ist Ende, nicht Anzahl.
     
  5. ukulele

    ukulele Datenbank-Guru

    Du drückst dich etwas unklar aus, hier mal der Code für Anzahl aller Logins von allen Mandanten die nicht länger als 20 Sekunden waren nach Datum und Stunde gruppiert:
    Code:
    SELECT    cast(    cast(datepart(yyyy,[timestamp]) AS CHAR(4)) + '-' +
                    cast(datepart(dd,[timestamp]) AS CHAR(2)) + '-' +
                    cast(datepart(mm,[timestamp]) AS CHAR(2)) + ' ' +
                    cast(datepart(hh,[timestamp]) AS CHAR(2)) + ':00:00.000'
                ) AS DATETIME) AS [Anfang Interval],
            count(*) AS Anzahl
    FROM    tabelle
    WHERE    dauer <= 20
    GROUP BY cast(    cast(datepart(yyyy,[timestamp]) AS CHAR(4)) + '-' +
                    cast(datepart(dd,[timestamp]) AS CHAR(2)) + '-' +
                    cast(datepart(mm,[timestamp]) AS CHAR(2)) + ' ' +
                    cast(datepart(hh,[timestamp]) AS CHAR(2)) + ':00:00.000'
                ) AS DATETIME)
    Falls es wirklich nur Anzahl der Mandanten ist, müssen wir das weiter verschachteln. Denn so ja ein Mandant, der sich 2 mal einloggt auch 2 mal gezählt worden.
     
  6. Tommi

    Tommi Datenbank-Guru

    Hallo zusammen,

    ich würde hier eine zusätzliche Funktion programmieren, die mir die Zeitbetrachtung nach beliebigem interval zurückgibt, mit Start- und End-Datum.
    Eine solche Funktion würde wie folgt aussehen:

    Code:
    CREATE FUNCTION dbo.fct_Zeitraster(@DatumVon datetime, @DatumBis datetime, @Interval_Minuten int)
    RETURNS @TA TABLE
        (    Nr int IDENTITIY(1,1),
              Datum varchar(25),
              Zeit_Von varchar(25),
              Zeit_Bis varchar(25),
              DT_Von datetime,
              DT_Bis datetime
        )
    AS
    BEGIN
        -- Startzeit auf 00:00 Uhr festlegen
        SET @DatumVon = CONVERT(datetime, CONVERT(varchar(25), @DatumVon, 112), 112)
        -- Endzeit auf 23:59 Uhr festlegen
        SET @DatumBis = DATEADD(minute, -1, DATEADD(day, 1, CONVERT(datetime, CONVERT(varchar(25), @DatumBis, 112), 112)))
     
        WHILE @DatumVon < @DatumBis
        BEGIN
              INSERT INTO @TA (Datum, Zeit_Von, Zeit_Bis, DT_Von, DT_Bis)
              SELECT CONVERT(varchar(25), @DatumVon, 108) as Datum,
              CONVERT(varchar(25), @DatumVon, 108) as Zeit_Von,
              CONVERT(varchar(25), DATEADD(minute, @Interval_Minuten-1, @DatumVon) as Zeit_Bis,
              @DatumVon as DT_Von,
              DATEADD(minute, @Interval_Minuten-1, @DatumVon) as DT_Bis
     
              SET @DatumVon = DATEADD(minute, @Interval_Minuten, @DatumVon)
        END
    END
    
    Mit dieser Funktion bilde ich im Prinzip die von Andreas genannte Funktion "generate_series" ab, die es auf dem SQL Server so nicht gibt.
    Mit den folgenden Select kann ich dan das Ergebnis von Andreas nachbilden, wobei ich hier ein festes Interval von 60 Minuten angegeben habe

    SELECT Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr, COUNT(T.Mandant) as Anzahl
    FROM dbo.fct_Zeitraster(GETDATE(), GETDATE(), 60) Z
    LEFT OUTER JOIN Tabelle1 T
    ON T.TimeStamp BETWEEN Z.DT_Von AND Z.DT_Bis
    GROUP BY Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr



    Wenn ein Mandant immer nur einmal im Zeitraum gezählt werden soll, egal wie viele Zugriffe er gemacht hat, kann man dies mit dem Aggregat "COUNT(DISTINCT T.Mandant) abbilden.

    Viele Grüße,
    Tommi
     
  7. thecrow1304

    thecrow1304 Benutzer

    @Tommi
    Genau das hab ich gebraucht.

    Nun benötige ich noch die Anzahl der Mandanten die
    a) innerhalb des abgegrenzten Zeitraums Zugriff hatten (siehe dein Beispiel)
    b) innerhalb des abgegrenzten Zeitraums max. 20 sek Zugriff hatten.

    Ich habe versucht das über eine zweite JOIN-Klausel zu realisieren...leider erfolglos:

    SELECT Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr, COUNT(T.Mandant) as Anzahl, COUNT(M.Mandant) as Anzahl_u20
    FROM dbo.fct_Zeitraster(GETDATE(), GETDATE(), 60) Z
    LEFT OUTER JOIN Tabelle1 T
    ON T.TimeStamp BETWEEN Z.DT_Von AND Z.DT_Bis
    LEFT OUTER JOIN (SELECT TimeStamp,Mandant FROM Tabelle1 WHERE Zugriff <=20) M
    ON M.TimeStamp BETWEEN Z.DT_Von AND Z.DT_Bis
    GROUP BY Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr


    Mfg

    Fabian
     
  8. Tommi

    Tommi Datenbank-Guru

    Hallo Fabian,

    das in der JOIN-Klausel zu lösen ist schon korrekt. Die Syntax müsste dann wie folgt aussehen:
    Code:
    SELECT Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr, COUNT(T.Mandant) as Anzahl_u20
    FROM dbo.fct_Zeitraster(GETDATE(), GETDATE(), 60) Z
    LEFT OUTER JOIN Tabelle1 T
         ON T.TimeStamp BETWEEN Z.DT_Von AND Z.DT_Bis
         AND T.Dauer<20
    GROUP BY Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr
    
    Es gibt natürlich noch eine weitere Lösung, mit der du die Gesamtzahl der Zugrife UND die Anzahl der Zugriffe mit einer Zugriffsdauer<20 ermitteln kannst. Das sieht dann so aus:
    Code:
    SELECT Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr, COUNT(T.Mandant) as Anzahl,
    COUNT(CASE WHEN T.Dauer<20 THEN T.Mandant ELSE NULL END) as Anzahl_u20
    FROM dbo.fct_Zeitraster(GETDATE(), GETDATE(), 60) Z
    LEFT OUTER JOIN Tabelle1 T
          ON T.TimeStamp BETWEEN Z.DT_Von AND Z.DT_Bis
    GROUP BY Z.Datum, Z.Zeit_Von, Z.Zeit_Bis, Z.Nr
    

    Viele Grüße,
    Tommi
     
  9. thecrow1304

    thecrow1304 Benutzer

    Hey Tommi,

    die zweite Variante ist die benötigte.

    Prima. Perfekt. Danke.

    Fabian
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden