Datenbankstruktur und Abfragen/Views bei m:n

[Markus92]

Hallo,

ich habe ein Problem und bin mir nicht ganz sicher wie ich es lösen kann:
meine aktuelle struktur sieht so aus:
tbl_kaestchen
ID|Anzahl|fk_1|fk_2|fk_3|fk_4
1|10000|3|NULL|NULL|NULL
2|5000|2|4|5|NULL
3|1000|1|3|5|6

Sagen wir es handelt sich hierbei um verschiedene Kästchen mit verschiedenen Münzen. Jedes Kästchen enhtält eine Unterschiedliche Anzahl an Münzen. Die fks beziehen sich alle auf die selbe Tabelle und stellen die Münzen dar.
Nun möchte ich wissen wie oft es jede Münze gibt:
query
MID|Anzahl
1|1000
2|5000
3|11000
4|5000
5|6000
6|1000

Leider weiß ich nicht wie ich diese Abfrage gestalten soll, da es mit einer einfachen "group by" Funktion nicht funktioniert.

Als ich so über das Problem nachgedacht habe dacht ich es wäre vielleicht besser das ganze in eine n:m-Funktion umzufunktionieren:
tbl_muenzen
ID|...
1|...
2|...
3|...
4|...
5|...
6|...

muenzen_kaestchen_klein
fk_muenzen|fk_kaestchen_klein
1|3
2|2
2|4
2|5
3|1
3|3
3|5
3|6

tbl_kaestchen_klein
ID|Anzahl
1|10000
2|5000
3|1000

Hierzu bräuchte ich dann allerdings einen view der mir tbl_kaestchen darstellt. Das Problem mit der Anzahl wäre dann aber gelöst.

Vielen Dank für eure Unterstützung.

 

[akretschmer]

es gelingt mir nicht, einen Zusammenhang zwischen z.B. deiner ersten Tabelle und der Anzahl in der zweiten Tabelle zu finden. Deine Tabelle "muenzen_kaestchen_klein" hat im Kopf 3 Spalten, dann aber jedoch nur 2 Werte, in der Tabelle danach wieder nicht nachvollziehbare Zahlen.

Sorry, so wird das wohl nix.

 

[Markus92]

Ok - dann versuch ich das ganze etwas zu überarbeiten

 

[Markus92]

tbl_kaestchen
ID|Anzahl|fk_MID1|fk_MID2|fk_MID3|fk_MID4
1|10000|3|NULL|NULL|NULL
2|5000|2|4|5|NULL
3|1000|1|3|5|6

Bedeutet die hinteren 4 Spalten sind für die MIDs die im Kästchen enthalten sind.
das erste kästchen enthält die münze 3 und es gibt davaon 10000
das zweite enthält die münzen 2,4,5 und es gibt davon 5000
das dritte enthält die münzen 1,3,5,6 und es gibt davon 1000
daraus die summen in der 2. tabelle (query)

Die Tabelle muenzen_kaestchen_klein hat 2 Spalten:
fk_muenzen
fk_kaestchen_klein
Diese 2 dienen als verlinkung für die m:n-Beziehung
Die Anzahlen in der Tabelle tbl_kaestchen_klein sind die Zahlen aus tbl_kaestchen

Also im Prinzip soll das 2x das selbe sein:
1. tbl_kaestchen
2. tbl_muenzen---1:n---tbl_muenzen_kaestchen_klein----n:1-----tbl_kaestchen_klein


Was ich dann am Ende möchte sind dann:
1. Tabelle 1 als Tabelle oder als view über 3-6
2. Tabelle 2 als View

Ich hoffe das ist so etwas verständlicher geworden

 

[akretschmer]

tbl_kaestchen
ID|Anzahl|fk_MID1|fk_MID2|fk_MID3|fk_MID4
1|10000|3|NULL|NULL|NULL
2|5000|2|4|5|NULL
3|1000|1|3|5|6

Bedeutet die hinteren 4 Spalten sind für die MIDs die im Kästchen enthalten sind.
das erste kästchen enthält die münze 3 und es gibt davaon 10000
das zweite enthält die münzen 2,4,5 und es gibt davon 5000
das dritte enthält die münzen 1,3,5,6 und es gibt davon 1000
daraus die summen in der 2. tabelle (query)

• id 3: wie viele Münzen von z.B. 2 gibt es?
• es gibt (mindestens) 6 verschiedene Münzen, aber nur 4 Spalten dafür.

Du willst: "Nun möchte ich wissen wie oft es jede Münze gibt:" - diese Info ist schlicht nicht verfügbar.

den Rest versteh ich grad (noch immer) nicht.

tl;dr

Das ist Müll.

 

[Markus92]

ok - ich beschränke mich mal auf die Ausgangssituation:

tbl_kaestchen
ID|Anzahl|fk_MID1|fk_MID2|fk_MID3|fk_MID4
1|10000|3|NULL|NULL|NULL
2|5000|2|4|5|NULL
3|1000|1|3|5|6

Das ist die aktuelle Tabelle
ID ist der PK
Anzahl sagt wie oft es diese ganze Kasette gibt.
die 4 fks stehen für maximal 4 münzen die in jeder kasette sein können.
jede mid steht für 1 münze
das bedeutet es gibt 3 verschiedene kasetten (insgesamt 16000).
es gibt 6 verschiedene münzen (insgesamt 10000+3*5000+4*1000=29000)

Die 2 Fragen sind:
1. Ist die Datenstruktur so korrekt?
2. wie erhalte ich die Summen der einzelnen Münzen?

 

[akretschmer]

test=*# select * from markus92 ;
 id | anzahl | fk1 | fk2 | fk3 | fk4
----+--------+-----+-----+-----+-----
  1 |  10000 |   3 |     |     |   
  2 |   5000 |   2 |   4 |   5 |   
  3 |   1000 |   1 |   3 |   5 |   6
(3 rows)

test=*#

test=*# select

  case when (fk1 = 1) or (fk2 = 1) or (fk3 = 1) or (fk4 = 1) then anzahl else 0 end as muenze1,
  case when (fk1 = 2) or (fk2 = 2) or (fk3 = 2) or (fk4 = 2) then anzahl else 0 end as muenze2,
  case when (fk1 = 3) or (fk2 = 3) or (fk3 = 3) or (fk4 = 3) then anzahl else 0 end as muenze3,
  case when (fk1 = 4) or (fk2 = 4) or (fk3 = 4) or (fk4 = 4) then anzahl else 0 end as muenze4,
  case when (fk1 = 5) or (fk2 = 5) or (fk3 = 5) or (fk4 = 5) then anzahl else 0 end as muenze5,
  case when (fk1 = 6) or (fk2 = 6) or (fk3 = 6) or (fk4 = 6) then anzahl else 0 end as muenze6

from markus92 ;
 muenze1 | muenze2 | muenze3 | muenze4 | muenze5 | muenze6
---------+---------+---------+---------+---------+---------
       0 |       0 |   10000 |       0 |       0 |       0
       0 |    5000 |       0 |    5000 |    5000 |       0
    1000 |       0 |    1000 |       0 |    1000 |    1000
(3 rows)

test=*# with foo as (select

  case when (fk1 = 1) or (fk2 = 1) or (fk3 = 1) or (fk4 = 1) then anzahl else 0 end as muenze1,
  case when (fk1 = 2) or (fk2 = 2) or (fk3 = 2) or (fk4 = 2) then anzahl else 0 end as muenze2,
  case when (fk1 = 3) or (fk2 = 3) or (fk3 = 3) or (fk4 = 3) then anzahl else 0 end as muenze3,
  case when (fk1 = 4) or (fk2 = 4) or (fk3 = 4) or (fk4 = 4) then anzahl else 0 end as muenze4,
  case when (fk1 = 5) or (fk2 = 5) or (fk3 = 5) or (fk4 = 5) then anzahl else 0 end as muenze5,
  case when (fk1 = 6) or (fk2 = 6) or (fk3 = 6) or (fk4 = 6) then anzahl else 0 end as muenze6

from markus92 )

