data_analysis/production_code/videoods.total_price_video.sql

-- Task: 视频维度表  ID: 1000579613  Type: ODPS_SQL
--@exclude_input=total_price_video
--@exclude_output=total_price_video
--@exclude_input=total_reward_video
--@exclude_output=total_reward_video
--@exclude_input=every_video_status_category
--@exclude_output=every_video_status_category
--@exclude_input=videoid_top_tag_name
--@exclude_output=videoid_top_tag_name
--odps sql 
--********************************************************************--
--author:liuzhongguo
--create time:2020-06-06 16:40:39
--********************************************************************--
--每个视频的一级标签
DROP TABLE IF EXISTS videoid_top_tag_name ;

CREATE TABLE videoid_top_tag_name AS
SELECT  a.id AS videoid
        ,concat_ws(',',collect_set(c.tag_name)) tag_name
FROM    videoods.wx_video a
LEFT JOIN videoods.wx_video_tag_rel b
ON      a.id = b.video_id LEFT
JOIN    (
            SELECT  tag_id
                    ,tag_name
                    ,level
            FROM    videoods.wx_video_tag
        ) c
ON      b.tag_id = c.tag_id
--WHERE   tag_name IN ('音乐','综艺','搞笑','舞蹈','祝福','旅行','百态','健康','科技','妙招','影视','美食','时尚','运动','游戏','抗肺炎','在家学','热点','社会','人文','生活','财富','情感','时尚' ,'游戏' ,'网课' ,'微商' ,'婚庆' ,'小语种' ,'广告' ,'宣传片' ,'聚会活动' ,'加密&可见' ,'低质内容' ,'宗教' ,'影视作品' ,'ASMR' ,'无法加载' ,'其他平台' ,'私密&删除' ,'内容无意义' ,'涉及政治' ,'邪教迷信' ,'群体事件' ,'暴恐血腥' ,'网络谣言' ,'淫秽色情' ,'涉嫌侵权' ,'低俗内容' ,'其他')
WHERE   level = 1
GROUP BY a.id
;





--每个视频的二级标签
DROP TABLE IF EXISTS videoid_second_tag_name ;

CREATE TABLE videoid_second_tag_name AS
SELECT  a.id AS videoid
        ,concat_ws(',',collect_set(c.tag_name)) tag_name
FROM    videoods.wx_video a
LEFT JOIN videoods.wx_video_tag_rel b
ON      a.id = b.video_id LEFT
JOIN    (
            SELECT  tag_id
                    ,tag_name
                    ,level
            FROM    videoods.wx_video_tag
        ) c
ON      b.tag_id = c.tag_id
WHERE   tag_name IN ('民生' ,'灾祸' ,'时政' ,'法制' ,'节庆活动' ,'正能量' ,'打抱不平' ,'八卦' ,'军事' ,'历史' ,'非遗文化' ,'人物传记' ,'法律' ,'哲学' ,'艺术' ,'心理' ,'文学' ,'人生经历' ,'风土人情' ,'才艺' ,'妙招' ,'生活常识' ,'家居' ,'美女' ,'萌娃' ,'动物' ,'种草' ,'祝福' ,'助眠' ,'生活观赏' ,'星座' ,'生活辟谣' ,'三农' ,'奇闻' ,'科技前沿' ,'科学科普' ,'数码' ,'机械' ,'汽车' ,'科学推理' ,'科学实验' ,'财经' ,'商贸' ,'创业分享' ,'两性' ,'爱情' ,'亲情' ,'友情' ,'职场' ,'人生哲理' ,'乡情' ,'性学' ,'养生' ,'医学' ,'育儿' ,'美食分享' ,'美食制作' ,'黑暗料理' ,'奢侈料理' ,'自制片' ,'电影' ,'电视剧' ,'动漫' ,'综艺' ,'航拍' ,'摄影' ,'VLOG' ,'地标' ,'户外探险' ,'自驾游' ,'段子' ,'恶搞' ,'街访' ,'鬼畜' ,'相声' ,'小品' ,'脱口秀' ,'搞笑配音' ,'糗事' ,'舞蹈展示' ,'舞蹈教学' ,'影视舞蹈' ,'健身' ,'赛事' ,'瑜伽' ,'足球' ,'篮球' ,'乒乓球' ,'轮滑' ,'滑雪' ,'极限运动' ,'游泳' ,'钓鱼' ,'运动教学' ,'唱歌' ,'MV' ,'音乐相册' ,'音乐随拍' ,'音乐现场' ,'戏曲' ,'说唱' ,'口技' ,'乐器' ,'教学' ,'穿搭' ,'美妆' ,'美甲' ,'美发' ,'T台秀' ,'手游' ,'PC游戏' ,'主机游戏' ,'页游' ,'桌游' ,'街机')
--WHERE   level = 2
GROUP BY a.id
;



