Uzyskiwanie zliczeń / sum na każdym poziomie hierarchicznego zapytania przy użyciu CONNECT BY
Mam z tym trochę czasu. Próbuję napisać zapytanie (przy użyciu Oracle), w stosunku do tabeli z relacją rekurencyjną (hierarchiczną) i uzyskać całkowitą liczbę rekordów przechowywanych w innej tabeli na i pod każdym węzłem w drzewie. Druga tabela ma tylko rekordy powiązane z węzłami liści. Chcę jednak uzyskać sumy na i pod każdym węzłem w drzewie. Na przykład powiedzmy, że mam dwie tabele. DIRS zawiera nazwy katalogów i relację rekurencyjną określającą strukturę katalogów, a PLIKI zawierają informacje o pliku z obcym kluczem do DIRS wskazującym katalog, w którym znajduje się plik:
DIRS
====
DIR_ID
PARENT_DIR_ID
DIR_NAME
FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE
Jeśli DIRS zawiera:
DIR_ID PARENT_DIR_ID DIR_NAME
====== ============= ========
1 ROOT
2 1 DIR1_1
3 1 DIR1_2
4 2 DIR2_1
5 2 DIR2_2
i zawiera PLIKI
FILE_ID FILE_NAME DIR_ID FILE_SIZE
======= ========= ====== =========
1 test1.txt 5 100
2 test2.txt 5 200
3 test5.txt 5 50
4 test3.txt 3 300
5 test4.txt 3 300
6 test6.txt 4 100
Chcę zapytanie, które zwraca ścieżkę wraz z liczbą plików w lub pod każdym węzłem w hierarchii. Zasadniczo zestawienie liczby plików. Wynik zapytania wyglądałby następująco:
Path File_Count
===== ===========
/ROOT 6
/ROOT/DIR1_1 4
/ROOT/DIR1_1/DIR2_1 1
/ROOT/DIR1_1/DIR2_2 3
/ROOT/DIR1_2 2
AKTUALIZACJA Skrypt SQL do tworzenia tabel z przykładowymi danymi pasującymi do powyższego:
create table DIRS (dir_id number(38) primary key
, parent_dir_id number(38) null references DIRS(dir_id)
, dir_name varchar2(128) not null);
create table FILES (file_id number(38) primary key
, file_name varchar2(128) not null
, dir_id number(38) not null references DIRS(dir_id)
, file_size number not null
, unique (dir_id, file_name));
insert into DIRS
select 1, null, 'ROOT' from dual
union all select 2, 1, 'DIR1_1' from dual
union all select 3, 1, 'DIR1_2' from dual
union all select 4, 2, 'DIR2_1' from dual
union all select 5, 2, 'DIR2_2' from dual;
insert into files
select 1, 'test1.txt', 5, 100 from dual
union all select 2, 'test2.txt', 5, 200 from dual
union all select 3, 'test5.txt', 5, 50 from dual
union all select 4, 'test3.txt', 3, 300 from dual
union all select 5, 'test4.txt', 3, 300 from dual
union all select 6, 'test6.txt', 4, 100 from dual;
commit;