Hilfe bei Query für Spaltenansicht.

[gondor]

Hallo liebe Boardies!

Ich habe ein großes Problem. Hier eine kurze Einführung:

Ich habe eine messDB programmiert, wo von aus Werte (°C, rH) von verschiedenen
Sensoren (s1, s2, s3,...) in eine Tabelle geschrieben werden. Diese Werte sollen
über ein ResultSet an mein Programm (Java) in eine jTable angezeigt werden.

Ein Tabellen-Überblick:

mess_sensor_value (id, fk_sensor_id, fk_record_id, sensor_date, sensor_value)

 1, 1, 1, 2004-12-12 13:00:10, 2 
 2, 2, 1, 2004-12-12 13:00:10, 12
 3, 1, 1, 2004-12-12 13:00:20, 11
 4, 2, 1, 2004-12-12 13:00:20, 23
 5, 1, 1, 2004-12-12 13:00:30, 15
 6, 2, 1, 2004-12-12 13:00:30, 12
 7, 1, 1, 2004-12-12 13:00:40, 20
 8, 2, 1, 2004-12-12 13:00:40, 23
 9, 3, 2, 2004-13-12 12:00:30, 5
10, 4, 2, 2004-13-12 12:00:30, 6
11, 3, 2, 2004-13-12 12:00:40, 16
12, 4, 2, 2004-13-12 12:00:40, 11
13, 3, 2, 2004-13-12 12:00:50, 33
14, 4, 2, 2004-13-12 12:00:50, 27
15, 3, 2, 2004-13-12 12:01:00, 4
16, 4, 2, 2004-13-12 12:01:00, 8
17, 1, 3, 2005-01-01 15:04:30, 14
18, 2, 3, 2005-01-01 15:04:40, 12
19, 1, 3, 2005-01-01 15:04:50, 45
20, 2, 3, 2005-01-01 15:05:00, 18
...

mess_sensor (id, sensor_name, sensor_unit)

1, s1, °C
2, s1, rH
3, s2, °C
4, s2, rH
...

mess_record (id, record_name, user_name, test_name)

1, protokoll 1, dirk, versuch 1 
2, protokoll 2, dirk, versuch 1
3, protokoll 2, dirk, versuch 2
...

Ich würde gerne eine Ausgabe folgend haben:

Alle Sensoren mit deren Werte aus Protokoll 1 (mess_record_id = 1) UND Protokoll 2
(mess_record_id = 2). Und zwar soll für JEDEN Sensortyp (mess_sensor_id) eine SPALTE
angezeigt werden, wo die jeweiligen Sensorwerte enthalten sind. Hier das Beipspiel:

Zeige alle Sensoren aus Protokoll 1 + 2 aus Versuch 1:

sensor_id 1	| sensor_id_ 2	| sensor_id 3	| sensor_id_4 
2		| 12			| 5			| 6
11		| 23			| 16			| 11
15		| 12			| 33			| 27
20		| 23			| 4			| 8
...

oder

Zeige alle °C-Sensoren aus Protokoll 1 + 2 aus Versuch 1:

sensor_id 1	| sensor_id 3
2		| 5
11		| 16
15		| 33
20		| 4
...

oder

Zeige alle Sensoren aus Protokoll 1 aus Versuch 1:

sensor_id 1	| sensor_id_ 2
2		| 12					
11		| 23					
15		| 12					
20		| 23					
...

oder

Zeige alle °C-Sensoren aus Protokoll 1 aus Versuch 1:

sensor_id 1
2
11
15
20

Leider bekomme ich die Anzeige über die Spalten nicht hin. Muss man da evtl. mit
'VIEWS' arbeiten? Ich habe schon Stunden probiert, bekomme aber nichts wirklich
gescheites zustande. Und wenn, dann kommen die Ausgaben immer 'zeilenweise'

Außerdem eine Frage:

Ist es sinnvoll nur 'eine' Tabelle für alle Sensoren zu benutzen, oder sollte für
jeden Sensor eine eigene Tabelle gemacht werden? Der Datenbestand umfasst bei einer
Messung etwa 500.000 Werte PRO Sensor. Sind also bei 8 Sensoren 4.000.000 Einträge,
und bei 5 Messungen 20.000.000 Einträge...

Habe leider dafür einfach zu wenig Ahnung über SQL bzw. Anfragen über Queries.

Die DB ist eine mySQL 4.x

Ich bitte hier um Hilfe... ist 'wirklich' wichtig!

Lieben Dank,

 

