TimeStamp kürzen und Group By, Distinct?

[Chrischaaan]

hallo zusammen,

ich habe ein problem mit einer sql abfrage...

ich bekomme mit der abfrage SELECT * FROM <tabellenname> WHERE ID = 3 and result_time BETWEEN <startzeit> AND <endzeit> folgendes ergebnis (ausschnitt):

Zeit 0: 2008-07-28 08:02:41.196543, temp 0: 605
Zeit 1: 2008-07-28 08:02:41.70929, temp 1: 605
Zeit 2: 2008-07-28 08:02:42.266339, temp 2: 605
Zeit 3: 2008-07-28 08:02:42.826665, temp 3: 596
Zeit 4: 2008-07-28 08:02:43.337787, temp 4: 605
Zeit 5: 2008-07-28 08:02:52.492276, temp 5: 606
Zeit 6: 2008-07-28 08:02:52.506806, temp 6: 605
Zeit 7: 2008-07-28 08:02:52.53315, temp 7: 605
Zeit 8: 2008-07-28 08:02:52.54999, temp 8: 597
Zeit 9: 2008-07-28 08:02:52.659397, temp 9: 604
Zeit 10: 2008-07-28 08:02:52.68858, temp 10: 604

die abfrage soll jetzt folgendes tun:

Zeit 0: 2008-07-28 08:02:41.196543, temp 0: 605 <- raus
Zeit 1: 2008-07-28 08:02:41.70929, temp 1: 605 <- raus
Zeit 2: 2008-07-28 08:02:42.266339, temp 2: 605 <- lassen
Zeit 3: 2008-07-28 08:02:42.826665, temp 3: 596 <- raus
Zeit 4: 2008-07-28 08:02:43.337787, temp 4: 605 <- raus
Zeit 5: 2008-07-28 08:02:52.492276, temp 5: 606 <- raus (unterschied zum vorgänger zu gering)
Zeit 6: 2008-07-28 08:02:52.506806, temp 6: 605 <- raus
Zeit 7: 2008-07-28 08:02:52.53315, temp 7: 605 <- lassen
Zeit 8: 2008-07-28 08:02:52.54999, temp 8: 597 <- lassen
Zeit 9: 2008-07-28 08:02:52.659397, temp 9: 604 <- raus
Zeit 10: 2008-07-28 08:02:52.68858, temp 10: 604 <-lassen

weiterhin besteht folgendes problem: ich habe ein diagramm, in dem ich mehrere kurven aus den abgefragten werden generieren kann. dieses diagramm benötigt allerdings immer gleiche werte auf der y-achse um die kurven übereinanderliegend darstellen zu können. jetzt müsste der also den timestamp so einkürzen, dass diese zahl nach den sekunden verschwindet (die ist ohnehin nie irgendwo gleich). und nachdem diese zeitangabe weg ist, müßte halt wieder verglichen werden ob die sekunden doppelt sind, wenn ja: raus. (doppelte sekunden wird nach der abfrage der doppelten temperaturwerte wahrscheinlich eh nicht mehr vorkommen, aber sicher ist sicher...). wenn allerdings, wie im beispiel, in einer sekunde 3 unterschiedliche werte auftreten: was kann man da am besten tun?

ich hab schon versucht nur die temperatur mit DISTINCT abzufragen, aber da schmeißt der zuviel raus. und die sache mit dem datum kürzen: keine ahnung.

ich bitte um hilfe.

bye, Christian.

[dimitri]

Hi,

dein Kriterium wann ein Satz aus der Menge genommen werden soll kann ich leider nicht nachvollziehen. Warum bleiben Zeit 2,7,8 und 10 im Ergebnis und die anderen nicht?

etzt müsste der also den timestamp so einkürzen, dass diese zahl nach den sekunden verschwindet

Das kannst mit to_char machen:
[oode]to_char(ts_feld,'DD-MM-YYYY HH24:MI:SS')[/code]

Oder auch jedes andere von dir gewünschte Format.

Dim

[Chrischaaan]

hallo,

vielen dank erstmal für deine hilfe.

ich versuchs nochmal anders zu erklären (ich bin im erklären nich so gut...)
also: pro sekunde soll ein datensatz bleiben. wenn mehrere pro sekunde sind, dann der, der sich zum vorgänger unterscheidet.


nachfolgend nochmal meine abfrage:

