微博数据分析

2019-09-16

1、(基础分析题)各频道参与博主的人数分布排行，求top5

1）需求分析：求对应分布的top5
select remark,num
from
(select remark,count(1) as num
from
weibo_user_base_info
group by remark) realtion
order by num desc
limit 5;

1.rank() over：查出指定条件后的进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次
2.dense_rank() over：与ran() over的区别是，两名学生的成绩并列以后，下一位同学并不空出所占的名次。
3.row_number() over这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名

select remark,num,topn as top5
from
(
select remark,num,dense_rank() over (order by num desc) as topn
from
(select remark,count(1) as num
from
weibo_user_base_info
group by remark) realtion
) ton_tab
where topn<=5;

2、(基础分析题)各频道评论量排行，求top5

select remark,num,topn as top5
from
(
select remark,num,dense_rank() over(order by num desc) as topn
from 
(select remark,count(1) as num from 
weibo_comment_info a left join weibo_user_base_info b
on a.uid=b.uid
group by remark
) realtion
) top_tab
where topn<=5;

3、(基础分析题)各频道评论转发量排行，求top5

直接借鉴上一题：

select remark,topn as top5
from
(
select remark,dense_rank() over(order by sum desc) as topn
from
(
select remark, sum(repostscount) as sum
from
weibo_comment_info a left join weibo_user_base_info b
on a.uid=b.uid
group by remark
) realtion
) top_tab
where topn<=5;

4、(基础分析题)各频道博文评论的回复(再回复)量排行，求top5

直接借鉴上一题：

select remark,sum ,topn as top5
from
(
select remark,sum,dense_rank() over(order by sum desc) as topn
from
(
select remark, sum((commentscount) ) as sum
from
weibo_comment_info a left join weibo_user_base_info b
on a.uid=b.uid
group by remark
) realtion
) top_tab
where topn<=5;

5、(综合分析题)各频道活跃度排行

*先定义合理的计算口径，然后写对应的SQL语句
（以下为数据分析题的步骤列表）
step1 : 候选影响因子集合找到
博主参与量uid，评论内容量comment,评论转发量repostscount，评论回复量commentscount
step2 : 拟定一个计算公式
影响因子的权重：基准为1=有评论内容即为1，
有博主参与权重1.1，
有转发的权重：2
有再评论的权重：1.5
加权求和：
step3 : sql编码、review与修正
step4 : 上线

1)关联表
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid;

2)统计打分-明细 
select
remark,
count(distinct(uid))*1.1 user_score,
count(1)*1 comment_score,
sum(repostscount)*2 repost_score,
sum(commentscount)*1.5 comments_score
from(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
GROUP BY remark;

3)统计打分-明细-汇总
select 
remark,
(user_score+comment_score+repost_score+comments_score) sum_score
from
(
select
remark,
count(distinct(uid))*1.1 user_score,
count(1)*1 comment_score,
sum(repostscount)*2 repost_score,
sum(commentscount)*1.5 comments_score
from(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
GROUP BY remark
) sum_tab
;

4)统计打分-明细-汇总-排名
select remark,sum_score,topn as top5
from
(
select remark,sum_score,dense_rank() over(order by sum_score desc) topn
from
( 
select 
remark,
(user_score+comment_score+repost_score+comments_score) sum_score
from
(
select
remark,
count(distinct(uid))*1.1 user_score,
count(1)*1 comment_score,
sum(repostscount)*2 repost_score,
sum(commentscount)*1.5 comments_score
from(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
GROUP BY remark
) sum_tab
) tmp_topn
) ton_tab
where topn<=5;

6、(综合分析题)各频道的博主的影响力排行，求top5

*先定义合理的计算口径，然后写对应的SQL语句
（以下为数据分析题的步骤列表）
step1 : 候选影响因子集合找到
评论内容量，评论转发量，再评论量(评论内容质量)
step2 : 拟定一个计算公式
影响因子的权重：基准为1=有评论内容即为1，
有转发的权重：2
有再评论的权重：1.5
加权求和：
step3 : sql编码、review与修正
step4 : 上线

基于上一题直接对uid group by求解
连表-统计打分-明细-汇总-排名

select uid,remark,sum_score,topn as top5
from
(
select uid,remark,sum_score,dense_rank() over(partition by remark order by sum_score desc) topn
from
( 
select 
uid,
remark,
(comment_score+repost_score+comments_score) sum_score
from
(
select
uid,
remark,
count(distinct(uid))*1.1 user_score,
count(1)*1 comment_score,
sum(repostscount)*2 repost_score,
sum(commentscount)*1.5 comments_score
from(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
GROUP BY uid,remark
) sum_tab
) tmp_topn
) ton_tab
where topn<=5;

7、(综合分析题)各频道的博文的影响力排行,求top5

*先定义合理的计算口径，然后写对应的SQL语句
（以下为数据分析题的步骤列表）
step1 : 候选影响因子集合找到
博文内容转发量，博文内容的评论量
step2 : 拟定一个计算公式
影响因子的权重：基准为1=一个转发即为1，
有评论的权重：0.6
加权求和：
step3 : sql编码、review与修正
step4 : 上线

基于上一题直接写sql
连表-统计打分-明细-汇总-排名

1)关联表
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid;
2)打分
select 
uid,
remark,
comment,
repostscount*1
commentscount*0.6
from
(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid
) join_tab

