Anzeige:
Ergebnis 1 bis 7 von 7

Thema: SQL: Frage zum erstellen einer Query

  1. #1
    Registrierter Benutzer
    Registriert seit
    22.12.2003
    Beiträge
    19

    SQL: Frage zum erstellen einer Query

    Hallo,
    ich bin gerade dabei ein Programm zu schreiben, welches auf eine SQL-Datenbank zugreift. Es funktioniert auch alles, nur leider werden soviele Queries erzeugt, dass die Geschwindigkeit schon ziemlich leidet.
    Das Programm soll aus einer Tabelle die aussieht wie folgt:
    timestamp | wert
    1 | 20
    2 | 30
    3 | 10
    4 | 40
    5 | 30
    6 | 200
    usw.

    immer z.B. die ersten 5 Werte zusammenfassen d.h. aus diesen Werten den maximalen Wert nehmen, so dass die Tabelle so aussieht:

    1 | 40
    usw.

    Nun soll es aus diesen resultierenden Werten den Durchschnitt bei gegebener timestamp bilden. So dass, wenn ich z.B. den Durchschnitt fuer timestamp 5 anfordere mit einem Intervall von 3, der Wert von 5,4,3 genommen wird, alles addiert wird, durch 3 geteilt und zurueckgeliefert.

    Puuuh ich hoffe ich hab mich bis jetzt einigermassen verständlich ausgedrückt

    Ich habe das ganze nun so programmiert:

    PHP-Code:
    fasseimmer5Wertezusammen(timestamp)
    {
         
    query:hole 5 werte und ermittle das maximum
         
    return maximalwert
    }

    ermittledenDurchschnitt(timestampintervall)
    {
         for(
    int i=timestamp-intervalli<=timestampi++)
         {
               
    ergebnis+=fasseimmer5Wertezusammen(i)
         }
       return 
    ergebnis/i

    Wie ihr euch vorstellen koennt braucht das ganze queries ohne Ende und laeuft grotten-langsam.
    Nun meine eigentliche Frage: kann man das "5-werte zusammenfassen" und "aus mehreren dieser 5er-Werte den Durschnitt bilden" nicht in eine einzige Query verpacken?

    Hoffe ich hab mich klar genug ausgedrückt, dass mit jemand helfen kann auf jedenfall vielen Dank schonmal...

  2. #2
    Registrierter Benutzer
    Registriert seit
    02.11.2005
    Beiträge
    16
    Also für das Zusammenfassen könntest du folgendes probieren:
    Code:
    SELECT (int(id/5)) AS nr, max(wert) as max
    FROM tabelle
    GROUP BY (int(id/5));
    Damit fasst du immer 5 Werte zu einer Gruppe zusammen und bestimmst von dieser das Maximum. Deine Id's sollten dann aber mit 0 oder einem Vielfachen von 5 beginnen, sonst ist die erste Gruppe kleiner.
    Eventuell musst du die Funktionen an deine SQL-Syntax anpassen.

    Der zweite Teil ist mir nicht ganz klar. In deinem Beispiel rufst du fasseImmer5WerteZusammen() mit 2, 3 und 4 auf.
    Soll bei 2 das Maximum des 2. 5er Blockes (also 6-10) oder das Maximum der nächsten 5 Zahlen ab 2 (also 2-6) zurückgegeben werden? Poste doch bitte mal die SELECT-Anweisung.

    Gruß,
    Paul
    Geändert von paule (19-09-2006 um 19:39 Uhr)

  3. #3
    Registrierter Benutzer
    Registriert seit
    22.12.2003
    Beiträge
    19
    also die SELECT-Anweisung ist bei mir ein bisschen fragmentiert, da die tabelle aus mehreren Werten und einer timestamp besteht und ich, wenn nichts gelesen werden konnte (keine Werte zur timestamp vorhanden), das ganze mit 0en auffuellen moechte(dazu wird das open gesucht, wenn das nicht vorhanden ist, wird davon ausgegangen, dass keine Werte zu der timestamp vorliegen) trotzdem mal der code:

    Code:
    ohlc *Datenbank::giveOHLC(string Symbol, int timestamp, int type)
    {
      timestamp=timestamp-timestamp%(type*60);
      ohlc *OHLC = new ohlc;
      work action(*Conn);
      result r;
      bool open=false;
      bool close=false;
      for(int timebuf=timestamp; timebuf <= timestamp+type*60; timebuf+=60)
        {
          //Schleife zur Suche des Open
          r=action.exec("SELECT open FROM \"" + Symbol + "_3\" WHERE timestamp=" + IntToString(timebuf) + ";");
          if(r.size()!=0)
    	{
    	  if(r[0]["open"].to(OHLC->open))
    	    {
    	      timebuf=timestamp+type*60+60;
    	      open=true;
    	    }
    	}
        }
    
      if(open)
        {
          for(int timebuf=timestamp+type*60; timebuf >= timestamp; timebuf=timebuf-60)
    	{
    	  //Schleife zur Suche des Close
    	  r=action.exec("SELECT close FROM \"" + Symbol +"_3\" WHERE timestamp=" + IntToString(timebuf) + ";");
    	  if(r.size()!=0)
    	    {
    	      if(r[0]["close"].to(OHLC->close))
    		{
    		  timebuf=timestamp-60;
    		  close=true;
    		}
    	    }
    	}
          //High/Low holen
          r=action.exec("SELECT max(high), min(low) FROM \"" + Symbol + "_3\" WHERE timestamp > " + IntToString(timestamp-60) + "AND timestamp<" + 
    		    IntToString(timestamp+type*60+60) + ";");  
          if(!r[0]["max"].to(OHLC->high))
    	OHLC->high=0;
          if(!r[0]["min"].to(OHLC->low))
    	OHLC->low=0;
    
          //Volumen holen
          r=action.exec("SELECT sum(volume) FROM \"" + Symbol + "_3\" WHERE timestamp > " + IntToString(timestamp-60) + " AND timestamp<" + 
    		    IntToString(timestamp+type*60+60) + ";");
          if(!r[0]["sum"].to(OHLC->volume))
    	OHLC->volume=0;
    
          OHLC->timestamp=timestamp;
        }
      else
        {
          OHLC->timestamp=0;
          OHLC->open=0;
          OHLC->close=0;
          OHLC->high=0;
          OHLC->low=0;
          OHLC->volume=0;
        }
      action.commit();
      return OHLC;
    }
    diese Methode erzeugt bei mir im Moment immer 5er-Paeckchen (wenn timestamp nicht vorhanden ist, wird der Wert, welcher zurueck geliefert wird mit 0en aufgefuellt), welche spaeter von einer anderen Methode abgefragt werden und verarbeitet (es werden x-viele 5-er-Paeckchen nach einander abgefragt und aus ihnen der Durchschnitt errechnet).
    Die Methode ist so geschrieben, dass type angibt aus wieviel timestamps ein Paeckchen gebildet wird.
    Die Schleife in der Methode, springt sozusagen immer von 5er-Paecken zu 5er-paecken und bestimmt die minima/maxima, summen. Fuer jeden Sprung ist halt eine SQL-Query faellig, deshalb ist es so langsam. Ist es moeglich so einen Schleife in eine SQL-Query zu packen?

    Ich hoffe das hilft weiter. Ich versuch nun mal deinen SQL-Code zu verstehen (bin noch ziemlicher SQL-Neuling) und bei mir einzubauen, Danke!
    Geändert von McRip (19-09-2006 um 20:46 Uhr)

  4. #4
    Registrierter Benutzer
    Registriert seit
    02.11.2005
    Beiträge
    16
    Also spontan:
    Die beiden Abfragen für High/Low und Volumen kannst du ja schon mal zusammenfassen, wenn ich da jetzt nix übersehen habe.

    Desweiteren brauchst du die beiden Schleifen nur, um Timestamps ohne Werte zu finden, richtig? Wenn du dir nun noch die Anzahl der betroffenen Datensätze zwischen den Timestamps ausgibst, kannst du doch diese mit type vergleichen.
    Code:
    SELECT max(high), min(low), sum(volume), count(*) FROM .. WHERE ..
    Sind also zu allen Timestamps Werte vorhanden, ist r[0]["count"] gleich type, ansonsten kleiner.

    Zu meinem ersten Post:
    GROUP BY zerlegt deine Tabelle nach dem angegebenen Kriterium in Gruppen.
    Wenn du zum Beispiel eine Tabelle personen(id, name, hobby) hast, dann bildet:
    Code:
    SELECT count(*) as anzahl, hobby FROM personen GROUP BY hobby;
    für jedes Hobby eine Gruppe und zählt die darin enthaltenen Personen. Pro Gruppe wird eine Ergebniszeile mit der Anzahl erzeugt. Bei mir habe ich als Kriterium nun einfach die Id genommen. Die Berechnung (eine ganzzahlige Division) sorgt lediglich dafür, dass immer n aufeinanderfolgende Paare in einer Gruppe landen.

    Gruß,
    Paul
    Geändert von paule (19-09-2006 um 22:16 Uhr)

  5. #5
    Registrierter Benutzer
    Registriert seit
    22.12.2003
    Beiträge
    19
    ahaa
    ich habe dein Beispiel (SELECT (int(id/5)) AS nr, max(wert) as max
    FROM tabelle
    GROUP BY (int(id/5))
    bei mir mal ausprobiert unter postgresql und bekomme einen Syntax-error bei der ersten Klammer bei (int(id/5)). Kann es sein, dass das von postgre nicht unterstützt wird?
    Ich habe das ganze auch mal mit id/5 statt (int(id/5)) ausprobiert, was mir aber nur die timestamps jeweils durch 5 geteilt auflistet. Hast du da vielleicht noch eine Idee?
    VIELEN Dank schonmal bis jetzt!!

  6. #6
    Registrierter Benutzer
    Registriert seit
    02.11.2005
    Beiträge
    16
    Ich habe doch geschrieben, dass du die Funktionen anpassen musst, da diese in jeder SQL-Implementierung anders heißen. Und ohne int kann es ja nicht funktionieren, da bei id/5 für jede Id eine andere Zahl herauskommt und er so keine Gruppen bilden kann.

    Schau dir folgendes an:
    Code:
    SELECT id, id/5, int(id/5) FROM tabelle;
    Herauskommt so etwas:
    id id/5 int(id/5)
    1 0.2 0
    2 0.4 0
    3 0.6 0
    4 0.8 0
    5 1.0 1
    6 1.2 1
    7 1.4 1
    8 1.6 1
    9 1.8 1
    10 2.0 2
    11 2.2 2
    12 2.4 2
    13 2.6 2
    14 2.8 2
    15 3.0 3
    16 3.2 3

    Damit hat er mehrere gleiche Einträge, nach denen er gruppieren kann. Er fasst jetzt alle 0, alle 1, alle 2 usw. jeweils zu einer Gruppe zusammen und bestimmt von dieser das Maximun.

    Laut PostgreSQl-Handbuch sollte / aber schon die ganzzahlige Division sein, d.h. obiges Bsp. sollte ohne int funktionieren (mit ganzen Zahlen als Id's). Wenn du den timestamp nehmen möchtest, kannst du versuchen mit [1] das gleiche hinzubekommen, z.B.
    Code:
    SELECT EXTRACT(HOUR FROM timestamp)/5 FROM tabelle;
    Du musst nur darauf achten, dass alle timestamps, die in ein 5er Packet sollen, durch irgendeine Berechnung die gleiche Nummer produzieren.

    Zu open/close:
    Du kannst zu der in meinem vorigen Posting vorgeschlagenen Abfrage noch "min(timestamp) as mints, max(timestamp) as maxts" hinzufügen. Dann ergibt ein:
    Code:
    SELECT open FROM tabelle WHERE timestamp = r[0]["mints"];
    den open-Wert der Zeile mit den kleinsten Timestamp, also den ersten Wert in dieser Gruppe. Analog das Gleiche für close.

    Wenn du diese Auswertungen nun über die ganze Tabelle haben möchtest, kannst du in einer Abfrage alles erledigen:
    Code:
    SELECT max(high) as high, min(low) as low, sum(volume) as vol,
    	count(*) as cnt, min(timestamp) as mints, max(timestamp) as maxts
    FROM tabelle
    GROUP BY int(id/5);
    Jetzt hast du in r für jede 5er Gruppe eine Zeile und kannst sinngemäß schreiben:
    Code:
    for (i=0; i<r.size; i++) {
    	ohlc *OHLC = new ohlc;
    	OHLC->high = r[i]["high"];
    	OHLC->low = r[i]["low"];
    	OHLC->volume = r[i]["vol"];
    
    	if (r[i]["cnt"] != 5) {
    		// dieses Packet hat weniger als 5 Timestamps
    	}
    
    	query: s = SELECT open FROM tabelle WHERE timestamp = r[i]["mints"]
    	OHLC->open = s[0]["open"];
    
    	query: s = SELECT close FROM tabelle WHERE timestamp = r[i]["maxts"]
    	OHLC->close = s[0]["close"];
    
    	//OHLC irgendwo speichern (Liste)
    }
    Gruß,
    Paul

    [1] Datums/Zeit-Operationen
    Geändert von paule (20-09-2006 um 10:52 Uhr)

  7. #7
    Registrierter Benutzer
    Registriert seit
    22.12.2003
    Beiträge
    19

    Thumbs up

    Super es klappt!! Vielen Dank!!

Lesezeichen

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •