[SQL] Agregacja danych hierarchicznych
Tomasz Wrzodak - 27-04-2006 00:17
[SQL] Agregacja danych hierarchicznych
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 - 27-04-2006 00:17
Tomasz Wrzodak <twrzodak@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
--
KN
(MVP MS Office Access)
archiwum grupy:
http://groups.google.pl/advanced_gro...up=pl*msaccess
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 27-04-2006 00:17
Tomasz Wrzodak napisał(a):
> 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ć?
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.
--
P.M.
=?ISO-8859-2?Q?Micha=B3?= Kuratczyk - 27-04-2006 00:17
Tomasz Wrzodak wrote:
> 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
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
--
Michał Kuratczyk
Tomasz Wrzodak - 27-04-2006 00:17
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 - 27-04-2006 00:17
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).
Krzysztof Naworyta - 27-04-2006 00:17
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 <twrzodak@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
(...)
Grzegorz Szyszlo - 28-04-2006 00:51
Tomasz Wrzodak wrote:
> 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 - 29-04-2006 00:14
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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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","MIESIECZ NA_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_eta tow),0)+nvl(Sum(Z3.liczba_etatow),0)
+nvl(Sum(Z4.liczba_etatow),0)+nvl(Sum(Z5.liczba_et atow),0) AS
liczba_etatow,
nvl(Sum(Z1.miesieczna_liczba_transakcji),0)+nvl(Su m(Z2.miesieczna_liczba_transakcji),0)
+nvl(Sum(Z3.miesieczna_liczba_transakcji),0)+nvl(S um(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
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
jak to =?ISO-8859-2?Q?zrobi=E6=2E=2E=2E=3F_TSQL_sql_server?==?ISO-8859-2?Q?_?=
Jak =?windows-1250?Q?pobra=E6_szacowan=B9_wielko=9C=E6_zbiory_wy nikowego_w_MS?==?windows-1250?Q?_SQL_2005=3F?=
=?iso-8859-2?Q?=5BMS_SQL=5D_Czy_mo=BFna_wywo=B3a=E6_funkcje_t ylko_raz_dla?==?iso-8859-2?Q?_ca=B3ego_zbioru_=BCr=F3d=B3owego=3F?=
[MSSQL] =?ISO-8859-2?Q?zgodno=B6ci_z_licencjami_Microsoft_?==?ISO-8859-2?Q?SQL_Server?=
=?ISO-8859-2?Q?k=B3opot_z_uruchomieniem_MY_SQL_dla_C?==?ISO-8859-2?Q?MS_i_CRM_na_Fedora_Core_3?=
Oracle PL/SQL Wstawianie =?ISO-8859-2?Q?wynik=F3w_kolekcji_d?==?ISO-8859-2?Q?o_tabeli?=
[MSSQL] ACCESS - SQL =?ISO-8859-2?Q?B=B3ad_w_konwersji_lic?==?ISO-8859-2?Q?zb?=
=?iso-8859-2?Q?=5Bmssql=5D_Zapytania_rekurencyjne__-_czy_sk=B3adnia_sql?==?iso-8859-2?Q?_co=B6_takiego_przewiduje_=3F?=
[Oracle PL/SQL] Cursor i zapis =?ISO-8859-2?Q?rekord=F3w_do_?==?ISO-8859-2?Q?kolejnych_plik=F3w?=
=?iso-8859-2?Q?=5BMySQL=5D_Co_minimalnie_potrzebne_zeby_mie=E 6_klienta_My?==?iso-8859-2?Q?SQL_na_Linuxie=3F?=
zanotowane.pldoc.pisz.plpdf.pisz.ploefg.opx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
