2017.02.23

优朋星娱乐和大片五分熟数据导出

大片五分熟

1238113941,8

1158036262,8

1204913540,9

1154250874,9

43697919,9

64522329,9

49363425,9

2688609,10

16749731,10

53364088,10

52803414,11

86236435,11

76934847,11

1227405843,11

1191868932,11

CREATE TABLE `2yy`(

`dim_movie_mid` string,

`mtype` string

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

load data local inpath '2yy.txt'into table 2yy;

导出数据

selecttmm.mtypecount,sum(if(tmm.busmode=1,1,0)),sum(if(tmm.busmode=2,1,0)) from

(

selecttm.dim_user_hid,tm.busmode,count(distinct tm.mtype) as mtypecount from

(selectt.mtype,m.dim_user_hid,collect_set(m.dim_oem_busmode)[0] as busmode from 2yy tjoin fact_vod_cube m on t.dim_movie_mid=m.dim_movie_mid wherem.day>='2016-01-01' group by t.mtype,m.dim_user_hid) tm group bytm.dim_user_hid,tm.busmode

) tmm group by tmm.mtypecount;

优朋星娱乐导出：（由于我建的是1yy）

selecttmm.mtypecount,sum(if(tmm.busmode=1,1,0)),sum(if(tmm.busmode=2,1,0)) from

(

select tm.dim_user_hid,tm.busmode,count(distincttm.mtype) as mtypecount from

(selectt.mtype,m.dim_user_hid,collect_set(m.dim_oem_busmode)[0] as busmode from 1yy tjoin fact_vod_cube m on t.dim_movie_mid=m.dim_movie_mid wherem.day>='2016-01-01' group by t.mtype,m.dim_user_hid) tm group bytm.dim_user_hid,tm.busmode

) tmm group by tmm.mtypecount