今天进行了项目,我们小组从开始的毫无头绪到后来一点一点做出了东西,挺有成就感的
--创建chat_data表
CREATE TABLE chat_data (
MessageTime string,--消息时间
SenderNickname VARCHAR(255),--发件人昵称
SenderAccount VARCHAR(255),--发件人账号
SenderGender VARCHAR(10),--发件人性别
SenderIP VARCHAR(50),--发件人IP
SenderIOS VARCHAR(255),--发件人系统
SenderPhoneModel VARCHAR(255),--发件人手机型号
SenderNetworkType VARCHAR(50),--发件人网络制式
SenderGPS string,--发件人GPS
RecipientNickname VARCHAR(255),--收件人昵称
RecipientIP VARCHAR(50),--收件人IP
RecipientAccount VARCHAR(255),--收件人账号
RecipientIOS VARCHAR(255),--收件人系统
RecipientPhoneModel VARCHAR(255),--收件人手机型号
RecipientNetworkType VARCHAR(50),--收件人网络制式
RecipientGPS string,--收件人GPS
RecipientGender VARCHAR(10),--收件人性别
MessageType VARCHAR(50),--消息类型
DistanceBetween string,--双方距离
MessageContent string--消息
);
load data inpath '/user/chat_data-10W.csv' into table chat_data;--导入表格
drop table chat_data;--删除
--1、创建今日消息总量表
create table if not exists total_msg
comment "今日消息总量"
as select day, count(*)
as total_msg_cnt
from chat
group by day;
drop table total_msg;
select * from total_msg;
--2、发送和接收用户数
create table if not exists table_usr
comment "今日发送消息人数、接受消息人数" as select day,
count(distinct SenderAccount) as sender_usr_cnt,
count(distinct RecipientAccount) as receiver_usr_cnt from chat group by day;
select * from table_usr;
--3、统计今日每小时消息量、发送和接收用户数
create table if not exists hour_msg
comment "每小时消息量趋势"
as select day, hour, count(*) as total_msg_cnt,
count(distinct SenderAccount) as sender_usr_cnt,
count(distinct RecipientAccount) as receiver_usr_cnt
from chat
group by day,hour;
select * from hour_msg;