String startTime = "TO_TIMESTAMP('" +
				(String) txt_start_yyyy.getText() + "-" +
				(String) txt_start_mm.getText() + "-" +
				(String) txt_start_dd.getText() + " " +
				(String) txt_start_hh.getText() + ":" +
				(String) txt_start_mi.getText() + ":" + 
				(String) txt_start_ss.getText() + ".000000000'" +
				", 'YYYY-MM-DD HH24:MI:SS.FF3')";

    	String endTime = "TO_TIMESTAMP('" +
				(String) txt_end_yyyy.getText() + "-" +
				(String) txt_end_mm.getText() + "-" +
				(String) txt_end_dd.getText() + " " +
				(String) txt_end_hh.getText() + ":" +
				(String) txt_end_mi.getText() + ":" + 
				(String) txt_end_ss.getText() + ".000000000'" +
				", 'YYYY-MM-DD HH24:MI:SS.FF3')";
String query = "SELECT * FROM " + TABLE + " WHERE nodeID = " + nodeID + " AND result_time BETWEEN " + startTime + " AND " + endTime;

hier konvertiere ich ja schon nach timestamp weils in der db so drin steht...

ich hab bestimmt wieder mal nen denkfehler. entschuldigt bitte meine blöden fragen.

wenn ich vorher nach char konvertiere geht das ja mit der db wieder nicht auf. oder?

bye, Christian.

[dimitri]

pro sekunde soll ein datensatz bleiben. wenn mehrere pro sekunde sind, dann der, der sich zum vorgänger unterscheidet.

Ok. Das unterschiedet sich dann von deinem Beispiel. Hier löscht Du ja auch Zeit5.

In deinem Fall kannst Du das mit max erledigen:

SELECT spalte1,spalte2,to_char( max(to_date(ts_spalte)),'DD-MM-YYYY HH24:MI:SS')  ts_spalte
 WHERE ....
GROUP BY spalte1,spalte2

Zuerst konvertiere ich den TS in ein Date und entferne damit den ms Sekunden Anteil. Danach nehme ich mit der max -Funktion pro Gruppe den höchsten Wert und konvertiere das Ergebnis in einen String.

Dim

PS: Du solltest keinen SELECT * verwenden und ausserdem PreparedStatements und Bindvariablen verwenden.

[Chrischaaan]

die Tabellenangabe (FROM tabellenname) kommt nach "spalte 2" rein?

[dimitri]

Stimmt die hab ich vergessen.
FROM kommt immer vor WHERE. Also so:

SELECT spalte1,spalte2,to_char( max(to_date(ts_spalte)),'DD-MM-YYYY HH24:MI:SS')  ts_spalte
 FROM ...
 WHERE...

Dim

[Chrischaaan]

jetzt bekomme ich den fehler, dass ein nicht-numerisches zeichen gefunden wurde wo ein numerisches erwartet wurde. ich hab aber nur numerische zeichen eingegeben. kann es sein, dass der bei der TO_DATE funktion was falsch interpretiert?

mein kompletter abfragestring, so wie er übergeben wird, sieht jetzt so aus:

SELECT temp, TO_CHAR(MAX(TO_DATE(result_time)), 'YYYY-MM-DD HH24:MI:SS') result_time FROM mts310_results_n WHERE nodeID = 26 AND result_time BETWEEN TO_TIMESTAMP('2008-07-28 08:00:00.000000000', 'YYYY-MM-DD HH24:MI:SS.FF3') AND TO_TIMESTAMP('2008-07-28 08:03:00.000000000', 'YYYY-MM-DD HH24:MI:SS.FF3') GROUP BY result_time, temp

ich seh da erstmal keine nicht-numerischen zeichen. und da es vorher ging (ohne die to_char-geschichte) muss es wohl daher irgendwie kommen.
hast du noch eine idee?

vielen dank schonmal!

bye, Christian.

[dimitri]

Hi,

ich vermute mal, das Feld nodeid ist als varchar2 definiert. Du frägst aber mit einem nummerischen Wert ab. Deshalb versucht Oracle das zu konvertieren, indem um die Spalte ein implizites to_number gelegt wird.
Irgendwo hast dann einen Buchstaben o.ä. drinnen und to_number schlägt fehl.

Aus

WHERE nodeid = 26

wird also ein

WHERE to_number(nodeid) = 26

Entweder frägst also wirklich einen String ab:

WHERE nodeid = '26'

Oder du bereinigst die Daten und wandelst das Feld in ein NUMBER um wenn wirklich nur Zahlen dort abgelegt werden sollen.

Dim

[dimitri]

Nachtrag: Du musst nur nach temp gruppieren.
Ins GROUP BY müssen nur Spalten, die in keiner Aggregatsfunktion sind (also count,avg,sum,max,min etc).

Ausserdem hab ich auch noch einen Fehler: to_date nimmt keinen Timestamp als Parameter.

Dim

[Chrischaaan]

die nodeID wird aus einer Variablen als int (Java) Wert übergeben.

hab den to_number() befehl trotzdem nochmal ausgeführt, hat aber nichts geholfen.

[dimitri]

Probier mal dieses Statement:

SELECT   temp,
         TO_CHAR (MAX (result_time),'YYYY-MM-DD HH24:MI:SS') result_time
    FROM mts310_results_n
   WHERE nodeid = 26
     AND result_time BETWEEN TO_TIMESTAMP ('2008-07-28 08:00:00.000000000',
                                           'YYYY-MM-DD HH24:MI:SS.FF3'
                                          )
                         AND TO_TIMESTAMP ('2008-07-28 08:03:00.000000000',
                                           'YYYY-MM-DD HH24:MI:SS.FF3'
                                          )
GROUP BY temp

Dim

[Chrischaaan]

hi,

mein string sieht nun so aus:

SELECT temp, TO_CHAR(MAX(TO_DATE(result_time))) FROM mts310_results_n WHERE to_number(nodeid) = 26 AND result_time BETWEEN TO_TIMESTAMP('2008-07-28 08:00:00.000000000', 'YYYY-MM-DD HH24:MI:SS.FF3') AND TO_TIMESTAMP('2008-07-28 08:05:00.000000000', 'YYYY-MM-DD HH24:MI:SS.FF3') GROUP BY temp

bringt aber immernoch diesen nicht-numerisches-zeichen-fehler.

[dimitri]

Ich glaub jetzt hast ein Posting übersprungen ;-)

Dim

[Chrischaaan]

stimmt.
also die abfrage, die du mir gegeben hast sortiert ordentlich aus.

allerdings sind von eigentlichen 211 ergebnissen nur noch 7 übrig.
und jetzt hab ich pro minute nur noch 2 werte... also nicht / sekunde einen...

[dimitri]

Ja du hast natürlich recht. Man sollte nicht gleichzeitig arbeiten und versuchen Fragen zu beantworten

Hier also eine getestete und funktionierede Abfrage. Leider ist ein bissl Umformatierungsarbeit nötig, da erst mal der ms Anteil des Timestamps entfernt werden muss:

WITH sec_date AS(SELECT temp,TO_CHAR(result_time,'YYYY-MM-DD HH24:MI:SS') ts 
                   FROM mts310_results_n
                  WHERE nodeid=26
                    AND result_time BETWEEN TO_TIMESTAMP ('2008-07-28 08:00:00.000000000',
                                                          'YYYY-MM-DD HH24:MI:SS.FF3'
                                                         )
                    AND                     TO_TIMESTAMP ('2008-07-28 20:03:00.000000000',
                                                          'YYYY-MM-DD HH24:MI:SS.FF3'
                                                         )
               )
SELECT   a.temp,
         a.ts,
         max(a.sec_number) sec_number
    FROM (SELECT temp,ts,row_number() over (partition by ts order by temp) sec_number
           FROM sec_date          
         )a     
GROUP BY a.temp,a.ts
ORDER BY temp,ts

In der WITH AS Klausel (man nennt das Subqueryfactoring) entferne ich den ms Anteil des Timestamps und schränkte gleich auf die benötigten Daten ein.

Danach greife ich auf diese Ergebnismenge zu und nummeriere sie mit der analytischen Funktion row_number durch. D.h. alle Datensätze mit der gleichen Zeit werden von 1 beginnend durchgezählt.
Da Zwischenergebnis sieht dann so aus:

TEMP                   TS                  SEC_NUMBER             
---------------------- ------------------- ---------------------- 
1                      2008-07-28 19:50:40 1                      
1                      2008-07-28 19:50:40 2                      
1                      2008-07-28 19:50:41 1                      
1                      2008-07-28 19:50:41 2                      
1                      2008-07-28 19:50:41 3                      
1                      2008-07-28 19:50:42 1                      
1                      2008-07-28 19:50:42 2                      
1                      2008-07-28 19:50:42 3                      
2                      2008-07-28 19:50:48 1                      
2                      2008-07-28 19:50:49 1                      
2                      2008-07-28 19:50:49 2                      
2                      2008-07-28 19:50:50 1                      
2                      2008-07-28 19:50:50 2                      
2                      2008-07-28 19:50:51 1                      
3                      2008-07-28 19:50:55 1

Im eigentlichen SELECT hol ich mir dann nur noch mit MAX() den jeweils größten Wert einer Gruppe (ich hät auch MIN() verwenden können) .
Und das ist das Endergebnis:

TEMP                   TS                  SEC_NUMBER             
---------------------- ------------------- ---------------------- 
1                      2008-07-28 19:50:40 2                      
1                      2008-07-28 19:50:41 3                      
1                      2008-07-28 19:50:42 3                      
2                      2008-07-28 19:50:48 1                      
2                      2008-07-28 19:50:49 2                      
2                      2008-07-28 19:50:50 2                      
2                      2008-07-28 19:50:51 1                      
3                      2008-07-28 19:50:55 1

Dim