-- 收入数据汇总
INSERT INTO ads.revenue_data ( relation_id,prodid,ds,ds_type,ds_name,channel,channel_type_name,merchant_name,platform_name,order_amount,money,active_num)
SELECT  t1.relation_id
        ,t1.product_id prodid
        ,t.ds
        ,t.ds_type
        ,t.ds_name
        ,t1.channel_id channel
        ,t1.channel_name channel_type_name
        ,t1.merchant_name merchant_name
        ,t1.platform_name platform_name
        ,SUM(COALESCE(tt4.order_amount,0)) order_amount
        ,SUM(COALESCE(tt5.money,0)) money
        ,SUM(COALESCE(tt3.active_num,0)) active_num
FROM    (
            SELECT  game_channel_id
                    ,game_identity
                    ,game_platform_id
                    ,ds
                    ,ds_type
                    ,ds_name
            FROM    dwd.active_account_df
            UNION
            SELECT  game_channel_id
                    ,game_identity
                    ,game_platform_id
                    ,ds
                    ,ds_type
                    ,ds_name
            FROM    dwd.order_df
        ) t
INNER JOIN dim.game_product_relation t1
ON      t.game_channel_id = t1.game_channel_id
AND     t.game_identity = t1.game_identity
AND     t.game_platform_id = t1.game_platform_id
LEFT JOIN dwd.active_account_df tt3
ON      t.game_channel_id = tt3.game_channel_id
AND     t.game_identity = tt3.game_identity
AND     t.game_platform_id = tt3.game_platform_id
AND     t.ds = tt3.ds
AND     t.ds_type = tt3.ds_type
LEFT JOIN dwd.order_df tt4
ON      t.game_channel_id = tt4.game_channel_id
AND     t.game_identity = tt4.game_identity
AND     t.game_platform_id = tt4.game_platform_id
AND     t.ds = tt4.ds
AND     t.ds_type = tt4.ds_type
left JOIN dwd.revenue_df tt5 on t1.relation_id=tt5.relation_id
AND tt4.ds=tt5.ds
and tt4.ds_type=tt5.ds_type
group by t1.relation_id
        ,t1.product_id
        ,t.ds
        ,t.ds_type
        ,t.ds_name
        ,t1.channel_id
        ,t1.channel_name
        ,t1.merchant_name
        ,t1.platform_name

ON CONFLICT (relation_id,channel,merchant_name,platform_name,ds,ds_type)
DO UPDATE SET prodid = EXCLUDED.prodid,order_amount = EXCLUDED.order_amount,money = EXCLUDED.money,
active_num = EXCLUDED.active_num
;