[SQL] Boolsche Matrix -> Liste von Paaren

[Medium]

Huhu DP!

Ich habe eine antike Tabelle, in der mögliche Quellen und Ziele einer Produktionsanlage einander zugeordnet sind. Diese Tabelle wurde damals (leider) als Matrix ausgeführt, ich brauche aber für meine Modernisierung der Anlage nun alle Paare, in denen in der Matrix eine "1" steht. Beispiel:

Code:

 SR | T1 | T2 | T3 | T4 | T5
  1 |  0 |  0 |  0 |  0 |  0 
  2 |  0 |  1 |  0 |  0 |  0 
  3 |  0 |  0 |  0 |  0 |  0 
  4 |  0 |  0 |  1 |  0 |  0 
  5 |  0 |  0 |  1 |  0 |  0

Soll werden zu: (id=auto_inc)

Code:

 id | SR | TG
  1 |  2 |  2
  2 |  4 |  3
  3 |  5 |  3

Das von Hand zu machen wäre etwas Fehleranfällig, da die Tabelle ~50 Spalten und ~100 Zeilen hat, und es insgesamt in rund 300 Paare resultieren dürfte. Kann ich das irgendwie direkt als SQL Statement verhackstücken, oder muss ich mir dafür ein Töölchen bauen? Danke vorab!

[jobo]

select sr,
case when t1=1 then t1
case when t2=1 then t2
else 'Error'
end
as Tg new
from ...

Case Syntax entsprechend maria/mysql weiß ich nicht genau auswendig.

Das Statement kann man notfalls in Excel mit einer Zellenfunktion "generieren" für so viel Spalten, wie benötigt.

[Frickler]

Enthält jede Zeile genau ein Matrixelement? Dann kannst Du auch sowas machen wie

Code:

SELECT SR, T1*1 + T2*2 + T3*3 + T4*4 + ... + T50*50 AS TG FROM matrix
WHERE TG <> 0

Wenn die leeren Einträge NULL statt 0 sind, muss jeder Tx Term ersetzt werden durch COALESCE(Tx, 0)*x.

[Delphi.Narium]

ungefähr sowas?

SQL-Code:

insert into neue_tabelle (sr, tg)
select sr, 
case 
  when t1 = 1 then 1
  when t2 = 1 then 2
  when t3 = 1 then 3
  when t4 = 1 then 4
  when t5 = 1 then 5
end as tg
from alte_tabelle 
where t1 + t2 + t3 + t4 + t5 > 0
order by 1

ID als Auto-Inc wird ja (hoffentlich) von der DB automatisch vergeben.

[Medium]

Hmmm, erstmal vielen Dank!

Die Varianten mittels case würden wohl für eine 50x100 Tabelle aufwändiger zu konstruieren als ein Progrämmchen, dass das macht. Es sind auch leider Zeilen und Spalten dabei (also egal ob transponiert oder nicht) die mehr als nur eine 1 haben. Hatte gehofft, dass es dazu eine einfache Standardlösung gibt, die ich einfach nur nicht kenne. Klingt erst mal nach einem "normalen" Problem vor dem viele hätten stehen können :) Ich glaube, ich gieße dann lieber einen kleinen 20-Zeiler in Delphi. Soll ja auch nur einmalig sein. Danke euch dennoch!

[p80286]

Die Delphi (oder Excel) -Lösung würde ich auch bevorzugen. Denn das könnte man bei solchen Problemen immer wieder mal nutzen, während die reine DB-Lösung zu unflexibel ist.
(die Feldnamen sollte die DB aber ausspucken können, da ist nichts mehr mit vertippen!)

Gruß
K-H

[jobo]

Tja, case when hätte es ja im Prinzip getan.
Aber bei Mehrfachnennungen wird's schwierig.

Eine bessere Datenbank wäre da auch hilfreich. Dann wäre es wohl bei einem SQL Statement geblieben. Bei 50 Spalten und 100 Zeilen (und Delphikenntnis) ist es aber auch wurscht.

[Medium]

Bessere DB? Gibt's da eine für ähnliche Preise, so einfach zu installieren und konfigurieren, ausreichend für ~5 Clients ohne besondere Ansprüche an Geschwindigkeit und/oder Skalierbarkeit? Immer im Hinterkopf dabei, dass wir hier keinen wirklichen DBMS Spezi haben?

[jobo]

