1 Temat przez przesq

  • przesq
  • +++
  • Nieaktywny
  • Zarejestrowany: 2011-03-23
  • Posty: 46

Temat: Różnica 2 tabel

Mam 2 tabele wynikowe utworzone przez widok, następnie wykorzystuję tabelę przestawną do zobrazowania tych danych.

Potrzebuję teraz zrobić różnicę tych tabel. Obie mają taką samą strukturę czyli:

id_gatunek
suma
id_punkt

Pierwsza pokazuje założenia, druga realizację tych założeń chociaż nie do końca bowiem może i występuje id_punkt nie ujęty w tabeli pierwszej - założeniu.

Podaję wartości na chwilę obecną wraz ze strukturą:
tabela założeniowa - wynik_zzr

DROP TABLE IF EXISTS wynik_zzr;
CREATE TABLE wynik_zzr (
  id_gatunek integer NOT NULL,
  suma integer NOT NULL,
  id_punkt integer NOT NULL
);

insert into wynik_zzr values (1, 118.48, 8); 
insert into wynik_zzr values (1, 196.53, 255); 
insert into wynik_zzr values (1, 86.8, 292); 
insert into wynik_zzr values (1, 215.91, 471); 
insert into wynik_zzr values (1, 210.11, 474); 
insert into wynik_zzr values (1, 108.22, 565); 
insert into wynik_zzr values (1, 38.78, 633); 
insert into wynik_zzr values (1, 31.56, 636); 
insert into wynik_zzr values (2, 40.21, 439); 
insert into wynik_zzr values (3, 27.57, 8); 
insert into wynik_zzr values (3, 9.95, 19); 
insert into wynik_zzr values (3, 20.12, 117); 
insert into wynik_zzr values (3, 38.2, 213); 
insert into wynik_zzr values (3, 1.93, 636); 
insert into wynik_zzr values (3, 10.08, 668); 
insert into wynik_zzr values (3, 5.03, 672); 
insert into wynik_zzr values (3, 70.01, 795); 
insert into wynik_zzr values (3, 5.06, 797); 
insert into wynik_zzr values (3, 5.01, 803); 
insert into wynik_zzr values (4, 49.89, 249); 
insert into wynik_zzr values (4, 49.78, 254); 
insert into wynik_zzr values (4, 61.22, 259); 
insert into wynik_zzr values (4, 88.9, 260); 
insert into wynik_zzr values (4, 69.48, 301); 
insert into wynik_zzr values (4, 72.58, 315); 
insert into wynik_zzr values (4, 90.7, 324); 
insert into wynik_zzr values (4, 37.74, 610); 
insert into wynik_zzr values (4, 86.35, 637); 
insert into wynik_zzr values (4, 35.43, 719); 
insert into wynik_zzr values (8, 18.51, 8); 
insert into wynik_zzr values (8, 120.49, 73); 
insert into wynik_zzr values (8, 79.56, 78); 
insert into wynik_zzr values (8, 99.89, 96); 
insert into wynik_zzr values (8, 120.48, 98); 
insert into wynik_zzr values (8, 119.17, 119); 
insert into wynik_zzr values (8, 150.79, 131); 
insert into wynik_zzr values (8, 120.14, 159); 
insert into wynik_zzr values (8, 171.27, 186); 
insert into wynik_zzr values (8, 49.58, 207); 
insert into wynik_zzr values (8, 40.52, 208); 
insert into wynik_zzr values (8, 34.97, 211); 
insert into wynik_zzr values (8, 438.52, 249); 
insert into wynik_zzr values (8, 50.05, 254); 
insert into wynik_zzr values (8, 42.14, 259); 
insert into wynik_zzr values (8, 40.23, 272); 
insert into wynik_zzr values (8, 159.82, 317); 
insert into wynik_zzr values (8, 35.61, 318); 
insert into wynik_zzr values (8, 134.69, 334); 
insert into wynik_zzr values (8, 161.12, 343); 
insert into wynik_zzr values (8, 87.28, 344); 
insert into wynik_zzr values (8, 185.4, 365); 
insert into wynik_zzr values (8, 10.02, 372); 
insert into wynik_zzr values (8, 41.26, 399); 
insert into wynik_zzr values (8, 40.01, 402); 
insert into wynik_zzr values (8, 51.1, 474); 
insert into wynik_zzr values (8, 170.35, 497); 
insert into wynik_zzr values (8, 103.56, 637); 
insert into wynik_zzr values (8, 20.73, 668); 
insert into wynik_zzr values (8, 4.95, 676); 
insert into wynik_zzr values (8, 53.14, 719); 
insert into wynik_zzr values (8, 191.98, 756); 
insert into wynik_zzr values (8, 411.59, 768); 
insert into wynik_zzr values (8, 9.08, 784); 
insert into wynik_zzr values (8, 15.29, 797); 
insert into wynik_zzr values (9, 19.09, 207); 
insert into wynik_zzr values (9, 24.54, 292); 
insert into wynik_zzr values (9, 3.44, 650); 
insert into wynik_zzr values (9, 10.43, 797); 
insert into wynik_zzr values (12, 5.12, 211); 
insert into wynik_zzr values (12, 32.36, 474); 
insert into wynik_zzr values (12, 16.94, 637); 
insert into wynik_zzr values (12, 6.68, 650); 
insert into wynik_zzr values (12, 7.6, 668); 
insert into wynik_zzr values (12, 5.08, 676); 
insert into wynik_zzr values (12, 10.07, 797); 
insert into wynik_zzr values (13, 16.07, 8); 
insert into wynik_zzr values (13, 30.12, 159); 
insert into wynik_zzr values (13, 30.02, 186); 
insert into wynik_zzr values (13, 14.3, 207); 
insert into wynik_zzr values (13, 1.53, 255); 
insert into wynik_zzr values (13, 11.38, 474); 
insert into wynik_zzr values (13, 34.36, 650); 
insert into wynik_zzr values (14, 9.02, 636); 
insert into wynik_zzr values (14, 14.51, 650); 
insert into wynik_zzr values (15, 4.99, 650);

