Temat: Raport z rozszerzoną opcją RollUp

Witajcie!

Muszę przygotować raport w SQL, który będzie pokazywał dane, grupując je po pracowniku. W efekcie chcę uzyskać sumę pośrednią dla każdego pracownika (rollup idealnie do tego się nadaje) oraz wartość średnią. Niestety, nie mam pomysłu jak zrobić dla każdego pracownika sumę pośrednią i średnią z jego wartości. Screen z Excela powinien rozjaśnić sytuację




Na początku wykombinowałem trzy selecty, z czego dwa ostatnie to były unie - jedna obliczała mi sumę, druga wartość średnią, ale cały kod wyświetlał dane tylko dla jednego pracownika. Nie wiedziałem jak rozszerzyć mój kod tak, aby wyświetlić dane dla wielu pracowników (tak jak w screenie powyżej). Wpadłem na pomysł, aby zrobić grupowanie z rollup - dla sumy działa, ale jak dodać jeszcze średnią?

Środowisko to SQL Developer, baza to Oracle 11g.

Pozdrawiam!

Temat: Raport z rozszerzoną opcją RollUp

spróbuj tak
select id as 'ID pracownika', nazwa as 'Imię i nazwisko', pole1, pole2, pole3 from TABELA
union
select id as 'ID pracownika', 'ZZ_Suma', pole1, pole2, pole3 from
(selec id, sum(pole1) pole1, sum(pole2) pole2, sum(pole3) pole3 from TABELA group by id)
union
select id as 'ID pracownika', 'ZZZ_Średnia', pole1/ile pole1, pole2/ile pole2, pole3/ile pole3 from
(selec id, count(*) ile, sum(pole1) pole1, sum(pole2) pole2, sum(pole3) pole3 from TABELA group by id)
te "ZETY" spozycjonuja Ci średnie i sumy pod pracownikiem, chyba, że jakiś ma na imię Żytomir :)
Ale to sobie sprawdź...
Może lepiej raport pociągnąć do EXCEL i tabelkę przestawną wypełnić?

Temat: Raport z rozszerzoną opcją RollUp

Maciej B.:

Może lepiej raport pociągnąć do EXCEL i tabelkę przestawną wypełnić

Witaj. Dzięki za sugestię - miałem podobnie i nie działało to tak, jak na przykładowym JPG. Eksport danych do Excela i zrobienie tabeli przestawnej to będzie ostateczność, jeśli się okaże, że zbyt duże mam wymagania co do SQL :)

Temat: Raport z rozszerzoną opcją RollUp

UNION posegreguje Ci w sposób naturalny według naturalnego klucza (kolejno po polach)
Posegregować można poprzez dodanie "czegoś do ID
"...
union
select id||'_Z' , 'Suma', pole1, pole2, pole3 from
(selec id, sum(pole1) pole1, sum(pole2) pole2, sum(pole3) pole3 from TABELA group by id)
..."
I wtedy nie zależysz od literki imienia...

Temat: Raport z rozszerzoną opcją RollUp

Ożeż... Faktycznie działa :) Na to bym nie wpadł...

Drobna modyfikacja - do wyliczenia wartości średniej skorzystałem z funkcji AVG

Dziękuję jeszcze raz.

Temat: Raport z rozszerzoną opcją RollUp