Postgres ist funktional sehr nahe an Oracle, teilweise vielleicht weiter, es bietet starke Indizierungsvarianten.
Es bietet eine sehr gute Programmierbarkeit (StoredProcedure bzw. Functions), nosql Funktionalität, SQL Anbindung für XML, JSON, JSONB, Arrays, .. Man kann also z.B. aus XML selectieren oder zu XML konvertieren.
Der SQL Standard ist recht weit ausgereift und natürlich gibt es nette Eigenheiten. IdR kann man da mit SQL "coolere" Sachen machen, als mit mysql/maria. Mit der neuen mysql 8er Version hat sich das etwas entschärft, also mysql hat aufgeholt, aber wie das weitergeht, weiß man nicht. Für Kreuztabellen liefert postgres z.B. eine Funktion. Die umgekehrte Form, die für Dich interessant gewesen wäre, ist nicht so straight forward (bei Oracle und MSSQL bspw. > unpivot), aber wahrscheinlich trotzdem ein Statement.
Und es ist sowohl skalierbar und als auch anspruchslos für kleine Installationen. Ein normales Setup ohne große Konfiguration ist kein Hexenwerk, kosten tut es auch nichts.
Was mir nicht so aktuell bekannt ist, sind die Treiberwelten für Delphi, also ADO usw.

[Medium]

Hmm, viele der Funktionen die du nennst brauchen wir aktuell und auch sehr wahrscheinlich künftig nicht, aber klingt dennoch als wäre es wert damit etwas herumzuspielen.

Doof ist nur, dass wir erst kürzlich unser veraltetes UniDAC für D2007 für ein aktuelles MyDAC eingetauscht haben (um Kosten zu sparen und wir all die Jahre nie einen anderen Provider brauchten). Von ADO bekomme ich noch immer böse Träume (vor Ewigkeiten mal mit rumgespielt), oder ich verwechsle das gerade mit ODBC.

Mein Problem mit funktional mächtigen Dingen ist: Oftmals heißt viele Möglichkeiten zu haben dann auch sich mit eben diesen auskennen zu müssen wenn man sein System einigermaßen im Griff haben will. Da die DB für uns aber nur Mittel zum Zweck ist, ein kleiner Teil unseres eigentlichen Geschäfts, bin ich da recht vorsichtig. (Vor allem nachdem ich mal in anderer Sache an einen MSSQL Server ran musste. Da fing mein Latein erst gar nicht an! :stupid:)

Und bis auf solche sehr seltenen Sonderfälle hat uns bei MySQL/MariaDB bisher nie etwas gefehlt. Viel komplexer als ein paar joins über vielleicht 3-4 Tabellen und einfache Filterung und ein wenig Fremdschlüsselei wird's nicht. Nichtmals für stored procedures habe ich bisher wirklich sinnvolle Anwendung gefunden bei uns.

[jobo]

Ich will Dir nichts aufschwätzen und Du bist ja offenbar zufrieden.
Ich würde es mal so sagen, alles was Du bis jetzt mit der alten DB gemacht hast, kannst Du auch mit der neuen machen (vermutlich 100% identisch) und Du kannst eben mehr machen.

Was bei pg seltsamerweise nicht so der Hit ist, ist die Integration in die Windowswelt. Obwohl es da auch seit einiger Zeit sowas wie XAMPP o.ä. gibt.
Ich glaube, die Jungs sind einfach Linux Freaks und finden Windows doof.

Neulich noch gelesen, dass die Entwickler eine Code Schenkung von Fujitsu abgelehnt haben, weil es keine freien Sourcen werden sollten. Das Standing muss man erstmal haben.

Bei mysql frag ich mich sehr, wie es mit Orcle weitergeht, die letzte Version hat ja schon ewig gedauert. Ob Maria dann die Defizite wegatmet, ist m.E. auch eher fraglich. Also insgesamt nicht nur eine Frage von "was brauch ich", sondern auch (wie immer) eine der Zukunftsperspektive.

Die Mächtigkeit eines solchen Systems ist ja kein Zwang. Das wartet alles geduldig.
Aber ich würde bspw. immer XML mit Komponenten erzeugen, bevor ich anfange Klammer auf zu usw. zu Fuß im Delphicode zu machen. Und wenn es halt direkt per SQL geht, umso besser.

Was die Komponenten angeht, tut's ja vielleicht firedac.
Evtl ist ja der Umstieg gerade in einer Situation, wo "nicht viel gebracht" wird, besonders einfach und naheliegend.

[Rollo62]

PG mag ja ganz OK sein, aber es ist ein recht fettes Teil.
Wir haben das mal eingesetzt für eine kleinere DB, aber das ist FB eine Feder dagegen.

[p80286]

Zitat:

Zitat von Medium (Beitrag 1410035)

Mein Problem mit funktional mächtigen Dingen ist: Oftmals heißt viele Möglichkeiten zu haben dann auch sich mit eben diesen auskennen zu müssen wenn man sein System einigermaßen im Griff haben will. Da die DB für uns aber nur Mittel zum Zweck ist, ein kleiner Teil unseres eigentlichen Geschäfts, bin ich da recht vorsichtig. (Vor allem nachdem ich mal in anderer Sache an einen MSSQL Server ran musste. Da fing mein Latein erst gar nicht an! :stupid:)

Zu wissen, daß man nichts weiß ist der erste Schritt zu mehr Wissen.

Zitat:

Zitat von Medium (Beitrag 1410035)

Und bis auf solche sehr seltenen Sonderfälle hat uns bei MySQL/MariaDB bisher nie etwas gefehlt.

Das wiederum ist Pfeifen im Wald. Ein paar mal kann man vielleicht etwas tricksen, aber dann wird es irgendwann einmal ernst.
Von "Datenbankbescheidenheit" kann ich nur abraten, das rächt sich irgendwann fürchterlich.

Gruß
K-H

[Medium]

Zitat:

Zitat von jobo (Beitrag 1410047)

Was bei pg seltsamerweise nicht so der Hit ist, ist die Integration in die Windowswelt. Obwohl es da auch seit einiger Zeit sowas wie XAMPP o.ä. gibt.
Ich glaube, die Jungs sind einfach Linux Freaks und finden Windows doof.

Da wir zu 100% ausschließlich auf Windows unterwegs sind, in der Regel auch die Möglichkeit haben dies zu diktieren, und niemanden im Haus haben der ansatzweise genug Linux-Wissen hat das über die Installation der "DAU"-Distros hinaus geht... ist das einem Totschlagargument schon sehr sehr nahe. WIR können uns es nicht leisten solche Ideale der Ideale wegen hoch zu halten, dafür sind wir zu klein und vollgepackt mit Aufträgen. Wir brauchen eine Lösung mit der wir unmittelbar umgehen können.

Zitat:

Bei mysql frag ich mich sehr, wie es mit Orcle weitergeht, die letzte Version hat ja schon ewig gedauert. Ob Maria dann die Defizite wegatmet, ist m.E. auch eher fraglich. Also insgesamt nicht nur eine Frage von "was brauch ich", sondern auch (wie immer) eine der Zukunftsperspektive.

MySQL haben wir spätestens seit Übernahme von Oracle durch MariaDB abgelöst, da wir da auch skeptisch sind. Bisher haben sie ihre Versprechen zwar nicht gebrochen, aber allein die völlig undurchsichtige Lizenzpolitik war Grund genug etwas anderes zu suchen. Und die Entwicklung an MariaDB scheint mir zumindest nach wie vor aktiv vorangetrieben zu werden. Am Ende ist man immer davon abhängig, dass die benutzten Tools weiter gepflegt werden. Kaufsoftware ist letztlich ähnlich stark betroffen von Konkursen wie freie Software vom Einschlafen der Community. Wenn es dann so weit sein sollte muss man sich eben umschauen. (Ein Grund weshalb mir Miet-Software gewaltig stinkt. Gekauft kann ich wenigstens die letzte Version so lange nutzen bis eine Alternative gefunden und eingebunden wurde.)

Zitat:

Die Mächtigkeit eines solchen Systems ist ja kein Zwang. Das wartet alles geduldig.

Bis zu dem Tag, an dem etwas nicht reibungslos läuft, und man dem Kunden sagen muss: Öhm, du hast jetzt mal 2 Tage Produktionsausfall, weil ich muss Doku lesen. Komplett illusorisch.

Zitat:

Aber ich würde bspw. immer XML mit Komponenten erzeugen, bevor ich anfange Klammer auf zu usw. zu Fuß im Delphicode zu machen. Und wenn es halt direkt per SQL geht, umso besser.

Da gibt's aber auch andere Lösungen, die nicht gleich ein ganzes DBMS-Ökosystem hinter sich her schlörren :). (Noch ein Pfeifen: Wir haben bis Dato noch nie Datenaustausch via XML gehabt. Im Industrieumfeld sind immer noch Fixed-Width-Field Textfiles und Verwandte davon en vogue. Wenn überhaupt mal etwas via Dateien übertragen wird.)

Zitat:

Was die Komponenten angeht, tut's ja vielleicht firedac.