select 1 as muenz_id, sum(muenze1) from foo
union all
select 2, sum(muenze2) from foo
union all
select 3, sum(muenze3) from foo
union all
select 4, sum(muenze4) from foo
union all
select 5, sum(muenze5) from foo
union all
select 6, sum(muenze6) from foo;
 muenz_id |  sum  
----------+-------
        1 |  1000
        2 |  5000
        3 | 11000
        4 |  5000
        5 |  6000
        6 |  1000
(6 rows)

test=*#

Irgendie ist das von hinten durch die Brust. Warum sind immer gleich viele Münzen da? Was pasiert, wenn dem mal nicht so ist?

 

[Markus92]

Ok, erstmal danke dafür, aber:

-wie sieht es aus bei wesentlich mehr Münzen (ca.3500), da ist die Abfrage so nicht mehr händelbar und auch nicht wirklich dynamisch

-die Anzahl der Münzen pro Kästchen ist relativ gleichbleibend als Maximum habe ich da 45 ausgemacht

Meine Idee wäre dann die Tabelle etwas aufzulösen und daraus eine m:n-Beziehung zu machen was die Mümzsummen vereinfachen würde.
Ich bräuchte dann allerdings einen view, der mir genau diese Daten so wie in der Ursprungstabelle dargestellt ausgibt.

 

[akretschmer]

Na, weil Du es bist:

test=*# select * from markus_neu ;
 id | anzahl | muenze
----+--------+--------
  1 |  10000 |      3
  2 |   5000 |      2
  2 |   5000 |      4
  2 |   5000 |      5
  3 |   1000 |      1
  3 |   1000 |      3
  3 |   1000 |      5
  3 |   1000 |      6
(8 rows)

test=*# with foo as (
  select id, anzahl, muenze, row_number() over (partition by id order by muenze) r from markus_neu
)
select
  id
  , anzahl
  , max(muenze) filter (where r=1) as fk1
  , max(muenze) filter (where r=2) as fk2
  , max(muenze) filter (where r=3) as fk3
  , max(muenze) filter (where r=4) as fk4
from foo group by id, anzahl order by id;
 id | anzahl | fk1 | fk2 | fk3 | fk4
----+--------+-----+-----+-----+-----
  1 |  10000 |   3 |     |     |   
  2 |   5000 |   2 |   4 |   5 |   
  3 |   1000 |   1 |   3 |   5 |   6
(3 rows)

test=*#

 

[Markus92]

Dazu hätte ich noch 2 Fragen:
1. was ist aus Normalisierungssicht besser?
2.ist das untere query dann auch dynamisch möglich?

 

[akretschmer]

1. die Tabelle markus_neu wäre besser, weil da keine Grenzen existieren.
2. nein, aber mit genügend krimineller Energie könnte man das in eine Funktion basteln, die diese Abfrage dynamisch erstellt ;-)

 

[i2stiller]

Hi Markus92

Ganz verstehe ich auch noch nicht die Idee hinter den Tabellen.
Fangen wir mal ganz von vorne und metasprachlich an
Du hast eine gewissen Anzahl von Münzarten
(Nicht die einzelnen Münzen, sondern nur der verschieden Arten/Typen!)
Du hast Kästchen mit Münzen gefüllt
In jeder diese Kästchen befinden sich Münzen verschiedener Arten
Durch einen glücklichen Zufall befinden sich in jedem Kasten immer die gleiche Anzahl von Münzen jeder Art.
(Dieser Punkt ist jener, der uns wohl an dem bisherigen Modell irritiert)

Soweit okay?

Und nun willst darüber eine Auswertung fahren, die was bewerkstelligen soll?
Welche Zahlen benötigst du
* Münzen pro Kästchen?
* Münzen pro Münzart?
*...
Wie gesagt, hier geht es noch nicht um ein SQL-konformes Datenmodell sonder eine "umgangssprachliche "Beschreibung des Problems

Wenn du mir da auf die Sprünge helfen würdest, könnt man ja
* ein passendes Datenmodel finden
* einen entsprechenden View darauf aufbauen