1 Temat przez przesq

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

Temat: tabela przestawna - crosstab

Witam,

Potrzebuję zliczyć sumę m3 w postaci:

id_klasa_wym_kat_jak_wym | id_gat_drzew

biorąc pod uwagę, że:

wod.rok = 2012
wod.zatwierdzony = 'TAK'
wod_gat_drzew_dane.id_zablokowany = 2

id_gat_drzew - pochodzi z tabeli gat_drzew i zamiast id_gat_drzew powinna byc wartość gatunku drzewa - pole gat_drzew
id_klasa_wym_kat_jak_wym - pochodzi z tabeli klasa_wym_kat_jak_wym i zamiast id_klasa_wym_kat_jak_wym powinna byc wartość klasy, kategorii wymiarowej - pole klasa_wym_kat_jak_wym

Ostatecznie powinno to wyglądać mniej więcej tak:

           | so | jd | bk | gb | itd...
------------------------------------
WA0 I  | 3  | 2.3| 4  | 0  |
WB0 II | 4  | 0  | 0  | 0  |
M1       | 5  | 4.4| 2  | 0  |
S2a      | 6  | 0  | 2  | 1  |
itd...     | ... | ... | ... | ... |

dane do testów:

DROP TABLE IF EXISTS sde.wod_gat_drzew_dane;
CREATE TABLE sde.wod_gat_drzew_dane (
  id_wod_gat_drzew_dane serial NOT NULL,
  id_wod_gat_drzew integer NOT NULL,
  m3 real NULL,
  id_klasa_wym_kat_jak_wym integer NOT NULL,
  id_zablokowany integer NOT NULL DEFAULT 2,
  CONSTRAINT wod_gat_drzew_dane_pkey PRIMARY KEY (id_wod_gat_drzew_dane)
)
WITH (OIDS=FALSE);
ALTER TABLE sde.wod_gat_drzew_dane OWNER TO mg;

GRANT SELECT, UPDATE, INSERT, REFERENCES, TRIGGER ON TABLE sde.wod_gat_drzew_dane TO mg;

insert into sde.wod_gat_drzew_dane values (1, 1, 2.17, 1, 2);
insert into sde.wod_gat_drzew_dane values (2, 1, 3.13, 1, 2);
insert into sde.wod_gat_drzew_dane values (3, 1, 1.81, 4, 2);
insert into sde.wod_gat_drzew_dane values (4, 2, 0.67, 5, 2);
insert into sde.wod_gat_drzew_dane values (5, 2, 0.41, 5, 2);
insert into sde.wod_gat_drzew_dane values (6, 2, 0.21, 8, 2);
insert into sde.wod_gat_drzew_dane values (7, 3, 1.11, 8, 2);
insert into sde.wod_gat_drzew_dane values (8, 3, 1.12, 1, 1);
insert into sde.wod_gat_drzew_dane values (9, 3, 2.34, 1, 1);
insert into sde.wod_gat_drzew_dane values (10, 4, 2.1, 1, 1);
insert into sde.wod_gat_drzew_dane values (11, 4, 3.1, 1, 2);
insert into sde.wod_gat_drzew_dane values (12, 4, 4.1, 11, 2);
insert into sde.wod_gat_drzew_dane values (13, 5, 5.1, 21, 2);
insert into sde.wod_gat_drzew_dane values (14, 5, 6.1, 21, 2);
insert into sde.wod_gat_drzew_dane values (15, 5, 7.1, 1, 2);
insert into sde.wod_gat_drzew_dane values (16, 6, 8.1, 17, 2);
insert into sde.wod_gat_drzew_dane values (17, 6, 9.1, 7, 2);
insert into sde.wod_gat_drzew_dane values (18, 6, 0.2, 7, 2);
insert into sde.wod_gat_drzew_dane values (19, 7, 0.4, 8, 2);
insert into sde.wod_gat_drzew_dane values (20, 7, 0.5, 8, 2);
insert into sde.wod_gat_drzew_dane values (21, 7, 0.8, 8, 2);
insert into sde.wod_gat_drzew_dane values (22, 7, 0.9, 1, 2);
insert into sde.wod_gat_drzew_dane values (23, 8, 2.1, 10, 2);
insert into sde.wod_gat_drzew_dane values (24, 8, 3.1, 15, 2);
insert into sde.wod_gat_drzew_dane values (25, 8, 4.1, 17, 2);
insert into sde.wod_gat_drzew_dane values (26, 9, 2.4, 13, 2);
insert into sde.wod_gat_drzew_dane values (27, 9, 2.2, 13, 2);
insert into sde.wod_gat_drzew_dane values (28, 9, 1.7, 13, 2);
insert into sde.wod_gat_drzew_dane values (29, 10, 2.8, 1, 2);
insert into sde.wod_gat_drzew_dane values (30, 10, 2.31, 6, 1);
insert into sde.wod_gat_drzew_dane values (31, 10, 1.9, 6, 2);