3)汇总

select uid,remark,comment,(repost_score+comment_score) sum
from
(
select 
uid,
remark,
comment,
repostscount*1 repost_score,
commentscount*0.6 comment_score
from
(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid
) join_tab
)sum_tab

4)排序
select uid,sum,remark,comment,dense_rank() over(partition by remark order by sum desc) as topn
from
(
select uid,remark,comment,(repost_score+comment_score) sum
from
(
select 
uid,
remark,
comment,
repostscount*1 repost_score,
commentscount*0.6 comment_score
from
(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid
) join_tab
)sum_tab
)ton_tab

5)排序后求top5
select uid,comment,remark,topn as top5
from
(
select uid,remark,comment,sum,dense_rank() over(partition by remark order by sum desc) as topn
from
(
select uid,remark,comment,(repost_score+comment_score) sum
from
(
select 
uid,
remark,
comment,
repostscount*1 repost_score,
commentscount*0.6 comment_score
from
(
select a.uid,remark,comment,repostscount,commentscount
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid
) join_tab
)sum_tab
)temp_topn
)top_tab
where topn<=5;

带时间去微博查看验证数据是否准确
SELECT uid
    ,comment
    ,remark
    ,TIME
    ,topn AS top5
FROM (
    SELECT uid
        ,remark
        ,comment
        ,TIME
        ,sum
        ,dense_rank() OVER (
            PARTITION BY remark ORDER BY sum DESC
            ) AS topn
    FROM (
        SELECT uid
            ,remark
            ,comment
            ,TIME
            ,(repost_score + comment_score) sum
        FROM (
            SELECT uid
                ,remark
                ,comment
                ,TIME
                ,repostscount * 1 repost_score
                ,commentscount * 0.6 comment_score
            FROM (
                SELECT a.uid
                    ,remark
                    ,comment
                    ,repostscount
                    ,commentscount
                    ,TIME
                FROM weibo_user_base_info a
                LEFT JOIN weibo_comment_info b ON a.uid = b.uid
                ) join_tab
            ) sum_tab
        ) temp_topn
    ) top_tab
WHERE topn <= 5;

8、(综合分析题)各频道的博主的行为特征分析之每天的活跃时间段分析，

即求各频道每天最活跃时间段top3，以小时为单位即可。
*先定义合理的计算口径，然后写对应的SQL语句
（以下为数据分析题的步骤列表）
step1 : 候选影响因子集合找到
参与人数，博文内容总数
step2 : 拟定一个计算公式
影响因子的权重：基准为1=一条微博内容即为1，
有一个人参与的权重：1.2
加权求和：
step3 : sql编码、review与修正
step4 : 上线

8.1) 先关联数据 

select a.uid,remark,comment
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid;

8.2) 打分
select
remark,
hour,
count(comment)*1 as comment_score,
count(distinct uid)*1.2 as user_score
from
(
select a.uid,remark,comment,hour(time) as hour
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
group by remark,hour;

8.3) 打分-汇总
select
remark,
hour,
(comment_score+user_score) as sum_score
from
(
select
remark,
hour,
count(comment)*1 as comment_score,
count(distinct uid)*1.2 as user_score
from
(
select a.uid,remark,comment,hour(time) as hour
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
group by remark,hour
) score_tab

8.4) 打分-汇总-排名
select 
remark,
hour,
sum_score,
dense_rank() over(partition by remark order by sum_score)
from
(
select
remark,
hour,
(comment_score+user_score) as sum_score
from
(
select
remark,
hour,
count(comment)*1 as comment_score,
count(distinct uid)*1.2 as user_score
from
(
select a.uid,remark,comment,hour(time) as hour
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
group by remark,hour
) score_tab
)top_tab

8.5) 打分-汇总-排名-top3

select
remark,
hour,
topn as top3
from
(
select 
remark,
hour,
sum_score,
dense_rank() over(partition by remark order by sum_score desc) as topn
from
(
select
remark,
hour,
(comment_score+user_score) as sum_score
from
(
select
remark,
hour,
count(comment)*1 as comment_score,
count(distinct uid)*1.2 as user_score
from
(
select a.uid,remark,comment,hour(time) as hour
from
weibo_user_base_info a left join weibo_comment_info b
on a.uid=b.uid) join_tab
group by remark,hour
) score_tab
)top_tab
)topn
where topn<=3;