--每个视频的三级标签
DROP TABLE IF EXISTS videoid_third_tag_name ;

CREATE TABLE videoid_third_tag_name AS
SELECT  a.id AS videoid
        ,concat_ws(',',collect_set(c.tag_name)) tag_name
FROM    videoods.wx_video a
LEFT JOIN videoods.wx_video_tag_rel b
ON      a.id = b.video_id LEFT
JOIN    (
            SELECT  tag_id
                    ,tag_name
                    ,level
            FROM    videoods.wx_video_tag
        ) c
ON      b.tag_id = c.tag_id
--WHERE   tag_name IN ('就业' ,'租房' ,'劳务' ,'食品' ,'出行' ,'天灾' ,'人祸' ,'刑事案件' ,'民事纠纷' ,'娱乐圈' ,'仪仗队' ,'阅兵' ,'军演' ,'武器' ,'战争' ,'中国古代史' ,'中国近代史' ,'国外历史' ,'建筑' ,'雕塑' ,'手工' ,'书法' ,'绘画' ,'魔术' ,'杂技' ,'房屋' ,'室内设计' ,'防火防盗' ,'园艺' ,'音乐喷泉' ,'展会' ,'民俗' ,'奇人' ,'世界之最' ,'未解之谜' ,'外星文明' ,'时空穿梭' ,'灵异事件' ,'风水玄说' ,'新产品' ,'新技术' ,'科学发现' ,'动物' ,'物理' ,'数学' ,'天文地理' ,'物质' ,'证券' ,'金融投资' ,'宏观经济' ,'产业经济' ,'房地产' ,'婚姻' ,'恋爱' ,'体疗' ,'食疗' ,'中医' ,'营养学' ,'医疗原理' ,'急救知识' ,'儿童健康' ,'儿童教育' ,'美食推荐' ,'吃播' ,'探店' ,'大胃王' ,'电影解说' ,'电影剪辑' ,'电视剧解说' ,'电视剧剪辑' ,'动漫解说' ,'动漫剪辑' ,'流浪' ,'品城' ,'暗访' ,'国外' ,'国内' ,'舞蹈种类（略）' ,'奥运会' ,'NBA' ,'世界杯' ,'优质' ,'翻唱' ,'反串' ,'合成' ,'乐器种类' ,'内衣秀' ,'旗袍秀' ,'时装秀')
WHERE   level = 2
GROUP by  a.id
;











--每个视频的各种状态
DROP TABLE IF EXISTS every_video_status_category ;

CREATE TABLE every_video_status_category AS
SELECT  a.video_id as videoid
        ,视频审核状态 as video_edit
        ,视频的数据状态 as video_data_stat
        ,视频可搜状态 as video_recommend
        ,视频种类 as video_category