tabela realizacji - wynik_zzw

DROP TABLE IF EXISTS wynik_zzw;
CREATE TABLE wynik_zzw (
  id_gatunek integer NOT NULL,
  suma integer NOT NULL,
  id_punkt integer NOT NULL
);


insert into wynik_zzw values (1, 30.54, 8); 
insert into wynik_zzw values (1, 56.85, 255); 
insert into wynik_zzw values (1, 89.22, 292); 
insert into wynik_zzw values (1, 232.1, 474); 
insert into wynik_zzw values (1, 24.48, 608); 
insert into wynik_zzw values (1, 13.32, 645); 
insert into wynik_zzw values (2, 4.08, 8); 
insert into wynik_zzw values (2, 18.42, 439); 
insert into wynik_zzw values (2, 0.24, 636); 
insert into wynik_zzw values (3, 23.37, 8); 
insert into wynik_zzw values (3, 2.68, 19); 
insert into wynik_zzw values (3, 34.55, 213); 
insert into wynik_zzw values (3, 0.71, 636); 
insert into wynik_zzw values (3, 67.41, 795); 
insert into wynik_zzw values (5, 8.32, 645); 
insert into wynik_zzw values (8, 154, 73); 
insert into wynik_zzw values (8, 94.16, 78); 
insert into wynik_zzw values (8, 123.9, 159); 
insert into wynik_zzw values (8, 172.2, 186); 
insert into wynik_zzw values (8, 66.35, 207); 
insert into wynik_zzw values (8, 41.3, 208); 
insert into wynik_zzw values (8, 14, 211); 
insert into wynik_zzw values (8, 303.1, 249); 
insert into wynik_zzw values (8, 16.8, 317); 
insert into wynik_zzw values (8, 105.7, 365); 
insert into wynik_zzw values (8, 10.5, 372); 
insert into wynik_zzw values (8, 1.4, 375); 
insert into wynik_zzw values (8, 147.7, 497); 
insert into wynik_zzw values (8, 117.6, 637); 
insert into wynik_zzw values (8, 5.6, 650); 
insert into wynik_zzw values (8, 187.6, 756); 
insert into wynik_zzw values (8, 130.1, 768); 
insert into wynik_zzw values (9, 18.9, 207); 
insert into wynik_zzw values (9, 26.45, 292); 
insert into wynik_zzw values (12, 7.8, 637); 
insert into wynik_zzw values (12, 5.2, 650); 
insert into wynik_zzw values (13, 13.65, 117); 
insert into wynik_zzw values (13, 32.5, 159); 
insert into wynik_zzw values (13, 9.1, 186); 
insert into wynik_zzw values (13, 26, 650); 
insert into wynik_zzw values (13, 4.55, 756); 
insert into wynik_zzw values (14, 11.7, 650); 
insert into wynik_zzw values (15, 1.49, 474); 
insert into wynik_zzw values (15, 9.1, 650); 
insert into wynik_zzw values (19, 0.75, 637); 
insert into wynik_zzw values (19, 0.76, 676); 
insert into wynik_zzw values (21, 0.75, 375); 
insert into wynik_zzw values (21, 1, 637);

