Untitled

 avatar
unknown
plain_text
a year ago
1.5 kB
4
Indexable
    INSERT INTO dds.fct_product_sales
    (product_id, order_id, "count", price, total_sum, bonus_payment, bonus_grant)
    with sub_t as (select 
            json_array_elements(object_value::json -> 'order_items' ) ->> 'id' as product_id,
            REPLACE((object_value::json -> '_id'):: varchar, '"', '') as order_id,
            (json_array_elements(object_value::json -> 'order_items' ) ->> 'quantity')::int as quantity,
            (json_array_elements(object_value::json -> 'order_items' ) ->> 'price')::numeric(19, 5) as price,
            (json_array_elements(object_value::json -> 'order_items' ) ->> 'price')::numeric(19, 5) * 
            (json_array_elements(object_value::json -> 'order_items' ) ->> 'quantity')::int as total_sum,
            (object_value::json -> 'bonus_grant')::varchar::numeric(19, 5) as bonus_grant,
            (object_value::json -> 'bonus_payment')::varchar::numeric(19, 5) as bonus_payment
            from stg.ordersystem_orders)
        select 
        dmp.id,
        do2.id,
        tt.quantity as "count",
        tt.price,
        tt.total_sum,
        tt.bonus_payment,
        tt.bonus_grant
        from sub_t as tt   
        left join dds.dm_products as dmp on dmp.product_id = tt.product_id
        left join dds.dm_orders as do2 on do2.order_key = tt.order_id    
        full join dds.fct_product_sales as fps on fps.product_id = dmp.id
        and fps.order_id = do2.id
        where fps.order_id is null;
Editor is loading...
Leave a Comment