--#######################--

DROP TABLE IF EXISTS sde.wod_gat_drzew;
CREATE TABLE sde.wod_gat_drzew (
  id_wod_gat_drzew serial NOT NULL,
  id_wod integer NOT NULL,
  id_gat_drzew integer NOT NULL,
  CONSTRAINT wod_gat_drzew_pkey PRIMARY KEY (id_wod_gat_drzew)
)
WITH (OIDS=FALSE);
ALTER TABLE sde.wod_gat_drzew OWNER TO mg;

GRANT ALL ON TABLE sde.wod_gat_drzew TO mg;

insert into sde.wod_gat_drzew values (1, 1, 2);
insert into sde.wod_gat_drzew values (2, 1, 5);
insert into sde.wod_gat_drzew values (3, 1, 8);
insert into sde.wod_gat_drzew values (4, 2, 7);
insert into sde.wod_gat_drzew values (5, 2, 12);
insert into sde.wod_gat_drzew values (6, 3, 11);
insert into sde.wod_gat_drzew values (7, 3, 12);
insert into sde.wod_gat_drzew values (8, 4, 1);
insert into sde.wod_gat_drzew values (9, 4, 2);
insert into sde.wod_gat_drzew values (10, 4, 7);

--#######################--

DROP TABLE IF EXISTS wod;
CREATE TABLE wod (
  id_wod serial NOT NULL,
  id_obwod_ochr integer NOT NULL,
  rok integer NOT NULL,
  zatwierdzony character varying(3) DEFAULT 'NIE',
  CONSTRAINT wod_pkey PRIMARY KEY (id_wod)
)
WITH (OIDS=FALSE);
ALTER TABLE wod OWNER TO mg;

GRANT SELECT, UPDATE, INSERT, REFERENCES, TRIGGER ON TABLE wod TO mg;

insert into wod values (1, 2, 2012, 'TAK');
insert into wod values (2, 6, 2012, 'TAK');
insert into wod values (3, 12, 2012, 'NIE');
insert into wod values (4, 2, 2012, 'TAK');

--#######################--

Ostatnio edytowany przez przesq (2012-01-31 15:37:44)

2 Odpowiedź przez gszpetkowski

  • gszpetkowski
  • Moderator
  • Nieaktywny
  • Zarejestrowany: 2011-04-12
  • Posty: 147

Odp: tabela przestawna - crosstab

Select
    id_wod_gat_drzew, id_klasa_wym_kat_jak_wym, sum(m3)
From
    sde.wod_gat_drzew Join wod Using(id_wod)
    Join sde.wod_gat_drzew_dane Using(id_wod_gat_drzew)  
Where
    rok = 2012 And zatwierdzony = 'TAK' And id_zablokowany = 2
Group By
    id_wod_gat_drzew, id_klasa_wym_kat_jak_wym
Order By
    id_wod_gat_drzew, id_klasa_wym_kat_jak_wym;

Ostatnio edytowany przez gszpetkowski (2012-02-01 01:53:05)

3 Odpowiedź przez przesq

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

Odp: tabela przestawna - crosstab

Rozwiązanie z crosstab (mi to kompletnie nie wychodziło) jest tym o co mi chodziło - mam tylko jeszcze jedno pytanie - zamiast id_klasa_wym_kat_jak_wym jak wstawić klasa_wym_kat_jak_wym z tabeli klasa_wym_kat_jak_wym
wod_gat_drzew_dane.id_klasa_wym_kat_jak_wym=klasa_wym_kat_jak_wym.id_klasa_wym_kat_jak_wym

gatunków drzew mam ok 20 więc sobie wpisze z listy.
klasa_wym_kat_jak_wym - jest 23

