[SQL] Agregacja danych hierarchicznych

[Tomasz Wrzodak]

Witam,
Zupełnie nie mam pomysłu, jak uzyskać wynik postaci:

id nazwa wartosc
==============================
1 1 310
2 1.1 70
4 1.1.2 60
RAZEM 310


z danych z tabeli postaci:

id nazwa wartosc rodzic
=======================================
1 1
2 1.1 1
3 1.1.1 10 2
4 1.1.2 20 2
5 1.1.2.1 40 4
6 1.2 80 1
7 1.3 160 1

W tej tabeli pole rodzic jest kluczem obcym do pola id tej samej
tabeli, czyli struktura jest hierarchiczna.
W wyniku chcę otrzymać wszystkie rekordy z tabeli, które są
rodzicami na jakimkolwiek poziomie hierarchii (w rzeczywistości tych
poziomów jest 5), wraz z sumą pola wartość wszystkich potomków
tych rekordów plus wartość pola "wartość" samego rekordu
będącego rodzicem (jeśli taką posiada). Np. w powyższych danych
chodzi o przypadek rekordu o id=4, który zarówno ma potomków, jak i
sam ma wartość <> null, więc w wyniku wartość dla niego=jego
wartość + suma wartości potomków.

Siedzę nad tym już drugi dzień, może ktoś może coś
podpowiedzieć?

[Krzysztof Naworyta]

Tomasz Wrzodak <***@gmail.com> napisał:

| Zupełnie nie mam pomysłu, jak uzyskać wynik postaci:
|
| id nazwa wartosc
| ==============================
| 1 1 310
| 2 1.1 70
| 4 1.1.2 60
| RAZEM 310
|
|
| z danych z tabeli postaci:
|
| id nazwa wartosc rodzic
| =======================================
| 1 1
| 2 1.1 1
| 3 1.1.1 10 2
| 4 1.1.2 20 2
| 5 1.1.2.1 40 4
| 6 1.2 80 1
| 7 1.3 160 1
|
| W tej tabeli pole rodzic jest kluczem obcym do pola id tej samej
| tabeli, czyli struktura jest hierarchiczna.
| W wyniku chcę otrzymać wszystkie rekordy z tabeli, które są
| rodzicami na jakimkolwiek poziomie hierarchii (w rzeczywistości tych
| poziomów jest 5), wraz z sumą pola wartość wszystkich potomków
| tych rekordów plus wartość pola "wartość" samego rekordu
| będącego rodzicem (jeśli taką posiada). Np. w powyższych danych
| chodzi o przypadek rekordu o id=4, który zarówno ma potomków, jak i
| sam ma wartość <> null, więc w wyniku wartość dla niego=jego
| wartość + suma wartości potomków.


SELECT
Z1.id
, Z1.nazwa
,
nz(Sum(Z1.wartosc))+nz(Sum(Z2.wartosc))+Nz(Sum(Z3.wartosc))+Nz(Sum(Z4.wartosc))
AS razem
FROM
((
Tabela1 AS Z1
LEFT JOIN
Tabela1 AS Z2
ON
Z1.ID = Z2.rodzic
)
LEFT JOIN
Tabela1 AS Z3
ON
Z2.ID = Z3.rodzic
)
LEFT JOIN
Tabela1 AS Z4
ON
Z3.ID = Z4.rodzic
WHERE
Exists (select * From Tabela1 x where x.rodzic=z1.id)
GROUP BY
Z1.ID, Z1.nazwa

[Krzysztof Naworyta]

ups, nie zwróciłem uwagi, że cross-post ...

oczywiście moje rozwiązanie dotyczy access'a i tylko pod warunkiem, że
poziomów jest skończona i z góry określona ilość.
--
KN
(MVP, M$ Office Access)


| Tomasz Wrzodak <***@gmail.com> napisał:
|
|| Zupełnie nie mam pomysłu, jak uzyskać wynik postaci:


(...)
| SELECT
| Z1.id
| , Z1.nazwa
| ,
|
nz(Sum(Z1.wartosc))+nz(Sum(Z2.wartosc))+Nz(Sum(Z3.wartosc))+Nz(Sum(Z4.wartosc))
| AS razem
| FROM
| ((
| Tabela1 AS Z1
| LEFT JOIN
| Tabela1 AS Z2
| ON
| Z1.ID = Z2.rodzic
| )
| LEFT JOIN
| Tabela1 AS Z3
(...)

[Paweł Matejski]

Post by Tomasz Wrzodak
Witam,
id nazwa wartosc
==============================
1 1 310
2 1.1 70
4 1.1.2 60
RAZEM 310
id nazwa wartosc rodzic
=======================================
1 1
2 1.1 1
3 1.1.1 10 2
4 1.1.2 20 2
5 1.1.2.1 40 4
6 1.2 80 1
7 1.3 160 1
W tej tabeli pole rodzic jest kluczem obcym do pola id tej samej
tabeli, czyli struktura jest hierarchiczna.
W wyniku chcę otrzymać wszystkie rekordy z tabeli, które są
rodzicami na jakimkolwiek poziomie hierarchii (w rzeczywistości tych
poziomów jest 5), wraz z sumą pola wartość wszystkich potomków
tych rekordów plus wartość pola "wartość" samego rekordu
będącego rodzicem (jeśli taką posiada). Np. w powyższych danych
chodzi o przypadek rekordu o id=4, który zarówno ma potomków, jak i
sam ma wartość <> null, więc w wyniku wartość dla niego=jego
wartość + suma wartości potomków.
Siedzę nad tym już drugi dzień, może ktoś może coś
podpowiedzieć?

Czytaj FAQ grupy bazy-danych. Tam pisze, że trzeba podać baze w jakiej
to robisz, bo w jednej da sie to zrobic tak, w innej inaczej.
Pozatym pisze tam również, jak radzić sobie ze strukturami drzewiastymi.

[Michał Kuratczyk]

Post by Tomasz Wrzodak
id nazwa wartosc
==============================
1 1 310
2 1.1 70
4 1.1.2 60
RAZEM 310
id nazwa wartosc rodzic
=======================================
1 1
2 1.1 1
3 1.1.1 10 2
4 1.1.2 20 2
5 1.1.2.1 40 4
6 1.2 80 1
7 1.3 160 1

Oczywiście nie napisałeś jaka baza i nie podałeś gotowych CREATE i INSERT,
albo proszę - wersja dla Oracle 10g:

SQL> select * from drzewko;

ID NAZWA WARTOSC RODZIC
---------- ------------------------ ---------- ----------
1 1
2 1.1 1
3 1.1.1 10 2
4 1.1.2 20 2
5 1.1.2.1 40 4
6 1.2 80 1
7 1.3 160 1

7 rows selected.

SQL> select id, nazwa,
2 (select sum(wartosc) from drzewko d2
3 start with d2.id=d.id connect by prior id=rodzic) wartosc
4 from drzewko d
5 where CONNECT_BY_ISLEAF=0
6 start with rodzic is null
7 connect by prior id=rodzic;

ID NAZWA WARTOSC
---------- ------------------------ ----------
1 1 310
2 1.1 70
4 1.1.2 60

[Tomasz Wrzodak]

Wielkie dzieki za odpowiedzi, faktycznie nie przejrzalem FAQ, mea
culpa.
Jesli chodzi o baze danych, to specjalnie nie podalem, bo jest mi
obojetna. Dane mam w Accesie, ale nie ma problemu, by je przetworzyc
np. na Oracle'u czy MS SQLu. Nie chcialem ograniczac liczby
odpowiedzi (fakt - moglem jawnie to napisac).

[Grzegorz Szyszlo]

Post by Tomasz Wrzodak
Wielkie dzieki za odpowiedzi, faktycznie nie przejrzalem FAQ, mea
culpa.
Jesli chodzi o baze danych, to specjalnie nie podalem, bo jest mi
obojetna. Dane mam w Accesie, ale nie ma problemu, by je przetworzyc
np. na Oracle'u czy MS SQLu. Nie chcialem ograniczac liczby
odpowiedzi (fakt - moglem jawnie to napisac).

przeczytaj FAQ. Tam jest opisane w jaki sposob skonstruowac
struktury drzewiaste. Zrob sobie tabele pomocnicza
z 3ma kolumnami, i wtedy wszelkie sumowania po drzewie
beda znacznie prostsze. jeden join, jeden group.
rozwiazanie jest niezalezne od bazy danych.

znik.

[Tomasz Wrzodak]

Wielkie dzieki za odpowiedzi, faktycznie nie przejrzalem FAQ, mea
culpa.
Jesli chodzi o baze danych, to specjalnie nie podalem, bo jest mi
obojetna. Dane mam w Accesie, ale nie ma problemu, by je przetworzyc
np. na Oracle'u czy MS SQLu. Nie chcialem ograniczac liczby odpowiedzi
(fakt - moglem jawnie to napisac).

[Tomasz Wrzodak]

Witam ponownie,
Po przejzeniu wyników obu powyzszych zapytan (z CONNECT BY dla
Oracle'a i ze zlaczeniami) zauwazylem drobne róznice w wynikach.
Nie wiem, co jest przyczyna, moze ktos podpowie, z ciekawosci
próbuje to rozgryzc...

Róznice pokaze na fragmencie moich danych (wszystko skladnia
Oracle'a):

CREATE TABLE "TWRZODAK"."TMP"(
"ID" NUMBER(10,0),
"ZAKLADKA_ARKUSZA" VARCHAR2(765 BYTE),
"RODZAJ_PROCESOW" VARCHAR2(765 BYTE),
"NUMER_PROCESU" VARCHAR2(765 BYTE),
"NAZWA_PROCESU" VARCHAR2(765 BYTE),
"LICZBA_ETATOW" NUMBER(28,6),
"MIESIECZNA_LICZBA_TRANSAKCJI" NUMBER(28,6),
"RODZIC" NUMBER(10,0)
) ;

-- INSERTING into TMP
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values ('212','ZAKLADKA1','rodzaj1','1.1','1.1','0.10625','230','211');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values ('213','ZAKLADKA1','rodzaj1','1.1.1','1.1.1',null,null,'212');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values
('214','ZAKLADKA1','rodzaj1','1.1.1.1','1.1.1.1','7.412291','5407.2','213');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values ('215','ZAKLADKA1','rodzaj1','1.1.2','1.1.2',null,null,'212');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values
('216','ZAKLADKA1','rodzaj1','1.1.2.1','1.1.2.1','4.171148','1089','215');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values
('217','ZAKLADKA1','rodzaj1','1.1.2.2','1.1.2.2','4.657054','2313','215');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values ('218','ZAKLADKA1','rodzaj1','1.1.3','1.1.3',null,null,'212');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values
('219','ZAKLADKA1','rodzaj1','1.1.3.1','1.1.3.1','5.650897','4292.2','218');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values
('220','ZAKLADKA1','rodzaj1','1.1.3.2','1.1.3.2','1.843772','2633.84','218');
Insert into TMP
("ID","ZAKLADKA_ARKUSZA","RODZAJ_PROCESOW","NUMER_PROCESU","NAZWA_PROCESU","LICZBA_ETATOW","MIESIECZNA_LICZBA_TRANSAKCJI","RODZIC")
values
('221','ZAKLADKA1','rodzaj1','1.1.3.3','1.1.3.3','0.188641','540','218');


Zapytanie (1) w wersji z CONNECT BY:
with vs as (
select id, zakladka_arkusza, rodzaj_procesow, numer_procesu,
nazwa_procesu,
(select sum(liczba_etatow) from tmp v2 start with v2.id=v.id connect by
prior id=rodzic) liczba_etatow,
(select sum(miesieczna_liczba_transakcji) from tmp v3 start with
v3.id=v.id connect by prior id=rodzic) miesieczna_liczba_transakcji
from tmp v
where CONNECT_BY_ISLEAF=0
start with rodzic=211
connect by prior id=rodzic
)
select numer_procesu,nazwa_procesu,round(vs.liczba_etatow,2) as le,
round(vs.miesieczna_liczba_transakcji,2) as lt
from vs
order by numer_procesu, nazwa_procesu


Zapytanie (2) w wersji ze zlaczeniami:
with vs as (
SELECT Z1.id, Z1.zakladka_arkusza, Z1.rodzaj_procesow,
Z1.numer_procesu, Z1.nazwa_procesu,
nvl(Sum(Z1.liczba_etatow),0)+nvl(Sum(Z2.liczba_etatow),0)+nvl(Sum(Z3.liczba_etatow),0)
+nvl(Sum(Z4.liczba_etatow),0)+nvl(Sum(Z5.liczba_etatow),0) AS
liczba_etatow,
nvl(Sum(Z1.miesieczna_liczba_transakcji),0)+nvl(Sum(Z2.miesieczna_liczba_transakcji),0)
+nvl(Sum(Z3.miesieczna_liczba_transakcji),0)+nvl(Sum(Z4.miesieczna_liczba_transakcji),0)
+nvl(Sum(Z5.miesieczna_liczba_transakcji),0) AS
miesieczna_liczba_transakcji
FROM tmp Z1
LEFT JOIN tmp Z2 ON Z1.ID=Z2.rodzic
LEFT JOIN tmp Z3 ON Z2.ID=Z3.rodzic
LEFT JOIN tmp Z4 ON Z3.ID=Z4.rodzic
LEFT JOIN tmp Z5 ON Z4.ID=Z5.rodzic
WHERE Exists (select * From tmp x where x.rodzic=z1.id)
GROUP BY Z1.ID, Z1.numer_procesu, Z1.nazwa_procesu,
Z1.zakladka_arkusza, Z1.rodzaj_procesow
)
select numer_procesu,nazwa_procesu,round(vs.liczba_etatow,2) as
liczba_etatow, round(vs.miesieczna_liczba_transakcji,2) as
liczba_transakcji
from vs
order by numer_procesu, nazwa_procesu


Wyniki zapytania (1):
"NUMER_PROCESU" "NAZWA_PROCESU" "Liczba_Etatów" "Liczba_Transakcji"
"1.1" "1.1" "24.03" "16505.24"
"1.1.1" "1.1.1" "7.41" "5407.2"
"1.1.2" "1.1.2" "8.83" "3402"
"1.1.3" "1.1.3" "7.68" "7466.04"

Wyniki zapytania (2):
"NUMER_PROCESU" "NAZWA_PROCESU" "L_ETATOW" "L_TRANSAKCJI"
"1.1" "1.1" "24.56" "17655.24"
"1.1.1" "1.1.1" "7.41" "5407.2"
"1.1.2" "1.1.2" "8.83" "3402"
"1.1.3" "1.1.3" "7.68" "7466.04"

Zapytanie (1) zwraca poprawne wyniki. Co jest przyczyna, ze (2), dla
NUMER_PROCESU=
"1.1" zwraca wieksza sume?

Pozdrawiam
Tomasz Wrzodak