【数据库】pgsql窗口函数，分组求sum的方式

select distinct
    quantity,
    standard_cost,
    sum(a.quantity*a.standard_cost) over(partition by purchase_scence_code,raw_material_code) amount,
     coalesce(purchase_scence_code,'unknown') as purchase_scence_code,
    coalesce(raw_material_code,'unknown') as raw_material_code
from ap.fact_outbound_order as a 
where
    a.material_type_code='3'
    and
    quantity<0

 

select
        purchase_scence_code,
        sum(amount)
from (
        select distinct
                quantity,
                standard_cost,
                sum(a.quantity*a.standard_cost) over(partition by purchase_scence_code,raw_material_code) amount,
                coalesce(purchase_scence_code,'unknown') as purchase_scence_code,
                coalesce(raw_material_code,'unknown') as raw_material_code
        from ap.fact_outbound_order as a 
        where a.material_type_code='3'
) t1
group by t1.purchase_scence_code