Wielkie dzięki i pozdrawiam.
Wiedziałem, że na ciebie można liczyć.

Po sprawdzeniu dokładniejszym zauważyłem błąd:

w tabeli wod tylko dla id=3 zostawiłem zatwierdzony=tak, wówczas id_gat_drzew= 6 oraz 7
wpisałem sobie gatunki drzew (id od 1 do 19 zgodnie z tabelą gat_drzew):

Drop View If Exists m3_sum_drzew;
Create View m3_sum_drzew As
    Select id_gat_drzew, id_klasa_wym_kat_jak_wym, sum(m3) As sum_m3
    From sde.wod_gat_drzew Join sde.wod Using(id_wod)
        Join sde.wod_gat_drzew_dane Using(id_wod_gat_drzew)  
    Where rok = 2012 And zatwierdzony = 'TAK' And wod_gat_drzew_dane.id_zablokowany = 2
    Group By id_gat_drzew, id_klasa_wym_kat_jak_wym
    Order By id_gat_drzew, id_klasa_wym_kat_jak_wym;

Select * From crosstab (
  'Select id_klasa_wym_kat_jak_wym, id_gat_drzew, sum_m3 From m3_sum_drzew Order By 1',
  'Select g From generate_series(1, 19) g'
) As (
  id_klasa_wym_kat_jak_wym int,
  "so" real,
  "md" real,
  "sw" real,
  "jd" real,
  "dg" real,
  "js" real,
  "bk" real,
  "gb" real,
  "wz" real,
  "kl" real,
  "jw" real,
  "brz" real,
  "ol" real,
  "os" real,
  "tp" real,
  "db" real,
  "lp" real,
  "poz l" real,
  "poz i" real
);

i błędnie przypisuje do js, bk a powinno być jw, brz.

wniosek z tego taki, że zamiast dla id_gat_drzew wyszukuje dla id_wod_gat_drzew_dane - 6,7


zamieniłem  id_wod_gat_drzew na  id_gat_drzew w 3 miejscach i jest ok tylko ta klasa_wym_kat_jak_wym?

OK już sobie poradziłem.

Ostatnio edytowany przez przesq (2012-02-01 15:23:17)

4 Odpowiedź przez przesq

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

Odp: tabela przestawna - crosstab

pisze osobny post ponieważ potrzebuję wykonać to samo zliczenie dla nieco innego warunku:

tabela klasa_wym_kat_jak_wym

DROP TABLE IF EXISTS sde.klasa_wym_kat_jak_wym;
CREATE TABLE sde.klasa_wym_kat_jak_wym (
  id_klasa_wym_kat_jak_wym serial NOT NULL,
  id_klasa_wym integer NOT NULL,
  id_kat_jak_wym integer NOT NULL,
  przelicznik integer NOT NULL,
  kat_wym character varying(1),
  kat_jak_wym character varying(3),
  klasa_wym_kat_jak_wym character varying(50),
  CONSTRAINT klasa_wym_kat_jak_wym_pkey PRIMARY KEY (id_klasa_wym_kat_jak_wym)
)
WITH (OIDS=FALSE);
ALTER TABLE sde.klasa_wym_kat_jak_wym OWNER TO sde;

GRANT SELECT, UPDATE, INSERT, REFERENCES, TRIGGER ON TABLE sde.klasa_wym_kat_jak_wym TO sde;

