onlineview.sql

onlineview.sql

SELECT 
    d.parent_id, views, cdn, 
    from_unixtime(1405267200 + row_number * 60) as step_time
FROM
    (SELECT 
        apptype_id, game_id, count(view_id) AS views, r.row_number,
        sum(bytes_loaded_cdn1 + bytes_loaded_cdn2 + bytes_loaded_cdn3 + bytes_loaded_cdn4) /duration as cdn
    FROM
        (SELECT 
        @curRow:=@curRow + 1 AS row_number
    FROM
        user_view
    JOIN (SELECT @curRow:=0) t
    WHERE
        @curRow < 1440
    LIMIT 1440) r
    JOIN (SELECT 
        apptype_id, start_time_utc, duration, view_id, game_id,
            bytes_loaded_cdn1, bytes_loaded_cdn2,
            bytes_loaded_cdn3, bytes_loaded_cdn4 
    FROM
        user_view
    WHERE
        end_hour_id BETWEEN 2014071300 AND 2014071523
            AND game_id = 1576763688) u 
    ON r.row_number >= ((start_time_utc div 1000 - 1405267200) div 60)
        AND r.row_number <= (((start_time_utc div 1000 - 1405267200) + duration) div 60)
    GROUP by r.row_number, apptype_id) q, dim_apptype_child d 
where q.apptype_id = d.apptype_id 
order by d.parent_id, step_time