potrzebuję stworzyć tabelę o takiej samej strukturze z dodatkowym polem o wartościach 1,2,3 dla założenia:

od sumy z tabeli wynik_zzw odejmujemy sumę z tabeli wynik_zzr

w zależności od wyniku

wartość dodatnia - dodatkowe pole przyjmie wartość 1
wartość ujemna - dodatkowe pole przyjmie wartość 2
wartość występuje w wynik_zzw natomiast nie występuje w wynik_zzr - dodatkowe pole przyjmie wartość 3

to dodatkowe pole potrzebuję ponieważ muszę znaleźć te id_punkt, które były w realizacji - wynik_zzw a nie były w założeniu - wynik_zzr

2 Odpowiedź przez c_michal

  • c_michal
  • *****
  • Nieaktywny
  • Zarejestrowany: 2009-01-28
  • Posty: 319

Odp: Różnica 2 tabel

select
w.id_gatunek , w.id_punkt,w.suma-r.suma as suma
, case to_char(w.suma-r.suma,'SG') when '+' then 1 when '-' then 2 else 3 end as znacznik
from wynik_zzw w
left join wynik_zzr r on r.id_gatunek=w.id_gatunek and r.id_punkt=w.id_punkt

Ostatnio edytowany przez c_michal (2012-11-07 22:49:22)

3 Odpowiedź przez przesq

  • przesq
  • +++
  • Nieaktywny
  • Zarejestrowany: 2011-03-23
  • Posty: 46

Odp: Różnica 2 tabel

Widzę, że działa ale nie do końca tak jak chciałem, może źle te warunki opisałem.
Też przy tworzeniu tabel teraz zauważyłem, że suma jest integer a ma być real aczkolwiek to nie ma większego znaczenia przy prawidłowym działąniu.

Zauważyłem, że jeżeli suma występuje w wynik_zzw a nie występuje w wynik_zzr to wartość sumy jest pusta np dla id_punkt 608 i gatunek=1 suma=24 a w wyniku jest puste i w tym przypadku wynik powinien być 24.

Kolejna sprawa jak widzę należałoby wprowadzić znacznik=4 dla tych wartości, które nie występują w wynik_zzr a występuje w wynik_zzw - tak jak pisałem wcześniej "to dodatkowe pole potrzebuję ponieważ muszę znaleźć te id_punkt, które były w realizacji - wynik_zzw a nie były w założeniu - wynik_zzr" - ten wynik wogóle nie uwzględnia tych wartości.

Jeżeli nie jest możliwe zrobienie tego w jednym zapytaniu to dla znacznik=4 może być osobne zapytanie bo i tak ta część będzie wyświetlana na końcu.

Ostatnio edytowany przez przesq (2012-11-08 11:32:04)

4 Odpowiedź przez c_michal

  • c_michal
  • *****
  • Nieaktywny
  • Zarejestrowany: 2009-01-28
  • Posty: 319

Odp: Różnica 2 tabel

5 Odpowiedź przez przesq

  • przesq
  • +++
  • Nieaktywny
  • Zarejestrowany: 2011-03-23
  • Posty: 46

Odp: Różnica 2 tabel

6 Odpowiedź przez c_michal

  • c_michal
  • *****
  • Nieaktywny
  • Zarejestrowany: 2009-01-28
  • Posty: 319

Odp: Różnica 2 tabel

Oj Oj - wstydź się zadawać tak banalne pytania .

Użyj jednej z tych funkcji ale uważaj na wartości ujemne (poczytaj help)

select trunc(2.525456789,2),round(2.525456789,2)

7 Odpowiedź przez przesq

  • przesq
  • +++
  • Nieaktywny
  • Zarejestrowany: 2011-03-23
  • Posty: 46

Odp: Różnica 2 tabel

Oj tam - wstyd to kraść. Pgsql to tylko kilka % mojej pracy a raczej moje czyste chęci. Dział jest dla początkujących.
Dzięki za pomoc.

Ostatnio edytowany przez przesq (2012-11-09 08:47:32)

8 Odpowiedź przez przesq

  • przesq
  • +++
  • Nieaktywny
  • Zarejestrowany: 2011-03-23
  • Posty: 46

Odp: Różnica 2 tabel

Doszedłem do crosstab i nie działa mi to nie wiem dlaczego.

