Zeitraster per SQL generieren

J

Joe1968

Benutzer
Beiträge
15
Hallo zusammen,

dies ist mein erster Beitrag und vielleicht hat ja jemand eine Idee zu meinem Problem. Ich habe eine Tabelle auf meinem Server, wo Anrufzeiten geloggt werden. Ein Anruf war um 08:13:12, der nächste um 09:27:18 usw.

Ich möchte per SQL eine Tabelle ausgeben, in der es festgelegte Zeilen nach einem Zeitraster gibt, die Datensätze deren Anrufzeit in dieses Zeitraster passen, dass auszählt.

Die Ausgabe soll z.B. so aussehen:
Anrufe
08:00-08:30 3
08:30-09:00 5

...und so weiter

Ich habe es bisher nur geschafft, die Stunde mit DATEPART(hour, Anrufzeit) und einem GROUP BY zu gruppieren. Mir ist es aber ein Rätsel, wie ich das mit dem Rest hinbekomme.

Weiß jemand weiter?

Viele Grüße
Joe
 
Werbung:
Joe1968 schrieb:
Ich habe es bisher nur geschafft, die Stunde mit DATEPART(hour, Anrufzeit) und einem GROUP BY zu gruppieren. Mir ist es aber ein Rätsel, wie ich das mit dem Rest hinbekomme.

Weiß jemand weiter?

Viele Grüße
Joe
Zum Vergrößern anklicken....

Ich denke, Du suchst eine Möglichkeit, eine entsprechende Tabelle 'on the fly' zu generieren, die Du dann mit den Daten JOINen kannst. Ich verwende Postgresql und damit könnte man diese Tabelle z.B. so erstellen:

Code: 
test=# select '2014-10-29 20:00:00'::timestamp + s * '30 minutes'::interval from generate_series(0,10) s;
  ?column?
---------------------
 2014-10-29 20:00:00
 2014-10-29 20:30:00
 2014-10-29 21:00:00
 2014-10-29 21:30:00
 2014-10-29 22:00:00
 2014-10-29 22:30:00
 2014-10-29 23:00:00
 2014-10-29 23:30:00
 2014-10-30 00:00:00
 2014-10-30 00:30:00
 2014-10-30 01:00:00
(11 rows)

Ich weiß nicht, ob M$SQL was vergleichbares hat.
 
Hi Joe.
Joe1968 schrieb:
Ich möchte per SQL eine Tabelle ausgeben, in der es festgelegte Zeilen nach einem Zeitraster gibt
Zum Vergrößern anklicken....

Das kannst du zum Beispiel mit CTE erreichen:
Code: 
WITH Raster (Token)
AS (
   SELECT CAST('09:00' as time) AS Token
UNION ALL
   SELECT DATEADD(minute, 30, Token)
   FROM Raster
   WHERE Token < CAST('12:00' as time)
)
SELECT *
FROM Raster

Code: 
Token
----------------
09:00:00.0000000
09:30:00.0000000
10:00:00.0000000
10:30:00.0000000
11:00:00.0000000
11:30:00.0000000
12:00:00.0000000

Gruß
Hony
 
Hallo Hony,

danke für den Tip. Ich arbeite mich gleich mal in das Thema ein. Auf die Schnelle verstehe ich das noch nicht. Aber vermutlich bin ich auch noch nicht weit genug bei SQL. Mal sehen, wie lange ich brauche um das zu begreifen und auch sicher anwenden zu können.

Vielen Dank :)
Joe
 
Hony% schrieb:
Hi Joe.


Das kannst du zum Beispiel mit CTE erreichen:
Zum Vergrößern anklicken....


Ich hab es für PostgreSQL leicht ändern müssen:

Code: 
WITH recursive Raster (Token)
AS (
  SELECT CAST('09:00' as time) AS Token
  UNION ALL
  SELECT token + '30 seconds'::interval
  FROM Raster
  WHERE Token < CAST('12:00' as time)
)
SELECT *
FROM Raster;

Mal so als kleiner Einwurf ;-)
 
Dann zeige ich auch noch eine Version für MSSQL, die aber im wesentlichen der von Hony% entspricht. Ich geh mal davon aus das MSSQL Syntax gebraucht wird und das kann leider kein generate_series() wie PG.
Code: 
WITH raster AS (
SELECT    cast('00:00' AS TIME) AS zeit_von,
        cast('00:14:59.9999999' AS TIME) AS zeit_bis
UNION ALL
SELECT    dateadd(mi,15,zeit_von) AS zeit_von,
        dateadd(mi,15,zeit_bis) AS zeit_bis
FROM    raster
WHERE    zeit_von <= cast('23:30' AS TIME)
)
SELECT    r.zeit_von,
        r.zeit_bis,
        t.deine_zusaetzliche_spalte,
        sum(CASE WHEN t.deine_zusaetzliche_spalte IS NOT NULL THEN 1 ELSE 0 END) AS anzahl
FROM    raster r
LEFT JOIN deine_tabelle t
ON        cast(t.dein_datum_oder_zeitstempel AS TIME) BETWEEN r.zeit_von AND r.zeit_bis
GROUP BY r.zeit_von,r.zeit_bis,t.deine_zusaetzliche_spalte
OPTION (MAXRECURSION 100)
 
ukulele schrieb:
Dann zeige ich auch noch eine Version für MSSQL, die aber im wesentlichen der von Hony% entspricht.
Zum Vergrößern anklicken....

Die von mir gepostete Version/Syntax ist eigentlich für MS-SQL. Zumindest ist sie mit Express 2008 lauffähig. Bei deiner Version sind mir ein paar Sachen aufgefallen. Aber vielleicht gibt es ja einen tieferen Grund oder Eigenheiten von MS-SQL die mir nicht bekannt sind.

1. Wozu das CASE und warum SUM()?
COUNT(t.deine_zusaetzliche_spalte) sollte hier völlig reichen.

2. Das OPTION sollte überflüssig sein.
Durch die WHERE-Clause ist die CTE selbst determinierend. Sicherheitsnetz?

Hier mal eine in MS-SQL lauffähige Demo mit vollen datetime Werten:
Code: 
WITH Raster (Token)
AS (
  SELECT CONVERT(datetime, '2014-10-29 07:00', 120) AS Token
UNION ALL
  SELECT DATEADD(MINUTE, 30, Token)
  FROM Raster
  WHERE Token < CONVERT(datetime, '2014-10-29 11:30', 120)
),
LoginLog (LogTime, LoginUser)
AS (
   SELECT CONVERT(datetime, '2014-10-29 08:13:12', 120), 'User A'
UNION
   SELECT CONVERT(datetime, '2014-10-29 09:27:18', 120), 'User B'
UNION
   SELECT CONVERT(datetime, '2014-10-29 09:29:36', 120), 'User B'
)
SELECT
   CAST(Token AS time(0)) AS 'Login ab',
   CAST(DATEADD(MINUTE, 30, Token) AS time(0)) AS 'Login vor',
   COUNT(LogTime) AS Anzahl
FROM Raster
LEFT JOIN LoginLog
   ON LogTime >= Token
   AND LogTime < DATEADD(MINUTE, 30, Token)
GROUP BY Token
LoginLog ist dabei eine Mock-Tabelle und soll für die Demo das Original ersetzen. BETWEEN wäre natürlich möglich und ist Geschmackssache. Letztendlich geht es hier aber nur um Feinheiten, die in der Praxis bestenfalls einen Unterschied von wenigen Millisekunden ausmachen sollten.

Die Demo sollte dieses Ergebnis liefern:
Code: 
Login ab         Login vor        Anzahl
---------------- ---------------- -----------
07:00:00         07:30:00         0
07:30:00         08:00:00         0
08:00:00         08:30:00         1
08:30:00         09:00:00         0
09:00:00         09:30:00         2
09:30:00         10:00:00         0
10:00:00         10:30:00         0
10:30:00         11:00:00         0
11:00:00         11:30:00         0
11:30:00         12:00:00         0
 
zu 1.)
sum(CASE...) weil sonst (da LEFT JOIN) auch NULL Werte mit 1 gezählt werden. Du hast also immer, wenn kein Wert gegeben ist, 1 gezählt, kannst aber nicht pauschal 1 abziehen.

PS: Das ganze verhält sich zumindest bei mir so (auch MSSQL Express 2008) mit einer tatsächlichen Log Tabelle.
Code: 
WITH raster AS (
SELECT    cast('00:00' AS TIME) AS zeit_von,
        cast('00:14:59.9999999' AS TIME) AS zeit_bis
UNION ALL
SELECT    dateadd(mi,15,zeit_von) AS zeit_von,
        dateadd(mi,15,zeit_bis) AS zeit_bis
FROM    raster
WHERE    zeit_von <= cast('23:30' AS TIME)
)
SELECT    r.zeit_von,
        r.zeit_bis,
        t.feld,
        count(*) AS anzahl_count,
        sum(CASE WHEN t.feld IS NOT NULL THEN 1 ELSE 0 END) AS anzahl_case
FROM    raster r
LEFT JOIN unt_log t
ON        cast(datum AS TIME) BETWEEN r.zeit_von AND r.zeit_bis
GROUP BY r.zeit_von,r.zeit_bis,t.feld
OPTION (MAXRECURSION 100)
zeit_von zeit_bis feld anzahl_count anzahl_case
08:30:00.0000000 08:44:59.9999999 NULL 1 0
08:45:00.0000000 08:59:59.9999999 FK Adresse 1 1
Zum Vergrößern anklicken....
PPS: Stimmt, mit count(t.feld) wäre es nicht nötig. Mit count(*) natürlich schon - mein Fehler.

zu 2.)
Ja ich bin darauf gestoßen weil bei mir mit 100 Rekursionen i.d.R. Schluss ist. Finde die Option aber dennoch wichtig. Wenn man z.B. in der WHERE-Klausel statt bis 23:30 bis 23:45 angibt wird es unendlich rekursiv bzw. läuft ins Limit. Vieleicht wollen wir auch größere Zeiträume dafür aber eine ganze Woche abbilden, die Option kann also schnell nützlich werden.
 
Zuletzt bearbeitet:
Werbung:
@ukulele
Deswegen benutze ich COUNT(). Wie du sehen kannst wird NULL da als 0 gezählt.

Bei den Rekursionen hast du Recht. Zumindest dann wenn man mit time-Werten arbeitet und an die Tagesgrenze kommt. Daher habe ich für die Demo auf datetime zurück gegriffen. datetime ist meiner Ansicht nach auch realistischer in so einem Anwendungsfall.

Aber stimmt, für größere Zeiträume muss das Limit entsprechend nach oben gesetzt werden.
 
Zurück
Oben