FROM    (
            SELECT  video_id
                    ,(
                        CASE    WHEN audit_status=1 THEN '审核中'
                                WHEN audit_status=2 THEN '不通过'
                                WHEN audit_status=3 THEN '待修改'
                                WHEN audit_status=4 THEN '自己可见'
                                WHEN audit_status=5 THEN '通过' 
                        END
                    ) AS 视频审核状态
                    ,(
                        CASE    WHEN video_status=1 THEN '有效'  --公开
                                WHEN video_status=2 THEN '已删除'
                                WHEN video_status=3 THEN '已屏蔽'
                                WHEN video_status=4 THEN '关注可见'
                                WHEN video_status=5 THEN '分享可见'
                                WHEN video_status=6 THEN '自己可见' 
                        END
                    ) AS 视频的数据状态
                    ,(
                        CASE    WHEN recommend_status=0 THEN '不可搜'
                                WHEN recommend_status=-6 THEN '待推荐'
                                WHEN recommend_status=1 THEN '普通推荐'     --普通推荐，编辑推荐都是推荐
                                WHEN recommend_status=10 THEN '编辑推荐'
                                WHEN recommend_status=-7 THEN '可搜索' 
                        END
                    ) AS 视频可搜状态
            FROM    videoods.wx_video_status
        ) a
FULL OUTER JOIN (
                    SELECT  video_id
                            ,(
                                CASE    WHEN category_id=1 THEN '内容'
                                        WHEN category_id=2 THEN '场景'
                                        WHEN category_id=3 THEN '工具'
                                        WHEN category_id=4 THEN '未分类'
                                        WHEN category_id=5 THEN '原创内容'
                                        WHEN category_id=6 THEN '转载内容' 
                                END
                            ) AS 视频种类
                            ,row_number() over(partition by video_id order by id desc) as rn
                    FROM    videoods.wx_video_category_info
                ) b
ON      a.video_id = b.video_id
AND     b.rn = 1
;



--赞赏总金额和赞赏人数、被赞赏视频数
DROP TABLE IF EXISTS total_reward_video ;

CREATE TABLE total_reward_video AS
SELECT  video_id AS videoid
        ,SUM(reward_amount)/100 AS total_reward    --赞赏总金额
        ,coalesce(
            SUM(
                CASE    WHEN FROM_UNIXTIME(CAST( create_time/ 1000 AS BIGINT) )='${bizdate}' THEN reward_amount 
                END
            )
            ,0
        )/100 AS currentday_reward    --当日赞赏金额
        ,COUNT(1) AS total_reward_times    --赞赏次数
        ,COUNT(DISTINCT uid) AS reward_person    --赞赏人数
FROM    videoods.wx_video_reward_record
GROUP BY video_id
;

--付费数据
DROP TABLE IF EXISTS total_price_video ;

CREATE TABLE total_price_video AS
SELECT  video_id AS videoid
        ,SUM(price) AS total_price    --付费总金额
        ,coalesce(
            SUM(
                CASE    WHEN FROM_UNIXTIME(CAST( gmt_payment_timestamp/ 1000 AS BIGINT) )='${bizdate}' THEN price 
                END
            )
            ,0
        ) AS currentday_price    --当日付费金额
        ,COUNT(1) AS total_price_times    --付费次数
        ,COUNT(DISTINCT uid) AS total_price_person    --付费人数
       
FROM    videoods.wx_video_purchase_record
GROUP BY video_id
;



-- 新增视频类型判别
DROP TABLE if_create_video;
CREATE TABLE  IF NOT EXISTS  if_create_video AS 
SELECT DISTINCT
id
,(CASE WHEN t2.video_id IS NULL THEN '上传视频'
       WHEN t2.video_id IS NOT NULL THEN '创作视频'
       END ) if_create
,if_change
,(CASE WHEN t2.from_scene=2 THEN '创作工具' WHEN t2.from_scene=3 THEN '普通上传转创作工具'
 WHEN t2.from_scene=4 THEN '后台转加工' WHEN t2.from_scene=5 THEN '卡点视频' END ) video_type
