Vervollständigung von Tabellen

[Christian H. Kuhn]

Hallo Gemeinde,

Die Kurzfassung: Ich habe eine Tabelle, in die zu einem mehrspaltigen
Schlüssel Werte eingetragen sind. Zur graphischen Darstellung möchte
ich, dass zu allen kombinatorisch möglichen Schlüsseln ein Eintrag
existiert, dass also für die Schlüssel, zu denen kein „echter“ Wert
existiert, 0 eingetragen wird. Dafür suche ich einen effizienten
Algorithmus.

Und jetzt ausführlich:
Als selbständiger Handelsvertreter bekomme ich von meinem Auftraggeber
jeden Monat eine Liste, in der die Monatsumsätze jedes Kunden in meinem
Gebiet enthalten sind. Monatliche Listen sind nicht hilfreich, um die
Übersicht über mehrere Jahre zu behalten. Ich schreibe mir daher gerade
ein kleines JavaFX-Tool, das Umsätze und Umsatzverläufe graphisch darstellt.

Ich habe also eine Postgresql-Datenbank angelegt, in die ich die
monatlichen Listen importiere. Ich benutze drei Tabellen:

institutions enthält die Kunden und hat die spalten key, name,
postalcode, city, street. key ist eine eindeutige ID, die von der
Software des Auftraggebers vergeben wird. key wird als Primärschlüssel
benutzt.

products hat eine Spalte name und enthält alle vorkommenden Produktnamen.

sales hat die Spalten key, product, sales, month, year, hv. key ist ein
Foreign key und bezieht sich auf institutions.key. product ist ein
foreign key und bezieht sich auf products.name. Der primary key ist
(month, year, hv, key, product).

Die vierte Tabelle ist für dieses Problem ohne Bedeutung.

Das Tool zeigt in den entsprechenden Graphen Linienverbindungen zwischen
existierenden Punkten. Das irritiert, da nicht jeder Kunde jedes Produkt
in jedem Monat bestellt. Ich hätte also gerne Nulleinträge für jeden
primary key. Die fehlenden Einträge kann ich aber nicht auf trivialem
Weg eingeben, da sie eben fehlen und daher nicht auswählbar sind.

Meine existierende Lösung funktioniert, ist aber alles andere als
effizient. Ich erzeuge jeden möglichen Schlüssel und trage 0 ein.
Existiert schon ein Wert, wird das Überschreiben mit einer ON
CONFLICT-Klausel verhindert.

String sql = "insert into sales (select distinct on (sales.month,
sales.year, products.name, institutions.key) institutions.key,
products.name, '0' as sales, '0' as provision, sales.month, sales.year,
" + hVName + " as hv from sales cross join products cross join
institutions) on conflict on constraint main do nothing;"

Das dauert natürlich. Nicht zuletzt, weil jeder bereits existierende
Datensatz angefasst werden muss.

Gibt es da elegantere Möglichkeiten? Ich habe mir auch überlegt, den
Vorgang außerhalb der Datenbank in Java durchzuführen und nur die neuen
Null-Zeilen einzutragen. Aber eigentlich sollte ein DBMS sowas intern
effizienter durchführen als ein externes Tool, dafür ist es doch da?

TIA
QNo

[Tim Landscheidt]

"Christian H. Kuhn" <qno-...@qno.de> wrote:

> […]

> Gibt es da elegantere Möglichkeiten? Ich habe mir auch überlegt, den
> Vorgang außerhalb der Datenbank in Java durchzuführen und nur die neuen
> Null-Zeilen einzutragen. Aber eigentlich sollte ein DBMS sowas intern
> effizienter durchführen als ein externes Tool, dafür ist es doch da?

Wenn Du eine Tabelle mit „LEFT JOIN“ in die Abfrage einbin-
dest, erhältst Du NULL als Werte für Zeilen ohne Verknüp-
fung. Diese kannst Du mittels COALESCE() in Nullen umwan-
deln.

Sprich: Du erzeugst zuerst eine Abfrage, die Dir für jeden
Kunden, jedes Produkt, jeden Monat jedes Jahres eine Zeile
ausgibt, à la (ungetestet):

| SELECT institutions.key, product.name, year, month
| FROM institutions,
| product,
| generate_series(2000, 2017) AS Years(Year),
| generate_series(1, 12) AS Months(Month);

(Du könntest Jahr und Monat auch aus sales übernehmen, aber
durch die Doppelverwendung und eventuelle Monate ohne Ein-
träge würde das das Konzept hier nur verkomplizieren.)

Das Ergebnis verknüpfst Du mit sales:

| SELECT key, name, year, month,
| sales
| FROM (SELECT institutions.key, product.name, year, month,
| sales
| FROM institutions,
| product,
| generate_series(2000, 2017) AS Years(Year),
| generate_series(1, 12) AS Months(Month)) AS SubQuery
| LEFT JOIN sales
| ON institutions.key = sales.key AND
| product.name = sales.product AND
| years.year = sales.year AND
| months.month = sales.month;

und ersetzt NULLen durch Nullen:

| SELECT key, name, year, month,
| COALESCE(sales, 0)
| FROM (SELECT institutions.key, product.name, year, month,
| sales
| FROM institutions,
| product,
| generate_series(2000, 2017) AS Years(Year),
| generate_series(1, 12) AS Months(Month)) AS SubQuery
| LEFT JOIN sales
| ON institutions.key = sales.key AND
| product.name = sales.product AND
| years.year = sales.year AND
| months.month = sales.month;

Tim

[Tim Landscheidt]

I wrote:

> […]

> | SELECT key, name, year, month,
> | COALESCE(sales, 0)
> | FROM (SELECT institutions.key, product.name, year, month,
> | sales
> | FROM institutions,
> | product,
> | generate_series(2000, 2017) AS Years(Year),
> | generate_series(1, 12) AS Months(Month)) AS SubQuery
> | LEFT JOIN sales
> | ON institutions.key = sales.key AND
> | product.name = sales.product AND
> | years.year = sales.year AND
> | months.month = sales.month;

Wie gesagt, es war ungetestet (und ist es immer noch), aber
der letzte Teil müsste natürlich:

| ON SubQuery.key = sales.key AND
| SubQuery.name = sales.product AND
| SALES.year = sales.year AND
| SubQuery.month = sales.month;

heißen.

Tim

[Tim Landscheidt]

I wrote:

> […]

> Wie gesagt, es war ungetestet (und ist es immer noch), aber
> der letzte Teil müsste natürlich:

> | ON SubQuery.key = sales.key AND
> | SubQuery.name = sales.product AND
> | SALES.year = sales.year AND
> | SubQuery.month = sales.month;

> heißen.

Aua. Statt "SALES.year" natürlich "SubQuery.year".

Tim