[bambi]

Alle Sensoren mit deren Werte aus Protokoll 1 (mess_record_id = 1) UND Protokoll 2
(mess_record_id = 2). Und zwar soll für JEDEN Sensortyp (mess_sensor_id) eine SPALTE
angezeigt werden, wo die jeweiligen Sensorwerte enthalten sind. ...

Meinst Du sowas: (musst Du natuerlich noch anpassen)

select tab_sensor1.value, tab_sensor2.value
from   (select sensor_value as value, sensor_date as date
          from   mess_sensor_value
          where fk_record_id = 1
          and     fk_sensor_id = 1)
          tab_sensor1,
       (select sensor_value as value, sensor_date as date
          from   mess_sensor_value
          where fk_record_id = 1
          and     fk_sensor_id = 2)
          tab_sensor2
where tab_sensor1.date = tab_sensor2.date

das waeren dann die daten fuer sensor 1 und 2 im 1. versuch. ich bin mir aber nicht
sicher, dass mysql diese tabellen im "from" erzeugen kann (musst auch sicher die
syntax anpassen - ich arbeite mit oracle und DB2...)
... so comfortabel iss mysql ja an einigen stellen dann doch nicht - aber eben fix...

iss aber mal 'ne idee - vielleicht hilft's dir ja... :wink:

 

[gondor]

hm... bin gerade am probieren.

was meinst du mit ' tab_sensor2' ... 'tab_sensor1.date' blicke da noch nicht wirklich durch die bezeichnungen welche tabelle gemeint sein könnte. zumindest gibt es nur eine tabelle, wo alle sensoren vertreten sind. nicht jeder sensor hat seine eigene...

wäre froh, wenn jemand helfen kann.

gondor(..)

 

[bambi]

hi,

in dem "from" habe ich 2 neue tabellen erzeugt - nur fuer's statement. Die gibt's ansonsten nicht:
tab_sensor1 und tab_sensor2 sind die namen der neuen tabellen.
tab_sensor1 enthaelt die daten fuer sensor 1

value | date
...| ...

die 2'te tabelle tab_sensor2 ist genauso aufgebaut - enthaelt die daten fuer sensor 2.

falls das in mysql nicht gehen sollte, dann wuerde ich's mit views ausprobieren - wuesste sonst
auch nicht, wie man die daten in verschiedene spalten schreiben koennte.

 

[gondor]

ok, ich probiere mal...

wie könnte das denn sonst mit den views gelöst werden? habe leider wenig plan :-( und die daten muss ich so, in der form erhalten...

wenn du noch (mal mehr) einen rat weisst?

gondor(..)

 

[gondor]

ah... ok. die syntax funktioniert

fehlen jetzt nur noch die queries:

Zeige alle Sensoren aus Protokoll 1 aus Versuch 1
Zeige alle °C-Sensoren aus Protokoll 1 aus Versuch 1
Zeige alle °C-Sensoren aus Protokoll 1 + 2 aus Versuch 1

can anybody help?

gondor(..)

 

[bambi]

Mal 'ne Frage:
fuer

Zeige alle Sensoren aus Protokoll 1 aus Versuch 1
Zeige alle °C-Sensoren aus Protokoll 1 aus Versuch 1

brauchst du ja eine Relation zwischen Deinen Tabellen "mess_sensor_value" und "mess_record".
Die kann ich hier aber nicht finden... Wie gehoeren die 2 Tabelllen denn zusammen? Man kann
den Namen ja nicht mit der ID gleichsetzen...

 

[gondor]

sry... doppelposting

 

[gondor]

@bambi

ah... vllt. kommen wir so einem fehler auf die spur

reicht das nicht aus, wenn ich einen FK_record_id in 'mess_sensor_value' habe? ich dachte, das würde für eine relation zwischen den beiden tabellen reichen...

wäre das nicht für:

Zeige alle Sensoren aus Protokoll 1 aus Versuch 1: record_id --> 1

Zeige alle °C-Sensoren aus Protokoll 1 aus Versuch 1: record_id --> 1

Zeige alle rH-Sensoren aus Protokoll 2 aus Versuch 1: record_id --> 2

gondor(..)

 

[gondor]

brauche jetzt 'nur' noch die sensor-werte für die einheiten, also:

zeige alle °C-Werte aus Protokoll 1
zeige alle rH-Werte aus Protokoll 1

der rest funktioniert schon...

wäre hier für hilfe noch einmal dankbar,

gondor(..)

 

[bambi]

zeige alle °C-Werte aus Protokoll 1

Versuch's mal hiermit (... weiss nicht, ob's richtig ist - iss halt etwas schwierig ohne DB ...)

