优朋星娱乐和大片五分熟数据导出
大片五分熟
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
