歌手影响力指数日统计思路分析

需求描述：这里统计歌手热度是根据歌曲特征日统计表：TW_SONG_FTUR_D计算得到，主要借助了微信指数来统计对应的歌手的热度，统计了当天、近7天、近30天每个歌手的热度信息存放在对应的EDS层的歌手影响力指数日统计表baizhan_music.TW_SINGER_RSI_D和MySQL的songresult.tm_singer_rsi。

数据来源Hive中的表:歌曲特征日统计TW_SONG_FTUR_D

分析思路：

xxxxxxxxxx
temp1:
SELECT SINGER1ID,
    SINGER1,
    sum(SING_CNT) as TOTAL_SING_CNT ,
    sum(SUPP_CNT) as TOTAL_SUPP_CNT ,
    sum(RCT_7_SING_CNT) as TOTAL_RCT_7_SING_CNT ,
    sum(RCT_7_SUPP_CNT) as TOTAL_RCT_7_SUPP_CNT ,
    sum(RCT_7_TOP_SING_CNT) as TOTAL_RCT_7_TOP_SING_CNT ,
    sum(RCT_7_TOP_SUPP_CNT) as TOTAL_RCT_7_TOP_SUPP_CNT ,
    sum(RCT_30_SING_CNT) as TOTAL_RCT_30_SING_CNT ,
    sum(RCT_30_SUPP_CNT) as TOTAL_RCT_30_SUPP_CNT ,
    sum(RCT_30_TOP_SING_CNT) as TOTAL_RCT_30_TOP_SING_CNT ,
    sum(RCT_30_TOP_SUPP_CNT) as TOTAL_RCT_30_TOP_SUPP_CNT
FROM  TW_SONG_FTUR_D 
WHERE data_dt = 20301011 
GROUP BY SINGER1ID,SINGER1
    
-- 套用公式计算热度 
temp2:
SELECT SINGER1ID,SINGER1,(xxx) as 1RSI,(xxx) as 7RSI,(xxx) as 30RSI,data_dt
FROM temp1 
-- 统计歌手1日热度以及排名：
SELECT "1" as  PERIOD，SINGER1ID as SINGER_ID，SINGER1 as SINGER_NAME，1RSI as RSI,row_number() over(partition by data_dt order by 1RSI desc) as RSI_RANK
FROM temp2
-- 统计歌手7日热度以及排名：
SELECT "7" as  PERIOD，SINGER1ID as SINGER_ID，SINGER1 as SINGER_NAME，7RSI as RSI,row_number() over(partition by data_dt order by 7RSI desc) as RSI_RANK
FROM temp2
-- 统计歌手30日热度以及排名：
SELECT "30" as  PERIOD，SINGER1ID as SINGER_ID，SINGER1 as SINGER_NAME，30RSI as RSI,row_number() over(partition by data_dt order by 30RSI desc) as RSI_RANK
FROM temp2  
-- 以上三个结果union 得到总结果 result
-- 将result结果插入Hive的表： TW_SINGER_RSI_D
insert overwrite table TW_SINGER_RSI_D partition(data_dt=${currentDate}) select * from result
-- 将result结果插入MySQL的表：songresult.tm_singer_rsi