FROM 
(SELECT 
id
FROM 
videoods.wx_video
) t1
LEFT JOIN 
(SELECT 
video_id,from_scene
FROM 
videoods.produce_video_project
) t2
ON t1.id=t2.video_id
LEFT JOIN 
(SELECT  pvp.video_id,
(CASE
WHEN pvp.parent_project_id is not null and pvp.uid!=pvp_parent.uid THEN '再创作'
WHEN  pvp.parent_project_id IS NOT NULL AND pvp.uid=pvp_parent.uid THEN '再编辑' END ) if_change
FROM    videoods.produce_video_project pvp
JOIN    videoods.produce_video_project pvp_parent
ON     CAST (pvp.parent_project_id AS STRING ) = pvp_parent.project_id
JOIN videoods.wx_video
ON    pvp.video_id = wx_video.id
UNION ALL 
SELECT  pvp.video_id,'原创'
FROM    videoods.produce_video_project pvp
JOIN   videoods.wx_video
ON    pvp.video_id = wx_video.id
WHERE pvp.parent_project_id IS NULL
) t3
ON t1.id=t3.video_id
;



--CREATE TABLE dim_video AS
INSERT OVERWRITE TABLE dim_video
SELECT  a1.videoid    --视频id
        ,a1.title    --视频标题
        ,distrubute_title    --视频分发标题
        ,video_category    --视频种类
        ,a3.tag_name    --视频一级分类
        ,a1.gmt_create    --上传时间  
        ,tags    --视频标签
        ,a1.uid    --生产者id
        ,video_edit    --视频审核状态
        ,video_data_stat    --视频的数据状态
        ,video_recommend    --视频可搜状态
        ,CASE    WHEN a7.cover_img_path IS NULL THEN '无' 
                 ELSE '有' 
         END AS is_img    --视频分发封面
        ,(
            UNIX_TIMESTAMP(datetrunc(GETDATE(), "DD"))-UNIX_TIMESTAMP(a1.gmt_create)
        )/(60*60*24) AS existence_days    --发布距今天数
        ,concat(
            'https://admin.yishihui.com/cms/post-detail/'
            ,a1.videoid
            ,'/info'
        ) AS video_url    --后台视频链接
        ,a1.total_time    --视频时长
        ,coalesce(a1.play_count,0) play_count    --播放人数
        ,coalesce(a1.play_count_total,0) play_count_total    --播放次数
        ,coalesce(total_reward,0) total_reward    --赞赏总金额
        ,coalesce(currentday_reward,0) currentday_reward    --当日赞赏金额
        ,coalesce(total_reward_times,0) total_reward_times    --赞赏次数
        ,coalesce(reward_person,0) reward_person    --赞赏人数
        ,coalesce(total_price,0) total_price    --付费总金额
        ,coalesce(currentday_price,0) currentday_price    --当日付费金额
        ,coalesce(total_price_times,0) total_price_times    --付费次数
        ,coalesce(total_price_person,0) total_price_person    --付费人数
        ----------------------------------新增----------------------------------
        ,app_recommend_status    --app推荐状态
        ,charge    --收费状态
        ,CASE    WHEN a10.video_id IS NULL THEN '未加密' 
                 ELSE '加密' 
         END AS is_pwd    --是否加密
        ,width    --视频宽度
        ,height    --视频高度
        ----------------------------------新增2---------------------------------
        ,a11.tag_name as tag_name2     --二级标签
        ,a12.tag_name as tag_name3     --三级标签
        ---------------------------------新增3----------------------------------
        ,a13.if_create --是否为创作视频
        ---------------------------------新增4----------------------------------    
        ,a13.if_change
        ,a9.app_type
        ,a9.descr
        ,a13.video_type
        ,a14.if_in_youthCommunity
        ,a14.if_in_agedCommunity
        ,a15.name
FROM    (
            SELECT  id AS videoid
                    ,uid
                    ,title
                    ,play_count
                    ,play_count_total
                    ,total_time
                    ,gmt_create
                    ,width
                    ,height
            FROM    videoods.wx_video
        ) a1
LEFT JOIN (    --视频标签
              SELECT  a.video_id
                      ,concat_ws(',', collect_set(b.tag_name)) AS tags
              FROM    (
                          SELECT  video_id
                                  ,tag_id
                          FROM    videoods.wx_video_tag_rel
                      ) a
              LEFT JOIN (
                            SELECT  tag_id
                                    ,tag_name
                            FROM    videoods.wx_video_tag
                        ) b
              ON      a.tag_id = b.tag_id
              GROUP BY video_id
          ) a2
