新浪微博大数据分析

**1、组织数据 (需要处理每条数据开头和结尾的中括号)
（1）创建Hive表weibo_json(json string)，表只有一个字段，导入所有数据，并验证查询前5条数据 **

    create table weibo_json(json string);
    load data local inpath '/root/weibo.txt' into table weibo_json;
    select * from weibo_json limit 5;

（2）解析完weibo_json当中的json格式数据到拥有19个字段的weibo表中，写出必要的SQL语句

    create table weibo as 
    select json_tuple(json,
    'beCommentWeiboId'
    ,'beForwardWeiboId'
    ,'catchTime'
    ,'commentCount'
    ,'content'
    ,'createTime'
    ,'info1'
    ,'info2'
    ,'info3'
    ,'mlevel'
    ,'musicurl'
    ,'pic_list'
    ,'praiseCount'
    ,'reportCount'
    ,'source'
    ,'userId'
    ,'videourl'
    ,'weiboId'
    ,'weiboUrl'
    ) as 
    (beCommentWeiboId
    ,beForwardWeiboId
    ,catchTime
    ,commentCount
    ,content
    ,createTime
    ,info1
    ,info2
    ,info3
    ,mlevel
    ,musicurl
    ,pic_list
    ,praiseCount
    ,reportCount
    ,source
    ,userId
    ,videourl
    ,weiboId
    ,weiboUrl
    )
    from weibo_json;

2、统计微博总量 和 独立用户数

    select count(*) from weibo;
    +----------+--+
    |   _c0    |
    +----------+--+
    | 1451868  |
    +----------+--+
    select count(*) as Independent from
    (select count(*) from weibo group by userId) tmp;
    +--------------+--+
    | independent  |
    +--------------+--+
    | 78540        |
    +--------------+--+

3、统计用户所有微博被转发的次数之和，输出top5用户，并给出次数

    #select source from weibo limit 10;
    reportCount
    select userId,sum(reportCount) as total from weibo group by userId order by total desc limit 5;
    +-------------+--------------+--+
    |   userid    |    total     |
    +-------------+--------------+--+
    | 1793285524  | 7.6454805E7  |
    | 1629810574  | 7.3656898E7  |
    | 2803301701  | 6.8176008E7  |
    | 1266286555  | 5.5111054E7  |
    | 1191258123  | 5.4808042E7  |
    +-------------+--------------+--+

4、统计带图片的微博数

    select count(*) as total from weibo where pic_list='[]';
    +---------+--+
    |  total  |
    +---------+--+
    | 701356  |
    +---------+--+

5、统计使用iphone发微博的独立用户数

    select count(*) as total from
    (select count(*) as total from weibo where source='iPhone客户端' group by userId) tmp;
    +------+--+
    | total  |
    +------+--+
    | 921  |
    +------+--+

6、将用户所有微博的点赞人数和转发人数相加求和，并将相加之和降序排列，取前10条记录，输出userid和总次数

    select userId,(sum(praiseCount)+sum(reportCount)) as total from weibo group by userId order by total desc limit 10;
    
    +-------------+---------------+--+
    |   userid    |     total     |
    +-------------+---------------+--+
    | 1793285524  | 1.14941096E8  |
    | 1629810574  | 9.761207E7    |
    | 1266286555  | 8.3789422E7   |
    | 2803301701  | 7.4208822E7   |
    | 1195242865  | 6.9292231E7   |
    | 1191258123  | 6.1985742E7   |
    | 1197161814  | 5.9093308E7   |
    | 2656274875  | 5.2380775E7   |
    | 2202387347  | 5.1623117E7   |
    | 1195230310  | 4.8321083E7   |
    +-------------+---------------+--+

7、统计微博中评论次数小于1000的用户ID与数据来源信息，将其放入视图，然后统计视图中数据来源是”ipad客户端”的用户数目

    #创建表接收微博中评论次数小于1000的用户ID与数据来源信息
    create table small1000 as
    select userId,source from weibo where commentCount<1000 group by userId,source;
    #查询数据来源是”ipad客户端”的用户数目
    select count(*) as total from small1000 where source='iPad客户端';
    +--------+--+
    | total  |
    +--------+--+
    | 537    |
    +--------+--+

**8、统计微博内容中出现”iphone”次数最多的用户，
最终结果输出用户id和次数（注意：该次数是”iphone”的出现次数，
不是出现”iphone”的微博数目） **

    select userId,count(userid) from weibo where content like '%iphone%' group by userId;

9、求每天发微博次数最多的那个家伙的ID和发微博的条数

    #时间函数，原表存储的数据的时间是字符串的时间戳，所以需要将其转成长整型bigint
    from_unixtime(cast(createTime as bigint)
    #首先获取数据的userId,以及时间并转换为"yyyy-MM-dd"格式
    select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo;
    #然后按照时间，用户进行分组查询，获取每日用户发布微博数量，这里定义该查询结果为a
    select tmp.Release_time as Release_time,
    tmp.userId as userId,
    count(tmp.userId) as total
    from
    (select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
    group by tmp.userId,tmp.Release_time;
    #从a中按照获取的时间再进行分组查询，获取当日发布微博最大值，这里将查询结果定义为b
    select tmp1.Release_time as Release_time,max(tmp1.total) as big from
    (select tmp.Release_time as Release_time,
    tmp.userId as userId,
    count(tmp.userId) as total
    from
    (select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
    group by tmp.userId,tmp.Release_time) tmp1
    group by tmp1.Release_time;
    #最后b与a进行内连接，以获取导userId
    select a.Release_time as Release_time,b.userId as userId,a.big as total from
    (select tmp1.Release_time as Release_time,max(tmp1.total) as big from
    (select tmp.Release_time as Release_time,
    tmp.userId as userId,
    count(tmp.userId) as total
    from
    (select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
    group by tmp.userId,tmp.Release_time) tmp1
    group by tmp1.Release_time) a
    join 
    (select tmp.Release_time as Release_time,
    tmp.userId as userId,
    count(tmp.userId) as total
    from
    (select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
    group by tmp.userId,tmp.Release_time) b
    on a.Release_time=b.Release_time and a.big=b.total;