CNT mit Bedingung

[thecrow1304]

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.

 

[akretschmer]

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.

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

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

 

[thecrow1304]

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

 

[akretschmer]

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

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.

 

[ukulele]

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!

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:

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.

 

[Tommi]

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:

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

 

[thecrow1304]

@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

 

[Tommi]

Hallo Fabian,

das in der JOIN-Klausel zu lösen ist schon korrekt. Die Syntax müsste dann wie folgt aussehen:

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:

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

 

[thecrow1304]

Hey Tommi,

die zweite Variante ist die benötigte.

Prima. Perfekt. Danke.

Fabian