D10.2.3 Professional - wir dürfen nur lokal. Letztlich wäre zwar ein eigener kleiner "Web"-Server cool, sodass unsere Clients nicht mehr direkt auf die DB müssten, aber das ist ein Mammut-Prjoket, dass ich mir zumindest auf absehbare Zeit nicht zusätzlich zum Port unserer VCL-verheirateten Software auf FMX (und generell neueres schöneres Delphi) aufbürden will/kann. Ich arbeite hier über 20 Jahre Stagnation der internen Tools und Arbeitsweisen auf, und bin ziemlich der einzige da dran. Alles zu seiner Zeit. Es muss ja nebenbei trotzdem noch alles laufen, es sind ständig neue Projekte da für die ich eine aktuelle funktionierende Lösung bieten muss, und ich will auch nicht 100 verschiedene Evolutionsstufen pflegen müssen, bedenkt man dass in unserer Branche Systeme auch gerne mal über 20 Jahre lang betrieben werden. Viel Rumprobieren führt da nur zu Chaos und Zeit die der Lebensunterhaltsgenerierung flöten geht.

Zitat:

Evtl ist ja der Umstieg gerade in einer Situation, wo "nicht viel gebracht" wird, besonders einfach und naheliegend.

"Nicht viel gebraucht" heißt ja nur, dass wir kaum mehr als absolute Basis-Features einer DB nutzen, nicht dass wir sie selten nutzen. Unsere ~50 Anlagen sind größtenteils 24/7 ganzjährig in Betrieb und müssen einfach laufen. Die DB ist schon noch das Backend der gesamten Anlagensteuerung und stellt die Schnittstellen zu den Bedienoberflächen zur Verfügung. So ist's ja nicht.

Ich merke doch recht oft in letzter Zeit, dass sich die industrielle Softwareentwicklung, insbesondere im Bereich der eher Hardwarenahen Maschinensteuerung, doch immer weiter von der restlichen Software-Welt entfernt. Gerade was Features, Langlebigkeit, Konnektivität und "Concept-Turnover" angeht. Wir sehen da bestimmt oft altbacken aus, aber das einzige oberste wichtigste Gebot in dem Bereich ist: Es muss laufen. Immer, auch nachts, auch an Wochenenden, und jede Stunde Ausfall kostet unmittelbar mehrere zigtausend Euros. Innovation und Experimenten steht man da auf Kundenseite in der Regel sehr skeptisch gegenüber. Viele Anbieter versuchen zwar auch hier mit tollen "Flashy" Tools und schmucken Schlagworten die Managerbärte zu schmieren, aber wenn man wie wir meistens mit dem technischen Personal arbeitet, merkt man ganz schnell dass die auch wissen, was die Manager wirklich glücklich macht: Wenn das Werk brummt und der Rubel rollt. Das kann keine noch so schmucke App allein leisten.

[jobo]

Die Linux/Windows Diskrepanz ist nicht so dramatisch, dass man das System nicht für Windows empfehlen kann. mysql/maria hat ja die gleiche Entstehungsgeschichte.
Du kannst Dir das System für Windows runterladen und loslegen, Du musst keine Linuxkenntnis haben. Es ist einfach so, dass Linux das (kostenlose) Hausbetriebssystem ist. Mag sein, dass das im Industrieeinsatz niemand interessiert. Ein paar Euro mehr, weil eben Windows benötigt wird, jucken bei den Gesamtkosten für Produktionsanlagen wahrscheinlich niemand.
Das Risiko, in dem von Dir beschriebenen Umfeld vor einem unlösbaren Problem zu stehen, sehe ich als sehr gering an und falls es eintritt, dann mit einer ähnlichen Wahrscheinlichkeit wie bei Maria.
Ein Schuh wird draus, wenn man sich mit den Systemen (maria oder pg) in die Linuxwelt begibt. Denn hier erhält man m.E. besser/schneller Hilfe von der Community. Professionell kann man natürlich für beide DB auch offizielle Wartungsverträge abschließen.

Wie auch immer, man kann sich nicht um alles kümmern und man wird wahrscheinlich so vorgehen, wie Du sagtest, wenn der Schuh drückt, schaut man sich um.
Einfach mal reinschnuppern schadet allerdings bestimmt nicht. Die einzige Hürde ist wahrscheinlich die erste Verbindung zur DB, das geht natürlich bei jedem System anders. Danach bist Du sicher gleich im Flow.

[Medium]

:thumb: Da hast du natürlich Recht. Kommt Muße, kommt pg!