insert into sde.klasa_wym_kat_jak_wym values (1, 2, 1, 0, 'W', 'WAO', 'WAO 25 - 34 cm');
insert into sde.klasa_wym_kat_jak_wym values (2, 3, 1, 0, 'W', 'WAO', 'WAO 35 cm i więcej');
insert into sde.klasa_wym_kat_jak_wym values (3, 1, 2, 0, 'W', 'WBO', 'WBO do 24 cm');
insert into sde.klasa_wym_kat_jak_wym values (4, 2, 2, 0, 'W', 'WBO', 'WBO 25 - 34 cm');
insert into sde.klasa_wym_kat_jak_wym values (5, 3, 2, 0, 'W', 'WBO', 'WBO 35 cm i więcej');
insert into sde.klasa_wym_kat_jak_wym values (6, 1, 3, 0, 'W', 'WCO', 'WCO do 24 cm');
insert into sde.klasa_wym_kat_jak_wym values (7, 2, 3, 0, 'W', 'WCO', 'WCO 25 - 34 cm');
insert into sde.klasa_wym_kat_jak_wym values (8, 3, 3, 0, 'W', 'WCO', 'WCO 35 cm i więcej');
insert into sde.klasa_wym_kat_jak_wym values (9, 1, 4, 0, 'W', 'WD', 'WD  do 24 cm');
insert into sde.klasa_wym_kat_jak_wym values (10, 2, 4, 0, 'W', 'WD', 'WD  25 - 34 cm');
insert into sde.klasa_wym_kat_jak_wym values (11, 3, 4, 0, 'W', 'WD', 'WD  35 cm i więcej');
insert into sde.klasa_wym_kat_jak_wym values (12, 4, 5, 0, 'S', 'S10', 'S10 9 - 11 cm');
insert into sde.klasa_wym_kat_jak_wym values (13, 5, 5, 0, 'S', 'S1O', 'S10 12 - 16 cm');
insert into sde.klasa_wym_kat_jak_wym values (14, 6, 8, 5, 'S', 'S3a', 'S3a 7 - 9 cm');
insert into sde.klasa_wym_kat_jak_wym values (15, 7, 8, 5, 'S', 'S3a', 'S3a 10 - 11 cm');
insert into sde.klasa_wym_kat_jak_wym values (16, 6, 9, 2, 'S', 'S3b', 'S3b 7 - 9 cm');
insert into sde.klasa_wym_kat_jak_wym values (17, 7, 9, 2, 'S', 'S3b', 'S3b 10 - 11 cm');
insert into sde.klasa_wym_kat_jak_wym values (18, 8, 9, 2, 'S', 'S3b', 'S3b 12 - 14 cm');
insert into sde.klasa_wym_kat_jak_wym values (19, 9, 6, 1, 'S', 'S2a', 'S2a');
insert into sde.klasa_wym_kat_jak_wym values (20, 9, 7, 1, 'S', 'S2a', 'S2b');
insert into sde.klasa_wym_kat_jak_wym values (21, 9, 10, 4, 'S', 'S4', 'S4');
insert into sde.klasa_wym_kat_jak_wym values (22, 10, 11, 3, 'M', 'M1', 'M1');
insert into sde.klasa_wym_kat_jak_wym values (23, 10, 12, 3, 'M', 'M2', 'M2');

ta tabela kończyła się po przeliczniku wzoru jednak z uwagi na to, że w poprzednim wątku nie mogłem skleić klasa_wym z kat_jak_wym dołożyłem pole klasa_wym_kat_jak_wym oraz kat_wym, kat_jak_wym pochodzące z tabel o tych samych nazwach łączonych z klasa_wym_kat_jak_wym  id_klasa_wym, id_kat_jak_wym.

teraz potrzebuję zliczyć grupując dla kat_wym:

           | so   | jd  | bk  | gb   | itd...
------------------------------------
W       | 23   | 2.3| 14  | 0     |
S        | 7.9  | 0   | 5    | 9.1  |

jeszcze raz zapytanie

Drop View If Exists m3_sum_drzew;
Create View m3_sum_drzew As
    Select kwkjw.id_klasa_wym_kat_jak_wym, kwkjw.klasa_wym_kat_jak_wym, wgd.id_gat_drzew, sum(wgdd.m3) As sum_m3
    From sde.klasa_wym_kat_jak_wym kwkjw, sde.wod_gat_drzew wgd Join sde.wod Using(id_wod)
        Join sde.wod_gat_drzew_dane wgdd Using(id_wod_gat_drzew)  
    Where wod.rok = 2012 And wod.zatwierdzony = 'TAK' And wgdd.id_zablokowany = 2 AND wgdd.id_klasa_wym_kat_jak_wym=kwkjw.id_klasa_wym_kat_jak_wym
    Group By wgd.id_gat_drzew, wgdd.id_klasa_wym_kat_jak_wym, kwkjw.id_klasa_wym_kat_jak_wym, kwkjw.klasa_wym_kat_jak_wym
    Order By wgd.id_gat_drzew, wgdd.id_klasa_wym_kat_jak_wym;

