Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Liebe NG,

ich habe 2 InnoDB-Tabellen mit folgenden Inhalten:

table_1 (beinhaltet Resourcen):

id bookable_from bookable_until timeslot
1 10:00:00 20:00:00 00:59:59
2 09:00:00 20:00:00 00:59:59

table_2 (beinhaltet Reservierungen, table_1.id wird als Fremdschlüssel
referenziert, hier als res_id bezeichnet):

res_id booked_from booked_until
1 10:00:00 10:59:59
1 11:00:00 11:59:59
1 12:00:00 12:59:59
1 13:00:00 13:59:59
2 09:00:00 09:59:59
2 10:00:00 10:59:59
2 11:00:00 11:59:59
2 16:00:00 16:59:59

ich würde mir gerne eine SELECT Anweisung zusammenbauen, die mir
folgende Ausgabe generiert:
(booking_slot wird gebildet durch CONCAT aus booked_from und
booked_until)

res_id booking_slot booked
1 10:00:00-10:59:59 true
1 11:00:00-11:59:59 true
1 12:00:00-12:59:59 true
1 13:00:00-13:59:59 true
1 14:00:00-14:59:59 false
1 15:00:00-15:59:59 false
1 16:00:00-16:59:59 false
1 17:00:00-17:59:59 false
1 18:00:00-18:59:59 false
1 19:00:00-19:59:59 false
2 09:00:00-19:59:59 true
2 10:00:00-10:59:59 true
2 11:00:00-11:59:59 true
2 12:00:00-12:59:59 false
2 13:00:00-13:59:59 false
2 14:00:00-14:59:59 false
2 15:00:00-15:59:59 false
2 16:00:00-16:59:59 true
2 17:00:00-17:59:59 false
2 18:00:00-18:59:59 false
2 19:00:00-19:59:59 false

Hat jemand eine Idee, wie ich eine solche Matrix erzeugen kann?
Ist es evtl. sinnvoll, eine Procedure mit While Schleife zu
implementieren?
Oder kriegt man so etwas auch über kombinierte JOINs hin?

Danke für Eure Vorschläge!!

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

In article <1187771729.058962.38090@q4g2000prc.googlegroups.com>,
Andreas Müller writes:

> Liebe NG,
> ich habe 2 InnoDB-Tabellen mit folgenden Inhalten:

> table_1 (beinhaltet Resourcen):

> id bookable_from bookable_until timeslot
> 1 10:00:00 20:00:00 00:59:59
> 2 09:00:00 20:00:00 00:59:59

> table_2 (beinhaltet Reservierungen, table_1.id wird als Fremdschlüssel
> referenziert, hier als res_id bezeichnet):

> res_id booked_from booked_until
> 1 10:00:00 10:59:59
> 1 11:00:00 11:59:59
> 1 12:00:00 12:59:59
> 1 13:00:00 13:59:59
> 2 09:00:00 09:59:59
> 2 10:00:00 10:59:59
> 2 11:00:00 11:59:59
> 2 16:00:00 16:59:59

> ich würde mir gerne eine SELECT Anweisung zusammenbauen, die mir
> folgende Ausgabe generiert:
> (booking_slot wird gebildet durch CONCAT aus booked_from und
> booked_until)

> res_id booking_slot booked
> 1 10:00:00-10:59:59 true
> 1 11:00:00-11:59:59 true
> 1 12:00:00-12:59:59 true
> 1 13:00:00-13:59:59 true
> 1 14:00:00-14:59:59 false
> 1 15:00:00-15:59:59 false
> 1 16:00:00-16:59:59 false
> 1 17:00:00-17:59:59 false
> 1 18:00:00-18:59:59 false
> 1 19:00:00-19:59:59 false
> 2 09:00:00-19:59:59 true
> 2 10:00:00-10:59:59 true
> 2 11:00:00-11:59:59 true
> 2 12:00:00-12:59:59 false
> 2 13:00:00-13:59:59 false
> 2 14:00:00-14:59:59 false
> 2 15:00:00-15:59:59 false
> 2 16:00:00-16:59:59 true
> 2 17:00:00-17:59:59 false
> 2 18:00:00-18:59:59 false
> 2 19:00:00-19:59:59 false