Ja na potrzeby "kontrolera" musiałem zrobić takiego "potworka" i działa poprawnie podając sprzedaż, narastająco od początku roku dla roku obecnego i kilku poprzednich lat... dla dowolnej daty roku.
"
select A.ROK, A.MIESIAC, C.DNI_SPRZ, sum(B.WARTNETTO * A.KURS_WALUTY) as WART_PLN, sum(B.ILOSC) as ILE,
sum(B.WARTNETTO * A.KURS_WALUTY) / sum(B.ILOSC) as ZA_ILE, sum(B.CENA_EWID * B.ILOSC) as WART_EWID,
sum(B.CENA_EWID * B.ILOSC) / sum(B.ILOSC) as WART_EWID_SZT,
sum(B.WARTNETTO * A.KURS_WALUTY) - sum(B.CENA_EWID * B.ILOSC) as POT_ZYSK,
(sum(B.WARTNETTO * A.KURS_WALUTY) - sum(B.CENA_EWID * B.ILOSC)) / sum(B.ILOSC) as POT_ZYSK_SZT,
100 * ((sum(B.WARTNETTO * A.KURS_WALUTY) - sum(B.CENA_EWID * B.ILOSC)) / sum(B.ILOSC)) / (sum(B.CENA_EWID * B.ILOSC) / sum(B.ILOSC)) as PROC_MAR ,
case when (c.dni_sprz =0)then 0 else (sum(B.wartnetto * A.KURS_WALUTY)/c.dni_sprz) end sr_na_dzien_PLN
from M_FAKTURY A
inner join M_FAKTURYPOZ B on B.ID_NAG = A.ID_NAG
inner join(select ROK, MIESIAC, count(DZIEN) DNI_SPRZ
from (select distinct ROK, MIESIAC, F_DAY(DATA_FAK) DZIEN
from M_FAKTURY
where ROK > 2012 and
MIESIAC = F_MONTH(:DATA_DO) and
F_DAY(DATA_FAK) <= F_DAY(:DATA_DO))
group by ROK, MIESIAC) C on C.ROK = A.ROK and
C.MIESIAC = A.MIESIAC
where A.ROK > 2012 and
A.MIESIAC = F_MONTH(:DATA_DO) and
F_DAY(A.DATA_FAK) <= F_DAY(:DATA_DO)
group by A.ROK, A.MIESIAC, C.DNI_SPRZ
union
select A.ROK, 'RAZEM', C.DNI_SPRZ, sum(B.WARTNETTO * A.KURS_WALUTY) as WART_PLN, sum(ILOSC) as ILE,
sum(B.WARTNETTO * A.KURS_WALUTY) / sum(B.ILOSC) as ZA_ILE, sum(B.CENA_EWID * B.ILOSC) as WART_EWID,
sum(B.CENA_EWID * B.ILOSC) / sum(B.ILOSC) as WART_EWID_SZT,
sum(B.WARTNETTO * A.KURS_WALUTY) - sum(B.CENA_EWID * B.ILOSC) as POT_ZYSK,
(sum(B.WARTNETTO * A.KURS_WALUTY) - sum(B.CENA_EWID * B.ILOSC)) / sum(B.ILOSC) as POT_ZYSK_SZT,
100 * ((sum(B.WARTNETTO * A.KURS_WALUTY) - sum(B.CENA_EWID * B.ILOSC)) / sum(B.ILOSC)) / (sum(B.CENA_EWID * B.ILOSC) / sum(B.ILOSC)) as PROC_MAR ,
case when (c.dni_sprz =0)then 0 else (sum(B.wartnetto * A.KURS_WALUTY)/c.dni_sprz) end sr_na_dzien_PLN
from M_FAKTURY A
inner join M_FAKTURYPOZ B on B.ID_NAG = A.ID_NAG
inner join(select WW.ROK, coalesce(YY.DNI_SPRZ, 0) + coalesce(XX.DNI_SPRZ, 0) DNI_SPRZ
from (select distinct ROK
from M_FAKTURY
where ROK > 2012 and
DATA_FAK <= :DATA_DO) WW
left join(select ROK, MIESIAC, count(DZIEN) DNI_SPRZ
from (select distinct ROK, MIESIAC, F_DAY(DATA_FAK) DZIEN
from M_FAKTURY
where ROK > 2012 and
MIESIAC = F_MONTH(:DATA_DO) and
F_DAY(DATA_FAK) <= F_DAY(:DATA_DO))
group by ROK, MIESIAC) YY on YY.ROK = WW.ROK
left join(select X.ROK, count(X.DZIEN) DNI_SPRZ
from (select distinct ROK, MIESIAC, F_DAY(DATA_FAK) DZIEN
from M_FAKTURY
where ROK > 2012 and
MIESIAC < F_MONTH(:DATA_DO)) X
group by X.ROK) XX on XX.ROK = WW.ROK) C on C.ROK = A.ROK
where A.ROK > 2012 and
(A.MIESIAC < F_MONTH(:DATA_DO) or (A.MIESIAC = F_MONTH(:DATA_DO) and
F_DAY(A.DATA_FAK) <= F_DAY(:DATA_DO)))
group by A.ROK, C.DNI_SPRZ
"
Wygląda potwornie ale wyświetla raport linijka po linijce.
najpierw aktualny miesiąc z roku do podanej daty, potem podsumowanie roku do podanej daty i tak przez wszystkie dopuszczalne lata....
Obrazka nie podam. :)
dla bazy firebird i środowiska REKORD - funkcje F_DAY, F_YEARitp.
Jak osiwiejesz przy analizie, daj znać.. Życie to nie bajka. :)

Temat: Raport z rozszerzoną opcją RollUp

Mateusz K.:
Ożeż... Faktycznie działa :) Na to bym nie wpadł...

Drobna modyfikacja - do wyliczenia wartości średniej skorzystałem z funkcji AVG

Dziękuję jeszcze raz.

Tak to jest z dinozaurami, że o modyfikacjach nie pamiętają. :)

Temat: Raport z rozszerzoną opcją RollUp

A może funkcje analityczne?

select

	pracownik_id,

	imie,

	nazwisko,

	pole1,

	pole2,

	pole3,

	sum(pole_1) over (partition by pracownik_id) sumaPracownik,

	avg(pole_1) over (partition by pracownik_id) sredniaPracownik,

	sum(pole_1) over (order by 1) sumaWszyscy,

	avg(pole_1) over (order by 1) sredniaWszyscy

from tabela;