ON      a1.videoid = a2.video_id LEFT
JOIN    (    --每个视频的一级分类
            SELECT  videoid
                    ,tag_name
            FROM    videoid_top_tag_name
        ) a3
ON      a1.videoid = a3.videoid
LEFT JOIN (    --每个视频的各种状态
              SELECT  videoid
                      ,video_edit
                      ,video_data_stat
                      ,video_recommend
                      ,video_category
              FROM    every_video_status_category
          ) a4
ON      a1.videoid = a4.videoid LEFT
JOIN    (    --赞赏总金额和赞赏人数、被赞赏视频数
            SELECT  videoid
                    ,total_reward
                    ,currentday_reward
                    ,total_reward_times
                    ,reward_person
            FROM    total_reward_video
        ) a5
ON      a1.videoid = a5.videoid
LEFT JOIN (    --付费数据
              SELECT  videoid
                      ,total_price
                      ,currentday_price
                      ,total_price_times
                      ,total_price_person
              FROM    total_price_video
          ) a6
ON      a1.videoid = a6.videoid LEFT
JOIN    (    --分发标题
            SELECT  video_id
                    ,title AS distrubute_title
                    ,cover_img_path
            FROM    videoods.wx_video_recommend_ext
        ) a7
ON      a1.videoid = a7.video_id
LEFT JOIN (    --app推荐
              SELECT  video_id
                      ,(
                          CASE    WHEN app_recommend_status=-1 THEN '未分类'
                                  WHEN app_recommend_status=0 THEN '不可搜'
                                  WHEN app_recommend_status=-7 THEN '可搜索'
                                  WHEN app_recommend_status=1 THEN '普通推荐'     --有app推荐
                                  WHEN app_recommend_status=-6 THEN '待推荐'
                                  WHEN app_recommend_status=10 THEN '编辑推荐'
                                  WHEN app_recommend_status=20 THEN '实时推荐'
                          END
                      ) AS app_recommend_status    --app推荐状态
              FROM    videoods.wx_video_status
          ) a8
ON      a1.videoid = a8.video_id LEFT
JOIN    (    --是否免费
            SELECT  video_id
                    ,CASE    WHEN charge=0 THEN '免费'
                             WHEN charge=1 THEN '收费' 
                     END AS charge
                    ,app_type
                    ,descr
            FROM    videoods.wx_video_detail
        ) a9
ON      a1.videoid = a9.video_id
LEFT JOIN (    --是否加密
              SELECT  video_id
              FROM    wx_video_pwd
          ) a10
ON      a1.videoid = a10.video_id LEFT
JOIN    videoid_second_tag_name a11    --二级标签
ON      a1.videoid = a11.videoid
LEFT JOIN videoid_third_tag_name a12    --三级标签
ON      a1.videoid = a12.videoid
LEFT JOIN if_create_video  a13
ON  a1.videoid=a13.id
LEFT JOIN (
SELECT  video_id
        ,CONCAT_WS(
            ','
            ,collect_set(
                CASE    WHEN repository_type=1 AND data_status=1 THEN '进入青年社区'
                        WHEN repository_type=1 AND data_status=0 THEN '未进入青年社区' 
                END
            )
        ) if_in_youthCommunity
        ,CONCAT_WS(
            ','
            ,collect_set(
                CASE    WHEN repository_type=0 AND data_status=1 THEN '进入老年社区'
                        WHEN repository_type=0 AND data_status=0 THEN '未进入老年社区' 
                END
            )
        ) if_in_agedCommunity
FROM    videoods.video_repository_type
GROUP BY video_id
) a14
ON a1.videoid=a14.video_id
LEFT JOIN 
(SELECT 
t1.video_id,t2.name
FROM 
videoods.video_topic t1 LEFT JOIN videoods.topic t2
ON t1.topic_id=t2.id
WHERE  t1.data_status=1
) a15
ON a1.videoid=a15.video_id
;