SQL left join  例子

 

--odps sql 
--********************************************************************--
--author:odps-game
--create time:2020-01-10 17:54:56
--********************************************************************--

    with a as(
        SELECT * from workspace_saiyisai.odps_base_sdkh_mid_roi
        WHERE   pt = '${s1}'
    ),
    b as(
        select _id,device_id,pt
        FROM sync_mongo_box.extract_sdkh_app_source__userinfo
        WHERE SUBSTR(create_time,1,10) = pt and pt = '${s1}'
        and _id in ("702615261", "711807701", "713043891" )       

    )
    SELECT  COUNT(a.device_id) AS devices
    ,round(SUM(duration)/60/COUNT(a.device_id),3) AS avg_duration
    ,round(SUM(front_end_duration)/60/COUNT(a.device_id),3) as avg_app_duration
    ,round(SUM(videos)/COUNT(a.device_id),3) as avg_videos
    ,round(SUM(coins)/COUNT(a.device_id),3) AS avg_coins
    ,ROUND(SUM(tixian_money),3) AS tixian_money
    ,round(SUM(tixian_money)/COUNT(a.device_id),3) AS avg_tixian_money
    ,ROUND(COALESCE(SUM(videos*video_price),0),3) AS ad_income
    ,round(COALESCE(SUM(videos*video_price)/COUNT(a.device_id),0),3) AS avg_ad_income
    ,ROUND(COALESCE((SUM(videos*video_price)-SUM(tixian_money)-SUM(active_cost)),0),3) AS roi
    ,round(COALESCE((SUM(videos*video_price)-SUM(tixian_money)-SUM(active_cost))/COUNT(a.device_id),0),3) AS avg_roi
    ,ROUND(COALESCE(SUM(active_cost),0),3) AS active_cost
    ,round(COALESCE(SUM(active_cost)/COUNT(a.device_id),0),3) AS avg_active_cost
    from  b LEFT  join a  on a.device_id = b.device_id and a.pt = b.pt;