Напредни извештаи од базата (SQL)

Вкупна количина потрошен инсулин за дијабетичари - ОДОБРЕНО

• Член: Никола Торбовски 151200
• Опис: Вкупна количина на потрошен конкретен инсулин за секој дијабетичар според вредностите внесени во секој запис со инсулин, групирани за сите години и сите месеци (прва група), за секоја година поединечно (втора група) и за секоја година и месец поединечно (трета група)

select
        c.id ,
        c.ime as dijabeticar_ime,
        c.prezime as dijabeticar_prezime,
        c.pol ,
        c.datum_na_ragjanje ,
        d.data_na_otkrivanje as datum_na_otkrivanje_na_dijabetes,
        td.ime as tip_dijabetes,
        tmp.godina,
        tmp.mesec,
        tmp.vkupno_doziran_insulin,
        m.ime as insulin_ime,
        m.proizvoditel as insulin_proizvoditel,
        ti.ime as insulin_tip
from (
        select 
                zidi.dijabeticar_id,
                extract(year from zidi."data") as godina,
                extract(month from zidi."data") as mesec,
                zidi.insulin_id,
                sum(zidi.kolicina) as vkupno_doziran_insulin
        from zapis_insulin_doziran_insulin zidi
        group by grouping sets(
                (zidi.dijabeticar_id, zidi.insulin_id)
                , (zidi.dijabeticar_id, extract(year from zidi."data"), zidi.insulin_id)
                , (zidi.dijabeticar_id, extract(year from zidi."data"), extract(month from zidi."data"), zidi.insulin_id)
        )
        order by zidi.dijabeticar_id, extract(year from zidi."data") nulls first, extract(month from zidi."data") nulls first, zidi.insulin_id
        ) as tmp
join insulin i on tmp.insulin_id = i.id 
join medikament m on i.id = m.id
join tip_insulin ti on i.tip_insulin_id = ti.id
join dijabeticar d on tmp.dijabeticar_id = d.id
join tip_dijabetes td on d.tip_dijabetes_id = td.id 
join covek c on d.id = c.id;

Три-месечен просек на измерен шеќер во крв кај дијабетичари - ОДОБРЕНО

• Член: Никола Торбовски 151200
• Опис: Три-месечен просек на измерен шеќер во крв кај дијабетичари пресметан за секој месец од годината за кој има запис и претходните два месеца(календарски)

  select 
          c.id ,
          c.ime ,
          c.prezime ,
          c.pol ,
          c.datum_na_ragjanje ,
          d.data_na_otkrivanje as datum_na_otkrivanje_na_dijabetes,
          td.ime as tip_dijabetes,
          extract(year from A.godina_mesec) as godina,
          extract(month from A.godina_mesec) as mesec,
          (sum(A.suma_vrednosti_za_mesec) over (partition by A.id order by A.godina_mesec range between '2 months' preceding and current row) / sum(A.broj_vrednosti_za_mesec) over(partition by A.id order by A.godina_mesec range between '2 months' preceding  and current row)) as prosek_za_prethodni_tri_meseci
  from
  (select
          zsis.dijabeticar_id as id,
          date_trunc('month', zsis."data") as godina_mesec,
          sum(zsis.vrednost) as suma_vrednosti_za_mesec,
          count(zsis.vrednost) as broj_vrednosti_za_mesec
  from zapis_so_izmeren_sekjer zsis
  group by zsis.dijabeticar_id , date_trunc('month', zsis."data")
  order by zsis.dijabeticar_id , date_trunc('month', zsis."data")
  ) as A
  join dijabeticar d on A.id = d.id
  join tip_dijabetes td on d.tip_dijabetes_id = td.id 
  join covek c on d.id = c.id;
  

ОПИС: Вкупна количина на тип на медикамент за секој дијабетичар што ја поседува

Кристијан Ристовски 90810

select
    c.id,
    c.ime,
    c.prezime,
    c.pol,
    c.datum_na_ragjanje,
    m.tip_medikament,
    m.ime as medikament_ime,
    m.proizvoditel as medikament_proizvoditel,
    ti.ime as tip_insulin,
    sum(kolicina) as vkupna_kolicina
from
    dijabeticar d
join
    covek c on d.id = c.id
join 
    tip_dijabetes td on d.tip_dijabetes_id = td.id
join
    inventar i on d.id = i.dijabeticar_id
join
    medikament m on i.medikament_id = m.id
left join
    insulin ins on m.id = ins.id
left join
    tip_insulin ti on ins.tip_insulin_id = ti.id
group by
    c.id,
    c.ime,
    c.prezime,
    c.pol,
    c.datum_na_ragjanje,
    m.tip_medikament,
    m.ime,
    m.proizvoditel,
    ti.ime;

ОПИС: Просечно внесени калории за секој дијабетичар по оброк во последните 3 месеци

Кристијан Ристовски 90810

select 
        dijabeticar_id,
        dijabeticar_ime,
        dijabeticar_prezime,
        datum_zapis,
        avg(vkupna_kolicina) 

from (select
    d.id as dijabeticar_id,
    c.ime as dijabeticar_ime,
    c.prezime as dijabeticar_prezime,
    zh."data" as datum_zapis,
    sum(o.kolicina) as vkupna_kolicina
from
    dijabeticar d
join
    covek c on d.id = c.id
join
    zapis z on d.id = z.dijabeticar_id
join
    zapis_hrana zh on z.dijabeticar_id = zh.dijabeticar_id and z."data" = zh."data"
join
    obrok o on zh.dijabeticar_id = o.dijabeticar_id and zh."data" = o."data"
join
    hrana h on o.hrana_id = h.id
where
    z."data" >= date_trunc('month', current_date - interval '3 months') 
group by
    d.id, 
    c.ime, 
    c.prezime,
    zh."data")
  
 group by
    dijabeticar_id,
        dijabeticar_ime,
        dijabeticar_prezime,
        datum_zapis   
;