> Hat jemand eine Idee, wie ich eine solche Matrix erzeugen kann?
> Ist es evtl. sinnvoll, eine Procedure mit While Schleife zu
> implementieren?
> Oder kriegt man so etwas auch über kombinierte JOINs hin?

Dazu brauchst Du eine Datenquelle für die Werte, die in table_2 _nicht_
drin sind. Nachdem MySQL leider nicht über die PostgreSQL-Funktion
"generate_series" verfügt, verwende ich dafür eine einmal angelegte
Hilfstabelle, die einen genügend großen Zahlenbereich enthält.
Diese Hilfstabelle liegt bei mir in der Datenbank "misc", heißt "seq"
und enthält in der (einzigen) Spalte "val" die Zahlen -50000 bis
50000, was für die meisten Fälle ausreichen sollte.

Nun muß man aus table_1 für jede ID die Anzahl der benötigten Zahlen
berechnen (also 10 bzw. 11), mit seq joinen, um die richtige Anzahl
von Zeilen zu erzeugen, seq.val in die richtige Zeit umrechnen und
schließlich einen Left Join mit table_2 durchführen:

SELECT d.id, d.time,
t2.res_id IS NOT NULL AS booked
FROM (
SELECT t1.id AS id,
sec_to_time(time_to_sec(t1.bookable_from) + (s.val - 1) * 3600) AS time
FROM table_1 t1
JOIN misc.seq s ON s.val BETWEEN 1 AND
(time_to_sec(t1.bookable_until) - time_to_sec(t1.bookable_from)) /
(time_to_sec(t1.timeslot) + 1)
) AS d
LEFT JOIN table_2 t2 ON t2.res_id = d.id AND t2.booked_from = d.time
ORDER BY d.id, d.time

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Harald Fuchs schrieb:

> In article <1187771729.058962.38090@q4g2000prc.googlegroups.com>,
> Andreas Müller writes:

Da fehlen noch Datum, Uhrzeit, Zeitzone, Newsreader, Zeichensatz ...

>> ich würde mir gerne eine SELECT Anweisung zusammenbauen, die mir
>> folgende Ausgabe generiert:
>> [...]
>
> Dazu brauchst Du eine Datenquelle für die Werte, die in table_2 _nicht_
> drin sind.

Es gibt LOOP, ITERATE, REPEAT, WHILE - damit sollte sich was basteln
lassen, auch ohne Hilfstabelle.

> Nachdem MySQL leider nicht über die PostgreSQL-Funktion

Mit Verlaub: _Ein_ penetranter PostgreSQL-Fanatiker in der MySQL-Gruppe
reicht. Einen zweiten brauchen wir nicht.

Gruß. Claus

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Am 23.08.2007 09:56 schrieb Claus Reibenstein:
> Harald Fuchs schrieb:

> Es gibt LOOP, ITERATE, REPEAT, WHILE - damit sollte sich was basteln
> lassen, auch ohne Hilfstabelle.
>

Es gibt ... *in Stored Procedures*. Die von Harald vorgeschlagene
Lösung funktioniert auch mit älteren MySQL-Versionen.

>> Nachdem MySQL leider nicht über die PostgreSQL-Funktion
>
> Mit Verlaub: _Ein_ penetranter PostgreSQL-Fanatiker in der MySQL-Gruppe
> reicht. Einen zweiten brauchen wir nicht.
>

Wo siehst Du hier Fanatismus? Der Hinweis auf Funktionen, die MySQL
bislang nicht bietet, wird doch wohl erlaubt sein.

--
Christian

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Christian Kirsch schrieb:

> Am 23.08.2007 09:56 schrieb Claus Reibenstein:
>
>> Es gibt LOOP, ITERATE, REPEAT, WHILE - damit sollte sich was basteln
>> lassen, auch ohne Hilfstabelle.
>
> Es gibt ... *in Stored Procedures*.

Ja. Und?

> Die von Harald vorgeschlagene
> Lösung funktioniert auch mit älteren MySQL-Versionen.

Über seine Version hat sich Andreas ausgeschwiegen. Also gehe ich mal
davon aus, dass es eine halbwegs aktuelle ist. Und wenn nicht, könnte
das hier ja vielleicht ein Grund sein, endlich mal upzugraden :-)

>>> Nachdem MySQL leider nicht über die PostgreSQL-Funktion
>>
>> Mit Verlaub: _Ein_ penetranter PostgreSQL-Fanatiker in der MySQL-Gruppe
>> reicht. Einen zweiten brauchen wir nicht.
>
> Wo siehst Du hier Fanatismus?

_Noch_ sehe ich keinen.

Aber ich gebe zu, dass ich mittlerweile etwas hypersensibel in dieser
Richtung geworden bin. Daran dürfte der eine Fanatiker nicht ganz
unschuldig sein ...

> Der Hinweis auf Funktionen, die MySQL
> bislang nicht bietet, wird doch wohl erlaubt sein.

Natürlich. Aber nur, wenn es sich um Standard-SQL-Funktionen handelt.
Deiner Formulierung nach zu urteilen, handelt es sich jedoch um eine
proprietäre PostgreSQL-Funktion, und die haben hier IMHO nichts verloren.

Gruß. Claus

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Am 23.08.2007 10:54 schrieb Claus Reibenstein:
> Christian Kirsch schrieb:

>> Der Hinweis auf Funktionen, die MySQL
>> bislang nicht bietet, wird doch wohl erlaubt sein.
>
> Natürlich. Aber nur, wenn es sich um Standard-SQL-Funktionen handelt.
> Deiner Formulierung nach zu urteilen, handelt es sich jedoch um eine
> proprietäre PostgreSQL-Funktion, und die haben hier IMHO nichts verloren.


CREATE SEQUENCE ist Teil von SQL:2003. Ist das Standard genug?


Ich finde allerdings, dass man unabhängig davon durchaus über den
Tellerrand gucken sollte. MySQL hat sich früher nicht besonders um
Standards gekümmert, sondern viele darüber hinaus gehende Funktionen
implementiert (und an vielen Stellen Dinge eingebaut, die der Norm
flagrant widersprachen). Wenn PG (oder Oracle oder DB2 oder Informix
....) etwas bieten, was der Standard nicht vorsieht, was aber trotzdem
sinnvoll ist (mein Lieblingsbeispiel wäre LDAP-Authentifizierung),
dann kann man doch durchaus darüber nachdenken, das als "Inspiration"
zu nehmen. Dafür muss man es aber kennen, und deshalb finde ich
Andreas' Beiträge meistens interessant.

Bspw. die in einem jüngeren Thread von ihm demonstrierten
Formatierungsfähigkeiten von PG in Verbindung mit dem Locale. Ich
halte es jedenfalls für kein Feature, dass MySQLs DATE_FORMAT-Funktion
keine LOCALE-Einstellungen berücksichtigt, sondern Monats- und
Tagnamen stumpf auf Englisch ausgibt (immerhin: nicht Schwedisch ;-)

Ich sehe gerade: ab 5.1.12 tut es das. Für
NUMERIC/FLOAT-Typen gibt's aber wohl noch nichts.

--
Christian

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Erstmal danke an alle, die auf meinen Eintrag geantwortet haben ;)
und ganz besonders an Harald Fuchs:

On 23 Aug., 00:21, Harald Fuchs wrote:
>
> SELECT d.id, d.time,
> t2.res_id IS NOT NULL AS booked
> FROM (
> SELECT t1.id AS id,
> sec_to_time(time_to_sec(t1.bookable_from) + (s.val - 1) * 3600) =
AS time
> FROM table_1 t1
> JOIN misc.seq s ON s.val BETWEEN 1 AND
> (time_to_sec(t1.bookable_until) - time_to_sec(t1.bookable_from))=
/
> (time_to_sec(t1.timeslot) + 1)
> ) AS d
> LEFT JOIN table_2 t2 ON t2.res_id =3D d.id AND t2.booked_from =3D d.time
> ORDER BY d.id, d.time

Diese Lösung war genau das, was ich gesucht hatte!!
Vielen Dank!!

Eine Frage an Claus Reibenstein:

> Da fehlen noch Datum, Uhrzeit, Zeitzone, Newsreader, Zeichensatz ...

Bezog sich das auf mich? Das sind alles Angaben, die ich in
groups.gougle.com meines Wissens nach nicht einstellen kann... Oder
doch??

Re: Erzeugen einer Matrix-Abfrage, vllt. mit Procedure oder JOINs??

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de