Напредни извештаи од базата (SQL и складирани процедури)

Извештај за сите сервиси,број на критики кои ги добиле и нивниот просечен рејтинг

select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , count(r2.review_rating) as review_count ,avg(r2.review_rating)::float as review_average
from project.repair_shop rs 
left join project.repair r on r.id_repair_shop = rs.id_repair_shop 
left join project.review r2 on r2.id_repair = r.id_repair 
group by rs.id_repair_shop 

Извештај за доставувачи за нивната најдобра нарачка. Се прикажуваат информации за доставувачот, која е неговата најдобра нарачка што ја доставил и кој производ е најскап во таа нарачка и истите се сортирани според најдобрата нарачка

select nar.deliverer, nar.deliverer_username,
        max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod 
        from project.delivery_man d 
        left join 
        (
                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
                ,sum(p2.amount*ocp.quantity_order) as nar_suma
                from project.delivery_man d
                left join project.users_table ut on ut.id_user = d.id_user 
                left join project.delivery d2 on d2.id_user = d.id_user 
                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
                left join project.part p on p.id_part = ocp.id_part 
                left join project.price p2 on p2.id_part = p.id_part
                group by (d.id_user,ut.username,delivery_num)
        ) as nar on d.id_user = nar.deliverer
        left join 
        (
                select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name,
                sum(p2.amount*ocp.quantity_order) as par_suma
                from project.delivery_man d
                left join project.users_table ut on ut.id_user = d.id_user 
                left join project.delivery d2 on d2.id_user = d.id_user 
                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
                left join project.part p on p.id_part = ocp.id_part 
                left join project.price p2 on p2.id_part = p.id_part
                group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name)
        ) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num
        group by nar.deliverer,nar.deliverer_username 
        order by najdobra_naracka desc

Извештај за успешноста на еден доставувач. Се прикажуваат информации за доставувачот, број на нарачки што ги доставил и вкупна вредност на сите нарачки што ги доставил

select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi,
        sum(nar.nar_suma) as vkupna_suma
        from project.delivery_man d 
        left join 
        (
                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
                ,sum(p2.amount*ocp.quantity_order) as nar_suma
                from project.delivery_man d
                left join project.users_table ut on ut.id_user = d.id_user 
                left join project.delivery d2 on d2.id_user = d.id_user 
                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
                left join project.part p on p.id_part = ocp.id_part 
                left join project.price p2 on p2.id_part = p.id_part
                group by (d.id_user,ut.username,delivery_num)
        ) as nar on d.id_user = nar.deliverer
        group by nar.deliverer,nar.deliverer_username 
        order by vkupna_suma desc

Извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија

select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
count(ocp.id_part) as category_by_car
from project.category as c
left join project.part_is_from_category pifc on pifc.id_category = c.id_category 
left join project.part p on p.id_part = pifc.id_part 
left join project.order_contains_part ocp on ocp.id_part = p.id_part 
left join project.order_table ot on ot.id_order = ocp.id_order 
left join project.users_table ut on ut.id_user = ot.id_user 
left join project.car_sample cs on cs.id_user = ut.id_user 
left join project.car c2 on c2.id_car = cs.id_car 
left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer 
group by c.id_category,cm.id_car_manufacturer  
order by category_by_car desc

Извештај за најкупуван артикл, во која количина и од која категорија е истиот

select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
        left join project.category c on c.id_category = pifc.id_category  
        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
        group by c.id_category , p.id_part  
        having coalesce(sum(ocp2.quantity_order), 0)=(
                select max(maxkol) from
                (
                        select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
                        left join project.part_is_from_category pifc on pifc.id_part = p.id_part 
                        left join project.category c on c.id_category = pifc.id_category  
                        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part 
                        group by c.id_category , p.id_part 
                ) q1
                )

Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 календарски месеци.

select p.id_part, p.part_name, cat.category_name, pm.pm_name, 
coalesce (f.narachani_vo_ovoj_mesec,0) as narachani_ovoj_mesec, 
coalesce (s.narachani_vo_prethoden_mesec,0) as narachani_prethoden_mesec,
coalesce (t.narachani_vo_predprethoden_mesec,0) as narachani_predprethoden_mesec
from project.part as p
join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
join project.part_is_from_category as pifc on p.id_part = pifc.id_part
join project.category as cat on pifc.id_category = cat.id_category
join project.order_contains_part as ocp on p.id_part = ocp.id_part
join project.order_table as o on ocp.id_order = o.id_order
left join (
select p.id_part, p.part_name, cat.category_name, pm.pm_name,
       sum(ocp.quantity_order) as narachani_vo_ovoj_mesec
from project.part as p
join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
join project.part_is_from_category as pifc on p.id_part = pifc.id_part
join project.category as cat on pifc.id_category = cat.id_category
join project.order_contains_part as ocp on p.id_part = ocp.id_part
join project.order_table as o on ocp.id_order = o.id_order
                                  and
                              extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
group by p.id_part, p.part_name, cat.category_name, pm.pm_name
) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
left join (
select p.id_part, p.part_name, cat.category_name, pm.pm_name,
       sum(ocp.quantity_order)  as narachani_vo_prethoden_mesec
from project.part as p
join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
join project.part_is_from_category as pifc on p.id_part = pifc.id_part
join project.category as cat on pifc.id_category = cat.id_category
join project.order_contains_part as ocp on p.id_part = ocp.id_part
join project.order_table as o on ocp.id_order = o.id_order
                                  and
                              extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month')
group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
left join (
select p.id_part, p.part_name, cat.category_name, pm.pm_name,
       sum(ocp.quantity_order)  as narachani_vo_predprethoden_mesec
from project.part as p
join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
join project.part_is_from_category as pifc on p.id_part = pifc.id_part
join project.category as cat on pifc.id_category = cat.id_category
join project.order_contains_part as ocp on p.id_part = ocp.id_part
join project.order_table as o on ocp.id_order = o.id_order
                                  and
                              extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month')
group by p.id_part, p.part_name, cat.category_name, pm.pm_name 
) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec

Извештај за деловите кои биле нарачани во последните 3 месеци, со нивни категории и производители и количината која била нарачана

select p.id_part, p.part_name, cat.category_name, pm.pm_name,
       sum(ocp.quantity_order) as narachani_posledni_3_meseci
from project.part as p
join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
join project.part_is_from_category as pifc on p.id_part = pifc.id_part
join project.category as cat on pifc.id_category = cat.id_category
join project.order_contains_part as ocp on p.id_part = ocp.id_part
join project.order_table as o on ocp.id_order = o.id_order
                                  and
                              o.order_date between now()-interval'3 months' and now()
group by p.id_part, p.part_name, cat.category_name, pm.pm_name