select msv.sencor_value
from   mess_sensor_value msv,
          mess_record_id mr,
          mess_sensor ms
where msv.fk_sensor_id = ms.id
and    msv.fk_record_id = mr.id
and    ms.sensor_unit = "C" -- hier ist kein "Grad" auf meiner Tastatur :o(
and    mr.record_name = "protokoll 1"
-- and   mr.test_name = "versuch 1"

[edit: sorry, hatte versuch 1 mit protokoll 1 verwechselt]

 

[bambi]

zeige alle rH-Werte aus Protokoll 1

.. dazu dann aequivalent ...

select msv.sencor_value
from   mess_sensor_value msv,
       mess_record_id mr,
       mess_sensor ms
where  msv.fk_sensor_id = ms.id
and    msv.fk_record_id = mr.id
and    ms.sensor_unit = "rH"
and    mr.record_name = "protokoll 1"

 

[bambi]

Mir ist noch was zu Deiner Frage eingefallen:

Ist es sinnvoll nur 'eine' Tabelle für alle Sensoren zu benutzen, oder sollte für
jeden Sensor eine eigene Tabelle gemacht werden?

Damit meinst Du ja sicher Deine Tabelle mess_sensor_value. Ja, das wuerde ich so lassen.
Es ist einfacher die Abfragen zu machen, wenn Du alles in einer Uebersich hast. An dieser
Stelle faende ich eine Trennung nicht so sinnvoll...

Ich wuerde mir aber vielleicht die anderen beiden Tabellen ansehen. Ich weiss nicht wieviel
Du ueber DBs weisst und wie man sie aufbaut. Im Studium hat man mir immer beigebrach:
"Immer schoen normalisieren - sonst brauchsts zuviel Speicher." Aber mal ehrlich: in der
Realitaet sieht's ja nun ganz ander aus: Speicherplatz iss nicht mehr teuer - wichtig ist Performance.

Eine Aufteilung (von mess_sensor) in

mess_sensor: sensor_id | sensor_name
sensor_unit: unit_id | unit_name

und natuerlich noch eine Tabelle fuer die Relation

sensor_unit_relation: sensor_id | unit_id

oder sonst - ohne relation-tabelle

mess_sensor: sensor_id | sensor_name | unit_id

ist vielleicht ganz sinnvoll. Dann musst Du in Deinen Queries nicht immer
" ... = 'rH' " oder " ... = 's1' "
und so schreiben - ich find's weniger fehleranfaellig, wenn man IDs verwendet.
Ist aber wie gesagt Ansichtssache - je nachdem, was man mit den Daten machen will...
Nur eine kleine Idee... :wink:

 

[foobar]

Immer schoen normalisieren - sonst brauchsts zuviel Speicher.

Das ist aber auch nur bis zu einem bestimmten Grad sinnvoll.

 

[gondor]

hey danke noch einmal für deine hilfe...

auch in bezug zur 'normalisierung'. ich habe meine darstellung der tabelle so kurz wie möglich gehalten. eigentlich ist mein db-schema schon ganz gut 'normalisiert'. war nur zu 'faul' alle tabellen aufzuführen

schliesse mal den thread. sollten noch fragen aufkommen, poste ich fröhlich weiter.

gruß,

gondor(..)

 

[gondor]

doch noch eine frage...

was passiert denn, wenn ich in einer spalte weniger werte habe (von der anzahl) als in einer anderen
spalte? mit der bisherigen query zeigt er immer nur die werte an, bis zur minimalen anzahl einer spalte.

benutze query:

SELECT 
tab_0.S1_H01, tab_1.S1_H01, tab_2.S1_H02, tab_3.S1_H02
FROM 
(SELECT DISTINCT sensor_value_value AS S1_H01 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 26) tab_0,
(SELECT DISTINCT sensor_value_value AS S1_H01 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 27) tab_1,
(SELECT DISTINCT sensor_value_value AS S1_H02 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 28) tab_2,
(SELECT DISTINCT sensor_value_value AS S1_H02 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 29) tab_3

beispiel:

wenn die spalte A 9 werte hat und spalte B 5 werte, kommt als ergebnis 2 Spalten A und B mit jeweils 5 werten:

Spalte A | Spalte B
1        | 1
2        | 2
3        | 3
4        | 4
5        | 5

dabei möchte ich solch eine ausgabe haben:

Spalte A | Spalte B
1        | 1
2        | 2
3        | 3
4        | 4
5        | 5
6        | 0 <- kein wert, 0 schreiben...
7        | 0
8        | 0
9        | 0

kann man da was 'joinen'? ist das überhaupt möglich? wie würde die query von oben dann aussehen?

danke für hilfe,

gondor(..)

 

[bambi]

Hab' hier mal was zu JOIN bei MySQL gefunden:
http://dev.mysql.com/doc/mysql/en/join.html
Du hast doch sicher noch ein(ige) WHERE Zeilen in Deinem Statement, oder? Die braucht man,
um das JOIN zu schreiben. Das wird aber sicher nicht so einfach, wenn Du 4 Tabellen verknuepfen
willst... Kannst Du mal Dein komplettes Statement posten?
THX

 

[gondor]

hi bambi!

hm... eigentlich funktioniert die query von oben ganz gut.

so nutze ich sie jedenfalls. WHERE habe ich nicht. je nachdem wieviele spalten ich angezeigt bekommen möchte,
wird mir diese query generiert. mal mit 6 spalten, 12 spalten oder 24... das format ist immer das selbe:

SELECT 
tab_0.S1_H01, tab_1.S1_H01, tab_2.S1_H02, tab_3.S1_H02, tab_4.S1_H03, tab_5.S1_H03, tab_6.S1_H04, tab_7.S1_H04, tab_8.S1_H01, tab_9.S1_H01, ...
FROM 
(SELECT DISTINCT sensor_value_value AS S1_H01 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 26) tab_0,
(SELECT DISTINCT sensor_value_value AS S1_H01 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 27) tab_1,
(SELECT DISTINCT sensor_value_value AS S1_H02 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 28) tab_2,
(SELECT DISTINCT sensor_value_value AS S1_H02 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 29) tab_3,
(SELECT DISTINCT sensor_value_value AS S1_H03 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 31) tab_4,
(SELECT DISTINCT sensor_value_value AS S1_H03 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 30) tab_5,
(SELECT DISTINCT sensor_value_value AS S1_H04 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 32) tab_6,
(SELECT DISTINCT sensor_value_value AS S1_H04 FROM mess_sensor_value WHERE sensor_value_record_id = 69 AND sensor_value_sensor_id = 33) tab_7,
(SELECT DISTINCT sensor_value_value AS S1_H01 FROM mess_sensor_value WHERE sensor_value_record_id = 55 AND sensor_value_sensor_id = 26) tab_8,
(SELECT sensor_value_value AS S1_H01 FROM mess_sensor_value WHERE sensor_value_record_id = 55 AND sensor_value_sensor_id = 27) tab_9, ...
;

und nu?

gondor(..)

 

[bambi]

wie jetzt - das geht? ich waer' mir jetzt sicher gewesen, dass da dann ein ...
wie heisst das jetzt noch??? -> dass die Daten alle irgendwie zusammengeworfen werden
also bei

tab1              tab2                  --> tab1_2
1                   11                  1       11
2                   12                  1       12
                                        2       11
                                        2       12

rauskommt...
Das wundert mich jetzt schon

Hat Dir der Link geholfen? JOINS sind mir persoenlich auch eher unsympatisch - nur wenn's denn unbedingt sein muss...
Also ich denk' mal, dass Du sowas brauchst wie

SELECT ...
FROM    (  OUTER JOIN 
               (...) tab1
               ON 
               (   OUTER JOIN
                   (...) tab2
                    ON
                   (...) tab3
               )
            )

Keine Garantie fuer das hier - einfach mal testen, was passiert... :wink:

 

[gondor]

@bambi

also, das OUTER JOIN funktioniert leider nicht. soll aber nicht mein jetziges problem sein. ich habe bislang immer nur mit 200-300 werten gearbeitet. nun habe ich einmal 100.000 werte in die tabelle gefüllt,...

die anfrage für die spaltenausgabe dauert bis zu 2min... auch
ein count(...) nach record und sensor dauert bis zu 40 sek. das darf nicht sein :-(

kann mir hier jemand helfen? das ist ja das problem, wenn ich alle daten in 'EINE' tabelle schaufel. dann muss er doch alles durchsuchen, oder?

wie bekomme ich die ergebnisse der queries schneller?

danke für antworten,

gondor(..)

 

[bambi]

Hast Du das direkt auf MySQL oder mit Deinen Java-Funktionen getestet?