DB-Abfrage Join mit Dual für Ausgabe Zeitpunkt

[BodyLove]

Hi,

ich habe diese Abfrage mir zusammengestellt.


select table_a, zeitpunkt, num, round(num/60,2) as TPS from (
select count(*) as num, zeitpunkt from
(select to_char(anlagezeit, 'IYYY-MM-DD HH24:MI') as zeitpunkt from table_a)
where zeitpunkt >= '18.06.2013'
group by zeitpunkt) order by zeitpunkt desc;


Das zeigt mir an, wieviele Datensätze angelegt wurden. Jetzt ist mein Problem, dass wenn kein Datensatz angelegt wurde, auch nichts angezeigt wird (verständlicherweise). Für meine Reports ist das aber nicht so gut, daher würde ich gerne mit Dual joinen um z.B. für ein Zeitpunkt, wo nichts angelegt wurde den Wert 0 zu erhalten.

2013-06-18 13:40 1
2013-06-18 13:41 0
2013-06-18 13:42 0
2013-06-18 13:43 4
etc.

Aktuelle Ausgabe ist:
2013-06-18 13:40 1

2013-06-18 13:43 4


Ist sowas möglich über SQL?

[Matrix316]

Geht net, sag ich jetzt mal so spontan, weil man ja nicht weiß, was ein "Zeitpunkt" überhaupt ist.

Wenn der Zeitpunkt 13:41 nirgendwo steht, kann man ihn ja nicht anzeigen. Man bräuchte eine Tabelle wo alle möglichen Zeitpunkte schon drinnen stehen und zu dieser könnte man die Anzahl aus table_a holen.

Und dann sowas wie:

Select Datum, Zeitpunkt,
(select count(*) from table_a where Zeitpunkt = a.Zeitpunkt and Datum = a.Datum) as Anzahl
from lookuptabelle a

lookuptabelle:
Datum Zeitpunkt
2013-06-18 13:40
2013-06-18 13:41
2013-06-18 13:42
2013-06-18 13:43
Und als Ergebnis haste dann dein Datum,Zeitpunkt,Anzahl, auch wenns 0 ist.

[BodyLove]

Genau, das denke ich auch. Kann man evtl. mit Dual ein Join durchführen und im Loop minuten ausgeben?

Das soll ja ein Report sein, sprich, das Statement erfolgt nachträglich, nciht in Echtzeit.

[Matrix316]

Dual sagt mir jetzt nix. Was für ein SQL Server ist das eigentlich? Ist das Oracle?

in MSSQL kannste auch eine temporäre Tabelle anlegen:

CREATE TABLE #TemptableX (
Datum smalldatetime null
)

und dann mit

insert into #TemptableX diese füllen. Eventuell vor dem Create ein Drop Table machen.


IF OBJECT_ID('tempdb..#TemptableX') IS NOT NULL
BEGIN
drop table #TemptableX
END


und dann die Abfrage drum bauen.

[BodyLove]

Ist leider ein Oracle. Dual ist auch eine temporäre Tabelle.

Was wäre es mit einer Schleife mit dem aktuellen Timestamp, welches nach einem join diesen runterzählt?

[Matrix316]

Zeig doch mal wie du das schreiben würdest.

[BodyLove]

Ich habe keine Ahnung, überall gibt es eine offene Frage. Da ich durch das Select-Statement keine temporäre Tabelle generiere, kann auch dieses vorgehen nicht funktionieren.

Im Prinzipp will ich der Anwendung nur sagen, dass wenn es nicht in der Tabelle unter zeitpunkt findet, er entsprechend die Minuten runter zählen und eine 0 in num eintragen soll.

Ich dachte da an sowas wie:

Pseudocode:


ältester Timestamp = xx
kontroll Timestamp = ältester Timestamp
neuester Timestamp = yy

while kontrollTS<neuesterTS

wenn KontrollTS in
select zeitpunkt as TPS from (
select count(*) as num, zeitpunkt from
(select to_char(anlagezeit, 'IYYY-MM-DD HH24:MI') as zeitpunkt from table_a)
where zeitpunkt >= '18.06.2013'
group by zeitpunkt) order by zeitpunkt desc;

then
kontroll Timestamp +1 min
Sonst
Insert KontrollTS kraftst
...

[Berni]

Wenn du eine Zeitentabelle hast wo jeder Timestamp drinsteht kannst du doch mit nem LEFT JOIN dann deine Zählungen dazujoinen und dann darüber für jeden Timestamp einen Satz kriegen. Oder verstehe ich da was falsch?

Solltest du keine solche Zeitentabelle haben könntest du diese auch generieren über ein rekursives SQL (=>google) wobei das sicherlich performancetechnisch nicht unbedingt optimal sein wird...

[BodyLove]

genau sowas wäre denkbar. Allerdings müsste ich diese Tabelle mit den Timestamps selbst erstellen. Könnte man evtl. auach eine Schleife erstellen, und für jede Minute, welches runtergezählt wird ein einfaches Selectstatement starten?

[Gast]

Wie und wo wertest du denn das Resultat von deinem Select-Statement aus? Sitzt da jemand mit direktem DB-Zugriff und will das wirklich das blanke Ergebnis des SQL-Statements sehen, oder wird das von einem Programm aufgerufen und dann verarbeitet/angezeigt?

[BodyLove]

nein, geplant war es, nach einem Test über Nacht anhand des Statements über den SQL-Developer am nächsten Morgen ein Report zu ziehen, wieviele Datensätze in der Minute angelegt wurden.

[Matrix316]

Ob ein Left outer join funktioniert, weiß ich auch net ganz, wegen dem "Count", was man dazujoinen muss. Deswegen würde ich das mit einem Subselect machen.

Leg doch eine Zieltabelle an mit allen möglichen Zeitpunkten. Dann morgens machste ein Update auf die Tabelle mit den Anzahl der Werten und dann über ein Select auf diese Tabelle kriegst du die Werte.

[Berni]

Ich habe kein Oracle hier (arbeite normal mit DB2) daher möglicherweise paar Fehler drin welche so nicht funktionieren aber an sowas in der Art hätte ich gedacht:
WITH zeiten (ts) AS (
SELECT '2013-06-18 00:00:00'
UNION ALL
SELECT ts + interval '1' minute
FROM zeiten
WHERE ts < '2013-06-18 23:59:59'
), anzahlen AS (
SELECT count(*) As num, anlagezeit
FROM table_a
WHERE zeitpunkt >= '2013-06-18 00:00:00'
AND zeitpunkt < '2013-06-18 23:59:59'
GROUP BY anlagezeit
)

SELECT Coalesce(anzahlen.anlagezeit, zeiten.ts) As anlagezeit,
Coalesce(anzahlen.num, 0) As Anzahl
FROM zeiten
LEFT JOIN anzahlen
/* Ich gehe hier davon aus, dass sich die Timestamps evtl. etwas im Sekundenbereich unterscheiden und trotzdem matchen sollen; wenn das nicht der Fall ist kann mans auch direkt joinen */
ON anzahlen.anlagezeit >= zeiten.ts
AND anzahlen.anlagezeit < zeiten.ts + interval '1' minute

[Asaraki]

Berni hat's schon aufgezeigt - ich hätte dafür ja Stunden xD

Trotzdem die ganz blöde Frage : Warum willst du das zwangsläufig schon im SQL tun?
A) wird das übelst lange laufen gemessen daran, wieviele echte Daten du eigentlich extrahierst. Spielt natürlich in vielen Umfelden keine Rolle, aber ich komme vom Hintergrund wo CPU-Time bezahlt werden muss ^^

B) wenn du das in einem zweiten Step machst mit ein bisschen Code bist du viel flexibler und musst auch das vergleichsweise 'hässliche' SQL-Statement nicht ständig anpassen.

Denn eigentlich willst du so gut wie nie Werte per SQL lesen, die nicht zumindest ein Produkt aus vorhandenen Columns ist. Daten 'adden' sollte man später, damit der SQL-Output clean bleibt.

Alternativ mach dir doch eine kleine Tabelle und lass dort per Trigger was inserten wenn auf deiner Maintable ein insert passiert. Dann hast du einen (vermutlich) Index-Only Zugriff für den Report und hast die volle Flexibilität bei der Auswertung, kannst den theoretisch sogar live anbieten da dies keine Performance mehr kosten wird. Trigger können auch sehr sophisticated gebaut werden, aber da wär ich erstmal vorsichtig, denn unsaubere Trigger können ganze DB Systeme töten :)

Ob ein Left outer join funktioniert, weiß ich auch net ganz, wegen dem "Count", was man dazujoinen muss. Deswegen würde ich das mit einem Subselect machen.

Leg doch eine Zieltabelle an mit allen möglichen Zeitpunkten. Dann morgens machste ein Update auf die Tabelle mit den Anzahl der Werten und dann über ein Select auf diese Tabelle kriegst du die Werte.

Das wäre zwar sehr performant im der schlussendlichen Auswertung, aber man kann nicht für jeden Report eine Tabelle erstellen - imho. Ist natürlich eine Frage der Prioritäten, wenn dieser Report das nonplusultra ist, dann her damit.

[Matrix316]

Ich dachte auch eher, dass der Report jeden Tag neu gemacht wird oder so. Man kann ja eine historische Sicherung machen, wo man vorher die Daten vom letzten Tag abspeichert.

[Asaraki]

Ich dachte auch eher, dass der Report jeden Tag neu gemacht wird oder so. Man kann ja eine historische Sicherung machen, wo man vorher die Daten vom letzten Tag abspeichert.

Sorry, hab ich mich vielleicht missverständlich ausgedrückt. Ich meinte pro "Report" (also nicht pro Tag) eine Tabelle, geht auch nur, wenn eben dieser exakte Report so wichtig ist. Sonst geb ich dir Recht, wenn das sogar gewünscht ist, würde das alles viel viel einfacher machen.

Ansonsten würd ich da auf einen einfachen SQL und ein bisschen script/batch zurückgreifen.