Select * From crosstab (
  'Select id_klasa_wym_kat_jak_wym, klasa_wym_kat_jak_wym, id_gat_drzew, sum_m3 From m3_sum_drzew Order By 1',
  'Select g From generate_series(1, 19) g'
) As (
  id_klasa_wym_kat_jak_wym int,
  "klasa_wym_kat_jak_wym" text,
  "so" real,
  "md" real,
  "sw" real,
  "jd" real,
  "dg" real,
  "js" real,
  "bk" real,
  "gb" real,
  "wz" real,
  "kl" real,
  "jw" real,
  "brz" real,
  "ol" real,
  "os" real,
  "tp" real,
  "db" real,
  "lp" real,
  "poz l" real,
  "poz i" real
);

Ostatnio edytowany przez przesq (2012-02-01 22:30:56)

5 Odpowiedź przez przesq

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

Odp: tabela przestawna - crosstab

Ponawiam temat (wcześniej poruszony wątek rozwiązany), gdyż mam mały problem przy tworzeniu tabeli przestawnej (już chwile kombinuje):

z wcześniejszych tabel należy uaktualnić:

DROP TABLE IF EXISTS sde.wod;
CREATE TABLE sde.wod (
  id_wod serial NOT NULL,
  id_obwod_ochr integer NOT NULL,
  rok integer NOT NULL,
  poz_ciec integer NOT NULL,
  zatwierdzony character varying(3) DEFAULT 'NIE',
  CONSTRAINT wod_pkey PRIMARY KEY (id_wod)
)
WITH (OIDS=FALSE);
ALTER TABLE sde.wod OWNER TO sde;

GRANT SELECT, UPDATE, INSERT, REFERENCES, TRIGGER ON TABLE sde.wod TO sde;

insert into sde.wod values (1, 2, 2012, 1, 'TAK');
insert into sde.wod values (2, 6, 2012, 1, 'TAK');
insert into sde.wod values (3, 12, 2012, 2,'TAK');
insert into sde.wod values (4, 2, 2012, 2,'TAK');

Drop View If Exists pc_m3_sum_drzew;
Create View pc_m3_sum_drzew As
    Select wod.poz_ciec, kwkjw.kat_wym, wgd.id_gat_drzew, sum(wgdd.m3) As sum_m3 
    From sde.klasa_wym_kat_jak_wym kwkjw, sde.wod_gat_drzew wgd Join sde.wod wod Using(id_wod)
        Join sde.wod_gat_drzew_dane wgdd Using(id_wod_gat_drzew)  
    Where wod.rok = 2012 And wod.zatwierdzony = 'TAK' And wgdd.id_zablokowany = 2 AND wgdd.id_klasa_wym_kat_jak_wym=kwkjw.id_klasa_wym_kat_jak_wym
    Group By wgd.id_gat_drzew, wod.poz_ciec, kwkjw.kat_wym
    Order By wgd.id_gat_drzew;

Select * From crosstab (
  'Select poz_ciec, kat_wym, id_gat_drzew, sum_m3 From pc_m3_sum_drzew Order By 1,2',
  'Select g From generate_series(1, 19) g'
) As (
  poz_ciec int,
  "kat_wym" text,
  "so" real,
  "md" real,
  "sw" real,
  "jd" real,
  "dg" real,
  "poz i" real,
  "js" real,
  "bk" real,
  "gb" real,
  "wz" real,
  "kl" real,
  "jw" real,
  "brz" real,
  "ol" real,
  "os" real,
  "tp" real,
  "db" real,
  "lp" real,
  "poz l" real
);

chodzi mi o zgrupowanie danych dla każdego z wystąpień:
poz_ciec - kat_wym

czyli:
1-W
1-S
2-W
2-S
W tym przypadku pobiera mi jedynie (zgodnie z Order by)
1-S
2-S
I wszystko się zgadza - jednak moje pytanie brzmi - czy można to pogrupować względem 2 komórek?
Wynik mógłby być jednym polem np:
poz_ciec-klasa_wym: 1-W

Ostatnio edytowany przez przesq (2012-02-21 14:39:59)

6 Odpowiedź przez przesq

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

Odp: tabela przestawna - crosstab

problem wydaje się być rozwiązany:

Select wod.poz_ciec || ' ' || kwkjw.kat_wym AS c

7 Odpowiedź przez arczif

  • arczif
  • Nowy
  • Nieaktywny
  • Zarejestrowany: 2011-03-18
  • Posty: 4

Odp: tabela przestawna - crosstab