Create View wynik_zzwr As
select
w.id_gatunek , w.id_punkt,w.suma-coalesce(r.suma,0.0) as suma
, case to_char(w.suma-r.suma,'SG') when '+' then 1 when '-' then 2 else 3 end as znacznik
from wynik_zzw w
left join wynik_zzr r on r.id_gatunek=w.id_gatunek and r.id_punkt=w.id_punkt
union all
select r.id_gatunek , r.id_punkt,-1.0 * r.suma as suma, 4 as znacznik
from wynik_zzr r
where not exists (select 1 from wynik_zzw w where w.id_gatunek=r.id_gatunek and w.id_punkt=r.id_punkt);

Drop View If Exists wynik_tmp; Create View wynik_tmp As select id_gatunek, id_punkt, suma, znacznik, id_punkt || '-' || znacznik AS punkt_zn from wynik_zzwr;

Select * From crosstab ( 'Select punkt_zn, id_punkt, znacznik, id_gatunek, suma From wynik_tmp Order By 1', 
'Select id_gatunek FROM gatunek WHERE id_gatunek IN (4,5,20,7,9,13,17,18,21)' ) 
As ( "punkt_zn" text, id_punkt int, znacznik int, "gatunek4" real, "gatunek5" real, "gatunek20" real, "gatunek7" real, "gatunek9" real, "gatunek13" real, "gatunek17" real, "gatunek18" real, "gatunek21" real );


DROP TABLE IF EXISTS gatunek;
CREATE TABLE gatunek (
  id_gatunek integer NOT NULL,
  gatunek varchar (15) NOT NULL
);

insert into gatunek values (1, 'gatunek1');
insert into gatunek values (2, 'gatunek2');
insert into gatunek values (3, 'gatunek3');
insert into gatunek values (4, 'gatunek4');
insert into gatunek values (5, 'gatunek5');
insert into gatunek values (6, 'gatunek6');
insert into gatunek values (7, 'gatunek7');
insert into gatunek values (8, 'gatunek8');
insert into gatunek values (9, 'gatunek9');
insert into gatunek values (10, 'gatunek10');
insert into gatunek values (11, 'gatunek11');
insert into gatunek values (12, 'gatunek12');
insert into gatunek values (13, 'gatunek13');
insert into gatunek values (14, 'gatunek14');
insert into gatunek values (15, 'gatunek15');
insert into gatunek values (16, 'gatunek16');
insert into gatunek values (17, 'gatunek17');
insert into gatunek values (18, 'gatunek18');
insert into gatunek values (19, 'gatunek19');
insert into gatunek values (20, 'gatunek20');
insert into gatunek values (21, 'gatunek21');

wynik_tmp - być może ta tabela jest nie potrzebna, być może na etapie tworzenia wynik_zzwr można dodać pole łączące id_punkt-znacznik aby przy crosstab znaleźć wszystkie kombinacje punkt-znacznik.

Za dużo dziur powstało.

Nasuwa mi się jeszcze jedno pytanie: czy jest możliwość aby przy crosstab pole suma przyjęło wartość: suma(jakiś znak)znacznik. Teraz każda kombinacja id_punkt - znacznik jest wyświetlana w osobnym wierszu.


Ps. dołączone tabele są tylko poglądowe. Chodzi jedynie o istote działania.


Miałem błąd w zapytaniu wyszukującym mi distinct id_gatunek stąd tyle dziur w tabeli powstało. Prawidłowo wygląda to tak:

Select * From crosstab ( 'Select punkt_zn, id_punkt, znacznik, id_gatunek, suma From wynik_tmp Order By 1', 
'Select id_gatunek FROM gatunek WHERE id_gatunek IN (1,2,3,4,5,8,9,12,13,14,15,19,21)' ) 
As ( "punkt_zn" text, id_punkt int, znacznik int, "gatunek1" real, "gatunek2" real, "gatunek3" real, "gatunek4" real, "gatunek5" real, "gatunek8" real, "gatunek9" real, "gatunek12" real, "gatunek13" real, "gatunek14" real, "gatunek15" real, "gatunek19" real, "gatunek21" real);

Nadal pozostaje aktualne: czy jest możliwość aby przy crosstab pole suma przyjęło wartość: suma(jakiś znak)znacznik. Teraz każda kombinacja id_punkt - znacznik jest wyświetlana w osobnym wierszu.

Ostatnio edytowany przez przesq (2012-11-12 09:19:16)

