agregowanie po miesiacach gdy mamy pełne daty

Witam

Mam tabelę w poniższym schemacie. Chciałbym z niej wyciągnąć informacje dotyczącą wartości (3cia kolumna) w poszczególnych miesiącach danego roku. Czyli docelowo dla każdego roku chciałbym uzyskać tabelę z 12toma miesiącami oraz sumie wartości miesięcznej. Da się coś takiego ogarnąć w SQL? Proszę chociaż o jakieś naprowadzenie bo póki co jestem początkujący. Z góry dziękuję za jakąkolwiek pomoc :)

Jak chcesz z takiej tabeli odczytać wartości miesięczne?

Spróbuj coś takiego, pisane z głowy - przy założeniu, że agregujemy wg 'DataDo'

select to_char(DataDo,'MM-YYYY'), sum(WartoscRoczna) from yourTable group by  to_char(DataDo,'MM-YYYY'),WartoscRoczna
 

Pytanie podstawowe - jak tu 2016-01-01 2017-12-31 123 jest wartość miesiąca wrzesień?

abrakadaber napisał(a):

Pytanie podstawowe - jak tu 2016-01-01 2017-12-31 123 jest wartość miesiąca wrzesień?

Wartość dla września powinna wynieść 123/12 ponieważ 123 jest wartością roczną. Najlepiej jakby było to 123/365 * ilość dni w danym miesiącu, ale może zacznijmy najpierw od prostszych rzeczy :)

Odnośnie propozycji zapytania powyżej to nie zadziała tak jakbym chciał ponieważ zagreguje tylko po DacieDo, a ja bym chciał żeby zapytanie dla samego pierwszego rekordu wypluwało tabele gdzie będą 24 pozycje - kolejne miesiące od 2016-01 aż do 2017-12 gdzie wszędzie będzie wpisana wartość miesięczna 123/12. Jest coś takiego możliwe w SQLu?

DDL

CREATE TABLE test(
  id INT,
  d_od DATE,
  d_do DATE,
  wartosc NUMBER(15,2));

CREATE SEQUENCE sq_test_id;

CREATE TRIGGER tr_test_id_bi 
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  :new.id := sq_test_id.NEXTVAL;
END;
/

dane

INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.06.2015', 'dd.mm.yyyy'), To_Date('31.12.2016', 'dd.mm.yyyy'), 324);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.01.2016', 'dd.mm.yyyy'), To_Date('31.12.2017', 'dd.mm.yyyy'), 123);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.03.2017', 'dd.mm.yyyy'), To_Date('31.12.2017', 'dd.mm.yyyy'), 45);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.06.2017', 'dd.mm.yyyy'), To_Date('31.12.2017', 'dd.mm.yyyy'), 75);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.01.2017', 'dd.mm.yyyy'), To_Date('31.12.2019', 'dd.mm.yyyy'), 898);
INSERT INTO test(d_od, d_do, wartosc) VALUES (To_Date('01.01.2017', 'dd.mm.yyyy'), To_Date('31.12.2018', 'dd.mm.yyyy'), 543);

widok, który zwraca wszystkie miesiące pomiędzy najmniejszą i największą datą dla podanych danych (dla uproszczenia zapytania)

CREATE VIEW test_miesiace as
select 
  add_months(d_min, level-1) d_od,
  add_months(d_min, level) - 1 d_do
from 
  (SELECT 
    Min(d_min) d_min, 
    Max(d_max) d_max 
  FROM 
    (SELECT 
      Least(d_od, d_do) d_min, 
      Greatest(d_od, d_do) d_max 
    FROM 
      test
    )
  )
CONNECT BY LEVEL <= Months_Between(Trunc(d_max,'MM'), Trunc(d_min,'MM'))+ 1;

liczymy wartość dla każdego miesiąca w danym roku

SELECT 
    To_Char(m.d_od, 'YYYY') rok, 
    To_Char(m.d_od, 'MM') miesiac,
    Round(Sum(CASE WHEN t.d_od <= m.d_do AND t.d_do >= m.d_od THEN Least(m.d_do, t.d_do) - Greatest(m.d_od, t.d_od) + 1 ELSE 0 END * t.wartosc / 365)) wartosc   
  FROM 
    test_miesiace m,
    test t
  GROUP BY
    To_Char(m.d_od, 'YYYY'),
    To_Char(m.d_od, 'MM')

i na koniec robimy z tego pivota

SELECT
  rok, 
  Sum(CASE WHEN miesiac = '01' THEN wartosc ELSE 0 END) styczen,
  Sum(CASE WHEN miesiac = '02' THEN wartosc ELSE 0 END) luty,
  Sum(CASE WHEN miesiac = '03' THEN wartosc ELSE 0 END) marzec,
  Sum(CASE WHEN miesiac = '04' THEN wartosc ELSE 0 END) kwiecien,
  Sum(CASE WHEN miesiac = '05' THEN wartosc ELSE 0 END) maj,
  Sum(CASE WHEN miesiac = '06' THEN wartosc ELSE 0 END) czerwiec,
  Sum(CASE WHEN miesiac = '07' THEN wartosc ELSE 0 END) lipiec,
  Sum(CASE WHEN miesiac = '08' THEN wartosc ELSE 0 END) sierpien,
  Sum(CASE WHEN miesiac = '09' THEN wartosc ELSE 0 END) wrzesien,
  Sum(CASE WHEN miesiac = '10' THEN wartosc ELSE 0 END) pazdziernik,
  Sum(CASE WHEN miesiac = '11' THEN wartosc ELSE 0 END) listopad,
  Sum(CASE WHEN miesiac = '12' THEN wartosc ELSE 0 END) grudzien
FROM 
  (SELECT 
    To_Char(m.d_od, 'YYYY') rok, 
    To_Char(m.d_od, 'MM') miesiac,
    Round(Sum(CASE WHEN t.d_od <= m.d_do AND t.d_do >= m.d_od THEN Least(m.d_do, t.d_do) - Greatest(m.d_od, t.d_od) + 1 ELSE 0 END * t.wartosc / (add_months(trunc(m.d_od,'year'), 12) - trunc(m.d_od,'year')))) wartosc   
  FROM 
    test_miesiace m,
    test t
  GROUP BY
    To_Char(m.d_od, 'YYYY'),
    To_Char(m.d_od, 'MM'))
GROUP BY
  rok
ORDER BY 
  rok

PS. to (add_months(trunc(m.d_od,'year'), 12) - trunc(m.d_od,'year')) liczy dni w roku - chodzi o lata przestępne

wynik: