vgrabovets
11/9/2016 - 10:52 AM

videos / tv

videos / tv

# movies query ####
movies_s = "SELECT
              o.cdn_obj_id
, o.obj_id
, o.own
, v2.type                                                                                          AS type
, v2.title                                                                                         AS title
, IF(t.timestamp IS NULL OR t.timestamp = 0, v1.duration_sec, LEAST(v1.duration_sec, t.timestamp)) duration_sec

FROM
daily.own o
LEFT JOIN megogo2.video v1 ON o.cdn_obj_id = v1.cdn_obj_id
LEFT JOIN megogo2.video v2 ON o.own = v2.obj_id
LEFT JOIN megogo2.video_titles t ON t.obj_id = o.obj_id
WHERE o.cdn_obj_id > 0
AND v1.type NOT IN ('ANNOUNCE', 'tv', 'news', 'music', 'trailer', 'adt', 'showfilm') AND v2.type in ('FILM', 'MULTFILM', 'SERIAL', 'MULTSERIAL')"

movies_q = runQuery(movies_s, TRUE)

# TV query ####
tv_s = "select cdn_obj_id, 'tv' type from megogo2.video_live
         where cdn_obj_id > 0
         group by cdn_obj_id"
tv_q = runQuery(tv_s)

videos_main = movies_q %>%
              bind_rows(tv_q)