9 Odpowiedź przez c_michal

  • c_michal
  • *****
  • Nieaktywny
  • Zarejestrowany: 2009-01-28
  • Posty: 319

Odp: Różnica 2 tabel

proponuje byś zmienił koncepcje podejścia do tego problemu (przyczyny crosstab wymaga zdefiniowania rekordu wynikowego więc jeśli dojdzie Ci nowy gatunek to będziesz musiał modyfikować typ rekordu zwracanego przez crosstaba)
Proponuje rozwiązać twój problem tak:
select w.id_punkt
      ,w.id_gatunek
      ,(select gatunek from gatunek where id_gatunek=w.id_gatunek) as gatunek
      ,sum(case when znacznik=1 then suma else null end) as znacznik_1
      ,sum(case when znacznik=2 then suma else null end) as znacznik_2
      ,sum(case when znacznik=3 then suma else null end) as znacznik_3
      ,sum(case when znacznik=4 then suma else null end) as znacznik_4
from wynik_zzwr w
group by w.id_punkt
      ,w.id_gatunek
order by 1,2

10 Odpowiedź przez przesq

  • przesq
  • +++
  • Nieaktywny
  • Zarejestrowany: 2011-03-23
  • Posty: 46

Odp: Różnica 2 tabel

Przed crosstab mam zapytanie wyszukujące distinct id_gatunek, gatunek z tabeli wynik_tmp, zapisuję to do zmiennej w php i doklejam to do zapytania crosstab tak więc nie definiuję ręcznie rekordu wynikowego. Wcześniej miałem zrobione dla każdego id_gatunek i czasami było mało wierszy a kolumn od groma pustych dlatego to zmieniłem.

Podobnie z suma(jakiś znak)znacznik - przepuścił bym sum_m3 przez funkcję explode w php i miałbym to w jednym wierszu.

11 Odpowiedź przez c_michal

  • c_michal
  • *****
  • Nieaktywny
  • Zarejestrowany: 2009-01-28
  • Posty: 319

Odp: Różnica 2 tabel

Twój select z crosstam dział jak najbardziej prawidłowo, problem polega na tym, że oczekujesz (tylko się domyślam), że w jednym wierszu otrzymasz wszystkie kombinacje id_gatunek dla zadanego id_punkt, a to zapytanie zwraca wszystkie id_gatunek dla kombinacji id_punkt i znacznik.
Aby nie było luk musisz wykonać poniższy select ale stracisz informacje o znaczniku

Select * From crosstab ( 'Select id_punkt, id_gatunek, suma From wynik_zzwr Order By id_punkt'
,'Select id_gatunek FROM gatunek WHERE id_gatunek IN (1,2,3,4,5,8,9,12,13,14,15,19,21)' )
As ( id_punkt int,  "gatunek1" real, "gatunek2" real, "gatunek3" real, "gatunek4" real, "gatunek5" real, "gatunek8" real, "gatunek9" real, "gatunek12" real, "gatunek13" real, "gatunek14" real, "gatunek15" real, "gatunek19" real, "gatunek21" real);

Mogę się tylko domyślać, że w zależności od pola znacznik chcesz wykonać jakieś operacje z wyświetlaniem strony (np formatowanie tabeli html), zatem przed załadowaniem danych wczytaj najpierw znaczniki, a potem wartości z poniższego selecta

Select 'wartosci' as opis,w.* From crosstab ( 'Select id_punkt, id_gatunek, suma From wynik_zzwr Order By id_punkt'
,'Select id_gatunek FROM gatunek WHERE id_gatunek IN (1,2,3,4,5,8,9,12,13,14,15,19,21)' )
As w( id_punkt int,  "gatunek1" real, "gatunek2" real, "gatunek3" real, "gatunek4" real, "gatunek5" real, "gatunek8" real, "gatunek9" real, "gatunek12" real, "gatunek13" real, "gatunek14" real, "gatunek15" real, "gatunek19" real, "gatunek21" real);
union all
Select 'znaczniki' as opis,w.* From crosstab ( 'Select id_punkt, id_gatunek, znacznik From wynik_zzwr Order By id_punkt'
,'Select id_gatunek FROM gatunek WHERE id_gatunek IN (1,2,3,4,5,8,9,12,13,14,15,19,21)' )
As w( id_punkt int,  "gatunek1" real, "gatunek2" real, "gatunek3" real, "gatunek4" real, "gatunek5" real, "gatunek8" real, "gatunek9" real, "gatunek12" real, "gatunek13" real, "gatunek14" real, "gatunek15" real, "gatunek19" real, "gatunek21" real);